| Bug #99003 | show engine innodb mutex should avoid the rw_lock_list_mutex | ||
|---|---|---|---|
| Submitted: | 20 Mar 2020 10:28 | Modified: | 1 Apr 2020 9:41 |
| Reporter: | Zongzhi Chen (OCA) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 8.0.* | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[20 Mar 2020 10:28]
Zongzhi Chen
[20 Mar 2020 13:37]
MySQL Verification Team
Hello Mr. zongzhi, Thank you for your bug report. However, we need so much feedback from you regarding your report. First of all, hash0hash.cc has (in 8.0.17) only 184 lines, so we can not have OS waits at that line. Next, I have run a sysbench and I did not manage to get that output. So, how exactly have your run a sysbench ??? Next, you are proposing a remedy for the situation, which is quite unclear. Next, innodb_show_mutex_status DOES need to hold a mutex , since no thread should create or destroy mutext during the run of that function. Last, but not least, you wrote: " I suggest the rwlock change way as mutex, such as register every rwlock a name like mysql_pfs_key_t trx_undo_mutex_key; mysql_pfs_rwlock_t trx_undo_rwlock_key; " I know quite well all the objects that you are mentioning, but I am clueless about what you are trying to say. Are you proposing to use mutex instead of rwlock. I guess that you know very well that those two are not easily interchangeable .... Also, do note that last two objects that you are mentioning are belonging to Performance Schema, so it is very tricky to change how InnoDB SE and P_S interact. Please, address all of the above points. .
[22 Mar 2020 21:19]
Zongzhi Chen
Hello Sinisa
I sorry for the wrong information that I provide for you..
The result I gave in this bug issue is in our 5.6 code version.. However, it is easier to get the same result in our 8.0.* version, and this is the result
```
| InnoDB | rwlock: dict0dict.cc:2535 | waits=4 |
| InnoDB | rwlock: dict0dict.cc:2535 | waits=4 |
| InnoDB | rwlock: dict0dict.cc:2535 | waits=10 |
| InnoDB | rwlock: fil0fil.cc:3324 | waits=40 |
| InnoDB | rwlock: fil0fil.cc:3324 | waits=3 |
| InnoDB | rwlock: fil0fil.cc:3324 | waits=3 |
| InnoDB | rwlock: fil0fil.cc:3324 | waits=2 |
| InnoDB | rwlock: fil0fil.cc:3324 | waits=26 |
| InnoDB | rwlock: fil0fil.cc:3324 | waits=3 |
| InnoDB | rwlock: fil0fil.cc:3324 | waits=5 |
| InnoDB | rwlock: fil0fil.cc:3324 | waits=10 |
| InnoDB | rwlock: dict0dict.cc:1048 | waits=370 |
| InnoDB | rwlock: hash0hash.cc:171 | waits=2 |
| InnoDB | rwlock: hash0hash.cc:171 | waits=2 |
| InnoDB | rwlock: hash0hash.cc:171 | waits=2 |
| InnoDB | rwlock: hash0hash.cc:171 | waits=1 |
| InnoDB | rwlock: hash0hash.cc:171 | waits=1 |
| InnoDB | rwlock: hash0hash.cc:171 | waits=5 |
| InnoDB | rwlock: hash0hash.cc:171 | waits=1 |
| InnoDB | rwlock: hash0hash.cc:171 | waits=2 |
| InnoDB | rwlock: hash0hash.cc:171 | waits=1 |
| InnoDB | rwlock: hash0hash.cc:171 | waits=3 |
| InnoDB | rwlock: hash0hash.cc:171 | waits=2 |
| InnoDB | rwlock: hash0hash.cc:171 | waits=2 |
| InnoDB | rwlock: hash0hash.cc:171 | waits=3 |
| InnoDB | rwlock: hash0hash.cc:171 | waits=1 |
| InnoDB | rwlock: hash0hash.cc:171 | waits=8 |
| InnoDB | rwlock: hash0hash.cc:171 | waits=5 |
| InnoDB | sum rwlock: buf0buf.cc:859 | waits=36455 |
+--------+----------------------------+----------------------------------------+
1369 rows in set (2.82 sec)
```
And The second, how to manage to get the result..
You an get the result by using a large buffer pool, such as in my test case I use the buffer_pool_size 380G.
And after running a IO-bound test case, you can find that the command "show engine innodb mutex" need a long time..
And the third, innodb_show_mutex_status don't need get the mutex_list_mutex, I find in the 5.6 code base, InnoDB DOSE need to get the mutex_list_mutex, however, in 8.0 version, InnoDB don't need get the mutex_list_mutex.
And the last, from 5.6 => 8.0. InnoDB has done work to reduce the cost to get mutex_list_mutex, and I suggest use the same way to redu the cost to get the mutex_list_mutex;
By the way, this is the profile when executing "show engine innodb mutex"
```
mysql> show profiles;
+----------+------------+--------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------+
| 1 | 2.62289225 | show engine innodb mutex |
+----------+------------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show profile for query 1;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 2.622463 |
| query end | 0.000089 |
| closing tables | 0.000024 |
| freeing items | 0.000076 |
| logging slow query | 0.000210 |
| cleaning up | 0.000031 |
+--------------------+----------+
6 rows in set, 1 warning (0.00 sec)
mysql> show profile all for query 1\G
*************************** 1. row ***************************
Status: starting
Duration: 2.622463
CPU_user: 2.636752
CPU_system: 0.000000
Context_voluntary: 1386
Context_involuntary: 1
Block_ops_in: 0
Block_ops_out: 48
Messages_sent: 0
Messages_received: 0
Page_faults_major: 0
Page_faults_minor: 0
Swaps: 0
Source_function: NULL
Source_file: NULL
Source_line: NULL
```
[23 Mar 2020 14:05]
MySQL Verification Team
Hi Mr. zongzhi, Thank you for your feedback. Also, you truly do not need to apologise. You have answered to all of our question, but one issue remains wide open. You wrote in your last comment: " I find in the 5.6 code base, InnoDB DOSE need to get the mutex_list_mutex, however, in 8.0 version, InnoDB don't need get the mutex_list_mutex. " We truly need a very detailed explanation and justification of the above claim. A full and proper code analysis should suffice. Waiting on your feedback.
[24 Mar 2020 11:01]
Zongzhi Chen
Hello Sinisa
In MySQL 5.6, in innodb_mutex_show_status() function
the code is
``` // hold the mutex_list_mutex, and iterate the mutex_list
12849 mutex_enter(&mutex_list_mutex);
12850
12851 for (mutex = UT_LIST_GET_FIRST(mutex_list); mutex != NULL;
12852 mutex = UT_LIST_GET_NEXT(list, mutex)) {
12853 if (mutex->count_os_wait == 0) {
.......
12893 }
12894
12895 mutex_exit(&mutex_list_mutex);
12896 // hold the mutex and iterate the rw_lock_list
12897 mutex_enter(&rw_lock_list_mutex);
```
And in MySQL 8.0, in innodb_show_mutex_status() function, InnoDB only need to iterate the latch_meta. Since InnoDB has put all the mutex in latch_meta;
typedef std::vector<latch_meta_t *, ut_allocator<latch_meta_t *>> LatchMetaData;
LatchMetaData latch_meta;
latch_meta init all the latch in sync_latch_meta_init() function..
When InnoDB need iterator the latch_meta, then it doesn't need to hold the mutex_list_mutex;
And after grep the code, I find there is not code that still using mutex_list_mutex;
so I guess the rw_lock can use the same way to avoid the rw_lock_list_mutex;
[24 Mar 2020 14:22]
MySQL Verification Team
Hi Mr. zongzhi, If you carefully read the code, you will see that these latches do contain mutexes. Specifically if InnoDB SE is built to include P_S objects. Hence, the mutex is then required. It could be, possibly, used with conditional compilation, but this is not sure. Last, which release are you analysing exactly ????
[25 Mar 2020 8:23]
Zongzhi Chen
Did you mean "Specifically if InnoDB SE is built to include P_S objects." will hold the mutex_list_mutex? If this is mutex is not hte mutex_list_mutex, then it will release the mutex quickly.. I think these two mutex not the same, you can check from the code that in 5.6, InnoDB need hold mutex_enter(&mutex_list_mutex); And in 8.0.17, InnoDB doesn't need to use the mutex_list_mutex.. my 8.0 code base is 8.0.17 release version
[26 Mar 2020 12:48]
MySQL Verification Team
Hi, I analysed 8.0.19 code. Also, your last comment is not very clear and precise.
[27 Mar 2020 21:11]
Zongzhi Chen
Let me show you the code in mysql 8.0.19
"show engine innodb mutex" will call innodb_show_latch_status().
And innodb_show_latch_status will call innodb_show_mutex_status() and innodb_show_rwlock_status() seperately.
In innodb_show_mutex_status() will call
ShowStatus collector;
DBUG_ASSERT(hton == innodb_hton_ptr);
mutex_monitor->iterate(collector);
This no lock in these three lines, the mutex_monitor->iterate won't get the mutex_list_mutex lock
However, in the innodb_show_rwlock_status() function, it will call
mutex_enter(&rw_lock_list_mutex);
// try to iterate the rw_lock_list
for (rw_lock_t *rw_lock = UT_LIST_GET_FIRST(rw_lock_list); rw_lock != NULL;
rw_lock = UT_LIST_GET_NEXT(list, rw_lock)) {
if (rw_lock->count_os_wait == 0) {
That is the different. When there is a large buffer pool, as in my case. It will take a long time to iterate the rw_lock_list.. This is the problem.
[30 Mar 2020 12:22]
MySQL Verification Team
Hi, That means that a problem is resolved in 8.0.19. If you look at the implementation of the mutex_enter() within InnoDB code, you will see that it is very fast. Hence, the only way to increase performance would be to replace linked lists with arrays, which is not a suitable solution. Not a bug.
[30 Mar 2020 21:30]
Zongzhi Chen
That is what I want.. can InnoDB change the rw_lock_list to an array? Since the rw_mutex_list has change to an vector. And In my test case, iterate the rw_lock_list really affect performance..
[31 Mar 2020 13:21]
MySQL Verification Team
Hi, My opinion on this matter is not relevant, so I will have to enquire.
[31 Mar 2020 13:40]
MySQL Verification Team
Hi Mr. zongzhi, I was informed that what you ask is possible. However, this project would be very, very low on the list of priorities. In that respect, you are asked very kindly, by InnoDB team, to provide a patch for your request. Thanks in advance.
[1 Apr 2020 9:41]
Zongzhi Chen
ok. I will provide a patch for this.. Thank you
[2 Apr 2020 12:19]
MySQL Verification Team
Hi Mr. zongzhi, Thank you in advance.
