Before one of the children's shoes hair:
5 version of the mail, in the case of large user volume, if done distributed, if on the back end MySQL on the execution:mysql> show global status like ' thread% ';
threads_cached 0
Threads_connected 793
Threads_created 2397086
Threads_running 260See the value of threads_created is particularly large, threads_cached is 0, and at this time MySQL connection number and load is particularly high case, must be in the my.cnf with the following parameters: thread_cache_sizestop each front end before restarting the backend MySQL.you will find that the total number of connections for each front-end machine MySQL is much smaller than the number of connections to the backend MySQL, because the connections are cached off.MySQL optimization notes:from slow query, number of connections, key_buffer_size, temp table, open table case, process usage, query cache, sort usage, File open number, table lock condition, table scan conditionthese aspects to optimize
==================================================================================== 1. Slow query:
Number of slow queries: mysql> show global status like '%slow_queries ';
Slow query log switch: mysql> show global variables like '%log_slow_queries '; (1) Turn on slow query log: my.cnf
Long_query_time = 5//execute over how long the SQL will be log down, here is 5 seconds
Log-slow-queries =/var/log/slowquery.log//set to write the log there
log-queries-not-using-indexes//Record SQL not using an index
Restart MySQL after configuration. (2) Analysis log: Mysqldumpslow-s, is the order, the main c,t,l,r and Ac,at,al,ar, respectively, according to the number of query, time, lock time and the number of records returned to order, preceded by a in reverse.
-T, which is the meaning of top N, which is to return the data of the previous number of bars
-G, you can write a regular match pattern behind, case insensitive mysqldumpslow-s c-t 20/var/log/slowquery.log//20 most visited SQL statements
Mysqldumpslow-s t-t 20/var/log/slowquery.log//Execution time up to 20 SQL statements
Mysqldumpslow-s r-t 20/var/log/slowquery.log//return record set up to 20 SQL
Mysqldumpslow-t 10-s t-g "left join"/var/log/slowquery.log//This returns the first 10 of the SQL statements that contain the links in the previous section. ================================================================================= 2. Number of connections
Maximum number of connections configuration: Show global variables like ' max_connections ';
The maximum number of connections used: Show global status like ' Max_used_connections ';
Current number of connections: Show global status like ' threads_connected ';
Number of threads used to process connections: show global status like ' threads_created '; Max_used_connections/max_connections * 100% (ideal value ≈85%) modify the maximum number of connections configuration:
Mysql> set GLOBAL max_connections=1000;
Mysql> Flush Privileges
You can also modify the max_connections in/ETC/MY.CNF:
Max_connections = max_used_connections:the Maximum number of connections that has been in use simultaneously sin Ce the server started. The maximum number of concurrent joins in the history data since MySQL was booted this time.
Threads_created:the number of Threads created to handle connections. ======================================================================================== 3. The Key_buffer_size Key_buffer_size is one of the most influential parameters for MyISAM table performance, and the InnoDB table does not apply. Mysql> show global variables like ' key_buffer_size ';
Mysql> show global Status where Variable_name in (' key_read_requests ', ' key_reads ');
Key_read_requests 737517278
Key_reads 276013
Mysql> Select 276013/737517278*100;
0.0374 A total of 737,517,278 index read requests, 276,013 requests were 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.0374%
If the miss probability is too large, you can increase the key_buffer_size
Mysql> show global Status where Variable_name in (' key_blocks_used ', ' key_blocks_unused ');
Key_blocks_unused 131673
Key_blocks_used 217227
Mysql> Select 217227/(131673+217227) *100;
62.2605 key_blocks_unused represents the number of unused cache clusters (blocks), and key_blocks_used indicates the maximum number of blocks that have ever been used
Key_blocks_used/(key_blocks_unused + key_blocks_used) * 100%≈62.2605% (ideal value ≈80%) ================================== ====================================================== temporary table
Mysql> show global status like ' created_tmp% ';
Created_tmp_disk_tables 1
Created_tmp_files 16
Created_tmp_tables 1145 each time a temporary table is created, created_tmp_tables increases, and created_tmp_disk_tables increases if a temporary table is created on disk; Created_tmp_files indicates the number of temporary file files created by the MySQL service: created_tmp_disk_tables/created_tmp_tables * 100% = 0.0873% (ideal value <= 25%)
Mysql> Show variables where variable_name in (' Tmp_table_size ', ' max_heap_table_size ');
Max_heap_table_size 16777216
Tmp_table_size 33554432 can be suitably increased tmp_table_size
======================================================================================== Open Table Condition
Mysql> show global status like ' open%tables% ';
Open_tables 512
Opened_tables 2008
Mysql> Show variables like '%table_cache% ';
Table_cache mysql> Select 512/2008*100;
25.4980
Mysql> Select 512/512*100;
100.0000 Open_tables represents the number of open tables, Opened_tables represents the number of open tables.
If the number of opened_tables is too large, it may be too small for table_cache (the value called Table_open_cache after 5.1.3) in the configuration. Open_tables/opened_tables * 100% = 38.5196% Ideal Value (>= 85%)
Open_tables/table_cache * 100% = 99.6094% Ideal Value (<= 95%) =========================================================== ============================= process Usage mysql> show global status like ' thread% ';
Threads_cached 7
threads_connected 112
Threads_created 2397
Threads_running mysql> Show variables like ' thread_cache_size ';
Thread_cache_size 64 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, and 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, and can appropriately increase the thread_cache_size value in the configuration file. ======================================================================================== Query Cache
Mysql> show global status like ' qcache% ';
Qcache_free_blocks 4119
Qcache_free_memory 11911976
Qcache_hits 873527
Qcache_inserts 1267423
Qcache_lowmem_prunes 713698
Qcache_not_cached 895888
Qcache_queries_in_cache 11301
Qcache_total_blocks 27096 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 check the server's configuration for Query_cache: mysql> show variables like ' query_cache% ';
Query_cache_limit 1048576
Query_cache_min_res_unit 4096
Query_cache_size 33554432
Query_cache_type on
Query_cache_wlock_invalidate OFF 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.
mysql> Select 4119/27096 *100;
15.2015
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.
Mysql> Select (33554432-11911976)/33554432 *100;
64.4995
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.
======================================================================================== Sort Usage mysql> Show Global status like ' sort% ';
Sort_merge_passes 2
Sort_range 18528
Sort_rows 390838
Sort_scan 3439 MySQL first tries to sort in memory, the memory size is determined by the system variable sort_buffer_size, and if it is not large enough to read all of the records into memory, MySQL will save the results of each sort in memory To the temp file, wait until MySQL finds all the records, and then sort the records in the temp file 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, seemingly by the wall)
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/
MySQL Tuning reference notes
