Requirements Specification and Design

As a system administrator, you probably have been asked to improve the performance of a MySQL database server. This is a creative and challenging task, but at the same time, it can be quite daunting. The database software in itself is a complex piece of software, and you also must account for external factors such as the running environment—the number of CPU cores and the amount of memory. On top of that, the actual table layout and the SQL statement structure play very important roles.

You may have already developed your own strategy for how to approach this problem. The reason I mention "your own strategy" is that, unfortunately, there is no universal solution to tuning the MySQL database. Each installation is unique and requires an individual approach. Various solutions are available to help you identify the most common issues within the database, including commercial options such as MySQL Enterprise Monitor (http: //mysql. com/products/enterprise/ monitor. html) and open source tools such as MySQLTuner (http : //blog. mysqltuner. com/). The main purpose of such tools is to automate the tuning process by providing insight into the system configuration and behavior.

Assuming that SQL statement tuning is a job for the software developers, you, as a system administrator, are effectively juggling with two parameters: the database configuration and the operating environment configuration. The feedback is provided to you in the form of internal database counters, such as the number of slow queries or the number of connections.

To put all this into perspective, MySQL Community Server 5.1.46 has 291 status variables and 287 configuration variables. I'm not even considering listing the operating environment variables, because that would be nearly impossible. So, it is humanly impossible to correlate all the variables and make meaningful observations at the larger scale.

The available tools attempt to inspect the configuration and, based on the observed status variables, make some suggestions for how to improve the configuration. This works well for basic tuning, but as you dig deeper, you probably will find that you need to modify the tool so that it is tuned to your needs, rather than is based on some generic observations. This is where you need a tool that is extensible and easy to adjust.

Was this article helpful?

0 0

Post a comment