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:
None 
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
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
[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.