Optimize the Mysql server based on status information _mysql

Mysql> show global status;

Can list the MySQL server running 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 |
+ ——————— + ——-+

Slow query is turned on in the configuration, and the execution time is more than 2 seconds. The system shows that there are 4,148 slow queries, you can analyze the slow query log, find the problem of the SQL statements, slow query time should not be set too long, otherwise meaningless, preferably within 5 seconds, if you need microsecond level of other slow query, You can consider a patch for MySQL: Http://www.percona.com/docs/wiki/release:start, remember to find the corresponding version.

Open a slow query log may have a little impact on system performance, if your MySQL is the master-from structure, you can consider to open one of the slow query log from the server, so that you can monitor the slow query, the system can have little impact on.

Second, the number of connections

Often meet the "Mysql:error 1040:too many connections" situation, one is that the traffic is really high, MySQL server can not resist, this time to consider increasing the spread from the server read pressure, the other is the MySQL configuration file in the 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, and then the maximum number of connections to the server response is queried:

Mysql> show global status like ' Max_used_connections ';
+ ———————-+ ——-+
| variable_name | Value |
+ ———————-+ ——-+
| max_used_connections | 245 |
+ ———————-+ ——-+

MySQL server in the past the maximum number of connections is 245, did not reach the maximum number of server connections 256, should not appear 1040 errors, the more ideal setting is:

Max_used_connections/max_connections * 100%≈85%

The maximum number of connections to the upper limit of 85% of the number of connections, if the ratio is found below 10%, MySQL server connection to the upper limit set too high.

Third, Key_buffer_size

Key_buffer_size is one of the most significant parameters for MyISAM table performance, and the following is a configuration that MyISAM as the primary storage Engine server:

Mysql> Show variables like ' key_buffer_size ';
+ ————— –+ ———— +
| variable_name | Value |
+ ————— –+ ———— +
| Key_buffer_size | 536870912 |
+ ————— –+ ———— +

Allocated 512MB memory to Key_buffer_size, let's take a look at the use of key_buffer_size:

Mysql> show global status like ' key_read% ';
+ ———————— + ————-+
| variable_name | Value |
+ ———————— + ————-+
| key_read_requests | 27813678764 |
| Key_reads | 6798830 |
+ ———————— + ————-+

There are 27,813,678,764 index read requests, 6,798,830 requests are not found in memory read the index directly from the hard disk, the probability of the index misses the cache is computed:

Key_cache_miss_rate = key_reads/key_read_requests * 100%

For example, the above data, Key_cache_miss_rate 0.0244%, 4,000 index read request only a direct read hard drive, has been very bt, key_cache_miss_rate in 0.1% The following are very good (each 1000 requests have a direct read hard disk), if the key_cache_miss_rate under 0.01%, Key_buffer_size allocated too much, can be appropriately reduced.

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), key_blocks_used indicates the maximum number of blocks used, such as this server, all caches are used, or increase key_buffer_size, Either the transition index or the cache is full. More Ideal settings:

Key_blocks_used/(key_blocks_unused + key_blocks_used) * 100%≈80%

Iv. Temporary tables

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 you create a temporary table, the 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, A more desirable configuration is:

Created_tmp_disk_tables/created_tmp_tables * 100% <= 25%

For example, the above server created_tmp_disk_tables/created_tmp_tables * 100% = 1.2%, should be quite good. Let's take a look at the MySQL server's 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_table_size | 536870912 |
+ ——————— + ——— –+

Only temporary tables below 256MB can be fully put in memory, and the hard disk temporary table will be used over.

V. Open table SITUATION

Mysql> show global status like ' open%tables% ';
+ ————— + ——-+
| variable_name | Value |
+ ————— + ——-+
| Open_tables | 919 |
| Opened_tables | 1951 |
+ ————— + ——-+

Open_tables represents the number of open tables, opened_tables indicates the number of tables opened, and if the opened_tables number is too large, the Table_cache in the configuration (5.1.3) Value may be too small, we 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% <= 95%

Vi. utilization 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, after the client disconnects, the server processing the client's thread will be cached in response to the next customer rather than destroyed (provided the cache count is not up to the upper limit). Threads_created represents the number of threads created, if the threads_created value is found to indicate that the MySQL server has been creating threads, which is also a resource consuming, 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 pretty healthy.

Vii. Query caching (queries 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 contiguous memory blocks in the cache. A large number indicates that there may be fragments. FLUSH QUERY Cache will defragment the cache to get a free block.
Qcache_free_memory: Free memory in cache.
Qcache_hits: Increases every time a query hits in the cache
Qcache_inserts: Increases each time you insert a query. The hit count divided by the number of inserts is an out of proportion.
Qcache_lowmem_prunes: The number of times the cache appears to be out of memory and must be cleaned up to provide space for more queries. This number is best for long periods of time; If the number is growing, it means that it may be very fragmented or that there is little memory. (The Free_blocks and free_memory above can tell you what kind of situation it belongs to)
Qcache_not_cached: The number of queries that are not suitable for caching, usually because these queries are not SELECT statements or 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 check 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 |
+ —————————— + ——— –+

Explanations for each field:

Query_cache_limit: Queries exceeding this size will not cache
Query_cache_min_res_unit: Minimum size of cache block
Query_cache_size: Query Cache Size
Query_cache_type: Cache type, determining what kind of query to cache, the example indicates that the Select Sql_no_cache query is not cached
Query_cache_wlock_invalidate: When there are other clients are writing to the MyISAM table, if the query cache, whether to return the cache results or wait for the write operation completed reread table to obtain results.

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 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 queries are small amounts of data.

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

Query cache utilization below 25% indicates that query_cache_size settings are too large to be appropriately reduced; query cache utilization is above 80% and Qcache_lowmem_prunes > 50 query_cache_ Size may be a 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 very poor, may write more frequently, and may be some fragments.

Eight, the use of the sort

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 do the sort in memory, using the memory size determined by the system variable sort_buffer_size, if it is not large enough to read all the records in memory, MySQL will be in memory every time the result of the order to save to the temporary file, such as MySQL Once all the records have been found, the records in the temporary file are sorted once. This again sort will add sort_merge_passes. In fact, MySQL will use another temporary file to store the results of the reordering, so you will often see that the sort_merge_passes increment is twice times the number of temporary files created. Because temporary files are used, the speed may be slow, 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)

In addition, increasing the value of Read_rnd_buffer_size (3.2.3 is record_rnd_buffer_size) has a little benefit for sorting operations, see also: 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 settings: Open_files/open_files_limit * 100% <= 75%

Ten, 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, preferably with the InnoDB engine, because InnoDB is row lock and MyISAM is 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 |
+ ——————— –+ ————-+

The fields are interpreted see http://hi.baidu.com/thinkinginlamp/blog/item/31690cd7c4bc5cdaa144df9c.html, 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 of more than 4000, indicating that too many table scans, it is very likely that the index has not been built, adding read_buffer_size value will have some benefits, but it is best not to exceed 8MB.

This article refers to the following Web 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 the specific value of the main reference to this tool