MySQL has a lot of secrets…and one of its better-kept ones
is the ability to generate statistics on server usage and performance in
real-time. This capability allows Web developers, SQL administrators, and
others charged with the task of optimizing RDBMS performance to obtain a
detailed view of how the server is handling requests and to analyze problems
and bottlenecks for corrections. If you’re concerned about how your server is
performing, or if you’re just curious about what’s happening behind the scenes,
this information is bound to come in useful to you in your role as MySQL administrator.
Among the information available to you, the administrator,
time taken per query
of running queries and the number of open tables
of times a particular command was executed
performance, including slow queries and joins
on open connections and what each client is doing
Each of the pages linked below gives you more detail on how
to obtain and understand the performance statistics that MySQL keeps.
How to use the SHOW STATUS command in MySQL
This is the place to start–it gives you a wealth of
statistics on how the MySQL database is performing. Learn the basics on how to use the SHOW STATUS
Understanding the performance statistics from SHOW STATUS
The SHOW STATUS command spits out a somewhat bewildering
array of statistics. Here’s a quick guide to some of the more interesting
MySQL performance statistics.
How to examine and kill MySQL client processes
Another very useful command when it comes to keeping track
of client-server transactions is the SHOW PROCESSLIST command. Learn how to use
this command to obtain process information and how to kill any errant
Specialized status commands in MySQL
A number of other useful commands also exist to obtain
other, more specialized types of status information. Here’s a handy little crib
sheet to some of the specialized status commands in MySQL.