Tuning MySQL to take advantage of a 4GB VPS

Question

Hello,

We're running a large site at the moment which has a dedicated VPS for it's database server which is running MySQL and nothing else. At the moment all four CPU cores are running at close to 100% all of the time but the memory usage sticks at around 268MB out of an available 4096MB. I'm wondering what we can do to better utilise the memory and reduce the CPU load by tweaking MySQL's settings?

Here is what we currently have in my.cnf: http://pastie.org/private/hxeji9o8n3u9up9mvtinbq

Thanks

Answer 1

I would start by setting the key_buffer_size variable to a value of around 1GB - see documentation here

There is a lot more that can be tuned but that should provide a simple good starting point.

It's also worth checking the indexes on the tables as that can also have a big impact and without good indexes the key_buffer_size will not have a huge impact.

Answer 2

I would highly recommend using MySQLTuner. Its a perl script which looks at your databases configuration and provides readable stats (e.g. cache hit rate). It also makes some configuration recommendations based on the observed statistics. While I have found the recommendations to be fairly good, make sure to allow the database to run for some time so that realistic stats can be observed.