preface
MySQL > MySQL > MySQL > MySQL > MySQL > MySQL
Common commands
1. Show variables View system variables
Mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql mysql
2.show status
MySQL server running statistics, such as the number of open tables, command count, qcache count, etc. Yes The system status is dynamic
Mode of operation
1. Slow query
Show variables like ‘%slow%’;
Log_show_queries Check whether slow query is enabled
Slow_launch_time Specifies the slow query time
show global status like ‘%slow%’; View the number of slow queries
show global variables like ‘slow_query_log_file’; View the file storage location
2. The number of connections
show variables like ‘max_connections’; View the maximum number of connections set
show global status like ‘Max_used_connections’; View the maximum number of connections that the server responded to
Max_used_connections/max_connections * 100% ≈ 85%
The maximum number of connections is about 85% of the maximum number of connections. If the number of connections is less than 10%, the maximum number of connections of the MySQL server is set too high.
3. Key_buffer_size shooting
Key_buffer_size is the parameter that has the greatest impact on MyISAM table performance.
mysql> show variables like 'key_buffer_size';
Value Specifies the memory allocated
show global status like 'key_read%';
Two parameters
Key_read_requests Number of index read requests in memory
Key_reads request not found in memory reads index directly from disk
Calculate the probability of missing the cache:
Key_cache_miss_rate = Key_reads/Key_read_requests * 100%
Key_cache_miss_rate is fine below 0.1% (one direct read disk per 1000 requests), but if key_cache_MISS_rate is below 0.01%, key_buffer_size is overallocated and can be reduced.
The MySQL server also provides the key_blocks_* argument:
Show global status like ‘key_blocks_u%’;
Two parameters
1.Key_blocks_unused Indicates the number of unused cache clusters (blocks)
2.Key_blocks_used Specifies the maximum number of blocks that have been used
The desired value is Key_blocks_used/(Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%
4. Temporary tables
show global status like 'created_tmp%';
Three parameters
Created_tmp_disk_tables Server automatically creates the number of temporary tables when the statement is executed
Created_tmp_files Number of temporary tables created by mysql
Created_tmp_tables Automatically creates temporary tables based on memory if the size is too large
Created_tmp_tables increases each time a temporary table is created. If a temporary table is created on disk, Created_tmp_disk_tables also increases. Created_tmp_files indicates the number of temporary files created by MySQL service.
Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%
MySQL > alter table temp;
Show variables where Variable_name in (‘ tmp_table_size ‘, ‘max_heap_table_size’);
max_heap_table_size
tmp_table_size
5. Open the Table
show global status like 'open%tables%';
Two parameters
Open_tables Number of open tables
Opened_tables Number of opened tables
If Opened_tables is too large, the table_cache value may be too small. If Opened_tables is too large, the table_cache value may be too small.
Mysql > show variables like ‘table_cache’;
Suitable values are:
Open_tables / Opened_tables * 100% >= 85%
Open_tables / table_cache * 100% <= 95%
6. Thread usage
show global status like ‘Thread%’;
Four parameters
Threads_cached
Threads_connected
Threads_created
Threads_running
If we set thread_cache_size in the MySQL server configuration file, when a client is disconnected, the server threads that handle that client will be cached in response to the next client rather than destroyed (if the number of caches has not reached the upper limit). Threads_created specifies the number of threads created. If Threads_created is too large, it indicates that the MySQL server is constantly creating threads. This is also expensive. Query server thread_cache_size configuration:
Mysql > show variables like ‘thread_cache_size’;
7. Query cache
show global status like 'qcache%';
variable
Qcache_free_blocks: number of adjacent memory blocks in the cache. A large number suggests possible fragments. FLUSH QUERY CACHE defragment the CACHE to produce a free block.
Qcache_free_memory: free memory in the cache.
Qcache_hits: increases each time a query is hit in the cache
Qcache_inserts: Grows each time a query is inserted. The number of hits divided by the number of inserts is the miss ratio.
Qcache_lowmem_prunes: The number of times the cache was out of memory and had to be cleaned up to make room for more queries. This number is best seen over a long period of time; If this number is growing, it could be very fragmented or with very little memory. (Free_blocks and free_memory above 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: number of blocks in the cache.
Query_cache configuration:
Query_cache_limit: Queries larger than this size will not be cached
Query_cache_min_res_unit: minimum size of cache block
Query_cache_size: Queries the cache size
Query_cache_type: Cache type. This type determines what queries to cache. In this example, this type does not cache select SQL_no_cache
Query_cache_wlock_invalidate: When another client writes to a MyISAM table, the query in query Cache returns the cache result or reads the table to obtain the result after the write operation is complete.
The query_cache_MIN_RES_unit configuration is a double-edged sword. The default value is 4KB, which is good for big data queries, but if your queries are all small data queries, it can cause memory fragmentation and waste.
Query cache fragmentation rate = Qcache_free_blocks/Qcache_total_blocks * 100%
If the QUERY CACHE fragmentation rate exceeds 20%, FLUSH QUERY CACHE to clean up CACHE fragmentation, or try reducing query_cache_min_res_unit if your queries are small.
Query_cache_size = (query_cache_size -qcache_free_memory)/query_cache_size * 100% If the query_cache_size is below 25%, the query_cache_size is too large. Can be appropriately reduced; If the query cache utilization is above 80% and Qcache_lowmem_prunes > 50, query_cache_size may be a bit small or too fragmented.
Query cache hit ratio = (Qcache_hits – Qcache_inserts)/Qcache_hits * 100% Sample server query cache fragmentation ratio = 20.46%, query cache utilization = 62.26%, query cache hit ratio = 1.94%, Poor hit ratio, probably write frequently, and probably some fragmentation.
8. Sort usage
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. The amount of memory used is determined by the system variable Sort_buffer_size. If it is not large enough to read all the records in memory, MySQL will store the results of each in-memory sort in a temporary file. After MySQL has found all the records, Sort the records in the temporary file again. This reordering increases Sort_merge_passes. In fact, MySQL uses another temporary file to store the results of the reorder, so it’s common to see Sort_merge_passes increase by twice the number of temporary files. It can be slow because temporary files are used, increasing Sort_buffer_size will reduce Sort_merge_passes and temporary file creation times, but blindly increasing Sort_buffer_size will not necessarily increase speed
9. 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%
10. Table locks
mysql> 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 released immediately. Table_locks_waited indicates the number of table locks to be waited.
If Table_locks_immediate/Table_locks_waited>5000, it is better to use InnoDB engine, because InnoDB is row lock and MyISAM is table lock, InnoDB will have better effect for high concurrent write applications.
The server Table_locks_immediate/Table_locks_waited = 235, MyISAM in the example is sufficient.
11. Table scanning status
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 | + — — — — — — — — — — — — — — — — — — — — — — – — — — — — — — — — — — — + 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 is over 4000, there are too many table scans and the index is most likely not built properly. Increasing the read_BUFFer_SIZE value will have some benefit, but it is best not to exceed 8MB.
12.QPS (Queries per second)
Show global status like ‘Questions’// Number of queries
Show global status like ‘Uptime’// Query the current running time of MySQL after the current startup. Divide the two values to obtain the query quantity per second
TPS(Transactions per second)
TPS = (Com_commit + Com_rollback) / seconds
Mysql > show global status like ‘Com_commit’;
Mysql > show global status like ‘Com_rollback’;
14) Innodb cache hit ratio
Show status like ‘Innodb_buffer_pool_ %’;
Innodb_buffer_pool_read_requests indicates the number of read requests, and Innodb_buffer_pool_reads indicates the number of read requests from physical disks.
(Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests * 100%.
In general, the hit ratio should not be lower than 99%. If it is lower than this, consider increasing the InnoDB buffer pool.
In addition, the Innodb_buffer_pool_pages_total parameter indicates the total number of cached pages (16K per page, so a total of 8M), Innodb_buffer_pool_pages_data indicates the number of cached pages with data, Innodb_buffer_pool_pages_free represents the number of cached pages that are not in use. If Innodb_buffer_pool_pages_free is too large, it indicates that a lot of the cache is not being used, so reduce the cache. If Innodb_buffer_pool_pages_data is too large, increase the cache.
The default value is 8M. Generally speaking, this value is not enough. It is recommended to set the system memory to 50%-80%, but the larger the better, depending on the project (about 1G for the operating system, 4M for mysql connections, and nM for host program cache). Innodb_buffer_pool_size =3G /etc/my.cnf/innodb_buffer_pool_size=3G
The last
I here organized a MySQL related information documents, Spring series of family barrel, Java systematic information (including Java core knowledge points, interview topics and 20 years of the latest Internet real questions, e-books, etc.) friends who need to pay attention to the public number can be obtained.