MySQL Bugs: #98945: show engine innodb mutex contain mutex of buffer pool blocks

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:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0.*	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
As the Document said that 

https://dev.mysql.com/doc/refman/8.0/en/show-engine.html

"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

Update the Synopsis

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.

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.

Hi, 

| 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.

"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.

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.

Thank you, Daniel ......