A.1 SHOW STATUS
The SHOW
STATUS command allows you to view a snapshot of
the many (over 120) internal status counters that MySQL maintains.
These counters track particular events in MySQL. For example, every
time you issue a SELECT query, MySQL increments
the Com_select counter.
This command is valuable because early signs of performance problems
often appear first in the SHOW
STATUS output—but you have to be looking for
them. By learning which counters are most important to server
performance and how to interpret them, you'll be
well prepared to head off problems before they become an issue for
your users.
This appendix is designed to do just that. Here
you'll find a brief summary of the more important
counters MySQL provides, as well as some discussion of what to watch
out for and how you might correct some of the problems highlighted
here. We've attempted to group related items
together rather than simply using an alphabetical list. And
we've omitted the counters that have little
relevance to MySQL performance. See the MySQL Reference
Manual for a full list of the counters available in your
version of MySQL.
Running the SHOW STATUS command repeatedly and
examining the results is a very tedious process. To make life a bit
easier, mytop automates much of the process. See
Appendix B for more about
mytop.
|
Note that these counters are stored as unsigned integers. On a 32-bit
platform such as Intel x86, that means the counters will wrap just
over the 4.2 billion mark. This can lead to very confusing numbers
and wildly incorrect conclusions. So be sure to check how long your
server has been online (Uptime) before jumping to
conclusions. The odds of a counter wrapping increase as time goes on.
As you read the descriptions in this appendix, consider how you might
add some of these counters to your monitoring infrastructure.
Third-party MySQL modules already exist for most of the freely
available rrdtool-based systems (Cricket, Cacti,
etc.). If none are available for your system, consider using one of
the free plug-ins as a starting point for building your own.
They're not very complicated.
|
|
A.1.1 Thread and Connection Statistics
Just because connections to MySQL are
very lightweight doesn't excuse applications that
poorly use their connections. A rapid-fire connect/disconnect cycle
will slow down a MySQL server. It may not be noticeable under most
circumstances, but when things get busy you don't
want it getting in the way.
Using information in the following counters, you can get a high-level
picture of what's going on with
MySQL's connections and the threads that service
them.
- Aborted_clients
-
This is the number of connections to the server that were aborted
because the client disconnected without properly closing the session.
This might happen if the client program dies abruptly from a runtime
error or is killed.
- Aborted_connects
-
This counter contains the number of connection attempts that failed.
These failures may be because of user privilege issues, such as an
incorrect password, or communications issues such as malformed
connection packets or connect_timeout being
exceeded—often as the result of a network or firewall problem.
- Bytes_received
-
Number of bytes received from all clients, including other MySQL
servers involved in replication.
- Bytes_sent
-
Number of bytes sent to all clients, including other MySQL servers.
- Connections
-
Total number of connection attempts, both successful and failed, to
the MySQL server.
- Max_used_connections
-
The peak number of simultaneous connections.
- Slow_launch_threads
-
Number of threads that have taken longer than
slow_launch_time to be created. A nonzero value
here is a often a sign of excessive CPU load on the server.
- Threads_cached
-
Number of threads in the thread cache. See Chapter 6 for more about MySQL's
thread cache.
- Threads_connected
-
Number of currently open connections.
- Threads_created
-
Total number of threads that have been created to handle connections.
- Threads_running
-
Number of threads that are doing work (not sleeping).
- Uptime
-
How long (in seconds) the MySQL server has been up and running.
A.1.2 Command Counters
A
large percentage of MySQL's counters are devoted to
counting the various commands or queries that you issue to a MySQL
server. Everything from a SELECT to a
RESET MASTER is counted.
- Com_*
-
The number of times each * command has
been executed. Most names map directly to SQL queries or related
commands. Some are derived from function names in the MySQL C API.
For example, Com_select counts
SELECT queries, while
Com_change_db is incremented any time you issue a
USE command to switch databases.
Com_change_db can also count the number of times
you change databases programmatically using the
mysql_change_db( ) function from the C API or a
language such as PHP.
- Questions
-
The total of number of queries and commands sent to the server. It
should be the same as summing all the Com_*
values.
A.1.3 Temporary Files and Tables
During normal operations, MySQL may
need to create temporary tables and files from time to time.
It's completely normal. If this happens excessively,
however, performance may degrade as a result of the additional disk
I/O required.
- Created_tmp_disk_tables
-
The number of temporary tables created while executing statements
that were stored on disk. The decision to put a temporary table on
disk rather than in memory is controlled by the
tmp_table_size variable. Tables larger than the
value of this variable will be created on disk, while those smaller
will be created in memory. But temporary tables created explicitly
with CREATE TEMPORARY TABLE
aren't governed by this. They always reside on disk.
- Created_tmp_tables
-
Similar to Created_tmp_disk_tables except that it
counts the number of implicit temporary tables created in memory and
on disk.
- Created_tmp_files
-
How many temporary files mysqld has created.
Comparing Created_tmp_tables to
Created_tmp_disk_tables will tell you the
percentage of your temporary tables that are being constructed on the
much slower disk as opposed to being created in much faster memory.
Obviously, you will never be able to completely eliminate the use of
on-disk temporary tables, but if too many of your tables are being
created on disk, you may want to increase your
tmp_table_size.
A.1.4 Data Access Patterns
The handler counters track the various
ways that rows are read from tables at the lower level. MySQL
communicates with each storage engine through a common API. Because
storage engines used to be known as table handlers, the counters
still refer to handler operations.
Studying these values will tell you how often MySQL can fetch the
exact records it needs as opposed to fetching lots of records and
checking field values to see if it really wanted the records.
Generally, the counters help to highlight when MySQL is or
isn't effectively using your indexes. For example,
if the Handler_read_first is too high, the server
is doing a lot of full index scans, which is probably not what you
want it to do.
On the other hand, if the Handler_read_key value
is high, MySQL is using the indexes to optimum effect and going right
after the row it needs quite often without having to dig around and
look for it, and your queries and tables are using indexes to optimum
effect.
- Handler_commit
-
Number of internal COMMIT commands.
- Handler_delete
-
Number of times MySQL has deleted a row from a table.
- Handler_read_first
-
Number of times the first entry was read from an index.
- Handler_read_key
-
Number of times a row was requested based on a key. The higher this
value is, the better. It means that MySQL is effectively using your
indexes.
- Handler_read_next
-
Number of requests to read next row using the key order. This is
incremented if you are querying an index column with a range
constraint or doing an index scan.
- Handler_read_prev
-
Number of requests to read previous row in key order. This is mainly
used when you have a query using ORDER BY ...
DESC.
- Handler_read_rnd
-
Number of requests to read a row based on a fixed position. If you do
a lot of queries that require sorting of the result, this figure will
likely be quite high.
- Handler_read_rnd_next
-
How many times MySQL has read the next row in a datafile. This figure
will be high if you are doing a lot of table scans. If that is the
case, it's likely that either your tables need to be
indexed, or the queries you are submitting need to be changed to take
better advantage of the indexes that do exist.
- Handler_rollback
-
Number of internal ROLLBACK commands.
- Handler_update
-
Number of requests to update a table row.
- Handler_write
-
Number of table rows that have been inserted.
A.1.5 MyISAM Key Buffer
As described in Chapter 4, the key buffer is where MySQL caches index
blocks for MyISAM tables. Generally speaking, a large key buffer
means hitting a disk less frequently, so queries will run more
efficiently. Increasing the size of the key buffer is often the
single biggest "bang for your buck"
adjustment you can make on a server that uses mostly MyISAM tables.
- Key_blocks_used
-
The number of 1024-byte blocks contained in the key cache.
- Key_read_requests
-
The number of times a block is requested to be read. It might be
found in cache, or it might be read from disk (in which case
Key_reads are also incremented).
- Key_reads
-
The number of physical reads during which a key block was read from
disk.
- Key_write_requests
-
The number of requests for a key block to be written.
- Key_writes
-
The number of physical writes during which key blocks were written to
the disk.
These last four counters tell you how often MySQL needed to
read/write a key block. Each time a
"request" occurs, there may or may
not be an actual read or write to match it. If
there's not, that's good, because
it means the data was already in memory, and the request never hit
the disk.
As a general rule of thumb, you want the request numbers to be
roughly 50-100 times higher than the corresponding read or write
numbers. Higher is better! If they're smaller than
that, increasing the size of the key buffer is likely in order.
A.1.6 File Descriptors
On
a MySQL server that handles hundreds or thousands of simultaneous
queries, you need to keep an eye on the number of open file
descriptors MySQL is using. The table_cache
setting has the largest impact on MySQL's file
descriptor usage if you're mainly using MyISAM
tables. For MyISAM tables, the numbers work out like this: each
.frm file is opened once when the table is first
accessed. The contents are cached, and it is immediately closed. The
index file (.MYI) is opened once and is shared
among all clients accessing it. The data file
(.MYD) is opened by each client using the table.
The table cache may reduce the number of times that the
.frm file is reopened on a system with many
active tables.
The following counters help keep track of MySQL's
file descriptor usage:
- Open_tables
-
The total number of tables that are currently open.
- Open_files
-
The total number of open files.
- Open_streams
-
Number of streams that are open. (These are mostly used for logging.)
- Opened_tables
-
Number of tables that have been opened since the server started. If
Opened_tables is significantly higher than
Open_tables, you should increase the size of
table_cache.
A.1.7 Query Cache
As described in Chapter 5, the query cache can provide an impressive
performance boost to applications that issue identical queries in a
repetitive manner. The following counters will help you understand
how effective the query cache is and whether you can safely increase
or decrease its size.
- Qcache_queries_in_cache
-
How many query results are in the query cache.
- Qcache_inserts
-
How many times MySQL has inserted the results of a query into the
cache.
- Qcache_hits
-
The number of times MySQL has found a query in the cache instead of
having to actually execute the query.
- Qcache_lowmem_prunes
-
Each time MySQL needs to prune the query cache (remove some entries)
because it has run out of memory, it increments this counter. Ideally
this counter should be 0. If the number increases with any
regularity, consider increasing the
query_cache_size.
- Qcache_not_cached
-
This is the number of queries that aren't cachable,
either because the query explicitly opted out of the cache, or the
result was larger than query_cache_limit.
- Qcache_free_memory
-
Free space (in bytes) remaining in the cache.
- Qcache_free_blocks
-
How many free (unused) blocks exist in the cache.
- Qcache_total_blocks
-
This is the total number of blocks in the cache. By subtracting
Qcache_free_blocks from this value, you can derive
the number of nonempty blocks. Because the query cache blocks are
allocated on an as-needed basis, this information
isn't terribly useful for anything other than
impressing your coworkers.
A.1.8 SELECTs
This group of counters tracks
SELECT queries that may be problematic. Typically
they're queries that might have been run more
efficiently if MySQL had been able to find an appropriate index to
use. If any of these are nonzero and growing at even a moderate rate,
go back to Chapter 4 to refresh your memory on
how MySQL's indexes work—you probably need to
add at least one.
- Select_full_join
-
Number of joins without keys. If this figure isn't
0, you should check your indexes carefully.
- Select_full_range_join
-
Number of joins that used a range search on reference table.
- Select_range
-
Number of joins that used ranges on the first table.
It's normally not critical even if this number is
big.
- Select_scan
-
Number of joins that did a full scan of the first table.
- Select_range_check
-
Number of joins that check for key usage after each row. If this
isn't 0, you should check your indexes.
- Slow_queries
-
Number of queries that have taken more than
long_query_time.
Unfortunately, there is no easy way to find out which query triggered
a particular counter increase. By enabling the slow query log,
however, you can at least capture all queries that take more than a
predefined number of seconds to complete. Sometimes
you'll find that those slow queries are also
suffering from one of the problems listed above. See Chapter 5 for more about MySQL's
query cache.
A.1.9 Sorts
Queries with
ORDER BY clauses are commonplace, but sorting a
nontrivial number of rows can become a burden if done frequently. The
Section 4.1.1.2 in Chapter 4 discusses some of the index-based sorting
optimizations present in MySQL 4.0 and beyond. If MySQL
can't use an index for sorting, however, it must
resort to old-fashioned sorting techniques.
- Sort_merge_passes
-
Number of merge-passes the sort algorithms have performed. If this
value gets too high, you may wish to increase
sort_buffer.
- Sort_range
-
Number of sorts done on ranges. This is better than sorting an entire
table.
- Sort_rows
-
The total number of rows that have been sorted.
- Sort_scan
-
Number of sorts that were done using a table scan. Ideally, this
shouldn't happen often. If it does, you probably
need to add an index somewhere.
A.1.10 Table Locking
Any
time MySQL waits for a table lock, it is a bad thing. How much of a
bad thing is often a function of the application and usage patterns,
but there's no way around the fact that a MySQL
thread waiting for a lock is getting absolutely no work done. To help
track locks and lock contention on tables, MySQL provides the
following two counters.
- Table_locks_immediate
-
Number of times the server acquired a table lock immediately.
- Table_locks_waited
-
Number of times the server had to wait on a table lock.
The goal is to have Table_locks_immediate as high
as possible and Table_locks_waited as close to
zero as possible. Realistically, there has to be a middle ground, but
those are the ideals we would hope for in a perfect world. For
lower-volume or single user applications, table locks are often a
nonissue. However, on large multiuser systems or high-volume web
sites, table locks can be a very serious problem.
A high percentage of Table_locks_waited is a sign
either that you need to make queries more efficient (so that they
hold locks for a short period of time) or that you may need to
consider an alternative table type. Moving from MyISAM to InnoDB
tables will often greatly reduce lock contention—but not
always. See Chapter 2 for more details about
table locking.
|