December 21, 2004
On the 1st December, 2004, MySQL AB released a new version of MySQL Administrator (version 1.0.17). I had not previously used this tool, but with some time on my hands and the work crisis averted thanks to the installation of our new database server, I thought I would see how it shapes up. Since we use PHP as a scripting language, we have always used phpMyAdmin, but the web interface can be frustratingly slow. MySQL Administrator is installed on your client, and so it responds quickly. It can be installed on a Linux machine with a graphical desktop, or Windows (NT, 2000, XP, or 2003 only). This month I give a quick tour, see how you could use it, and highlight some of the places where you can find useful data to help optimize your databases.
Once installed, open MySQL Administrator and it will ask for a connection. Enter your login details (the server can be remote or local - connecting remotely disables some of the features, though many will not be running GTK on their lean and mean database servers, so local installation may not be an option). Your server connection can be saved under Tools/Save current connection. Once connected (and you can connect to multiple servers at the same time), navigation is simple - there is a sidebar with various main sections, and options within these. These are the sidebar options.
Most administrators will find the health feature particularly useful, so let's start with that one. At first you'll be presented with the connection health, which consists of three graphs: connections (a graphic of the percentage of total available connections used, as well as figures for the current, minimum, maximum and average number of connections), traffic (a graphic of kB being sent, as well as current, minimum, maximum and average kB sent) and number of SQL queries (again a graphic of the number of queries, and figures for the current, minimum, maximum and average number of queries).
So what does this tell us? I am going to use a live server as an example, with the data taken from off-peak hours. Let's look at a screenshot.
Firstly, the number of connections is not a problem. It may be off-peaks, but the maximum is still way below the limit. The database server has recently been upgraded, and the previous maximum connection limit has been maintained. Because the server processes the queries much more quickly now, the connections have not been building up nearly as much.
Secondly, the bytes sent are running at about one sixth of the maximum, but slightly above average, as one would expect from an off-peak server (remembering that excessive highs or lows skew averages).
Finally, the number of queries running is quite low, again corresponding to the time of day. The main probable call to action from this screen would be if the connection usage were approaching 100%, indicating one should up the max_connections in the config file.
This contains two sets of data, the query cache hit rate and the key efficiency (both as a percentage), Let's look at a screenshot. The query cache hit rate is averaging at 66%. Whether it can be improved or not depends on many factors unique to your server. Remember that each INSERT or UPDATE clears the query cache for that table, so the greater the UPDATE/SELECT ratio, the less effective your query cache will be. See the article on MySQL's Query Cache for more details on this feature.
The key efficiency looks quite healthy too. The horizontal bar measures the proportion of the key_buffer in use. If this approaches 100%, up the key_cache_size.
As you would expect, this section shows the status variables, which are invaluable in fine-tuning your database server. They are also ordered logically. You may find displaying the full list with mysqladmin or from within the MySQL client more convenient, but the display on MySQL Administrator is a less cluttered. I have not used it in a crisis situation yet so I do not have much of a preference (I think the option for displaying all the variables in one list should be there, and probably will be there soon) but the format used could be preferable to some. The category breakdown is as follows:
Let's look at some of the potentially useful data from here. Understanding how to optimize the server variables is beyond the scope of this article - for more details see optimizing the mysqld variables. The general/temporary tab can give a useful snapshot. Before the recent upgrade of the database server I am using as an example, the number of temporary tables on disk as opposed to in memory was high. There were points, as the data set grew, where we noticed a marked deterioration in performance, I suspect due to the average query suddenly not fitting in memory anymore. Have a look at this screenshot. There are almost as many disk temporary tables as there are in memory. The ratio is 1:100 on a similar, less powerful database server, a much healthier ratio. Therefore, this is a warning flag, and requires further investigation - ideally adding more memory tmp_table_size variable will improve this.
The Performance tab is particularly useful. You can get more info on the query cache from the performance/query cache tab, and one of the best ratios to check is the key_reads to key_read_requests ratio. 1:10 is terrible; 1:100 is the barest acceptable level. Looking at the key values on the sample database things look healthy. If the ratio on your setup is not acceptable, be sure to up the key_buffer_size if you can, or get more memory to allow you to do so.
Additional useful data can be found under the miscellaneous/handler tab. Here's another sample screenshot. A handy MySQL Administrator feature is that it provides a description of the status_variables. Therefore, if the name is not descriptive enough, MySQL Administrator tells more, as well as sometimes giving a clue about what to look out for. In the example, the Handler_read_rnd_next is highlighted as one that should not be high, telling us to investigate our indexes.
You can edit the variables from the interface (by double-clicking on them), but note that this does not edit the config file, and the new setting will only be effective until the server restarts.
I am quite impressed with MySQL Administrator. It has some advantages over the command line, and phpMyAdmin, and I will probably continue to use it at times. Since it is developed by MySQL, I expect to see further rapid progress, as they seem to be focusing on ways to boost MySQL outside of simply server code improvements.