mysql 筆記: Innodb_rows_read 的算法

2019030411:01

在一個生產線上的 mysql 機器上
常常可以看到 reads/s 數字很大,明明每秒鐘執行的 select 命令只有幾十個(或幾百)
但 reads/s 的值都是極高..
mysql> show engine innodb status\G
::   ::   ::
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1825, Main thread ID=140577020323584, state: sleeping
Number of rows inserted 475002266, updated 8348052, deleted 1293264, read 280223166954
26.00 inserts/s, 2.83 updates/s, 0.33 deletes/s, 118617.56 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)
 
mysql> show status like '%innodb_row%';
+-------------------------------+--------------+
| Variable_name                 | Value        |
+-------------------------------+--------------+
| Innodb_row_lock_current_waits | 0            |
| Innodb_row_lock_time          | 71436        |
| Innodb_row_lock_time_avg      | 5            |
| Innodb_row_lock_time_max      | 735          |
| Innodb_row_lock_waits         | 12835        |
| Innodb_rows_deleted           | 1293264      |
| Innodb_rows_inserted          | 4750022266   |
| Innodb_rows_read              | 280227276239 |
| Innodb_rows_updated           | 8348052      |
+-------------------------------+--------------+
9 rows in set (0.01 sec)


因為
Innodb_rows_read 的算法 並非一個 select 的命令,就 +1
而是指讀取過(掃描過)多少筆資料

例如 (在一台剛啟動 mysqld 的機器上,尚無任何的讀寫)
 mysql> select count(*) from myphoto;
+----------+
| count(*) | 
+----------+ 
|     4656 | 
+----------+
1 row in set (0.22 sec) 
 
mysql> show engine innodb status\G
: : :
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 11057, id 1316209744, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 4656
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 221.70 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)
 
mysql> select * from myphoto where folder=10;     ==>folder無索引,所以是全表掃描 總共4656筆
Empty set (0.11 sec)
 
mysql> show engine innodb status\G
: : :

1 row in set (0.00 sec)

============================
END OF INNODB MONITOR OUTPUT
----------------------------
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 113.56 reads/s
==>(4656+4656 = 9312)      9312Number of rows inserted 0, updated 0, deleted 0, read
Main thread process no. 12048, id 1316217936, state: sleeping
0 read views open inside InnoDB
0 queries inside InnoDB, 0 queries in queue
--------------
ROW OPERATIONS
--------------

大概這樣
:-)





 


  •   mini 於 2019-03-12 16:04 1F
  • 我們的國家公園