MySQL

kaola4549

　　The SHOWSTATUScommand 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 cyclewill 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_clientsThis 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_connectsThis 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_timeoutbeing exceeded—often as the result of a network or firewall problem.

Bytes_receivedNumber of bytes received from all clients, including other MySQL servers involved in replication.

Bytes_sentNumber of bytes sent to all clients, including other MySQL servers.

ConnectionsTotal number of connection attempts, both successful and failed, to the MySQL server.

Max_used_connectionsThe peak number of simultaneous connections.

Slow_launch_threadsNumber of threads that have taken longer than slow_launch_timeto be created. A nonzero value here is a often a sign of excessive CPU load on the server.

Threads_cachedNumber of threads in the thread cache. See Chapter 6for more about MySQL's thread cache.

Threads_connectedNumber of currently open connections.

Threads_createdTotal number of threads that have been created to handle connections.

Threads_runningNumber of threads that are doing work (not sleeping).

UptimeHow 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 SELECTto a RESET MASTERis 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_selectcounts SELECTqueries, while Com_change_dbis incremented any time you issue a USEcommand to switch databases. Com_change_dbcan 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.

QuestionsThe 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_tablesThe 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_sizevariable. 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 TABLEaren't governed by this. They always reside on disk.

Created_tmp_tablesSimilar to Created_tmp_disk_tablesexcept that it counts the number of implicit temporary tables created in memory and on disk.

Created_tmp_filesHow many temporary files mysqldhas created.
Comparing Created_tmp_tablesto Created_tmp_disk_tableswill 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_firstis 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_keyvalue 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_commitNumber of internal COMMITcommands.

Handler_deleteNumber of times MySQL has deleted a row from a table.

Handler_read_firstNumber of times the first entry was read from an index.

Handler_read_keyNumber 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_nextNumber 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_prevNumber of requests to read previous row in key order. This is mainly used when you have a query using ORDER BY ... DESC.

Handler_read_rndNumber 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_nextHow 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_rollbackNumber of internal ROLLBACKcommands.

Handler_updateNumber of requests to update a table row.

Handler_writeNumber 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_usedThe number of 1024-byte blocks contained in the key cache.

Key_read_requestsThe 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_readsare also incremented).

Key_readsThe number of physical reads during which a key block was read from disk.

Key_write_requestsThe number of requests for a key block to be written.

Key_writesThe 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_cachesetting 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 .frmfile 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 .frmfile is reopened on a system with many active tables.
The following counters help keep track of MySQL's file descriptor usage:

　　
Open_tablesThe total number of tables that are currently open.

Open_filesThe total number of open files.

Open_streamsNumber of streams that are open. (These are mostly used for logging.)

Opened_tablesNumber of tables that have been opened since the server started. If Opened_tablesis 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_cacheHow many query results are in the query cache.

Qcache_insertsHow many times MySQL has inserted the results of a query into the cache.

Qcache_hitsThe number of times MySQL has found a query in the cache instead of having to actually execute the query.

Qcache_lowmem_prunesEach 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_cachedThis 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_memoryFree space (in bytes) remaining in the cache.

Qcache_free_blocksHow many free (unused) blocks exist in the cache.

Qcache_total_blocksThis is the total number of blocks in the cache. By subtracting Qcache_free_blocksfrom 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 SELECTqueries 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_joinNumber of joins without keys. If this figure isn't 0, you should check your indexes carefully.

Select_full_range_joinNumber of joins that used a range search on reference table.

Select_rangeNumber of joins that used ranges on the first table. It's normally not critical even if this number is big.

Select_scanNumber of joins that did a full scan of the first table.

Select_range_checkNumber of joins that check for key usage after each row. If this isn't 0, you should check your indexes.

Slow_queriesNumber 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 BYclauses 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_passesNumber of merge-passes the sort algorithms have performed. If this value gets too high, you may wish to increase sort_buffer.

Sort_rangeNumber of sorts done on ranges. This is better than sorting an entire table.

Sort_rowsThe total number of rows that have been sorted.

Sort_scanNumber 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_immediateNumber of times the server acquired a table lock immediately.

Table_locks_waitedNumber of times the server had to wait on a table lock.
The goal is to have Table_locks_immediateas high as possibleand Table_locks_waitedas 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_waitedis 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.