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,

  • Server
  • Average
    time taken per query
  • Number
    of running queries and the number of open tables
  • Number
    of times a particular command was executed
  • Query
    performance, including slow queries and joins
  • Information
    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
MySQL processes

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.