MySQL Bugs: #87494: "ROW OPERATIONS" is incorrect for SHOW ENGINE INNODB STATUS

Bug #87494	"ROW OPERATIONS" is incorrect for SHOW ENGINE INNODB STATUS
Submitted:	21 Aug 2017 15:20	Modified:	22 Sep 2017 14:00
Reporter:	Jennifer Smith	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.18	OS:	Red Hat (RHEL7)
Assigned to:		CPU Architecture:	Any

Description:
Currently all processes are in a "Sleep" state in performance_schema.threads.

But SHOW ENGINE INNODB STATUS shows the following...

--------------
ROW OPERATIONS
--------------
21 queries inside InnoDB, 5 queries in queue
0 read views open inside InnoDB
Process ID=14671, Main thread ID=139729360152320, state: sleeping
Number of rows inserted 13353863, updated 255566, deleted 6577, read 826874022
19.53 inserts/s, 0.31 updates/s, 0.00 deletes/s, 37077.84 reads/s

I will attach the full output.

Since the database thinks there are 5 queries in the queue, if I set innodb_thread_concurrency <= 5 then all future queries will queue indefinitely.

I've since just changed innodb_thread_concurrency = 0 to prevent the issue.

How to repeat:
The database has consistently been in this state for days.

Suggested fix:
I suspect that SHOW ENGINE INNODB STATUS is showing wrong information.

SHOW ENGINE INNODB full output

Attachment: innodb_status_08212017.out (application/octet-stream, text), 70.93 KiB.

Hi!

On the live server, status can change very, very fast. Try to reproduce the case that you are reporting, but in a different manner. First run:

SHOW FULL PROCESSLIST

then :

SHOW ENIGNE INNODB STATUS

then again processlist.

Try to see if those are comparable. You should all execute them simultaneously, with a single shell command.

InnoDB concurrency is set in accordance with CPU and I/O system that you have. This setting has nothing to do with a speed of executing five consecutive queries, provided that those are optimised queries that execute fast. 

You can run 5  long-running and never-ending queries and then, indeed, sixth query will never be processed. This, however is not a bug, but badly designed application and badly configured installation.

Hi Sinisa,

I think maybe I didn't explain this correctly.

The SHOW ENGINE INNODB STATUS always shows...
21 queries inside InnoDB, 5 queries in queue

Even if nothing is running.  I did a show processlist both before and after.
And there were no actively running queries.

Thanks,
Jenn

Results of show processlist & show engine innodb status

Attachment: 87494_bugreport.txt (text/plain), 936.17 KiB.

Hi!

I was not able to repeat your situation at all.

There is probably some mis-configuration. If you are using thread - cache, then don't. Clean up your configuration and use our binary.

After you do that if you experience the same behaviour, then send us your global configuration file.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".