As with any web application, a great level of thought and planning goes into the optimization of the webapp to ensure fast response times. However, what often gets overlooked is the configuration of the LAMP stack itself. There is plenty of benefit to be gained from optimizing the configuration of Apache, PHP and MySQL.
Although all the different components provide ‘optimised’ configurations, they’re generalised and not written for an applications specific requirements. The aim of the ‘Optimising the LAMP Stack’ series is not to provide an example configuration, but to explain the more important configuration options and what impact those settings have on your web applications – giving a greater in site into how to configure it correctly.
Your MySQL configuration can generally be found in /etc/my.cnf – heres a list of what I see as the most important variables.
- query_cache_size:
- MySQL 4 onwards provides query caching, whereby if a database has to continually run the same queries on the same dataset it will cache the results. Although this level of caching should generally be taken care of by the application, this is a good alternative and saves the server repeating tasks.
- thread_cache:
- Thread creation/destruction can be expensive, and this happens on every new connection. Keep this in line with the amount of connections you expect your server to receive. If you start to see a spike in threads created, increase it. The goal is to not have threads created during normal operation.
- key_buffer_size:
- The key buffer is used with indexes. The larger you set the buffer, the quicker queries will complete and return a result. I recommend setting this somewhere between a quarter, but no more than half of the systems totally memory. In an ideal situation, this will be sufficient to contain all your table indexes.
- table_cache:
- Each time MySQL opens a table, it’s placed in the cache. The more tables you open, the higher cache you’ll require. Also bare in mind that MySQL is multi-threaded, therefore you could end up opening the same table multiple times in different processes and need to account for this.
The above list is the most important options for optimising a standard MySQL servers workload. Although there are plenty more, a lot can degrade performance when used incorrectly or when an applications requirements differ.
Should you wish to further optimize your configuration, I would suggest looking through the sample ‘optimized’ configuration provided with the MySQL server (Generally located in /usr/share/mysql). MySQLTuner is also pretty useful for configuration reccommendations when your database has been up and running for a few days.
Rob Greenwood, Technical Lead
This entry was posted on Friday, October 16th, 2009 at 7:30 pm and is filed under Helping Hints from our Techies. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.
