| 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: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
Description: Hi all: at some time,we add a partition .this operation is so slow! any select is hang! like this: mysql> show processlist; +----+-----------------+-----------+------+---------+------+------------------------------------------+----------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+------+---------+------+------------------------------------------+----------------------------------------------------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 165 | Waiting on empty queue | NULL | | 8 | root | localhost | test | Query | 4 | Waiting for table metadata lock | select * from sales | | 10 | root | localhost | test | Query | 25 | committing alter table to storage engine | alter table sales add partition (partition p_2035 values less than (2035)) | | 11 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-----------------+-----------+------+---------+------+------------------------------------------+----------------------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> show processlist; +----+-----------------+-----------+------+---------+------+------------------------------------------+----------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+------+---------+------+------------------------------------------+----------------------------------------------------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 185 | Waiting on empty queue | NULL | | 8 | root | localhost | test | Query | 3 | Waiting for table metadata lock | select * from sales partition(p_2019) | | 10 | root | localhost | test | Query | 45 | committing alter table to storage engine | alter table sales add partition (partition p_2035 values less than (2035)) | | 11 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-----------------+-----------+------+---------+------+------------------------------------------+----------------------------------------------------------------------------+ The stack like : (gdb) bt #0 0x0000000004fa1e73 in rec_offs_n_fields (offsets=0xb479910) at /opt/mysql/mysql-8.0.21/storage/innobase/rem/rec.h:449 #1 0x0000000004fa3d82 in rec_offs_data_size (offsets=0xb479910) at /opt/mysql/mysql-8.0.21/storage/innobase/include/rem0rec.ic:935 #2 0x0000000004fa7ad1 in rec_validate (rec=0x7fff450e7516 "", offsets=0xb479910) at /opt/mysql/mysql-8.0.21/storage/innobase/rem/rem0rec.cc:1337 #3 0x000000000519eeea in rec_fold (rec=0x7fff450e7516 "", offsets=0xb479910, n_fields=0, n_bytes=1, fold=108872348850564, index=0xb46d900) at /opt/mysql/mysql-8.0.21/storage/innobase/include/rem0rec.ic:1122 #4 0x00000000051a32e1 in btr_search_drop_page_hash_index (block=0x7fff4352f158) at /opt/mysql/mysql-8.0.21/storage/innobase/btr/btr0sea.cc:1177 #5 0x00000000051a38c3 in btr_search_drop_page_hash_when_freed (page_id=..., page_size=...) at /opt/mysql/mysql-8.0.21/storage/innobase/btr/btr0sea.cc:1275 #6 0x00000000051a3d23 in btr_drop_ahi_for_table (table=0xb46bc10) at /opt/mysql/mysql-8.0.21/storage/innobase/btr/btr0sea.cc:1354 #7 0x0000000004e61295 in alter_part_normal::try_commit (this=0xbaadc30, table=0x0, altered_table=0xbb75500, old_part=0xb474738, new_part=0xbb293c8) at /opt/mysql/mysql-8.0.21/storage/innobase/handler/handler0alter.cc:8550 #8 0x0000000004e4cdd5 in alter_parts::prepare_or_commit_for_new (this=0xb97f240, old_dd_tab=..., new_dd_tab=..., altered_table=0xbb75500, prepare=false) at /opt/mysql/mysql-8.0.21/storage/innobase/handler/handler0alter.cc:9657 #9 0x0000000004e4c6fe in alter_parts::try_commit (this=0xb97f240, old_dd_tab=..., new_dd_tab=..., table=0xaaef7b0, altered_table=0xbb75500) at /opt/mysql/mysql-8.0.21/storage/innobase/handler/handler0alter.cc:9584 #10 0x0000000004e4f83e in ha_innopart::commit_inplace_alter_partition (this=0xb47a018, altered_table=0xbb75500, ha_alter_info=0x7fff602dc670, commit=true, old_dd_tab=0xaaf06e8, new_dd_tab=0xb924368) at /opt/mysql/mysql-8.0.21/storage/innobase/handler/handler0alter.cc:10515 #11 0x0000000004e4e8ee in ha_innopart::commit_inplace_alter_table (this=0xb47a018, altered_table=0xbb75500, ha_alter_info=0x7fff602dc670, commit=true, old_table_def=0xaaf06e8, new_table_def=0xb924368) at /opt/mysql/mysql-8.0.21/storage/innobase/handler/handler0alter.cc:10216 #12 0x0000000003ad89a3 in handler::ha_commit_inplace_alter_table (this=0xb47a018, altered_table=0xbb75500, ha_alter_info=0x7fff602dc670, commit=true, old_table_def=0xaaf06e8, new_table_def=0xb924368) at /opt/mysql/mysql-8.0.21/sql/handler.cc:4885 I think this operation is AHI maintain, when AHI rows is large this operation is slow,But this operation is protect by mdl x at DDL commit stage,so select is hang! How to avoid this ? set AHI OFF? thanks!! How to repeat: 1. CREATE TABLE `sales` ( `order_date` datetime NOT NULL, `id` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!50100 PARTITION BY RANGE (year(`order_date`)) (PARTITION P_2017 VALUES LESS THAN (2017) ENGINE = InnoDB, PARTITION P_2018 VALUES LESS THAN (2018) ENGINE = InnoDB, PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB, PARTITION p_2020 VALUES LESS THAN (2020) ENGINE = InnoDB, PARTITION p_2021 VALUES LESS THAN (2021) ENGINE = InnoDB */; 2. insert 500W data 3. alter table sales add partition (partition p_2035 values less than (2035)); 4. when innodb_adaptive_hash_index set OFF,add partition is not slow!! Suggested fix: no Suggest