Bug #98945 show engine innodb mutex contain mutex of buffer pool blocks
Submitted: 13 Mar 2020 17:57 Modified: 19 Mar 2020 19:35
Reporter: Zongzhi Chen (OCA) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0.* OS:Any
Assigned to: CPU Architecture:Any

[13 Mar 2020 17:57] Zongzhi Chen
As the Document said that 


"SHOW ENGINE INNODB MUTEX skips the mutexes and rw-locks of buffer pool blocks, as the amount of output can be overwhelming on systems with a large buffer pool."

However, I find  SHOW ENGINE INNODB MUTEX do contain the buffer pool mutex..

The code is in  buf_block_init()
  mutex_create(LATCH_ID_BUF_BLOCK_MUTEX, &block->mutex);

And the result from SHOW ENGINE INNODB MUTEX:

mysql> show engine innodb mutex;                                                                                                                               +--------+----------------------------+-----------------------------------------------+
| Type   | Name                       | Status                                        |
| InnoDB | BUF_POOL_LRU_LIST          | spins=978873086,waits=28193810,calls=10885171 |
| InnoDB | BUF_BLOCK_MUTEX            | spins=4788008,waits=140017,calls=220180       |
| InnoDB | DICT_SYS                   | spins=56917,waits=1311,calls=2346             |
| InnoDB | LOG_WRITER                 | spins=18916,waits=613,calls=371               |
| InnoDB | FLUSH_LIST                 | spins=430959,waits=452,calls=167969           |
| InnoDB | BUF_POOL_FLUSH_MGR         | spins=41185,waits=258,calls=31008             |
| InnoDB | DICT_PERSIST_DIRTY_TABLES  | spins=4183,waits=129,calls=176                |
| InnoDB | BUF_POOL_FREE_LIST         | spins=72303,waits=111,calls=80411             |

How to repeat:
read the code and test
[13 Mar 2020 17:58] Zongzhi Chen
Update the Synopsis
[17 Mar 2020 13:03] MySQL Verification Team
Hi Mr. zongzhi,

Thank you for your bug report.

I have ran your test case and you are correct in your conclusions.

I will verify this bug as a code bug, but will leave to others to decide whether it is documentation bug or not.

Verified as reported.
[18 Mar 2020 15:13] MySQL Verification Team
Hi Mr. zongzhi,

After a discussion with InnoDB team, it was concluded that this is not a code bug, nor is it a documentation bug.

It is not a bug, at all, because  documentation talks about block mutexes and locks not buffer pool mutexes and locks. Therefore if the output show the buffer pool mutex or lock, AFAICT it conforms to what’s in the documentation.

Not a bug.
[18 Mar 2020 15:44] Fungo Wang

| InnoDB | BUF_BLOCK_MUTEX            | spins=4788008,waits=140017,calls=220180       |

This is buf block mutex (block->mutex).

| InnoDB | sum rwlock: buf0buf.cc:781 | waits=1110328 |

This is the buf block rw lock (block->lock) (The reporter did not post it, it's at the bottom of `show engine innodb mutex` output)

According to documentation, these two rows should not be there, but they are there.
[19 Mar 2020 4:07] Sunny Bains
"However, I find  SHOW ENGINE INNODB MUTEX do contain the buffer pool mutex.." -- this claim was confusing. Buffer pool mutex is a distinctly separate mutex.

Your complaint it seems is about mentioning  "by default InnoDB prints the sum of all waits/spins for block rw-locks and mutexes because ..." in the documentation.
[19 Mar 2020 19:35] Daniel Price
Posted by developer:
The referenced documentation was updated as follows:

"SHOW ENGINE INNODB MUTEX does not list mutexes and rw-locks for each buffer pool block, as the amount of output would be overwhelming on systems with a large buffer pool. SHOW ENGINE INNODB MUTEX does, however, print aggregate BUF_BLOCK_MUTEX spin, wait, and call values for buffer pool block rw-locks and mutexes."

Thank you for the bug report.
[20 Mar 2020 12:59] MySQL Verification Team
Thank you, Daniel ......