Over a million developers have joined DZone.

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

· Performance Zone

Discover 50 of the latest mobile performance statistics with the Ultimate Guide to Digital Experience Monitoring, brought to you in partnership with Catchpoint.

[This article was written by Peter Boros]

Some 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 Tockerthen 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:

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

Percona Server 5.5:

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

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: 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: 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, 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).

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

Is your APM strategy broken? This ebook explores the latest in Gartner research to help you learn how to close the end-user experience gap in APM, brought to you in partnership with Catchpoint.

Topics:

Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}