在一個生產線上的 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 --------------
大概這樣
:-)
