Why ALTER TABLE runs faster on Percona Server 5.5 vs. MySQL_MySQL

Some of us Perconians are atOpenStack summitthis week in Atlanta.Matt Griffin, our director of product management,tweetedabout the turbo-hipster CI talk about their experience of ALTER TABLEs running faster on Percona Server. Oracle’s Morgan Tockerthentweeted in response, asking why this was the case. I decided that the simplest way to answer that was here in this post.

The reason for this is the expand_fast_index_creation feature of Percona Server. I did a quick schema change on MySQL 5.5 and Percona Server 5.5 to demonstrate this(in the talk, the speaker mentioned that these versions were used).

The schema modifications in the talk could fall in 2 categories, the ones that could use fast index creation and the ones that could not.

I did the following tests on my laptop, on a sysbench tale with 300k records.

Vanilla MySQL 5.5:

mysql> alter table sbtest1 add index idx_c(c);Query OK, 0 rows affected (4.37 sec)

mysql>altertablesbtest1addindexidx_c(c);

QueryOK,0rowsaffected(4.37sec)

Percona Server 5.5:

mysql> alter table sbtest1 add index idx_c(c);Query OK, 0 rows affected (3.90 sec)

mysql>altertablesbtest1addindexidx_c(c);

QueryOK,0rowsaffected(3.90sec)

We know that this used fast index creation from the 0 rows affected. In this case, there is nor substantial difference between the 2 servers, also probably my laptop with CPU frewquency scaling doesn’t have the most consistent performance in the world.

For the second schema change, I added a column which copies the table.

Vanilla MySQL 5.5:

mysql> alter table sbtest1 add column d int default 0;Query OK, 300000 rows affected (37.05 sec)Records: 300000Duplicates: 0Warnings: 0

mysql>altertablesbtest1addcolumndintdefault0;

QueryOK,300000rowsaffected(37.05sec)

Records:300000 Duplicates:0 Warnings:0

Percona Server 5.5:

mysql> alter table sbtest1 add column d int default 0;Query OK, 300000 rows affected (9.51 sec)Records: 300000Duplicates: 0Warnings: 0

mysql>altertablesbtest1addcolumndintdefault0;

QueryOK,300000rowsaffected(9.51sec)

Records:300000 Duplicates:0 Warnings:0

The reason for this speed difference is that in case of Percona Server, for the table copy, the table is created only with a primary key, and the secondary indexes are built at the end of the process (rather than on the fly). For more details, checkAlexey’s blog poston this topic.

This can be tuned further, by tuning innodb_merge_sort_block_size (in Percona Server 5.6, this is replaced by innodb_sort_buffer_size).

mysql> select @@innodb_merge_sort_block_size/1024/1024;+------------------------------------------+| @@innodb_merge_sort_block_size/1024/1024 |+------------------------------------------+| 1.00000000 |+------------------------------------------+1 row in set (0.00 sec)mysql> set innodb_merge_sort_block_size=8*1024*1024;Query OK, 0 rows affected (0.00 sec)mysql> alter table sbtest1 add column d int default 0;Query OK, 300000 rows affected (8.61 sec)Records: 300000Duplicates: 0Warnings: 0

mysql>select@@innodb_merge_sort_block_size/1024/1024;

+------------------------------------------+

|@@innodb_merge_sort_block_size/1024/1024|

+------------------------------------------+

| 1.00000000|

+------------------------------------------+

1rowinset(0.00sec)

mysql>setinnodb_merge_sort_block_size=8*1024*1024;

QueryOK,0rowsaffected(0.00sec)

mysql>altertablesbtest1addcolumndintdefault0;

QueryOK,300000rowsaffected(8.61sec)

Records:300000 Duplicates:0 Warnings:0

So, in order to be accurate, schema changes are faster in Percona Server if they are table copies and if the tables have secondary indexes.