MySQL Performance view (multi-indicator)

There are a lot of articles on the web to teach how to configure MySQL server, but considering the different server hardware configuration, the specific application of the difference, those articles can only be used as a preliminary setting reference, we need to configure the optimization according to their own situation, good practice is that the MySQL server stable run after a period of time to run, Optimized based on the state of the server.

Open the Slow Query method: Actually open the MySQL slow query log is very simple, only need to in the MySQL configuration file (Windows system is My.ini,linux system is my.cnf) the [mysqld] below add the following code:

log-slow-queries =/usr/local/mysql/var/slowquery.loglong_query_time = 1  #单位是秒log-queries-not-using-indexes

Or:

Use SQL statements to modify: cannot be modified according to the items in the my.conf. Modified by "show VARIABLES like"%slow% ""
Statement to list the variables, run the following sql:

Set Global log_slow_queries = On;set global slow_query_log = on; #set global long_query_time=0.1; #设置大于0.1s SQL statements recorded, I did not succeed in the experiment

Results:

The slow query log is turned on as follows:

To view slow log configuration conditions:

Mysql> show VARIABLES like "%slow%";
+---------------------------+-------------------------------+
| variable_name | Value |
+---------------------------+-------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| Slow_launch_time | 2 |
| Slow_query_log | On |
| Slow_query_log_file | /var/lib/mysql/cmos1-slow.log |
+---------------------------+-------------------------------+

View Slow Query time:

Mysql> Show variables like "Long_query_time";
+-----------------+-----------+
| variable_name | Value |
+-----------------+-----------+
| Long_query_time | 10.000000 |
+-----------------+-----------+
1 row in Set (0.01 sec)

Queries of more than 10 seconds will be recorded and tested as follows:

Find a client to execute select Sleep (11);

See Show status and log again:

Mysql> Show processlist;+----+------+---------------------+---------+---------+------+------------+---------- --------+| Id | User | Host | db | Command | Time | State |  Info |+----+------+---------------------+---------+---------+------+------------+------------------+| 3 | Root | 10.202.37.75:54830 | Ud_omcs |    Sleep |            3 | |  NULL | | 4 | Root | 10.202.11.118:16644 | Ud_omcs |    Sleep |            0 | |  NULL | | 7 | Root | localhost | Ud_omcs |    Query | 0 | Starting |  Show Processlist | | 8 | Root | 10.118.62.181:51533 | Ud_omcs | Sleep |            1328 | |  NULL | | 9 | Root | 10.118.62.181:51551 | Ud_omcs |    Query | 9 | User Sleep | Select Sleep (11) | | 10 | Root | 10.118.62.156:52938 | Omcs |    Sleep |            0 | | NULL | | 11 | Root | 10.118.62.156:52939 | Omcs |    Sleep |            0 | | NULL |+----+------+---------------------+---------+---------+------+------------+------------------+ 

Mysql> show global status like '%slow% '; +---------------------+-------+| Variable_name       | Value |+---------------------+-------+| Slow_launch_threads | 0     | | Slow_queries        | 1     |+---------------------+-------+2 rows in Set (0.00 sec)

# #select DB, Query_time, Lock_time, rows_examined, sql_text from Mysql.slow_logselect db, Query_time, Lock_time, Rows_exa Mined, Sql_text from Mysql.slow_log

The slow log is as follows:

Remember to turn off slow logs when you're done analyzing.

Perform:

Set Global log_slow_queries = Off;set global slow_query_log = OFF;

Results:

Mysql> show VARIABLES like "%slow%";
+---------------------------+-------------------------------+
| variable_name | Value |
+---------------------------+-------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| Slow_launch_time | 2 |
| Slow_query_log | OFF |
| Slow_query_log_file | /var/lib/mysql/cmos1-slow.log |
+---------------------------+-------------------------------+
5 rows in Set (0.00 sec)

The configuration of the record slow query, the execution time of more than 2 seconds is slow query, the system shows that there are 4,148 slow query, you can analyze the slow query log, find the problem of SQL statements, slow query time should not be set too long, otherwise the meaning is not very good, preferably within 5 seconds, if you need a microsecond level of slow query, Consider a patch for MySQL: Http://www.percona.com/docs/wiki/release:start, remember to find the corresponding version.
Mysql> show global status;
The MySQL server can be listed to run various status values, in addition, query the MySQL server configuration information statement:
Mysql> Show variables;
One, slow query
Mysql> Show variables like '%slow% ';
+------------------+-------+
| variable_name | Value |
+------------------+-------+
| log_slow_queries | On |
| Slow_launch_time | 2 |
+------------------+-------+
Mysql> show global status like '%slow% ';
+---------------------+-------+
| variable_name | Value |
+---------------------+-------+
| Slow_launch_threads | 0 |
| slow_queries | 4148 |
+---------------------+-------+

Turning on the slow query log may have a little impact on system performance, and if your MySQL is a master-slave structure, you can consider opening one of the slow query logs from the server, which can monitor slow queries and have little impact on system performance.

Second, the number of connections
Often meet "mysql:error 1040:too many connections" situation, one is the traffic is really high, MySQL server can not resist, this time to consider increasing the load from the server to read the pressure, another situation is the MySQL configuration file Max_ Connections value is too small:
Mysql> Show variables like ' max_connections ';
+-----------------+-------+
| variable_name | Value |
+-----------------+-------+
| max_connections | 256 |
+-----------------+-------+

The maximum number of connections for this MySQL server is 256, then query the maximum number of connections the server responds to:
Mysql> show global status like ' Max_used_connections ';
MySQL server in the past the maximum number of connections is 245, did not reach the maximum number of server connections 256, there should be no 1040 error, the more ideal setting is:
Max_used_connections/max_connections * 100%≈85%
The maximum number of connections is about 85% of the maximum number of connections, if the ratio is found below 10%, the maximum number of MySQL server connections is set too high.

Third, Key_buffer_size
Key_buffer_size is one of the most influential parameters for MyISAM table performance, and the following is a configuration of the primary storage engine server with MyISAM:
Mysql> Show variables like ' key_buffer_size ';
+-----------------+------------+
| variable_name | Value |
+-----------------+------------+
| Key_buffer_size | 536870912 |
+-----------------+------------+

Allocated 512MB of memory to Key_buffer_size, let's look at the usage of key_buffer_size:
Mysql> show global status like ' key_read% ';
+------------------------+-------------+
| variable_name | Value | Mysql
+------------------------+-------------+
| key_read_requests | 27813678764 |
| Key_reads | 6798830 |
+------------------------+-------------+

A total of 27,813,678,764 index read requests, with 6,798,830 requests not found in memory directly from the hard disk to read the index, calculate the probability of index misses cache:
Key_cache_miss_rate = key_reads/key_read_requests * 100%
For example, the above data, Key_cache_miss_rate is 0.0244%, 4,000 index read requests have a direct read hard disk, already very bt, key_cache_miss_rate under 0.1% is very good (every 1000 requests have a direct read hard disk If Key_cache_miss_rate is below 0.01%, the key_buffer_size is allocated too much and can be appropriately reduced.
The MySQL server also provides the key_blocks_* parameter:
Mysql> show global status like ' key_blocks_u% ';
+------------------------+-------------+
| variable_name | Value |
+------------------------+-------------+
| key_blocks_unused | 0 |
| key_blocks_used | 413543 |
+------------------------+-------------+

Key_blocks_unused represents the number of unused cache clusters (blocks), key_blocks_used indicates the maximum number of blocks ever used, such as this server, all caches are used, or key_buffer_size is added, It's either a transition index or a full cache. More Ideal settings:
Key_blocks_used/(key_blocks_unused + key_blocks_used) * 100%≈80%

Four, temporary table  
mysql> show global status like ' created_tmp% ';  
+-------------------------+------- --+ 
| variable_name | Value | 
+-------------------------+---------+ 
| Created_tmp_disk_tables | 21197 | 
| Created_tmp_files | | 
| Created_tmp_tables | 1771587 | 
+-------------------------+---------+

       each time you create a temporary table, created_tmp_tables increases, and if you create a temporary table on disk, Created_tmp_disk_tables also increases, Created_tmp_files represents the number of temporary file files created by the MySQL service, and the ideal configuration is:
Created_tmp_disk_tables/created_tmp_tables * 100% <= 25% &NBSP:
such as the server above created_tmp_disk_tables/created_tmp_tables * 100% = 1.2%, should be quite good. Let's look at the MySQL server configuration for temporary tables:  
mysql> Show variables where variable_name in (' Tmp_table_size ', ' Max_heap_table_ Size ');  
+---------------------+-----------+ 
| variable_name | Value | 
+---------------------+-----------+ 
| max_heap_table_size | 268435456 | 
| tmp_tab Le_size | 536870912 | 
+---------------------+-----------+

Only temporary tables below 256MB can be used for all memory, and the hard disk temporary tables will be available.
V. Open table case
Mysql> show global status like ' open%tables% ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| Open_tables | 919 |
| Opened_tables | 1951 |
+---------------+-------+

Open_tables indicates the number of open tables, opened_tables indicates the number of open tables, and if the opened_tables quantity is too large, the Table_cache in the configuration (5.1.3 This value is called Table_open_cache) The value may be too small, let's check the server Table_cache value:
Mysql> Show variables like ' Table_cache ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| Table_cache | 2048 |
+---------------+-------+
The more appropriate values are:
Open_tables/opened_tables * 100% >= 85%
Open_tables/table_cache * 100% <= 95%

Vi. use of the process
Mysql> show global status like ' thread% ';
+-------------------+-------+
| variable_name | Value |
+-------------------+-------+
| threads_cached | 46 |
| threads_connected | 2 |
| threads_created | 570 |
| threads_running | 1 |
+-------------------+-------+

If we set the thread_cache_size in the MySQL server configuration file, when the client disconnects, the server processes the client's thread to cache in response to the next customer instead of destroying it (provided the cache count is not up to the limit). Threads_created indicates the number of threads created, if the threads_created value is found to be too large, it indicates that the MySQL server has been creating threads, which is also a relatively resource-intensive, can appropriately increase the thread_cache_size value in the configuration file, Query Server Thread_cache_size configuration:
Mysql> Show variables like ' thread_cache_size ';
+-------------------+-------+
| variable_name | Value |
+-------------------+-------+
| Thread_cache_size | 64 |
+-------------------+-------+

The server in the example is still quite healthy.

Querying cache (query caches)
Mysql> show global status like ' qcache% ';
+-------------------------+-----------+
| variable_name | Value |
+-------------------------+-----------+
| Qcache_free_blocks | 22756 |
| Qcache_free_memory | 76764704 |
| Qcache_hits | 213028692 |
| Qcache_inserts | 208894227 |
| Qcache_lowmem_prunes | 4010916 |
| qcache_not_cached | 13385031 |
| Qcache_queries_in_cache | 43560 |
| Qcache_total_blocks | 111212 |
+-------------------------+-----------+

MySQL Query cache variable interpretation:
Qcache_free_blocks: The number of contiguous memory blocks in the cache. A large number indicates that there may be fragmentation. FLUSH QUERY Cache organizes the fragments in the cache to get a free block.
Qcache_free_memory: Free memory in the cache.
Qcache_hits: Increases each time the query hits the cache
Qcache_inserts: Increases each time a query is inserted. The number of hits divided by the number of inserts is not the ratio.
Qcache_lowmem_prunes: The number of times that the cache is out of memory and must be cleaned up to provide space for more queries. This number is best seen over a long period of time, and if the number is growing, it can mean that fragmentation is very serious, or that there is little memory. (The above free_blocks and free_memory can tell you which kind of situation)
Qcache_not_cached: The number of queries that are not appropriate for caching, usually because these queries are not a SELECT statement or are using functions such as now ().
Qcache_queries_in_cache: The number of queries (and responses) that are currently cached.
Qcache_total_blocks: The number of blocks in the cache.
Let's look at the server's configuration for Query_cache:
Mysql> Show variables like ' query_cache% ';
+------------------------------+-----------+
| variable_name | Value |
+------------------------------+-----------+
| Query_cache_limit | 2097152 |
| Query_cache_min_res_unit | 4096 |
| Query_cache_size | 203423744 |
| Query_cache_type | On |
| Query_cache_wlock_invalidate | OFF |

       + —————————— + ——— –+
Explanation of the fields:
Query_cache_limit: Queries that exceed this size will not be cached
Query _cache_min_res_unit: The minimum size of the cache block
Query_cache_size: Query Cache size
Query_cache_type: The cache type that determines what queries are cached, and the example indicates that the select Sql_ is not cached No_cache query  
Query_cache_wlock_invalidate: When a query is being written to the MyISAM table by another client, Whether to return the cache result or wait for the write operation to complete before reading the table to get the results. The
Query_cache_min_res_unit configuration is a "double-edged sword", the default is 4KB, setting the value of large data query is good, but if your query is small data query, it is easy to create memory fragmentation and waste.
Query Cache Fragmentation Rate = Qcache_free_blocks/qcache_total_blocks * 100%
If the query cache fragmentation rate exceeds 20%, you can use flush query cache to defragment the cache, or try to reduce the query _cache_min_res_unit, if your query is a small amount of data.
Query Cache utilization = (query_cache_size-qcache_free_memory)/query_cache_size * 100%
Query Cache utilization below 25% description query_cache_size settings Is too large, can be appropriately reduced; query cache utilization is above 80% and Qcache_lowmem_prunes > 50 indicates that query_cache_size may be a bit small, or too many fragments.  
Query Cache Hit Ratio = (qcache_hits-qcache_inserts)/qcache_hits * 100%
Sample Server query cache Fragmentation rate = 20.46%, query cache utilization = 62.26%, query slow Save hit rate = 1.94%, the hit rate is poor, may write more frequent bar, and may be some fragments.

Eight, sort usage  
mysql> show global status like ' sort% ';  
+-------------------+------------+&nbs P
| variable_name | Value | 
+-------------------+------------+ 
| sort_merge_passes | | 
| Sort_range | 37432840 | 
| Sort_rows | 9178691532 | 
| Sort_scan | 1860569 | 
+-------------------+------------+

The

       sort_merge_passes includes two steps. MySQL first tries to sort in memory, the memory size is determined by the system variable sort_buffer_size, if it is not large enough to read all the records into memory, MySQL will save the results of each in-memory sorted into a temporary file, and so on MySQL Once all the records have been found, the records in the temporary file are sorted once. This re-ordering will increase the sort_merge_passes. In fact, MySQL will use another temporary file to save the re-ordering results, so you will usually see that the sort_merge_passes increment is twice times the number of temporary files built. Because temporary files are used, the speed may be slower, and increasing sort_buffer_size will reduce the number of sort_merge_passes and temporary files created. But blindly increasing sort_buffer_size does not necessarily improve speed, see how fast can you Sort data with MySQL? (quoted from Http://qroom.blogspot.com/2007/09/mysql-select-sort.html, seemingly by the wall) MySQL
Additionally, add read_rnd_buffer_size ( The value of 3.2.3 is Record_rnd_buffer_size) is also a bit of a benefit for sorting operations, see: http://www.mysqlperformanceblog.com/2007/07/24/ what-exactly-is-read_rnd_buffer_size/

Ix. Number of open files (open_files)
Mysql> show global status like ' Open_files ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| Open_files | 1410 |
+---------------+-------+
Mysql> Show variables like ' open_files_limit ';
+------------------+-------+
| variable_name | Value |
+------------------+-------+
| Open_files_limit | 4590 |
+------------------+-------+

More appropriate setting: Open_files/open_files_limit * 100% <= 75%

Ten, the table lock situation
Mysql> show global status like ' table_locks% ';
+-----------------------+-----------+
| variable_name | Value |
+-----------------------+-----------+
| Table_locks_immediate | 490206328 |
| table_locks_waited | 2084912 |
+-----------------------+-----------+

Table_locks_immediate indicates that the number of table locks is released immediately, table_locks_waited indicates the number of table locks that need to wait, if table_locks_immediate/table_locks_waited > 5000, it is best to use the InnoDB engine, because InnoDB is a row lock and MyISAM is a table lock, for high concurrent write application InnoDB effect will be better. The server table_locks_immediate/table_locks_waited = 235,myisam In the example is sufficient.

Xi. Table Scan Situation
Mysql> show global status like ' handler_read% ';
+-----------------------+-------------+
| variable_name | Value |
+-----------------------+-------------+
| Handler_read_first | 5803750 |
| Handler_read_key | 6049319850 |
| Handler_read_next | 94440908210 |
| Handler_read_prev | 34822001724 |
| Handler_read_rnd | 405482605 |
| Handler_read_rnd_next | 18912877839 |
+-----------------------+-------------+

For each field explanation see http://hi.baidu.com/thinkinginlamp/blog/item/31690cd7c4bc5cdaa144df9c.html, call up the number of query requests completed by the server:
Mysql> show global status like ' Com_select ';
+---------------+-----------+
| variable_name | Value |
+---------------+-----------+
| Com_select | 222693559 |
+---------------+-----------+

Calculate table Scan Rate:
Table Scan rate = Handler_read_rnd_next/com_select
If the table scan rate exceeds 4000, indicating that there are too many table scans, it is likely that the index is not built, and that increasing the read_buffer_size value will have some benefits, but it is best not to exceed 8MB.
Postscript:
The article mentions that some numbers are reference values, understand the basic principle, in addition to the various status values provided by MySQL, some performance indicators of the operating system are also very important, such as common top,iostat, especially Iostat, the current system bottlenecks are generally on disk IO, About the use of iostat.

MySQL Performance view (multi-indicator)