Mysql – Difference between key_buffers and recommendation

MySQLubuntu-10.04

I'm looking to add a bit of memory to MySQL on a Linode VPS server on which I've got a small facebook (canvas app) PHP app using MySQL running. I'm not super familiar with MySQL optimization so I'm hoping to find a simple answer. I think I want to increase the key_buffer size (the default is 16M) to something like 32M to start, but I'm not sure if I need to tweak anything else as well. All I've done so far is increase the query_cache_size to 32M from 16M.

There's also key_buffer under [mysqld] and key_buffer under [isamchk]. What are the difference between those two?

If I have Linode 2048MB (http://www.linode.com) VPS, what would recommend I set the buffers to? I don't expect this site to have tons of visitors, but I'd like it to be as optimized as possible. Definitely way more heavy on the database access than PHP and very few HTTP requests.

Best Answer

You should learn about related variables from SHOW GLOBAL STATUS output. They will tell you if any of caches needs to be increased. Increase them as log as it is effective (but don't set query_cache_size bigger than 128M, there is bug related to big query caches)

[mysqld] section is for mysqld process (i.e. for the server), [isamchk] for myisam maintanence utility. (So you need [mysqld] to change server parameters)

Related Topic