MySQL running status and optimization (2) _ MySQL

MySQL running status and optimization (2) bitsCN.com

1. view the configuration information of the MySQL server.

mysql> show variables;

2. View various status values of the MySQL server.

mysql> show global status;

3. slow query

mysql> show variables like '%slow%';+------------------+-------+| Variable_name    | Value |+------------------+-------+| log_slow_queries | OFF   || slow_launch_time | 2     |+------------------+-------+mysql> show global status like '%slow%';+---------------------+-------+| Variable_name       | Value |+---------------------+-------+| Slow_launch_threads | 0     || Slow_queries        | 279   |+---------------------+-------+

Slow query of records is disabled in configuration (it is best to enable it to facilitate optimization and enable slow query). if it exceeds 2 seconds, it is a slow query. There are a total of 279 slow queries.

4. number of connections

mysql> show variables like 'max_connections';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_connections | 500   |+-----------------+-------+mysql> show global status like 'max_used_connections';+----------------------+-------+| Variable_name        | Value |+----------------------+-------+| Max_used_connections | 498   |+----------------------+-------+

The maximum number of connections is 500, and the number of response connections is 498.
Max_used_connections/max_connections * 100% = 99.6% (ideal value: ≈ 85%)

5. key_buffer_size is one of the most influential parameters on MyISAM table performance. However, most of the databases are Innodb.

mysql> show variables like 'key_buffer_size';+-----------------+----------+| Variable_name   | Value    |+-----------------+----------+| key_buffer_size | 67108864 |+-----------------+----------+mysql> show global status like 'key_read%';+-------------------+----------+| Variable_name     | Value    |+-------------------+----------+| Key_read_requests | 25629497 || Key_reads         | 66071    |+-------------------+----------+

There are a total of 25629497 index read requests, 66071 of which are not found in the memory to directly read the index from the hard disk, calculate the probability that the index does not hit the cache: key_cache_miss_rate = Key_reads/Key_read_requests * 100% = 0.27% increase the key_buffer_size

mysql> show global status like 'key_blocks_u%';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Key_blocks_unused | 10285 || Key_blocks_used   | 47705 |+-------------------+-------+

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

6. temporary table

mysql> show global status like 'created_tmp%';+-------------------------+---------+| Variable_name           | Value   |+-------------------------+---------+| Created_tmp_disk_tables | 4184337 || Created_tmp_files       | 4124    || Created_tmp_tables      | 4215028 |+-------------------------+---------+

Created_tmp_tables is added each time a temporary table is created. if a temporary table is created on a disk, Created_tmp_disk_tables is added. Created_tmp_files indicates the number of temporary files created by the MySQL service: created_tmp_disk_tables/Created_tmp_tables * 100% = 99% (ideal value <= 25%)

mysql> show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');+---------------------+-----------+| Variable_name       | Value     |+---------------------+-----------+| max_heap_table_size | 134217728 || tmp_table_size      | 134217728 |+---------------------+-----------+

Tmp_table_size needs to be added

7. open table

mysql> show global status like 'open%tables%';+---------------+-------+| Variable_name | Value |+---------------+-------+| Open_tables   | 1024  || Opened_tables | 1465  |+---------------+-------+

Open_tables indicates the number of opened tables, and Opened_tables indicates the number of opened tables. if the number of Opened_tables is too large, the value of table_cache (table_cache after 5.1.3 is called table_open_cache) in the configuration may be too small, query the table_cache value of the server.

mysql> show variables like 'table_cache';+---------------+-------+| Variable_name | Value |+---------------+-------+| table_cache   | 1024  |+---------------+-------+

Open_tables/Opened_tables * 100% = 69% ideal values (> = 85%) Open_tables/table_cache * 100% = 100% ideal values (<= 95%)

8. process usage

mysql> show global status like 'Thread%';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Threads_cached    | 31    || Threads_connected | 239   || Threads_created   | 2914  || Threads_running   | 4     |+-------------------+-------+

If we set thread_cache_size in the MySQL server configuration file, after the client is disconnected, the thread on which the server processes this customer will be cached in response to the next customer rather than destroyed (provided that the number of caches has not reached the upper limit ). Threads_created indicates the number of created threads. if the value of Threads_created is too large, it indicates that the MySQL server has been creating threads, which is also resource-consuming. you can increase the value of thread_cache_size in the configuration file as appropriate, query the server thread_cache_size configuration:

mysql> show variables like 'thread_cache_size';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| thread_cache_size | 32    |+-------------------+-------+

9. query cache)

mysql> show global status like 'qcache%';+-------------------------+----------+| Variable_name           | Value    |+-------------------------+----------+| Qcache_free_blocks      | 2226     || Qcache_free_memory      | 10794944 || Qcache_hits             | 5385458  || Qcache_inserts          | 1806301  || Qcache_lowmem_prunes    | 433101   || Qcache_not_cached       | 4429464  || Qcache_queries_in_cache | 7168     || Qcache_total_blocks     | 16820    |+-------------------------+----------+

Qcache_free_blocks: number of adjacent memory blocks in the cache. A large number of fragments may exist. Flush query cache sorts the fragments in the CACHE to obtain an idle block. Qcache_free_memory: Idle memory in the cache. Qcache_hits: increases Qcache_inserts every time a query hits the cache: increases every time a query is inserted. By dividing the number of hits by the number of inserts, this is the ratio of no hits. Qcache_lowmem_prunes: the cache has insufficient memory and must be cleaned up to provide more space for queries. It would be better to look at this number for a long time; if this number continues to grow, it may indicate that the fragmentation is very serious, or the memory is very small. (The above free_blocks and free_memory can tell you what the situation is.) Qcache_not_cached: the number of queries that are not suitable for caching, usually because these queries are not SELECT statements or use now () and so on. Qcache_queries_in_cache: number of queries (and responses) cached currently. Qcache_total_blocks: Number of cached blocks.
Let's query the server's query_cache configuration:

mysql> show variables like 'query_cache%';+------------------------------+----------+| Variable_name                | Value    |+------------------------------+----------+| query_cache_limit            | 33554432 || query_cache_min_res_unit     | 4096     || query_cache_size             | 33554432 || query_cache_type             | ON       || query_cache_wlock_invalidate | OFF      |+------------------------------+----------+

Description of each field:
Query_cache_limit: the query_cache_min_res_unit: minimum size of the cache block query_cache_size: Query cache size query_cache_type: cache type, determines what kind of query to cache, in the example, select SQL _no_cache is not cached to query query_cache_wlock_invalidate: if other clients are performing write operations on the MyISAM table, whether to return the cache result or wait until the write operation is complete and then read the table to obtain the result.
The configuration of query_cache_min_res_unit is a double-edged sword. the default value is 4 kB. setting a large value is good for big data queries. However, if all your queries are small data queries, this can easily 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 sort out the CACHE fragmentation, 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%, the query_cache_size setting is too large and can be appropriately reduced. if the query cache utilization is above 80% and Qcache_lowmem_prunes> 50, the query_cache_size may be small, or too many fragments.
Query cache hit rate = (Qcache_hits-Qcache_inserts)/Qcache_hits * 100%
The cache fragmentation rate of the sample server is 20.46%, the query cache utilization is 62.26%, the query cache hit rate is 1.94%, and the hit rate is very low. it is possible that write operations are frequent and there may be some fragments.

10. sorting usage

mysql> show global status like 'sort%';+-------------------+----------+| Variable_name     | Value    |+-------------------+----------+| Sort_merge_passes | 2136     || Sort_range        | 81888    || Sort_rows         | 35918141 || Sort_scan         | 55269    |+-------------------+----------+

Sort_merge_passes consists of two steps. MySQL first tries to sort data in the memory. the memory size is determined by the system variable Sort_buffer_size. if the memory size is insufficient, all the records will be read to the memory, mySQL stores the sorting results in the memory to a temporary file. After MySQL finds all the records, it sorts the records in the temporary file. Sort_merge_passes will be added when sorting again. In fact, MySQL uses another temporary file to store the result of re-sorting. Therefore, we usually see that the Sort_merge_passes increase by twice the number of created temporary files. Because temporary files are used, the speed may be slow. increasing Sort_buffer_size will reduce the number of times Sort_merge_passes and temporary files are created. However, blindly increasing Sort_buffer_size does not necessarily increase the speed. see How fast can you sort data with MySQL? (From the http://qroom.blogspot.com/2007/09/mysql-select-sort.html)
In addition, adding the value of read_rnd_buffer_size (3.2.3 is record_rnd_buffer_size) also has some benefits for sorting operations, see: http://www.mysqlperformanceblog.com/2007/07/24/what-exactly-is-read_rnd_buffer_size/

11. number of opened files (open_files)

mysql> show global status like 'open_files';+---------------+-------+| Variable_name | Value |+---------------+-------+| Open_files    | 821   |+---------------+-------+mysql> show variables like 'open_files_limit';+------------------+-------+| Variable_name    | Value |+------------------+-------+| open_files_limit | 65535 |+------------------+-------+

Suitable settings: Open_files/open_files_limit * 100% <= 75%
Normal

12. Table lock status

mysql> show global status like 'table_locks%';+-----------------------+---------+| Variable_name         | Value   |+-----------------------+---------+| Table_locks_immediate | 4257944 || Table_locks_waited    | 25182   |+-----------------------+---------+

Table_locks_immediate indicates the number of table locks to be released immediately, Table_locks_waited indicates the number of table locks to wait. if Table_locks_immediate/Table_locks_waited> 5000, InnoDB engine is recommended because InnoDB is a row lock and MyISAM, innoDB provides better performance for applications with high concurrent writes.

13. table scanning

mysql> show global status like 'handler_read%';+-----------------------+-----------+| Variable_name         | Value     |+-----------------------+-----------+| Handler_read_first    | 108763    || Handler_read_key      | 92813521  || Handler_read_next     | 486650793 || Handler_read_prev     | 688726    || Handler_read_rnd      | 9321362   || Handler_read_rnd_next | 153086384 |+-----------------------+-----------+

For field explanations, see http://hi.baidu.com/thinkinginlamp/blog/item/31690cd7c4bc5cdaa144df9c.html,

Number of query requests completed by the recall server:

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 scanning rate of a table exceeds 4000, too many table scans are performed. it is very likely that the index has not been created. increasing the value of read_buffer_size may be advantageous, but it is best not to exceed 8 MB.

BitsCN.com