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.
