Shulou(Shulou.com)06/01 Report--

Let's learn about the use of show variables like query in MySQL. I believe you will benefit a lot after reading it. The text is not much in essence. I hope you want to use show variables like query in MySQL.

1. View the configuration information of MySQL CVM

Mysql > show variables

2. View the various status values of the MySQL CVM

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

The slow query of records is turned off in the configuration (preferably on, to facilitate optimization). It is a slow query after more than 2 seconds. There are a total of 279 slow queries.

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, while the number of responding connections is 498

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

5, key_buffer_size

Key_buffer_size is the parameter that has the greatest impact on the performance of the MyISAM table, but most of 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. +- -+

There are a total of 25629497 index read requests, of which 66071 are not found in memory to read the index directly from the hard disk, calculating the probability of the index missing the cache:

Key_cache_miss_rate = Key_reads / Key_read_requests * 100% = 0.27%

Key_buffer_size needs to be increased 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), and Key_blocks_used represents the maximum number of blocks ever used

Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 18% (ideal value ≈ 80%)

6, temporary watch

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 a temporary table is created on disk, Created_tmp_disk_tables also increases. Created_tmp_files represents the number of temporary files created by the MySQL service:

Created_tmp_disk_tables / Created_tmp_tables * 100% = 99% (ideal value 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 add tmp_table_size

7. 7 the situation of open table

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, and Opened_tables indicates the number of opened tables. If the number of Opened_tables is too large, the value of table_cache (called table_open_cache after 5.1.3) in the configuration may be too small. Let's query the table_ cache value of the CVM.

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 (> = 85%)

Open_tables / table_cache 100% ideal value (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 thread_cache_size in the MySQL CVM configuration file, when the client is disconnected, the thread of the CVM processing this client will be cached to respond to the next customer instead of being destroyed (provided that the cache number does not reach the limit). Threads_created indicates the number of threads created. If the Threads_created value is found to be too high, it means that the MySQL CVM has been creating threads, which is also resource-consuming. You can appropriately increase the thread_cache_ size value in the configuration file to query the thread_cache_size configuration of the CVM:

1. Mysql > show variables like 'thread_cache_size' 2. +-+-+ 3. | Variable_name | Value | 4. +-+-+ 5. | thread_cache_size | 32 | 6. +-+-+

9, query cache (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 | 13. +- -+-+

Qcache_free_blocks: the number of adjacent memory blocks in the cache. A large number means there may be fragments. FLUSH QUERY CACHE will defragment the cache to get a free block.

Qcache_free_memory: free memory in the cache.

Qcache_hits: increments every time a query hits in the cache

Qcache_inserts: increments each time a query is inserted. The miss ratio is the number of hits divided by the number of inserts.

Qcache_lowmem_prunes: the number of times the cache runs out of memory and must be cleaned to provide space for more queries. This number is best seen over a long period of time; if it is growing, it means that the fragmentation may be very serious, or there may be very little memory. (the free_blocks and free_memory above can tell you which case it is.)

Qcache_not_cached: the number of queries that are not suitable for caching, usually because they are not SELECT statements or use functions such as now ().

Qcache_queries_in_cache: the number of queries (and responses) currently cached.

Qcache_total_blocks: the number of blocks in the cache.

Let's query the configuration of query_cache on CVM again:

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 larger than this size will not be cached

Query_cache_min_res_unit: minimum size of the cache block

Query_cache_size: query cache size

Query_cache_type: cache type, which determines what kind of query to cache. In the example, select sql_no_cache query is not cached.

Query_cache_wlock_invalidate: when another client is writing to the MyISAM table, if the query is in query cache, whether to return the cache result or wait for the write operation to complete and then read the table to get the result.

Query_cache_min_res_unit configuration is a "double-edged sword", the default is 4KB, set a large value for big data query is good, but if your queries are small data queries, it is easy to cause 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 query is a small amount of data.

Query cache utilization = (query_cache_size-Qcache_free_memory) / query_cache_size * 100%

If the query cache utilization is below 25%, it means that the query_cache_size setting is too large and can be reduced appropriately; if the query cache utilization is above 80% and the Qcache_lowmem_prunes is more than 50, the query_cache_size may be a little small, or there may be too many fragments.

Query cache hit ratio = (Qcache_hits-Qcache_inserts) / Qcache_hits * 100%

Example CVM query cache fragmentation rate = 20.46%, query cache utilization = 62.26%, query cache hit rate = 1.94%, the hit rate is very poor, maybe write operations are more frequent, and there may be some fragments.

10, sort 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. +-+-+ |

Sort_merge_passes consists of two steps. MySQL will first try to sort in memory, and the memory size used is determined by the system variable Sort_buffer_size. If it is not big enough to read all the records into memory, MySQL will save the results of each sort in memory to a temporary file, and wait for MySQL to find all the records, and then sort the records in the temporary file again. This sort again increases the Sort_merge_passes. In fact, MySQL uses another temporary file to store the re-sorted results, so it is common to see that Sort_merge_passes increases by twice the number of temporary files created. Because temporary files are used, the speed may be slow, and increasing Sort_buffer_size reduces the number of times Sort_merge_passes and temporary files are created. But blindly adding 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) also has a little benefit for sorting operations, see: http://www.mysqlperformanceblog.com/2007/07/24/what-exactly-is- read_rnd_buffer_size/

11. Number of file openings (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% 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 the number of table locks to be released immediately, and Table_locks_waited indicates the number of table locks to wait. If Table_locks_immediate / Table_locks_waited > 5000, it is best to use InnoDB engine, because InnoDB is a row lock and MyISAM is a table lock. InnoDB works better for applications with high concurrent writes.

Table scan 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. +-+.

The number of query requests completed by calling up the CVM:

Mysql > show global status like 'com_select';+-+-+ | Variable_name | Value | +-- + | Com_select | 2693147 | +-+-+

Calculate the table scan rate:

Table scan rate = Handler_read_rnd_next / Com_select

If the table scan rate exceeds 4000, there are too many table scans, and it is very likely that the index is not well built. Increasing the read_buffer_size value has some benefits, but it is best not to exceed 8MB.

After reading this article using show variables like query in MySQL, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.