2

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?

EEAA
  • 109,363
  • 18
  • 175
  • 245
Matthew
  • 1,859
  • 4
  • 22
  • 32

5 Answers5

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.

hobodave
  • 2,840
  • 2
  • 24
  • 34
  • +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 04:35
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.

EEAA
  • 109,363
  • 18
  • 175
  • 245
  • 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 04: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 04:59
1

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.

Liam
  • 499
  • 3
  • 5
0
  • 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.

Rod
  • 371
  • 4
  • 10
-1

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.

maristgeek
  • 314
  • 1
  • 2