Which is Faster? Alter table with algorithm=copy or pt-osc?

Polyglot Projects Percona Toolkit
1

On a MySQL 5.7 test instance (non-production), assume I have 10 billion rows and the schema has 5 columns, 1 primary key, 1 unique key, and 1 foreign key. Which would be faster to execute to add a new column?

  1. Running alter table with algorithm=copy?
  2. Running pt-online-schema-change?

Thanks.

1 Like
2

Hi Brian,

Thank you for posting here and being part of the community. Probably alter table will be faster. But I made one assumption: there is no significant activity during the alter on the table.

pt-online-schema-change is slower, but it allows everyday activities on the table: insert, update and delete.

You must also consider the possible impact in replication, as the alter will be replicated on all the replicas. This can lead to replication latency.

Thank you,

Pep

1 Like
3

Hi Pep,

Thanks. So the server does not have any activity at all and there is no replication set up. Would “alter table” still be faster? Why is that? And how slow will pt-osc be (eg. estimated at 2 times slower? - I don’t think so.)

I like pt-online-schema-change because it shows the ‘time remaining’ for it to finish while I don’t know how long the straight alter will be done (note this is on an Amazon Aurora MySQL, so I cannot see a temp tables that are being created). So, if the pt-osc and straight alter will finish roughly the same (eg. if the straight alter finish by 2-3 hours ahead, that is still fine), then I would like to use pt-osc instead.

Thanks,
Brian

1 Like
4

Hi Brian,

It is hard to tell how fast will be, but it will be significantly faster. Remember that the alter will take place in the database server, without moving any data out of the server, while pt-osc will involve communications between the database and the node running the pt-osc.

If the difference of time is your main concern, my recommendation is that you perform some tests with smaller tables to compare the behavior in your environment.

Thanks,

Pep

1 Like
5

@brianestrada658,
As Pep said, native ALTER will always be faster because the data just shuffles around internally whereas pt-osc uses SQL to select and re-insert the data. If you simply need to add a new column, might I recommend you first upgrade to MySQL 8.0 (FYI: 5.7 EOL next year) and then you can use the native ALGORITHM=INSTANT which only takes a few seconds to add a column.

1 Like