MYSQL 8 優化之01 (show status 参数)

show status 参数详解

1. 通過show status 命令了解各種SQL的執行頻率

#語法
show [session | global ] status 可以提供服務器狀態信息

#連接信息
root@127.0.0.1 : testdb【04:13:32】235 SQL->show status like 'Conn%';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| Connection_errors_accept          | 0     |
| Connection_errors_internal        | 0     |
| Connection_errors_max_connections | 0     |
| Connection_errors_peer_address    | 0     |
| Connection_errors_select          | 0     |
| Connection_errors_tcpwrap         | 0     |
| Connections                       | 16    |
+-----------------------------------+-------+

2. 慢查询

root@127.0.0.1 : testdb【05:27:02】249 SQL->show variables like '%slow%';
+---------------------------+-------------------------------+
| Variable_name             | Value                         |
+---------------------------+-------------------------------+
| log_slow_admin_statements | OFF                           |
| log_slow_extra            | OFF                           |
| log_slow_slave_statements | OFF                           |
| slow_launch_time          | 2                             |
| slow_query_log            | ON                            |
| slow_query_log_file       | /usr/local/mysql/log/slow.log |
+---------------------------+-------------------------------+

#耗时大于long_query_time 记录在 slow_query_log_file   文件中
root@127.0.0.1 : (none)【10:37:09】5 SQL->show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.100000 |
+-----------------+----------+

root@127.0.0.1 : testdb【05:29:27】250 SQL->show global status like '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 30    |
+---------------------+-------+

[root@XAG816 ~]# mysqldumpslow /usr/local/mysql/log/slow.log

Reading mysql slow query log from /usr/local/mysql/log/slow.log
Count: 1  Time=0.26s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  insert into test_text select * from test_text

3. 连接数

#经常会遇见”MySQL: ERROR 1040: Too manyconnections”的情况，一种是访问量确实很高，MySQL服务器抗不住，
#这个时候就要考虑增加从服务器分散读压力，另外一种情况是MySQL配置文件中max_connections值过小：
root@127.0.0.1 : testdb【05:33:06】6 SQL->show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 2000  |
+-----------------+-------+
#查詢MySQL服务器过去的最大连接数
root@127.0.0.1 : testdb【05:34:21】7 SQL->show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 2     |
+----------------------+-------+

4. Key_buffer_size

#key_buffer_size是对MyISAM表性能影响最大的一个参数，下面一台以MyISAM为主要存储引擎服务器的配置：
#可通过修改 my.cnf配置文件修改参数【[mysqld]下面】 key_buffer_size = 512M
root@127.0.0.1 : testdb【05:37:01】14 SQL->show variables like 'key_buffer_size';
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
#如上顯示分配了8MB内存给key_buffer_size，我们再看一下key_buffer_size的使用情况：
#对于一般 MyISAM 数据库建议将至少1/4的可用内存分配给key_buffer_size 
#一般来讲 索引块物理读比率 Key_reads / Key_read_requests 应小于0.01
#索引块物理写比率  Key_writes / Key_write_requests 应尽可能小，但与应用特点有关，
#对于更新和删除特别多的应用 索引块物理写比率 可能会接近 1
#对于每次更新很多行记录的应用 索引块物理写比率就会比较小
#通过评估 key buffer的使用率来判断索引缓存设置的是否合理
#key buffer 使用率计算公式：1-((key_blocks_unused*key_cache_block_size)/key_buffer_size)
#一般来讲key buffer 使用率在 80%左右比较合理，大于80%可能因索引缓存不足而导致性能下降，小于80%会导致内存浪费
root@127.0.0.1 : testdb【12:08:24】4 SQL->show global status like 'key_%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0     |
| Key_blocks_unused      | 6698  |
| Key_blocks_used        | 0     |
| Key_read_requests      | 0     |
| Key_reads              | 0     |
| Key_write_requests     | 0     |
| Key_writes             | 0     |
+------------------------+-------+
root@127.0.0.1 : testdb【12:14:07】8 SQL->show variables like 'key_cache_block_size';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| key_cache_block_size | 1024  |
+----------------------+-------+

5. 臨時表

root@127.0.0.1 : testdb【05:37:49】16 SQL->show global status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 43    |
| Created_tmp_tables      | 33    |
+-------------------------+-------+
#每次创建临时表，Created_tmp_tables增加，如果是在磁盘上创建临时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数，比较理想的配置是：
Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%
比如上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% =0/33＝ 0 %，应该相当好了。

#我们再看一下MySQL服务器对临时表的配置：
#tmp_table_size：
#内存中临时表的最大值。不负责限制MEMORY/HEAP表最大容量，如果执行SQL产生
#临时表超过 tmp_table_size/max_heap_table_size，则会产生基于磁盘的MyISAM表

#max_heap_table_size
#负责设置MEMORY/HEAP表最大容量，不管其他执行SQL产生的临时表，如果内存不够用，则不允许写入新的数据，
#MEMORY/HEAP表也不会转成磁盘表，只会告警超限后拒绝写入
#这个变量支持动态改变，即set @max_heap_table_size=#
root@127.0.0.1 : testdb【05:40:00】17 SQL->show variables where Variable_name in ('tmp_table_size', 'max_heap_table_size');
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
| tmp_table_size      | 16777216 |
+---------------------+----------+
#起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果内存临时表超出了限制(以上參數說明只有16MB以下的临时表才能全部放内存，超过的就会用到硬盘临时表。)，
#MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下
root@127.0.0.1 : testdb【06:15:10】18 SQL->show variables like "tmpdir";
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| tmpdir        | /usr/local/mysql/tmp |
+---------------+----------------------+

6. Open Table情况

root@127.0.0.1 : testdb【06:30:37】28 SQL->show global status like 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 210   |
| Opened_tables | 330   |
+---------------+-------+
#Open_tables 表示打开表的数量，Opened_tables表示打开过的表数量，如果Opened_tables数量过大，
#说明配置中 table_cache(5.1.3之后这个值叫做table_open_cache)值可能太小，我们查询一下服务器table_cache值：
root@127.0.0.1 : testdb【06:31:05】30 SQL->show variables like 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 4000  |
+------------------+-------+
#比较合适的值为：
Open_tables / Opened_tables   * 100% >= 85%              #本次  210/330 =63.6%
Open_tables / table_open_cache * 100% <= 95%           #本次 210/4000=5%

7. 线程使用情况

root@127.0.0.1 : testdb【06:59:26】35 SQL->show global status like 'Thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 1     |
| Threads_connected | 1     |
| Threads_created   | 2     |
| Threads_running   | 2     |
+-------------------+-------+
#如果我们在MySQL服务器配置文件中设置了thread_cache_size，当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户 
#而不是销毁（前提是缓存数未达上限）。Threads_created表示创建过的线程数，如果发现Threads_created值过大的话，
#表明 MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，查询服务器 thread_cache_size配置：

root@127.0.0.1 : testdb【06:59:28】36 SQL->show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 28    |
+-------------------+-------+

8. 排序使用情况

root@127.0.0.1 : testdb【07:12:31】51 SQL->show global status like 'sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 0     |
| Sort_rows         | 45    |
| Sort_scan         | 6     |
+-------------------+-------+
#Sort_merge_passes 包括两步。MySQL 首先会尝试在内存中做排序，使用的内存大小由系统变量Sort_buffer_size 决定，
#如果它的大小不够把所有的记录都读到内存中，MySQL 就会把每次在内存中排序的结果存到临时文件中，等MySQL 找到所有记录之后，
#再把临时文件中的记录做一次排序。这再次排序就会增加 Sort_merge_passes。实际上，MySQL会用另一个临时文件来存再次排序的结果，
#所以通常会看到 Sort_merge_passes增加的数值是建临时文件数的两倍。因为用到了临时文件，所以速度可能会比较慢，
#增加 Sort_buffer_size 会减少Sort_merge_passes 和 创建临时文件的次数，但盲目的增加Sort_buffer_size 并不一定能提高速度
root@127.0.0.1 : testdb【07:14:43】54 SQL->show variables like 'Sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+

9. 文件打开数(open_files)

root@127.0.0.1 : testdb【07:12:32】52 SQL->show global status like 'open_files';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_files    | 5     |
+---------------+-------+

root@127.0.0.1 : testdb【07:13:02】53 SQL->show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 65535 |
+------------------+-------+
#比较合适的设置：Open_files / open_files_limit * 100% <= 75％

10. 表锁情况

root@127.0.0.1 : testdb【07:23:54】58 SQL->show global status like 'table_locks%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 52    |
| Table_locks_waited    | 0     |
+-----------------------+-------+
#Table_locks_immediate 表示立即释放表锁数，Table_locks_waited表示需要等待的表锁数，
#如果Table_locks_immediate / Table_locks_waited >5000，最好采用InnoDB引擎，
#因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些。

11. 表扫描情况

root@127.0.0.1 : testdb【07:26:27】61 SQL->show global status like 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 79    |
| Handler_read_key      | 3670  |
| Handler_read_last     | 0     |
| Handler_read_next     | 5303  |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 45    |
| Handler_read_rnd_next | 46116 |
+-----------------------+-------+

root@127.0.0.1 : testdb【07:26:29】62 SQL->show global status like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 68    |
+---------------+-------+
#计算表扫描率：
#表扫描率 ＝ Handler_read_rnd_next / Com_select
#如果表扫描率超过4000，说明进行了太多表扫描，很有可能索引没有建好，
#增加read_buffer_size值会有一些好处，但最好不要超过8MB。
#本次 46116/68 =678

root@127.0.0.1 : testdb【07:30:08】65 SQL->show variables like 'read_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| read_buffer_size | 131072 |
+------------------+--------+