Find out the difference between Com_Select and InnoDB_rows_read
This morning one MySQL cluster ( 4 Master ,4 Slave) , one mysql server ‘s response time raises up.
The raid config of the server
Adapter 0 -- Virtual Drive Information: Virtual Drive: 0 (Target Id: 0) Name : RAID Level : Primary-1, Secondary-0, RAID Level Qualifier-0 Size : 99.999 GB State : Optimal Strip Size : 64 KB Number Of Drives per span:2 Span Depth : 6 Default Cache Policy: WriteBack, ReadAheadNone, Direct, Write Cache OK if Bad BBU Current Cache Policy: WriteBack, ReadAheadNone, Direct, Write Cache OK if Bad BBU Access Policy : Read/Write Disk Cache Policy : Disabled Encryption Type : None Virtual Drive: 1 (Target Id: 1) Name : RAID Level : Primary-1, Secondary-0, RAID Level Qualifier-0 Size : 1.533 TB State : Optimal Strip Size : 64 KB Number Of Drives per span:2 Span Depth : 6 Default Cache Policy: WriteBack, ReadAheadNone, Direct, Write Cache OK if Bad BBU Current Cache Policy: WriteBack, ReadAheadNone, Direct, Write Cache OK if Bad BBU Access Policy : Read/Write Disk Cache Policy : Disabled Encryption Type : None
The pressure tends of this server
The first trend is MySQL Com_select , the second trend picture is MySQL InnoDB_rows_read. You can find out that the InnoDB_rows_read is larger than the counter of Com_Select.
At the beginng , it makes me confused that why the result of innodb_rows_read is bigger than com_select.
And what’s more , iostat tells me that disk is very busy and the avg response time is over 5ms. Yes, Response time is not ideal.
Finally , i observe the running sql in MySQL.
I found that a large number of this sql is running
SELECT id, user_id, subscribe_id, twoway, group_ids, gmt_create, gmt_modified FROM table_0485 WHERE user_id =376297445 ORDER BY gmt_create DESC LIMIT 0 , 2000
What’s the problem of this sql?
1. It is query 2000 rows from InnoDB, if we have 100 sql like this is running, the innodb_rows_read = 100*2000;
2. the column user_id is not necessary to select once again, because we have post the user_id . We can redure the traffic after remove user_id,just like this “SELECT id, subscribe_id, twoway, group_ids, gmt_create, gmt_modified ”
So , return to our topic “Find out the difference between Com_Select and InnoDB_rows_read”
Right, if we have some query that need over thounds row, although the com_select is little, but the InnoDB_rows_read may is large.