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

There are many articles on the Internet to teach how to configure the MySQL server, but considering the different hardware configuration of the server and the differences in specific applications, the practice of those articles can only be used as a preliminary setting reference. We need to optimize the configuration according to our own situation. The good practice is to run the MySQL server steadily for a period of time and optimize it according to the "status" of the server.

Mysql > show global status

You can list the various status values that the MySQL server is running. In addition, query the MySQL server configuration information statement:

Mysql > show variables

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

+-+ +

Slow query is opened in the configuration. If the execution time is more than 2 seconds, it is slow query. The system shows that there are 4148 slow queries. You can analyze the slow query log and find out the problematic SQL statements. Slow query time should not be set too long, otherwise it is not meaningful, preferably within 5 seconds. If you need microsecond-level slow query, you can consider patching MySQL: http://www.percona.com/docs/wiki/release:start. Remember to find the corresponding version.

Opening the slow log may have a slight impact on system performance. If your MySQL is a master-slave structure, consider opening the slow log of one of the slave servers, so that you can monitor slow queries with little impact on system performance.

Second, the number of connections

We often encounter the situation of "MySQL: ERROR 1040: Too many connections". One is that the traffic is really high and the MySQL server cannot resist it. At this time, you need to consider increasing the decentralized reading pressure from the server, and the other is that the max_connections value in the MySQL configuration file 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, and then query the maximum number of connections that the server responded to:

Mysql > show global status like 'Max_used_connections'

The maximum number of connections to the MySQL server in the past is 245.It does not reach the server connection limit of 256. there should be no 1040 error. The ideal setting is:

Max_used_connections / max_connections * 100% ≈ 85%

The maximum number of connections accounts for about 85% of the upper limit. If the proportion is found to be less than 10%, the maximum number of MySQL server connections is set too high.

III. Key_buffer_size

Key_buffer_size is a parameter that has the greatest impact on the performance of the MyISAM table. The following is a configuration of a server with MyISAM as the main storage engine:

Mysql > show variables like 'key_buffer_size'

+-+ +

| | Variable_name | Value |

+-+ +

| | key_buffer_size | 536870912 | |

+-+ +

After allocating 512MB memory to key_buffer_size, let's take a look at key_buffer_size usage:

Mysql > show global status like 'key_read%'

+-+ +

| | Variable_name | Value | mysql |

+-+ +

| | Key_read_requests | 27813678764 |

| | Key_reads | 6798830 | |

+-+ +

There are 27813678764 index read requests, of which 6798830 are not found in memory to read the index directly from the hard disk. Calculate the probability that the index misses the cache:

Key_cache_miss_rate = Key_reads / Key_read_requests * 100%

For example, for the above data, there is only one direct read hard disk with a key_cache_miss_rate of 0.0244% and 4,000 index read requests, which is already very BT. The key_cache_miss_rate is good below 0.1% (there is one direct read hard disk for every 1000 requests). If the key_cache_miss_rate is less than 0.01%, the key_buffer_size allocation is too much and can be reduced appropriately.

The MySQL server also provides key_blocks_* parameters:

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), and Key_blocks_used indicates the maximum number of blocks ever used, such as this server, where all the cache is used, either adding key_buffer_size or transitional indexes, filling up the cache. Ideal settings:

Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%

IV. Temporary watch

Mysql > show global status like 'created_tmp%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Created_tmp_disk_tables | 21197 | |

| | Created_tmp_files | 58 | |

| | Created_tmp_tables | 1771587 | |

+-+ +

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. The ideal configuration is:

Created_tmp_disk_tables / Created_tmp_tables * 100% show variables where Variable_name in ('tmp_table_size',' max_heap_table_size')

+-+ +

| | Variable_name | Value |

+-+ +

| | max_heap_table_size | 268435456 | |

| | tmp_table_size | 536870912 | |

+-+ +

Only temporary tables below 256MB can be stored in memory, and hard disk temporary tables will be used if they are exceeded.

V. Open Table situation

Mysql > show global status like 'open%tables%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Open_tables | 919 | |

| | Opened_tables | 1951 | |

+-+ +

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 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% show global status like 'Thread%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Threads_cached | 46 | |

| | Threads_connected | 2 | |

| | Threads_created | 570 |

| | Threads_running | 1 | |

+-+ +

If we set thread_cache_size in the MySQL server configuration file, when the client is disconnected, the thread that the server processes this client will be cached to respond to the next client rather than destroy (provided that the cache number does not reach the limit). Threads_created indicates the number of threads created. If the Threads_created value is too high, it means that the MySQL server has been creating threads, which is also resource-consuming. You can appropriately increase the value of thread_cache_ size in the configuration file to query the 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 quite healthy.

7. Query cache (query cache)

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 explanation:

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 the number is growing, it may be very fragmented or have 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 check the server's configuration for query_cache again:

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

VIII. Sorting usage

Mysql > show global status like 'sort%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Sort_merge_passes | 29 | |

| | Sort_range | 37432840 | |

| | Sort_rows | 9178691532 | |

| | Sort_scan | 1860569 | |

+-+ +

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, it looks like being walled) mysql

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/

IX. Number of document openings (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% show global status like 'table_locks%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Table_locks_immediate | 490206328 | |

| | Table_locks_waited | 2084912 | |

+-+ +

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. The server in the example Table_locks_immediate / Table_locks_waited = 235 MyISAM is sufficient.

11. Table scanning

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 the explanation of each field, please 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 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.

Postscript:

Some numbers mentioned in this paper are reference values, and it is OK to understand the basic principles. In addition to the various status values provided by MySQL, some performance indicators of the operating system are also very important, such as the commonly used top,iostat, especially iostat. Now the system bottleneck is generally on the disk IO, about the use of iostat.

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.