mysql状态分析

MySQL 状态分析语句

打开慢查询日志

show global status like '%slow%';

• mysql> show global status like '%slow%';
  +---------------------+-------+
  | Variable_name       | Value |
  +---------------------+-------+
  | Slow_launch_threads | 0     |
  | Slow_queries        | 0     |
  +---------------------+-------+
  2 rows in set (0.00 sec)
  
  mysql> 
  

查看MySQL服务器的最大连接数

show variables like 'max_connections';

• mysql> show variables like 'max_connections';
  +-----------------+-------+
  | Variable_name   | Value |
  +-----------------+-------+
  | max_connections | 151   |
  +-----------------+-------+
  1 row in set (0.00 sec)
  
  mysql> 
  

查看MySQL服务器过去的最大连接数

show global status like 'MAX_used_connections';

• mysql> show global status like 'MAX_used_connections';
  +----------------------+-------+
  | Variable_name        | Value |
  +----------------------+-------+
  | Max_used_connections | 2     |
  +----------------------+-------+
  1 row in set (0.00 sec)
  

查看打开表的数量

show gobal status like 'Open%tables%';

• mysql> show global status like 'Open%tables%';
  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | Open_tables   | 103   |
  | Opened_tables | 110   |
  +---------------+-------+
  2 rows in set (0.01 sec)
  
  

  • 其中Open_tables为打开表的数量
  • Opened_tables表示打开过表的数量

查看线程使用情况

show global status like 'Thread%';

• mysql> show global status like 'Thread%';
  +-------------------+-------+
  | Variable_name     | Value |
  +-------------------+-------+
  | Threads_cached    | 0     |
  | Threads_connected | 2     |
  | Threads_created   | 2     |
  | Threads_running   | 1     |
  +-------------------+-------+
  4 rows in set (0.00 sec)
  

查询缓存使用情况

• query cache

show global status like 'qcache%';

• mysql> show global status like 'qcache%';
  +-------------------------+----------+
  | Variable_name           | Value    |
  +-------------------------+----------+
  | Qcache_free_blocks      | 1        |
  | Qcache_free_memory      | 16760152 |
  | Qcache_hits             | 0        |
  | Qcache_inserts          | 0        |
  | Qcache_lowmem_prunes    | 0        |
  | Qcache_not_cached       | 4        |
  | Qcache_queries_in_cache | 0        |
  | Qcache_total_blocks     | 1        |
  +-------------------------+----------+
  8 rows in set (0.01 sec)
  

• 查询query_cache的配置

• show variables like 'qurey_cache%';
  

• mysql> show variables like 'query_cache%';
  +------------------------------+----------+
  | Variable_name                | Value    |
  +------------------------------+----------+
  | query_cache_limit            | 1048576  |
  | query_cache_min_res_unit     | 4096     |
  | query_cache_size             | 16777216 |
  | query_cache_type             | OFF      |
  | query_cache_wlock_invalidate | OFF      |
  +------------------------------+----------+
  5 rows in set (0.00 sec)
  

查看文件打开数

show global status like 'open_files';

• mysql> show global status like 'open_files';
  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | Open_files    | 14    |
  +---------------+-------+
  1 row in set (0.00 sec)
  

查看打开文件数限制

show variables like 'open_files_limit';

• mysql> show variables like 'open_files_limit';
  +------------------+-------+
  | Variable_name    | Value |
  +------------------+-------+
  | open_files_limit | 5000  |
  +------------------+-------+
  1 row in set (0.00 sec)
  
  

查看表锁情况

show global status like 'table_locks%';

• mysql> show global status like 'table_locks%';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | Table_locks_immediate | 123   |
  | Table_locks_waited    | 0     |
  +-----------------------+-------+
  2 rows in set (0.00 sec)
  

查看表扫描情况

show global status like 'handler_read%';

• mysql> show global status like 'handler_read%';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | Handler_read_first    | 8     |
  | Handler_read_key      | 6     |
  | Handler_read_last     | 0     |
  | Handler_read_next     | 2     |
  | Handler_read_prev     | 0     |
  | Handler_read_rnd      | 0     |
  | Handler_read_rnd_next | 21059 |
  +-----------------------+-------+
  7 rows in set (0.00 sec)
  

------------------end----------------------

PS:整理针对Skills

参考：mysql状态分析之show global status - davygeek - 博客园 (cnblogs.com)