August 21, 2014

Queries Active vs Transactions Active

What is wrong here (the part of SHOW INNODB STATUS):

————–
ROW OPERATIONS
————–
8 queries inside InnoDB, 9 queries in queue
100 read views open inside InnoDB

It is relationship between queries active – queries inside innodb+queries in the queue totalling 17 with “read views open inside InnoDB” which is a fancy way of saying “active transactions” which is 100.

Typically you would want this ratio to be close to 1, may be 2 which would correspond all active transactions doing active work and spending little time waiting on the application. Waiting transactions are bad because they hold lock as well as other resources, such as preventing innodb purge operation from proceeding.

If you see something like this in your envinronment it often makes sense to check the the list of transactions too. Chances are you would see something like:

—TRANSACTION 0 1044183147, ACTIVE 963 sec, process no 27713, OS thread id 1227987264 waiting in InnoDB queue

which corresponds to transaction active for over 15 minutes.

Sometimes you would see some query associated with such transactions showing up often in the processlist, which often corresponds to some long batch jobs, sometimes you would just see the processlist showing connection is in “Sleep” mode for a long time which means application is holding transaction open while waiting for something else. Most of such situations are bugs either in application itself or connection pooling software.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Speak Your Mind

*