1

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.

Improve this question
3
  • 4
    You want a ramdisk to be the size it is plus the free space of the hard disk? You need a TARDIS ramdisk for that.
    – John Gardeniers
    Sep 27, 2011 at 9:09
  • 6
    @JohnGardeniers A search for TARDIS ramdisk got me nowhere. Do you have anything else to search for?
    – David
    Sep 27, 2011 at 13:44
  • 3
    @David Try dropping ramdisk from your search. And yes, the first wikipedia result is relevant.
    – MDMarra
    Sep 27, 2011 at 17:07

3 Answers 3

Reset to default
7

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.)

Improve this answer
3
  • Add to that Phantasia's self-replicating brooms and the universal NanoTech Grey Goo
    – Fiasco Labs
    Sep 27, 2011 at 20:25
  • will the brooms clean my apartment?
    – voretaq7
    Sep 28, 2011 at 4:37
  • 1
    +1 for the honesty about a fun time answering
    – Zlatko
    Dec 10, 2013 at 13:03
0

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

Improve this answer
1
  • You can probably do some black magic with volume management to create a RAID 0 and always fill "disk 0" first, but you'd need to map RAM as a raw disk device instead of a tmpfs partition (do-able), and you'd probably have Badness on reboot (physical disk has LVM metadata, newly-created empty RAM "disk" is blank or randomized) - seems kinda dirty & begging for trouble later on...
    – voretaq7
    Sep 27, 2011 at 17:28
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .