WhyALTERTABLErunsfasteronPerconaServer5.5vs. MySQL_MySQL

Source: Internet
Author: User
Tags percona server
WhyALTERTABLErunsfasteronPerconaServer5.5vs. mySQL5.5 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 coshould fall in 2 categories, the ones that cocould use fast index creation and the ones that cocould not.

I did the following tests on my laptop, on a sysbench tale with 300 k 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, 0 rowsaffected (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, 0 rowsaffected (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, 300000 rowsaffected (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, 300000 rowsaffected (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, 1.00000000 |

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

1 rowinset (0.00sec)

Mysql> setinnodb_merge_sort_block_size = 8*1024*1024;

QueryOK, 0 rowsaffected (0.00sec)

Mysql> altertablesbtest1addcolumndintdefault0;

Queryokay, 300000 rowsaffected (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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.