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

OpenStack 2014Some of us Perconians are at OpenStack summit this week in Atlanta. Matt Griffin, our director of product management, tweeted about the turbo-hipster CI talk about their experience of ALTER TABLEs running faster on Percona Server. Oracle’s Morgan Tocker then tweeted 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:

Percona Server 5.5:

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:

Percona Server 5.5:

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, check Alexey’s blog post on 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).

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.

Share this post

Comments (3)

  • Morgan Tocker Reply

    Makes sense. Thank you for providing more details.

    May 15, 2014 at 10:19 am
  • Doug Reply

    Thanks for the information, Peter.

    May 16, 2014 at 4:34 pm
  • Olivier DASINI Reply

    Hi Peter,
    I guess results would be the same with Percona server 5.6 vs MySQL 5.6 ?

    May 20, 2014 at 4:59 am

Leave a Reply