3

I was wondering if, and how much, using tmpfs could improve MySQL performance and how it should be done ? My guess would be to do mount -t tmpfs -o size=256M /path/to/mysql/data/DatabaseName, and to use the database normally but maybe I'm wrong (I'm using MyISAM tables only).

Will a hourly rsync between the tmpfs /path/to/mysql/data/DatabaseName and /path/to/mysql/data/DatabaseName_backup penalize performances ? If so, how should I make the backup of the tmpfs database ?

So, is it a good way to do things, is there a better way or am I losing my time ?

Serty Oan
  • 755
  • 1
  • 7
  • 15

1 Answers1

3

I answered a similar question here when someone was asking how to load a MySQL database to memory. There's a native ENGINE in MySQL among other potential solutions, which are better for most situations.

For MyISAM tables, your proposed solution would be functional.

The rsync shouldn't necessarily penalize performance but it could if your system was really low on resources, as high I/O utilization would impact system performance as a whole. In most cases, I'd say it wouldn't but only way to be sure is to test.

The ramdisk solution seems risky to me. In case of power loss, data could be lost, as your rsync would not be realtime. You could replication the database to a slave, which could store on a disk. The startup on MySQL on the ramdisk server could scp from that host into the ramdisk before starting the instance.

If your MySQL instance was not well optimized, you would notice substantial performance gains. If well optimized, I'd suspect that the gains would be less. The only way to be sure is to use tools like Super Smack, MySQL Benchmark, and sysbench to quantity the performance.

Good luck.

Community
  • 1
Warner
  • 23,756
  • 2
  • 59
  • 69
  • This database can have about 30min of data loss without being critical. And indded at power on I will add a script to mount the tmpfs and copy the data of the last backup. Thanks for the tips. – Serty Oan Jun 16 '10 at 19:04
  • +1 for not using filebased backup but replication – Wrikken Jun 16 '10 at 19:34
  • Won't replication penalize the performances more than a punctual rsync ? Performances are my main concern here, that's why I ask – Serty Oan Jun 17 '10 at 07:21
  • In most cases, it will use less system I/O, which will be better for performance. If you are working with a small data set, the difference will be insignificant. – Warner Jun 17 '10 at 13:25