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.

< Day Day Up >