MySQL Administrator

Overview

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
.

Features

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.

  • Server
    Information: High-level overview with information about the user, host, port,
    MySQL client and server versions, and general client and server information.

  • Service
    Control: Only available when you are connecting to a database server on the
    local host, this allows you to start and stop the MySQL server, and configure
    the MySQL service (on Windows).

  • Startup
    Variables: Also only available when connected to localhost, this allows you to
    set startup variables for the MySQL server.

  • User
    Administration: An easier way to administer privileges, you can fully control
    user access to the MySQL server in this section.

  • Server
    Connections: Useful when the server is taking strain, here you can list the
    threads, as well as see how many instances of each user is connected to the
    server. You can also kill individual threads or all connections from a user.

  • Health:
    In-depth data about your connections, memory usage, status variables and system
    variables.

  • Server Logs:
    If connected to localhost, you can view entries from the error, slow and
    general logs

  • Replication
    Status: View information about replicated slaves and masters.

  • Backup:
    Backup, or schedule backups

  • Restore:
    Restore backed up databases.

  • Catalogs:
    Information about databases, as well as the ability to create and edit tables
    and perform routine maintenance, such as optimizing and checking.

Server Health

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.

Memory Health

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.

Status Variables

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:

  • General: High
    level overview of open files, tables, uptimes and queries, as well as detail on
    threads and temporary tables.

  • Performance: slow_queries
    and slow_launch threads, as well as details on the query cache, indexes,
    sort types, delayed writes, selects, locks and InnoDB data.

  • Networking:
    Aborted clients and connects, total and max connections as well as byte traffic
    and replication data.

  • Commands
    executed: Total number of various types of queries run on the server, as well
    as ordered into Data Definition Language (DDL) and Data Modification Language
    (DML) queries, show queries and replication queries.

  • Miscellaneous:
    Handler data, such as the number of COMMIT or ROLLBACK statements, and read
    types.

  • New variables:
    Variables added in MySQL 4.1, such as binlog_cache_disk_use and handler_discover

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
.

System Variables

  • Whereas the
    status variables change constantly, helping you pinpoint problems, the system
    variables are determined from the configuration file at startup, loaded into
    memory, and remain constant until explicitly changed. You will usually
    want to look at them after an examination of the status variables has
    highlighted a problem.General:
    High-level information such as server version, port etc, details on which features
    are running (for example BDB tables and the query cache), which directories are
    used for files such as the error log and data files, logging variables,
    performance variables, and other miscellaneous variables such as the default
    storage engine.

  • Connections:
    Connection defaults (such as max_connections and wait_timeout.),
    networking variables (such as connect_timeout and max_allowed_packet),
    and security variables (currently as skip_networking and skip_show_database

  • SQL:
    High-level query settings (such as tx_isolation and sql_mode, and
    Character sets, date and time formats, and full text settings

  • Memory:
    Variables such as tmp_table_size and thread_stack, and various
    buffer and cache variables.

  • Table Types:
    The default table type, as well as InnoDB, BDB and MyISAM-specific variables.

  • New Variables:
    Others specific to MySQL 4.1, such as have_csv and myisam_data_pointer_size

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.

Conclusion

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.

More resources

»


See All Articles by Columnist
Ian Gilfillan

Ian Gilfillan
Ian Gilfillan
Ian Gilfillan lives in Cape Town, South Africa. He is the author of the book 'Mastering MySQL 4', published by Sybex, and has been working with MySQL since 1997. These days he develops mainly in PHP and MySQL, although confesses to starting out with BASIC and COBOL, way back when, and still has a soft spot for Perl. He developed South Africa's first online grocery store, and has developed and taught internet development and other technical courses for various institutions. He has majors in Programming and Information Systems, as well as English and Philosophy. For 5 years he was Lead Developer and IT Manager for Independent Online, South Africa's premier news portal. However, he has now 'retired' from fulltime work, and is hoping that his next book will be more in the style of William Blake and Allen Ginsberg.

Latest Articles