Detailed mysql runtime parameters through showvariableslikexxx

Using showvariableslikexxx to explain mysql runtime parameters, refer to the following webpage: runtime 3. www. ibm. comworkflow work

Through show variables like xxx detailed mysql runtime parameters this article reference 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/developerwork

Use show variables like xxx to explain mysql runtime Parameters

Refer to the following webpage:
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. The specific value of the http://www.day32.com/MySQL/tuning-primer.sh mainly refer to this tool

?

1. Check the MySQL server configuration information?

Java code ??

1. Mysql>? Show? Variables ;??

2. How many Status values does the MySQL server run?

Java code ??

1. Mysql>? Show? Global? Status ;??

3. Slow query?

Java code ??

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

Slow query of records is disabled in the configuration (it is best to enable it to facilitate optimization). If it takes more than 2 seconds, it is slow query. Is there 279 slow queries in total?

4. Number of connections?

Java code ??

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 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?
Key_buffer_size is a parameter that has the greatest impact on the performance of MyISAM tables. However, most of the databases are Innodb?

Java code ??

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, 66071 of which are not found in the memory to directly read the index from the hard disk. The probability that the index does not hit the cache is calculated :?
Key_cache_miss_rate = Key_reads/Key_read_requests * 100% = 0.27%?
Need to increase the key_buffer_size?

Java code ??

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 indicates the number of unused cache clusters (blocks), and Key_blocks_used indicates the maximum number of blocks used?
Key_blocks_used/(Key_blocks_unused + Key_blocks_used) * 100% ≈ 18% (Ideal Value ≈ 80% )?

6. Temporary table?

Java code ??

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

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 also added. Created_tmp_files indicates the number of temporary file files created by the MySQL service :?
Created_tmp_disk_tables/Created_tmp_tables * 100% = 99% (ideal value <= 25% )?

Java code ??

1. Mysql>? 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. What about open table?

Java code ??

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 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, let's query the server table_cache value?

Java code ??

1. Mysql>? Show? Variables? Like? 'Table _ cache ';??
2. + --------------- + ------- + ??
3. |? Variable_name? |? Value? | ??
4. + --------------- + ------- + ??
5. |? Table_cache ??? |? 1024 ?? | ??
6. + --------------- + ------- + ??

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

8. Process usage?

Java code ??

1. Mysql>? 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 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 :?

Java code ??

1. Mysql>? Show? Variables? Like? 'Thread _ cache_size ';??
2. + ------------------- + ------- + ??
3. |? Variable_name ????? |? Value? | ??
4. + ------------------- + ------- + ??
5. |? Thread_cache_size? |? 32 ???? | ??
6. + ------------------- + ------- + ??

9. query cache )?

Java code ??

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: 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 get a free block .?
Qcache_free_memory: idle memory in the cache .?
Qcache_hits: Does the query increase when it hits the cache?
Qcache_inserts: It 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. It is generally because these queries are not SELECT statements or use functions such as now .?
Qcache_queries_in_cache: number of queries (and responses) cached currently .?
Qcache_total_blocks: Number of cached blocks .?

Let's query the query_cache configuration on the server :?

Java code ??

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

Description of each field :?

Query_cache_limit: Will queries exceeding this size not be cached?
Query_cache_min_res_unit: What is the minimum size of the cache block?
Query_cache_size: query the cache size?
Query_cache_type: Specifies the cache type. In this example, select SQL _no_cache query is not cached?
Query_cache_wlock_invalidate: when another client is performing write operations on the MyISAM table, if the query is in the query cache, will it 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, it 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 Usage is below 25%, the query_cache_size setting is too large and can be appropriately reduced. If the query Cache Usage 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. Maybe write operations are frequent, and there may be some fragments .?

10. Sorting usage?

Java code ??

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 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 open files (open_files )?

Java code ??

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

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

Normal?

12. Table lock status?

Java code ??

1. Mysql>? 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, 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, the InnoDB performance will be better for applications with high concurrent writes .?

13. What are the table scans?

Java code ??

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

For more information about each field, see explain :?

Java code ??

1. Mysql>? Show? Global? Status? Like? 'Com _ select ';??
2. + --------------- + --------- + ??
3. |? Variable_name? |? Value ??? | ??
4. + --------------- + --------- + ??
5. |? Com_select ???? |? 2693147? | ??
6. + --------------- + --------- + ??

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.

----------------------------------------------