MySQL - 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.

 

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. I t 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

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

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, 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. 

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. 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.