MySQL show command usage

MySQL has a lot of basic commands, and the show command is also one of them. In many users, the use of the show command is also prone to confusion. This article summarizes the many usage of the show command.
1. Show tables or show tables from database_name; -- displays the names of all tables in the current database.
2. Show databases; -- display the names of all databases in MySQL.
3. Show columns from table_name from database_name; or show columns from database_name.table_name; -- display the column name in the table.
4. Show grants for user_name; -- displays the permissions of a user. The result is similar to the grant command.
5. Show index from table_name; -- displays the table index.
6. Show status; -- displays information about specific system resources, such as the number of running threads.
7. Show variables; -- display the name and value of the system variable.
8. Show processlist; -- display all processes running in the system, that is, the query being executed. Most users can view their own processes, but if they have the process permission, they can view all processes, including passwords.
9. Show table status; -- displays information about each table in the currently used or specified database. The information includes the table type and the latest update time of the table.
10. Show privileges; -- display different permissions supported by the server.
11. Show create database database_name; -- displays whether the create database statement can create a specified database.
12. Show create table table_name; -- displays whether the create database statement can create a specified database.
13. Show engines; -- display available storage engines and default engines after installation.
14. Show InnoDB status; -- displays the InnoDB Storage engine status.
15. Show logs; -- displays the logs of the bdb storage engine.
16. Show warnings; -- displays errors, warnings, and notifications generated by the last executed statement.
17. Show errors; -- displays only the Errors generated by the last statement.
18. Show [Storage] engines; -- display available storage engines and default engines after installation.

 

 

Example:

 

1. display the character set used by the database connection

Show variables like '% char % ';

 

2. view various status values of the MySQL server.

Show global status;

 

3 connections

01
Mysql> show variables like 'max _ connections ';
02
+ ----------------- + ------- +
03
| Variable_name | value |
04
+ ----------------- + ------- +
05
| Max_connections | 500 |
06
+ ----------------- + ------- +
07

08
Mysql> show global status like 'max _ used_connections ';
09
+ ---------------------- + ------- +
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%)

 

4 key_buffer_size
Key_buffer_size is one of the most influential parameters on the Performance of MyISAM tables.

01
Mysql> show variables like 'key _ buffer_size ';
02
+ ----------------- + ---------- +
03
| Variable_name | value |
04
+ ----------------- + ---------- +
05
| Key_buffer_size | 67108864 |
06
+ ----------------- + ---------- +
07

08
Mysql> show global status like 'key _ read % ';
09
+ ------------------- + ---------- +
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, calculate the probability that the index does not hit the cache:
Key_cache_miss_rate = key_reads/key_read_requests * 100% = 0.27%
Increase key_buffer_size

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%)

 

 

 

Max_used_connections/max_connections * 100% = 99.6% (Ideal Value: ≈ 85%)

 

5. Open Table

1
Mysql> show global status like 'open % tables % ';
2
+ --------------- + ------- +
3
| Variable_name | value |
4
+ --------------- + ------- +
5
| Open_tables | 1024 |
6
| Opened_table | 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, query the table_cache value of the server.

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% ideal value (<= 95%)

 

6. Process usage

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:

1
Mysql> show variables like 'thread _ cache_size ';
2
+ ----------------- + ------- +
3
| Variable_name | value |
4
+ ----------------- + ------- +
5
| Thread_cache_size | 32 |
6
+ ----------------- + ------- +
9. query Cache)

01
Mysql> show global status like 'qcache % ';
02
+ ------------------------- + ---------- +
03
| Variable_name | value |
04
+ ------------------------- + ---------- +
05
| Qcache_free_blocks | 1, 2226 |
06
| Qcache_free_memory | 10794944 |
07
| Qcache_hits | 5385458 |
08
| Qcache_inserts | 1806301 |
09
| Qcache_lowmem_prunes | 433101 |
10
| Qcache_not_cached | 4429464 |
11
| Qcache_queries_in_cache | 7168 |
12
| Qcache_total_blocks | 1, 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 obtain an idle block.
Qcache_free_memory: idle memory in the cache.
Qcache_hits: increases when a query 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 is best to look at this number for a long time; if this number is growing, it indicates that the fragmentation may be very serious, or the memory is very small. (The free_blocks and free_memory above 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 server's query_cache Configuration:

01
Mysql> show variables like 'query _ cache % ';
02
+ ------------------------------ + ---------- +
03
| Variable_name | value |
04
+ ------------------------------ + ---------- +
05
| Query_cache_limit | 33554432 |
06
| Query_cache_min_res_unit | 4096 |
07
| Query_cache_size | 33554432 |
08
| Query_cache_type | on |
09
| Query_cache_wlock_invalidate | off |
10
+ ------------------------------ + ---------- +
Description of each field:

Query_cache_limit: queries exceeding this size will not be cached.
Query_cache_min_res_unit: Minimum cache block size
Query_cache_size: query the cache size
Query_cache_type: cache type, which determines the type of queries to be cached. In this example, select SQL _no_cache query is not cached.
Query_cache_wlock_invalidate: when another client is performing a write operation on the MyISAM table, if the query is in the query cache, whether to 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, this 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 utilization is below 25%, the query_cache_size setting is too large and can be appropriately reduced. If the query cache utilization 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. It is possible that write operations are frequent and there may be some fragments.

 

7 open files (open_files)

01
Mysql> show global status like 'open _ files ';
02
+ --------------- + ------- +
03
| Variable_name | value |
04
+ --------------- + ------- +
05
| Open_files | 821 |
06
+ --------------- + ------- +
07

08
Mysql> show variables like 'open _ files_limit ';
09
+ ------------------ + ------- +
10
| Variable_name | value |
11
+ ------------------ + ------- +
12
| Open_files_limit | 65535 |
13
+ ------------------ + ------- +
Suitable settings: open_files/open_files_limit * 100% <= 75%

Normal

 

8 Table locks

1
Mysql> show global status like 'table _ locks % ';
2
+ ----------------------- + --------- +
3
| Variable_name | value |
4
+ ----------------------- + --------- +
5
| Table_locks_immediate | 4257944 |
6
| Maid | 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, innoDB provides better performance for applications with high concurrent writes.

 

9 Table scans

01
Mysql> show global status like 'handler _ read % ';
02
+ ----------------------- + ----------- +
03
| Variable_name | value |
04
+ ----------------------- + ----------- +
05
| Handler_read_first | 108763 |
06
| Handler_read_key | 92813521
07
| Handler_read_next | 486650793 |
08
| Handler_read_prev | 688726 |
09
| Handler_read_rnd| 9321362 |
10
| Handler_read_rnd_next | 1, 153086384 |
11
+ ----------------------- + ----------- +
For more information about each field, see explain:

1
Mysql> show global status like 'com _ select ';
2
+ --------------- + --------- +
3
| Variable_name | value |
4
+ --------------- + --------- +
5
| Com_selected | 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.