MySQL show variables like xxx detailed

1. View MySQL server configuration information
Mysql> Show variables;

2. View the various status values running on the MySQL server
Mysql> show global status;

3, slow query

1.  mysql> show variables like ‘%slow%‘;  2.  +------------------+-------+  3.  | Variable_name    | Value |  4.  +------------------+-------+  5.  | log_slow_queries | OFF   |  6.  | slow_launch_time | 2     |  7.  +------------------+-------+  8.  mysql> show global status like ‘%slow%‘;  9.  +---------------------+-------+  10. | Variable_name       | Value |  11. +---------------------+-------+  12. | Slow_launch_threads | 0     |  13. | Slow_queries        | 279   |  14. +---------------------+-------+  

Slow query is turned off in configuration (preferably open, easy to optimize), more than 2 seconds is slow query, a total of 279 slow query

4, Number of connections

1.  mysql> show variables like ‘max_connections‘;  2.  +-----------------+-------+  3.  | Variable_name   | Value |  4.  +-----------------+-------+  5.  | max_connections | 500   |  6.  +-----------------+-------+  7.    8.  mysql> show global status like ‘max_used_connections‘;  9.  +----------------------+-------+  10. | Variable_name        | Value |  11. +----------------------+-------+  12. | Max_used_connections | 498   |  13. +----------------------+-------+  

The maximum number of connections set is 500, and the number of connections to the response is 498

Max_used_connections/max_connections * 100% = 99.6% (ideal value ≈85%)

5, Key_buffer_size
Key_buffer_size is one of the most significant effects on MyISAM table performance, but most of the data in the database is InnoDB

1.  mysql> show variables like ‘key_buffer_size‘;  2.  +-----------------+----------+  3.  | Variable_name   | Value    |  4.  +-----------------+----------+  5.  | key_buffer_size | 67108864 |  6.  +-----------------+----------+  7.    8.  mysql> show global status like ‘key_read%‘;  9.  +-------------------+----------+  10. | Variable_name     | Value    |  11. +-------------------+----------+  12. | Key_read_requests | 25629497 |  13. | Key_reads         | 66071    |  14. +-------------------+----------+  

A total of 25,629,497 index read requests, with 66,071 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% =0.27%
Need to increase key_buffer_size appropriately

1.  mysql> show global status like ‘key_blocks_u%‘;  2.  +-------------------+-------+  3.  | Variable_name     | Value |  4.  +-------------------+-------+  5.  | Key_blocks_unused | 10285 |  6.  | Key_blocks_used   | 47705 |  7.  +-------------------+-------+  

Key_blocks_unused represents the number of unused cache clusters (blocks), key_blocks_used indicates the maximum number of blocks ever used
Key_blocks_used/(key_blocks_unused + key_blocks_used) * 100%≈18% (ideal value ≈80%)

6, temporary table

1.  mysql> show global status like ‘created_tmp%‘;  2.  +-------------------------+---------+  3.  | Variable_name           | Value   |  4.  +-------------------------+---------+  5.  | Created_tmp_disk_tables | 4184337 |  6.  | Created_tmp_files       | 4124    |  7.  | Created_tmp_tables      | 4215028 |  8.  +-------------------------+---------+  

Each time a temporary table is created, created_tmp_tables increases, if the temporary table is created on disk, Created_tmp_disk_tables also increases, created_tmp_files represents the number of temporary file files created by the MySQL service:
Created_tmp_disk_tables/created_tmp_tables * 100% = 99% (ideal value <= 25%)

1.  mysql> show variables where Variable_name in (‘tmp_table_size‘, ‘max_heap_table_size‘);  2.  +---------------------+-----------+  3.  | Variable_name       | Value     |  4.  +---------------------+-----------+  5.  | max_heap_table_size | 134217728 |  6.  | tmp_table_size      | 134217728 |  7.  +---------------------+-----------+  

Need to increase tmp_table_size

7,open table Condition

1.  mysql> show global status like ‘open%tables%‘;  2.  +---------------+-------+  3.  | Variable_name | Value |  4.  +---------------+-------+  5.  | Open_tables   | 1024  |  6.  | Opened_tables | 1465  |  7.  +---------------+-------+  

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

1.  mysql> show variables like ‘table_cache‘;  2.  +---------------+-------+  3.  | Variable_name | Value |  4.  +---------------+-------+  5.  | table_cache   | 1024  |  6.  +---------------+-------+  

Open_tables/opened_tables 100% =69% Ideal Value (>= 85%)
Open_tables/table_cache 100% = 100% Ideal Value (<= 95%)

8, Process usage

1.  mysql> show global status like ‘Thread%‘;  2.  +-------------------+-------+  3.  | Variable_name     | Value |  4.  +-------------------+-------+  5.  | Threads_cached    | 31    |  6.  | Threads_connected | 239   |  7.  | Threads_created   | 2914  |  8.  | Threads_running   | 4     |  9.  +-------------------+-------+  

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:

1.  mysql> show variables like ‘thread_cache_size‘;  2.  +-------------------+-------+  3.  | Variable_name     | Value |  4.  +-------------------+-------+  5.  | thread_cache_size | 32    |  6.  +-------------------+-------+  

9, query cache

  1. Mysql> Show global status like ' qcache% ';  2. +-------------------------+----------+ 3. | variable_name |  Value |  4. +-------------------------+----------+ 5. | Qcache_free_blocks |  2226 | 6. | Qcache_free_memory |  10794944 | 7. | Qcache_hits |  5385458 | 8. | Qcache_inserts |  1806301 | 9. | Qcache_lowmem_prunes |  433101 | 10. | qcache_not_cached |  4429464 | 11. | Qcache_queries_in_cache |  7168 | 12. | Qcache_total_blocks |  16820 | +-------------------------+----------+  

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:

1.  mysql> show variables like ‘query_cache%‘;  2.  +------------------------------+----------+  3.  | Variable_name                | Value    |  4.  +------------------------------+----------+  5.  | query_cache_limit            | 33554432 |  6.  | query_cache_min_res_unit     | 4096     |  7.  | query_cache_size             | 33554432 |  8.  | query_cache_type             | ON       |  9.  | query_cache_wlock_invalidate | OFF      |  10. +------------------------------+----------+  

Explanation of each field:

Query_cache_limit: Queries that exceed this size will not be cached
Query_cache_min_res_unit: Minimum size of cache block
Query_cache_size: Query Cache Size
Query_cache_type: Cache type, determines what queries are cached, example indicates that select Sql_no_cache queries are not cached
Query_cache_wlock_invalidate: When there are other clients that are writing to the MyISAM table, if the query is to be returned with the cache result or wait for the write operation to complete, the table gets the result.

Query_cache_min_res_unit configuration is a "double-edged sword", the default is 4KB, set 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 query_cache_min_res_unit if your queries are small data volumes.

Query Cache utilization = (query_cache_size–qcache_free_memory)/query_cache_size * 100%

Query cache utilization below 25% indicates that the query_cache_size setting is too large to be appropriately reduced; query cache utilization is above 80% and Qcache_lowmem_prunes > 50 query_cache_ Size may be a little bit small, or too much fragmentation.

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 cache Hit Ratio = 1.94%, hit ratio is poor, probably write more frequently, and possibly some fragments.

10, sorting usage

1.  mysql> show global status like ‘sort%‘;  2.  +-------------------+----------+  3.  | Variable_name     | Value    |  4.  +-------------------+----------+  5.  | Sort_merge_passes | 2136     |  6.  | Sort_range        | 81888    |  7.  | Sort_rows         | 35918141 |  8.  | Sort_scan         | 55269    |  9.  +-------------------+----------+  

The sort_merge_passes consists of 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)

In addition, increasing the value of Read_rnd_buffer_size (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/

11. Number of open files (open_files)

1.  mysql> show global status like ‘open_files‘;  2.  +---------------+-------+  3.  | Variable_name | Value |  4.  +---------------+-------+  5.  | Open_files    | 821   |  6.  +---------------+-------+  7.    8.  mysql> show variables like ‘open_files_limit‘;  9.  +------------------+-------+  10. | Variable_name    | Value |  11. +------------------+-------+  12. | open_files_limit | 65535 |  13. +------------------+-------+  

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

Normal

12. Table lock Condition

1.  mysql> show global status like ‘table_locks%‘;  2.  +-----------------------+---------+  3.  | Variable_name         | Value   |  4.  +-----------------------+---------+  5.  | Table_locks_immediate | 4257944 |  6.  | Table_locks_waited    | 25182   |  7.  +-----------------------+---------+  

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.

13. Table Scan Condition

1.  mysql> show global status like ‘handler_read%‘;  2.  +-----------------------+-----------+  3.  | Variable_name         | Value     |  4.  +-----------------------+-----------+  5.  | Handler_read_first    | 108763    |  6.  | Handler_read_key      | 92813521  |  7.  | Handler_read_next     | 486650793 |  8.  | Handler_read_prev     | 688726    |  9.  | Handler_read_rnd      | 9321362   |  10. | Handler_read_rnd_next | 153086384 |  11. +-----------------------+-----------+  

Number of query requests paged out by the server:

1. Mysql> show global status like ' Com_select ';
2. +---------------+---------+
3. | variable_name | Value |
4. +---------------+---------+
5. | Com_select | 2693147 |
6. +---------------+---------+

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.

This article refers to the following pages:
1.http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.htm
2.http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
3.http://www.ibm.com/developerworks/cn/linux/l-tune-lamp-3.html
4.http://www.day32.com/mysql/tuning-primer.sh specific values refer to this tool mainly

MySQL show variables like xxx detailed