What is wrong here (the part of SHOW INNODB STATUS):
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.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.