Mysql show status核心参数含义

mingjie

发布于 2022-05-12 09:45:09

9720

innodb行锁

mysql> show global status like '%innodb%lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 51185 |
| Innodb_row_lock_time_avg      | 5118  |
| Innodb_row_lock_time_max      | 51003 |
| Innodb_row_lock_waits         | 10    |
+-------------------------------+-------+

Innodb_row_lock_current_waits：正在发生的等锁行数
Innodb_row_lock_time：行锁总时间（毫秒）
Innodb_row_lock_time_avg：行锁等待平均时间
Innodb_row_lock_time_max：行锁等待最大时间
Innodb_row_lock_waits：行锁等待次数

测试

-- 两个事务更新同一行
-- 事务1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update test set i=2 where i=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 事务2
mysql> update test set i=3 where i=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

-- Innodb_row_lock_waits:10----->11

innodb连接全表扫次数

mysql> show global status like '%Select_scan%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Select_scan   | 210   |
+---------------+-------+

Select_scan：只记录join中第一个表全表扫的次数

innodb临时表

mysql> show global status like '%tmp%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 11      |
| Created_tmp_files       | 56      |
| Created_tmp_tables      | 6047222 |
+-------------------------+---------+

Created_tmp_disk_tables：磁盘上创建的临时表数量
- 临时表大小超了tmp_table_size和max_heap_table_size就开始在盘上创建临时表
Created_tmp_files：磁盘上临时文件的数量
Created_tmp_tables：内存中临时表的数量

Innodb排序次数

mysql> show global status like '%sort%';
+-------------------+------------+
| Variable_name     | Value      |
+-------------------+------------+
| Sort_merge_passes | 0          |
| Sort_range        | 6047097    |
| Sort_rows         | 1209407450 |
| Sort_scan         | 6047093    |
+-------------------+------------+

Sort_merge_passes：使用临时文件完成排序操作的次数。MySQL在进行排序操作时，首先尝试在普通排序缓存中完成排序。如果缓存空间不够用，MySQL将利用缓存进行多次排序。并把每次的排序结果存放到临时文件中，最后再把临时文件中的数据做一次排序。Sort_merge_passes值就是记录了使用文件进行排序的次数。由于文件排序要牵涉到读文件，打开文件句柄，然后关闭文件等操作。所以读取文件的系统消耗比较大，通过增大普通排序缓存sort_buffer_size来减少使用临时文件排序的次数，从而增加排序的性能。
Sort_range：使用范围排序的次数
Sort_rows：排序的行数
Sort_scan：触发排序的次数，SQL执行了就加1，就算没有跑完

缓存请求次数/物理读次数

mysql> show global status like '%requests%';
+-----------------------------------+------------+
| Variable_name                     | Value      |
+-----------------------------------+------------+
| Innodb_buffer_pool_read_requests  | 3595145635 |
| Innodb_buffer_pool_write_requests | 956047096  |
+-----------------------------------+------------+

mysql> show global status like '%Innodb_buffer_pool_reads%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| Innodb_buffer_pool_reads | 184421999 |
+--------------------------+-----------+

Innodb_buffer_pool_read_requests：缓存读的次数
Innodb_buffer_pool_write_requests：缓存写的次数
Innodb_buffer_pool_reads：bp里面没有必须要从磁盘中读的次数

read命中率：

(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) /
Innodb_buffer_pool_read_requests * 100%

缓冲池flush/读写

mysql> show global status like '%Innodb_buffer_pool_pages_flushed%';
+----------------------------------+----------+
| Variable_name                    | Value    |
+----------------------------------+----------+
| Innodb_buffer_pool_pages_flushed | 53761915 |
+----------------------------------+----------+

mysql> show global status like '%data_read%';
+-------------------+---------------+
| Variable_name     | Value         |
+-------------------+---------------+
| Innodb_data_read  | 3202270515712 |
| Innodb_data_reads | 195483982     |
+-------------------+---------------+

mysql> show global status like '%data_writ%';
+---------------------+---------------+
| Variable_name       | Value         |
+---------------------+---------------+
| Innodb_data_writes  | 94264653      |
| Innodb_data_written | 2399803565056 |
+---------------------+---------------+

Innodb_buffer_pool_pages_flushed：从bp中flush的数量
Innodb_data_read：读的数据总量（字节）
Innodb_data_reads：读的总次数
Innodb_data_writes：写的总次数
Innodb_data_written：写的数据总量（字节）

fsync次数

mysql> show global status like '%fsync%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| Innodb_data_fsyncs           | 40679926 |
| Innodb_os_log_fsyncs         | 4656424  |
+------------------------------+----------+

odirect的数据直接写需要fsync刷inode cache和directory cache，日志用vfs缓冲区需要fsync刷一切

Innodb_data_fsyncs：刷数据fsync的次数
Innodb_os_log_fsyncs：刷redo日志fsync的次数

日志

mysql> show global status like '%innodb_log%';
+---------------------------+-----------+
| Variable_name             | Value     |
+---------------------------+-----------+
| Innodb_log_write_requests | 152140181 |
| Innodb_log_writes         | 4615988   |
+---------------------------+-----------+
mysql> show global status like '%Innodb_os_log_written%';
+-----------------------+-------------+
| Variable_name         | Value       |
+-----------------------+-------------+
| Innodb_os_log_written | 68112805376 |
+-----------------------+-------------+