mysql - is there a good way of storing it in ram

Question

I'm trying to think of ways to make mysql faster. Is there a proven way of storing it in ram to allow it to work faster?

Answer 1

How about the MEMORY Storage Engine?

Another option would be to create a ramdisk and use MyISAM or INNODB tables within that. I would imagine, though, that performance would be better using the MEMORY Storage Engine, due to the fact that it was designed from the ground up to be stored in memory, whereas the other table types operate under the assumption that they're being stored on disk.

Answer 2

First, do yourself a favor and take the MEMORY engine suggestions with a large grain of salt.

Unless, of course, you don't need any of the following:

• MVCC
• Transactions
• Multi-threaded access
• Clustered indices
• Foreign key support
• More than an occasional INSERT/UPDATE
• Your data

If you don't need any of those, then you shouldn't be using MySQL. Use a simple key-value store like memcached.

If you need any of those (hint: you do), then use InnoDB, as much RAM as you can afford, and a properly configured innodb_buffer_pool_size. The MySQL Performance Blog has an excellent article on configuring innodb_buffer_pool_size. Short and sweet - you'll want enough RAM to fit the entire DB tablespace and indices into RAM, and set innodb_buffer_pool_size to 70-80% of total system RAM for a dedicated MySQL server.

Answer 3

RAM SAN very very expensive but mind blowing IO. RAMdisk is a very very bad idea because in the event of a loss of system power or a fatal system error the RAMdisk is gone as it is in RAM.

Answer 4

• Try memcached to cache heavy queries.
• Try native mysql query cache function wich comes natively disabled and makes huge difference on static/semi-static tables queries.
• Try to NOT sort huge queries at mysql, throw the raw data into code and sort it there then cache.
• Try to raise mysql memory limit.

You might want to put some monitor (like munin) on your server to make sure that mysql is the real problem, usually it's very disk expencive when you're dealing with non cached queries. If everything fails you will need to expend some coin getting a SSD storage solution.

Answer 5

Play with the MEMORY storage engine as ErikA suggested. But make sure you are also optimizing all of your query cache, thread cache and buffer settings. mysqltuner.pl is a great starting point.