I am sure that you know Mysql quite well: this advanced, very used open source database is one of the key softwares behind the LAMP stack (Linux, Apache, MySQL, PHP). Sun (now Oracle) estimate that about 50% of all deployed databases are mysql installations.
Very often, these installations are kept at default settings – and for good reason: generally, default parameters are configured for safety and, if you don't really know what you are doing, you can severely impair your server reliability and/or performance. Moreover, many mysql installations deal with basic, simple tasks, where performance is not a crucial factor.
However, there are cases where you really need to speed up your mysql deployment: for example, think to a frequently visited web site with mysql backend, or an application that executes a lot of queries/transactions. In this case, what you can do to extract more speed from your beloved mysql server? This article will shed some light on this topic.
However, let me first warning you about three things:
- I will cover basic server parameters here. Do not think that a simple, small article can cover all aspects of mysql performance tuning (in fact, I myself have a lot to learn...);
- this article will focus itself on InnoDB storage engine. Mysql enable you to choose between different storage engines, with different options and performance considerations;
- before resorting to heavy, complex server-side tuning, consider how to optimize your SQL code. I can not stress this point enough. If your application performs poorly and you have already optimized basic server variables, you really need to review your SQL code to find any performance bottleneck.
With these warnings in mind, we can begin to describe some basic server-side options and their behavior...