MySQL Bugs: #83435: ALTER TABLE is very slow when using PARTITIONED table

Bug #83435	ALTER TABLE is very slow when using PARTITIONED table
Submitted:	19 Oct 2016 0:40	Modified:	19 Oct 2016 5:34
Reporter:	Roel Van de Paar	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S1 (Critical)
Version:	5.7.15/5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
mysql> insert INTO t1 values(unix_timestamp('2004-10-31 01:00:00'),'2004-10-31 01:00:00'),(unix_timestamp('2004-10-31 02:00:00'),'2004-10-31 02:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59'),(unix_timestamp('2004-10-31 04:00:00'),'2004-10-31 04:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59');
ERROR 1406 (22001): Data too long for column 'c2' at row 1
mysql> insert INTO t1 values(0,'2004-01-01 00:00:00'),(0,'2004-01-01 01:00:00'),(0,'2004-02-01 00:00:00');
ERROR 1406 (22001): Data too long for column 'c2' at row 1
mysql> alter table t1 engine=InnoDB;

Query OK, 0 rows affected (7.56 sec)      <<<<<<< (!)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1 WHERE c1<'1971-01-01 00:00:01' ORDER BY c1 DESC LIMIT 2;
Empty set, 2 warnings (2.19 sec)       <<<<<<< (!)

How to repeat:
DROP DATABASE test;CREATE DATABASE test;USE test;
SET @@session.default_storage_engine=MYISAM;
CREATE TABLE t1(c1 INT,c2 CHAR)PARTITION BY LINEAR KEY(c1) PARTITIONS 99;
CREATE UNIQUE INDEX i1 ON t1(c1);
insert INTO t1 values(unix_timestamp('2004-10-31 01:00:00'),'2004-10-31 01:00:00'),(unix_timestamp('2004-10-31 02:00:00'),'2004-10-31 02:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59'),(unix_timestamp('2004-10-31 04:00:00'),'2004-10-31 04:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59');
insert INTO t1 values(0,'2004-01-01 00:00:00'),(0,'2004-01-01 01:00:00'),(0,'2004-02-01 00:00:00');
alter table t1 engine=InnoDB;
SELECT * FROM t1 WHERE c1<'1971-01-01 00:00:01' ORDER BY c1 DESC LIMIT 2;

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.17 Source distribution PULL: 2016-OCT-14

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > DROP DATABASE test;CREATE DATABASE test;USE test;
ERROR 1008 (HY000): Can't drop database 'test'; database doesn't exist
Query OK, 1 row affected (0.00 sec)

Database changed
mysql 5.7 > SET @@session.default_storage_engine=MYISAM;
Query OK, 0 rows affected (0.00 sec)

mysql 5.7 > CREATE TABLE t1(c1 INT,c2 CHAR)PARTITION BY LINEAR KEY(c1) PARTITIONS 99;
Query OK, 0 rows affected (0.07 sec)

mysql 5.7 > CREATE UNIQUE INDEX i1 ON t1(c1);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.7 > insert INTO t1 values(unix_timestamp('2004-10-31 01:00:00'),'2004-10-31 01:00:00'),(unix_timestamp('2004-10-31 02:00:00'),'2004-10-31 02:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59'),(unix_timestamp('2004-10-31 04:00:00'),'2004-10-31 04:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59');
ERROR 1406 (22001): Data too long for column 'c2' at row 1
mysql 5.7 > insert INTO t1 values(0,'2004-01-01 00:00:00'),(0,'2004-01-01 01:00:00'),(0,'2004-02-01 00:00:00');
ERROR 1406 (22001): Data too long for column 'c2' at row 1
mysql 5.7 > alter table t1 engine=InnoDB;
Query OK, 0 rows affected (33.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.7 > SELECT * FROM t1 WHERE c1<'1971-01-01 00:00:01' ORDER BY c1 DESC LIMIT 2;
Empty set, 2 warnings (12.30 sec)

mysql 5.7 >

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.35 Source distribution PULL: 2016-OCT-14

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > DROP DATABASE test;CREATE DATABASE test;USE test;
Query OK, 0 rows affected (0.03 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
mysql 5.6 > SET @@session.default_storage_engine=MYISAM;
Query OK, 0 rows affected (0.00 sec)

mysql 5.6 > CREATE TABLE t1(c1 INT,c2 CHAR)PARTITION BY LINEAR KEY(c1) PARTITIONS 99;
Query OK, 0 rows affected (0.03 sec)

mysql 5.6 > CREATE UNIQUE INDEX i1 ON t1(c1);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.6 > insert INTO t1 values(unix_timestamp('2004-10-31 01:00:00'),'2004-10-31 01:00:00'),(unix_timestamp('2004-10-31 02:00:00'),'2004-10-31 02:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59'),(unix_timestamp('2004-10-31 04:00:00'),'2004-10-31 04:00:00'),(unix_timestamp('2004-10-31 02:59:59'),'2004-10-31 02:59:59');
ERROR 1062 (23000): Duplicate entry '1099202399' for key 'i1'
mysql 5.6 > insert INTO t1 values(0,'2004-01-01 00:00:00'),(0,'2004-01-01 01:00:00'),(0,'2004-02-01 00:00:00');
ERROR 1062 (23000): Duplicate entry '0' for key 'i1'
mysql 5.6 > alter table t1 engine=InnoDB;
Query OK, 5 rows affected (41.47 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql 5.6 > SELECT * FROM t1 WHERE c1<'1971-01-01 00:00:01' ORDER BY c1 DESC LIMIT 2;
+------+------+
| c1   | c2   |
+------+------+
|    0 | 2    |
+------+------+
1 row in set, 4 warnings (0.02 sec)

mysql 5.6 >

Note the different query outcome on 5.6 also.

Also see;
https://bugs.launchpad.net/percona-server/+bug/1634708

Thank you for the bug report.

I'm trying to understand what this bug report is really about.
So I made did some tests on my 5.7.16 windows that has write caching enabled on disk:

Server only starts with --open-files-limit=20000 --table-open-cache=10000

created in myisam and alter to innodb: http://pastebin.com/DXwWHW8a
created directly in innodb           : http://pastebin.com/9zgWSpP6

Can you check if the above give unexpected times?

Thanks. Yes, testcase can be reduced for slow duration;

mysql> CREATE TABLE t1(c1 INT,c2 CHAR,UNIQUE KEY(c1)) ENGINE=MyISAM PARTITION BY LINEAR KEY(c1) PARTITIONS 99;
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE t1 ENGINE=InnoDB;
Query OK, 0 rows affected (7.77 sec)
Records: 0  Duplicates: 0  Warnings: 0

There is also query difference.

DROP is also very slow and seems to be fairly linear based on partition count.

Using SET PROFILING=1 on these shows the following results:

For an ALTER on a table with 40 partitions, 0 rows:

*************************** 12. row ***************************
             Status: committing alter table to stor
           Duration: 7.299994
           CPU_user: NULL
         CPU_system: NULL
  Context_voluntary: NULL
Context_involuntary: NULL
       Block_ops_in: NULL
      Block_ops_out: NULL
      Messages_sent: NULL
  Messages_received: NULL
  Page_faults_major: NULL
  Page_faults_minor: NULL
              Swaps: NULL
    Source_function: mysql_inplace_alter_table
        Source_file: sql_table.cc
        Source_line: 7003

And the DROP TABLE on the same 0 row, 40 partition:

*************************** 2. row ***************************
             Status: checking permissions
           Duration: 7.726601
           CPU_user: NULL
         CPU_system: NULL
  Context_voluntary: NULL
Context_involuntary: NULL
       Block_ops_in: NULL
      Block_ops_out: NULL
      Messages_sent: NULL
  Messages_received: NULL
  Page_faults_major: NULL
  Page_faults_minor: NULL
              Swaps: NULL
    Source_function: check_access
        Source_file: sql_parse.cc
        Source_line: 5909

Related: Bug#94610.

I also have similar behavior, but with TRUNCATE PARTITION in recent 5.7, I think it might be related.

Met this bug at 8.0.16. I have a table with 60 partitions, each partition file is about 60-70MB, but DROP PARTITION for one partition takes 10-40 minutes. Facepalm.