Maybe the title is confusing, but let me explain a little...

Our mysql servers (on xen virtual machine) sometimes generate huge tmp files on disk. So we need a big tmpdir to store them. And we decide to mount tmpdir to SSD for better performance. But as you know SSD is somehow expensive and mysql does not always generate that huge tmp files. So if we give every vm mysql a big SSD then it's a waste. So we want to only give small SSD (actually virtual disk backed on xen SSD) to each tmpdir ( e.g. 10G ).

But as said earlier sometimes mysql generate huge tmp files more than 10G. If that happens then mysql queries will be failed. So we want it dose like this:

combine an ssd and hdd into one drive (so it can be mounted to mysql tmpdir). when ssd is used up all space then use hdd.

Then I thought dmseupt or LVM maybe can do this. So I did a simple test:

1 SSD 0.5G | 1 HDD 10G | A logical volumn span two drive above | A ext4 file system on logical volumn above

And use dd tool to generate some file of size 0.3 G. Then I can see SSD usage grown 0.3G and HDD not changed. Everything is great until now! But when I delete that file, then create, then delete, ... do like so.

It turns out the hdd space usage is continually increasing!

So I guest ext4 dose not tend to overwrite the deleted file? Can we disable this feature or is there another file system prefer write low LBA first?

Thanks for reading!

  • The first thing to do is to fix the MySQL query that is causing a 10GB temporary table to be created. – Michael Hampton Oct 19 '12 at 14:52
  • @MichaelHampton Thanks for your reponse! Actuall I'm not a DBA and the DBA of my company tell me that's normal for our application to use 10GB temporary table. So... – fubupc Oct 28 '12 at 16:38
  • It may be normal, but it's probably still completely wrong. – Michael Hampton Oct 28 '12 at 16:58

You want to combine the low cost of spinning disks with the high performance of SSDs? Yes, it can be done: hybrid storage pools are a great idea. However, I don't think your plan to reinvent hybrid storage is a good idea.

Hybrid storage is implemented beautifully in a filesystem called ZFS. You can use ZFS for free, today:

  1. Try spinning up a separate virtual machine as a ZFS storage server running your choice of Nexenta, OpenIndiana, or FreeBSD.
  2. Create a ZFS storage pool with appropriately specified SSDs for ZIL (write cache) and L2ARC (read cache).
  3. Serve up the hybrid storage volumes that you need, either as iSCSI targets or however makes the most sense in your environment.
  4. Let ZFS automagically manage which blocks are stored in RAM or SSD as well as on spinning disks.
  • Hi Miles, thanks for replying! As I know, hybrid storage means: using SSD as cache and HDD as back, right? Actually we have deployed similar storage (e.g flashcache) for database. But it's not suitable for tmpdir. Because tmpdir is for tmporary files. these files will be removed quickly. But flashcache will move those data from SSD to HDD at some point and then delete them. That's a lot waste of disk IO I think. – fubupc Oct 19 '12 at 6:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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