statistics state

[Pavel Francírek]

What does mean "statisics" state of InnoDB. We have lots of threads locked in this state (this situation seem occured since 2007/01/01) and performance drops for 7000q/s at 50%CPU load to 3000-4000q/s at 100%CPU load. After upgrade from 4.0.24 to 5.0.27 the situation is even worse.

Current SHOW INNODB STATUS is like:

UPDATE clients SET hits_dnes = hits_dnes + 1 WHERE ID = '191020'
---TRANSACTION 0 63039333, not started, process no 19293, OS thread id 123682857 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 68584, query id 103781109 www1 10.0.0.1 asp statistics
SELECT celkem, kategorie, media, ignore_IP, pages, paid_level, hits_dnes, hits_multiplier FROM clients WHERE ID = '43800' LIMIT 1
---TRANSACTION 0 63039374, not started, process no 19276, OS thread id 123654178 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 68581, query id 103781169 www1 10.0.0.1 asp statistics
SELECT celkem, kategorie, media, ignore_IP, pages, paid_level, hits_dnes, hits_multiplier FROM clients WHERE ID = '89239' LIMIT 1
---TRANSACTION 0 63039371, not started, process no 19289, OS thread id 123666469 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 68579, query id 103781147 www5 10.0.0.5 asp Updating
UPDATE clients SET hits_dnes = hits_dnes + hits_multiplier WHERE ID = '19611'
---TRANSACTION 0 63039347, not started, process no 19283, OS thread id 123662372 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 68578, query id 103781186 www1 10.0.0.1 asp statistics
SELECT celkem, kategorie, media, ignore_IP, pages, paid_level, hits_dnes, hits_multiplier FROM clients WHERE ID = '247076' LIMIT 1
---TRANSACTION 0 63039331, not started, process no 19277, OS thread id 123658275 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 68575, query id 103781184 www1 10.0.0.1 asp statistics
SELECT celkem, kategorie, media, ignore_IP, pages, paid_level, hits_dnes, hits_multiplier FROM clients WHERE ID = '99579' LIMIT 1
---TRANSACTION 0 63039441, not started, process no 19274, OS thread id 123645984 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 68571, query id 103781236 www5 10.0.0.5 asp statistics
SELECT celkem, kategorie, media, ignore_IP, pages, paid_level, hits_dnes, hits_multiplier FROM clients WHERE ID = '156476' LIMIT 1
---TRANSACTION 0 63039430, not started, process no 19273, OS thread id 123641887 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 68570, query id 103781248 www1 10.0.0.1 asp statistics
SELECT celkem, kategorie, media, ignore_IP, pages, paid_level, hits_dnes, hits_multiplier FROM clients WHERE ID = '6076' LIMIT 1
---TRANSACTION 0 63038749, not started, process no 19271, OS thread id 123633693 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 68568, query id 103781185 www5 10.0.0.5 asp statistics
SELECT celkem, kategorie, media, ignore_IP, pages, paid_level, hits_dnes, hits_multiplier FROM clients WHERE ID = '159049' LIMIT 1
---TRANSACTION 0 63039531, not started, process no 19270, OS thread id 123629596
MySQL thread id 68567, query id 103781051 www1 10.0.0.1 asp
.....
--------------
ROW OPERATIONS
--------------
8 queries inside InnoDB, 234 queries in queue
34 read views open inside InnoDB
Main thread process no. 21555, id 32777, state: sleeping
Number of rows inserted 14307697, updated 10292458, deleted 2189461, read 2704833482
67.46 inserts/s, 676.26 updates/s, 85.46 deletes/s, 133783.29 reads/s


No changes in configuration.