Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
2  
Appropriate indexes? –  ring0 Sep 25 '10 at 1:04
    
@ring0 - Did you mean caches? Unless I'm mistaken, MySQL indexes are still stored on disk. –  EEAA Sep 25 '10 at 1:12
    
@ring0 - not that appropriate indexes are a bad thing, though. :) –  EEAA Sep 25 '10 at 1:28
1  
What I mean is that a wrong or missing index may have much more impact on your DB than having everything in memory... Besides, the key_buffer option is usually given a lot of RAM in my.cnf to have all indexes in memory. –  ring0 Sep 25 '10 at 1:43
    
Yes, most certainly agreed. –  EEAA Sep 25 '10 at 1:44

5 Answers 5

up vote 1 down vote accepted

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.

share|improve this answer
1  
The situations where the MEMORY engine would be a proper solution are exceedingly rare enough for this to be a bad answer, particularly given the information (lack) provided by the OP. –  hobodave Sep 25 '10 at 4:56
    
I agree about the rarity of usefulness of the MEMORY engine. The OP asked a very explicit question, though, and I gave a very explicit answer. If he knows enough to ask about storing his DB in RAM, he surely knows the risks of doing so as well. –  EEAA Sep 25 '10 at 4:59

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.

share|improve this answer

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.

share|improve this answer
  • 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.

share|improve this answer

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.

share|improve this answer
    
+1 MySQL is pretty good at caching query results in memory. Give it more memory and you'll see performance increase (unless you're just writing). –  Qwerky Sep 30 '10 at 13:03
    
I can confirm this from direct experience. I ran a large dataset on a machine with very little buffer size -- took forever. Moved it to a big server with a huge innodb_buffer_pool, the queries ran in just milliseconds. –  Will Glass Oct 11 '10 at 4:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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