Description:
We're typically seeing a 3x - 5x slower DDL operation on table with many partitions. See testcase output on my 5.7.19 :
table t - 600 partitions
table r - 300 partitions
table s - not partitioned
mysql> alter table t add column c int, algorithm=inplace, lock=none;
Query OK, 0 rows affected (42.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table r add column c int, algorithm=inplace, lock=none;
Query OK, 0 rows affected (37.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table s add column c int, algorithm=inplace, lock=none;
Query OK, 0 rows affected (7.67 sec)
Records: 0 Duplicates: 0 Warnings: 0
How to repeat:
#Compare the last three statements timings.
-- --------
drop table if exists t,r,s;
create table t(a int not null auto_increment primary key, b int)engine=innodb partition by key(a) partitions 600;
create table r(a int not null auto_increment primary key, b int)engine=innodb partition by key(a) partitions 300;
create table s(a int not null auto_increment primary key, b int)engine=innodb ;
set @a:=1000000;
insert into t(b) values(floor(rand()*@a)),(floor(rand()*@a)),(floor(rand()*@a));
insert into t(b) values(floor(rand()*@a)),(floor(rand()*@a)),(floor(rand()*@a));
insert into t(b) select floor(rand()*@a) from t,t a,t b,t c,t d,t e,t f,t g;
insert into r(b) select b from t;
insert into s(b) select b from t;
analyze table t;
analyze table r;
analyze table s;
flush tables;
do sleep(10);
alter table t add column c int, algorithm=inplace, lock=none;
alter table r add column c int, algorithm=inplace, lock=none;
alter table s add column c int, algorithm=inplace, lock=none;
-- --------
Suggested fix:
Persistent stats and fsyncs incurred would attribute to some overhead.
So I do expect some overhead with many partitions, but not 400% !!
