So, every now and then we get customers asking if they can increase their memory because MySQL keeps on being killled by the kernel, mainly because on say a 2GB physical RAM server, MySQL eats it all up and even tries to use more than is there. So the kernel scheduler is like ‘no.. stop that’. This kind of thing could be avoided by configuring MySQL with proper limits as to not flood the physical hardware.

This is often and commonly overlooked in MySQL databases, and no tuning is done, but it’s important to base the MySQL configuration (/etc/my.conf) on the physical hardware of the server. So IF you increase the RAM on the server, to get the optimum speed you’d want to increase some of the RAM usage. A friend of mine said of a great trick used by many organisations of pointing the mysql filesystem to memory, this is a great performance increase, as it completely avoids the filesystem, the only downside is if the box turns off, the database is gone 😀

innodb_buffer_pool_size = 384M
key_buffer = 256M
query_cache_size = 1M
query_cache_limit = 128M
thread_cache_size = 8
max_connections = 400
innodb_lock_wait_timeout = 100

I found this config for a 2GB server on stack overflow, and it looks just about right. Adjusting the connections to suit should ensure that the box doesn’t get too overloaded, but also the memory is important too. One thing to bare in mind, by restricting RAM queries might not run as fast, but the database won’t suddenly go offline and process be killed this way. That’s what you want, really isn’t it;.