Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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 ?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
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 7: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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.