# Using Mysql and a Ram Disk



## aggressivenetworks (Dec 29, 2014)

I was trying to get a understanding of using a ram disk and mysql versus ssd storage. Maybe my old brain just does not get it when it comes to Mysql. But wouldnt storing your database on a ram disk reduce your query and write times? Also mysql will only cache a certain amount of database in memory before it eventually has to hit the disk to either read or write?


----------



## perennate (Dec 29, 2014)

If you want to store your tables in memory, you should use the MEMORY engine instead of RAM disk. The InnoDB and MyISAM engines are specifically designed to have some guarantees about data integrity (with InnoDB offering stronger guarantees; they will not mark query as completed until data is flushed to disk or at least processed in some way), while the MEMORY engine stores all data in memory. RAM disk doesn't make much sense since MySQL probably will cache in memory and then RAM disk is already in memory, b-trees also not so useful then.

Edit: although depending on workload, MEMORY might not be too great either. Really for in-memory cache you should use software like redis or memcached that is designed for that purpose, not a database.


----------



## drmike (Dec 29, 2014)

aggressivenetworks said:


> I was trying to get a understanding of using a ram disk and mysql versus ssd storage. Maybe my old brain just does not get it when it comes to Mysql. But wouldnt storing your database on a ram disk reduce your query and write times? Also mysql will only cache a certain amount of database in memory before it eventually has to hit the disk to either read or write?


My brain is probably older and more broken than your's 

This topic has come up quite a bit in the years that MySQL has existed.  Then again, when I started computing, relational open source databases did not exist.  Computer had puny RAM and floppy drives were exotic non-consumer products.  I've long done RAM-based "databases".

If MySQL isn't performing well enough for you [likely why this conversation happens about RAM-based] you need to get comfy with MySQL configuration options and tuning.  After that, start looking at derivatives like Percona and MariaDB which many have moved onto for different enhancements including performance.

The standard response here in the MySQL world is basically that where you have ample RAM MySQL itself is going to (when configured) use RAM to cache / pre-cache tables (notably the indexes).  So in effect your database is per se RAM based.

If your writes are a problem, there is some functionality to schedule/delay writes so less chatter and more grouped in talking to drives.  In case of power loss, stand to lose data moreso though.

Big picture, databases tend to suffer from bad design / no DBA and MySQL is just horrible in my opinion for large table spaces (think 10's of millions of rows and above).  This is because the indexes are huge, take forever to create, chew up too many resources to recreate, etc.  So, ideal to have good relational schema a DBA would approve and where there growth tablespaces, need a routine to move the data to other space for self-archiving.

The MEMORY storage in MySQL everyone runs into at some point because it indeed is RAM based option. I forget the shortcomings of it, there are many.  Let me just say, based on my propensity, impatience, etc.  I never found MEMORY table space useful for much of anything.  It's half baked at best and don't think development has continued on it to make it better.

As @perennate mentioned memory storage = redis or memcached.  These tend to be key storage solutions.  Historically called NOSQL storage to poke at SQL in general I guess. Very abstract if you are a relational database familiar person.  Bound to be better solutions that overlay relationship database / SQL queries over top of key storage and do feeding of data to disk - because memcached in particular is RAM based and no cooked in option to dump to disk to sustain reboots and all (which means post reboot systems get smashed with load to run everything fresh - versus hot-pre-loading prior recent values).


----------



## Kris (Dec 29, 2014)

Seems like you're looking for something like this : http://wiki.centos.org/TipsAndTricks/TmpOnTmpfs

Instead of /tmp like in the setup, use /var/ramdisk, etc. 

tmpfs will still used cached memory, and after creating it and mounting, you only need to set a flag in my.cnf to use the tmpdir flag and map the directory you've created, such as


[mysqld]
tmpdir=/var/ramdisk
Don't forget to restrict chmod and user permissions to the MySQL user.

Not the best way to do things, but this will get you a proper RAM-disk like setup.

Another tutorial: http://www.fromdual.com/mysql-tmpdir-on-ram-disk

While you're in there, check out MySQLTuner for other suggestions.


----------



## aggressivenetworks (Dec 29, 2014)

Thanks everyone! I really have a better understanding now.


----------

