Home / server / Questions / 315853
Accepted
David
Asked: 2011-09-28 00:29:26 +0800 CST

How to avoid out-of-disk-space on ramdisk?

  • 772

I am considering placing the tempdir of my MySQL database onto a ramdisk under Linux. The reason for this is to work around the 4 gb maximum of in-memory temporary tables in MySQL. My only worry is how to avoid out-of-disk-space errors if the MyISAM-based temporary table on the ramdisk would become bigger than the ramdisk? Could one some-how create a spill-over mechanism where the ramdisk size is the size of the ramdisk plus the free space on the harddisk. If the ramdisk would become full, the rest would be written to the harddisk.

linux mysql ramdisk

3 Answers

  1. Best Answer

    Think about this logically and ignore the fact that it's a RAM-backed virtual disk: You are asking for a disk device that gets bigger because you tried to store more stuff on it.

    You are asking for a blessed +30 bag of holding.
    You are asking for a Dimensional Hole.
    You are asking for a TARDIS ("It's bigger on the inside").

    You are asking for something that doesn't exist. At least not as far as I've ever seen...

    Using tmpfs/RAM-backed storage for temporary tables is a very elegant way to get around the 4GB limit. Be aware though that the RAM you lock up won't be available for MySQL to do stuff like SORTing, JOINing, etc. so you're going to need GOBS of RAM to make this practical - you need the tmpfs to be big enough to hold as many temp tables (of presumably substantial size) as you'll need at any one time, and you need to still have enough RAM left over that you won't wind up swapping when you run big queries (otherwise your performance will go down the tubes anyway because SOMETHING is gonna have to be swapped in and out!).

    A more practical solution may be to cleverly craft explicit (and efficient) JOINs that eliminate the need for temporary tables (if possible), and saving them as views. Without knowing more about your environment I can't give you specific guidance, but you can always ask on the DBA site with some basic details about what you're doing and see if they have suggestions...

    (I'm sorry to be the bearer of bad news, but if it's worth anything I did have fun typing this answer.)

    • 7

  2. In theory I guess you could create a JBOD filesystem using physical volumes from the ramdisk and the hard disk - but I'm not sure if it would automatically fill up the ram disk first. This doc might shine a bit more light on the topic

    • 0

  3. Your best bet is just purchase a 64GB SSD and put your tempdir on it and nothing else. They are cheap ones about $1/GB (on sale) so for ~$64 and $2 a GB not on sale, you could have a super fast tempdir that you don't have to worry about being to small.

    • 0