Bug #101900 | Add partition is slow | ||
---|---|---|---|
Submitted: | 8 Dec 2020 3:21 | Modified: | 8 Dec 2020 17:21 |
Reporter: | peng gao | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
Version: | 8.0.21 | OS: | Linux (7.6) |
Assigned to: | CPU Architecture: | Any |
[8 Dec 2020 3:21]
peng gao
[8 Dec 2020 3:35]
peng gao
In test environment I add a sleep in btr_drop_ahi_for_table. But in our product environment this add partition operation expand 120 seconds.
[8 Dec 2020 6:27]
peng gao
In 8.0.19 also have this problem
[8 Dec 2020 16:22]
MySQL Verification Team
Hi, I'm having issues reproducing this on 8.0.22 mysql [localhost:8022] {msandbox} (test) > select count(*) from sales; +----------+ | count(*) | +----------+ | 33554432 | +----------+ 1 row in set (15.60 sec) mysql [localhost:8022] {msandbox} (test) > select @@innodb_adaptive_hash_index ; +------------------------------+ | @@innodb_adaptive_hash_index | +------------------------------+ | 1 | +------------------------------+ mysql [localhost:8022] {msandbox} (test) > alter table sales add partition (partition p_2035 values less than (2035)); Query OK, 0 rows affected (0.78 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql [localhost:8022] {msandbox} (test) > select @@innodb_adaptive_hash_index ; +------------------------------+ | @@innodb_adaptive_hash_index | +------------------------------+ | 0 | +------------------------------+ 1 row in set (0.00 sec) mysql [localhost:8022] {msandbox} (test) > alter table sales1 add partition (partition p_2035 values less than (2035)); Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql [localhost:8022] {msandbox} (test) > select @@version; +-----------+ | @@version | +-----------+ | 8.0.22 | +-----------+ 1 row in set (0.00 sec) time with and withouyt adaptive hash index to add the partition is 100% identical all best Bogdan
[8 Dec 2020 16:24]
MySQL Verification Team
Hi, Apologies I'm blind 0.17 vs 0.79, that's almost 5x slower! I'll verify this. Thanks for reporting the bug Bogdan
[8 Dec 2020 17:21]
peng gao
Thanks for Bogdan: In my test 1.6 vs 0.3 ,the same as your result at most 5x slower! I think the most slowest point is: ->btr_search_drop_page_hash_when_freed ->btr_search_drop_page_hash_index ->rec_fold ->ut_fold_ulint_pair because every block、every row and every field will calculate fold.
[17 Mar 2:44]
shaoqing sun
mysql version is 8.0.23 Add partition is not only slow, when i kill the running this session, mysql server is down. ASSERTION FAILURE: DICT0DICT.CC:1885:TABLE->GET_REF_COUNT == 0
[17 Mar 2:44]
shaoqing sun
mysql version is 8.0.23 Add partition is not only slow, when i kill the running this session, mysql server is down. ASSERTION FAILURE: DICT0DICT.CC:1885:TABLE->GET_REF_COUNT == 0