My last post about server optimization dates February because the last weeks/months have been quite busy. I promised to continue with MySQL optimisation which I will be doing now. As with the other posts, I will only write down significant new information and otherwise link to the information in the web so that you can use this guide as a condensed view on the topic.
First of all visit documentation at mysql.com which contains a whole chapter about optimization. If you only want to
optimise the MySQL server
this subchapter will
suit your needs.
The MySQL Query Cache
The query cache, although not a panacea, can bring big performance improvements and is, at least with mysql 5.0, disabled by default. However you have to know that it only optimises queries. It cannot
“look” into your application and group all queries of the same request or similar.
The MySQL Performance Blog has a nice tutorial about configuration and background of the query cache.
Adapting MySQL Cache Sizes
MySQL has several caches which need to be adapted to your personal needs. If you are using PHPMyAdmin, it can give you hints about the cache sizes which need to be optimised in your current setup. Just open server_status.php which is linked on the start page as Show
MySQL runtime information. To change values edit your /etc/mysql/my.cnf and restart MySQL afterwards.
If you want to know how to view this information directly via SQL command, look at this optimisation guide underneath the subheading Getting information about current values or into the MySQL documentation. Both guides also lists other possible improvements.
Identifying slow queries
Optimising your web application or developing it with database performance in mind, normally should be the first step to do. Though it may often be the case that you cannot access the code of your application, have not the required skills, not enough time or that you do not want to change the standard application for easier updates. Problems in this area can often be avoided or at least minimized by using an object relational mapper like hibernate for Java as it optimises the queries on object level before executing them.
If you cannot use an ORM mapper for some reasons or if you want to know which queries use most resources you can activate the MySQL Slow Query Log. Pete Freitag directly states which entries are needed in the my.cnf file.
Optimising your application
After you identified slow queries or even before generating them, this blog post for PHP experts can help you finding database related performance hits in PHP with PHP.
As a general measure I strongly recommend using indexes for frequently used or searched attributes. Although from 2001 the following guide explains these topics and their background greatly.
Optimising the compilation
If you compile mysql by yourself, you can also get speed improvements by compiling MySQL with special
options. The MySQL
documentation lists several possibilities.
Optimising your Linux kernel for MySQL
Apart from MySQL
itself you can also optimise your kernel parameters (sysctl.conf) for
MySQL. If you want to, refer to this guide.
Other resources
If this still is not enough you will find plenty of other resources in this forum post. Some of the links listed there were already mentioned before.
Finally…
Before investing in new hardware be sure to check the configuration of your database and, when you can, the database queries in your application. Even if the latter is not possible, simple server side tuning can bring huge improvements especially when the query cache is not activated or it or other caches are too small so that MySQL hast to write more data to disk than necessary.
I hope this small guide/list of links gave you an overview over MySQL performance tuning. For me it will serve as an aid to memory and thus it will be expanded if future issues arise.
Regards,
Phillip