Adding new columns on 350 B size table by using pt-osc utility

Polyglot Projects Percona Toolkit
1

Hello Team,

I am real scenario like , I have one table having 340 GB table size…I want to add 2 new columns on this table. already i have 65 columns in this table…I tested pt-osc utility…it took 45 hrs . Is that behavior like this ?
I used the below syntax in stage environment.

pt-online-schema-change --execute D=dbname,t=tablename,h=localhost,u=root -p --alter “ADD COLUMN column1 VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_bin,ADD COLUMN column2 TINYINT;” --max-load=Threads_running:800 --critical-load=Threads_running:800 --recursion-method=none --skip-check-slave-lag --preserve-triggers --no-drop-old-table &

It took almost 45 Hr’s .
But when i used native ALTER command i.e below one , it took almost 25 Hrs .
Which one is the best and to do in this scenario .

ALTER table dbname.tablename add column column1 VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_bin, ADD column column2 TINYINT, ALGORITHM=INPLACE, LOCK=NONE;" &

Is there anything to tune parameters in both cases from DB level ? If yes please share those parameters .Will share the current values.

Thanks in advance.

1 Like
2

Hi,

Using pt-osc is not going to be as fast as regular DDL. This is because pt-osc always creates a new table. In your case, for a moment you will be using around 500GB of space due to having 2 tables.

Using the ALTER with ALGORITHM=INPLACE, LOCK=NONE; options will ensure that the table is still available to read and write.

A few settings that you can optimize is setting sync_binlog=0 and innodb_flush_log_at_trx_commit=0.

Once the command is finished, return these values to the original value since they break ACID compliance.

Also, make sure you have a proper redo log size:

[How to calculate a good InnoDB log file size - Percona Database Performance Blog]

1 Like
3

Thank you for your replying.
Here i would like to inform one thing…

We have master - slave replication to do this …
In that case as you mentioned 2 parameters should i run in master server and hit pt-osc command ? or can i mentione these 2 parameters in pt-osc command …?

Neither nor if i provide these 2 ,what will happen in the slave side ? will it applicable or not ?

1 Like
4

You can run on both. It will optimize the operation of both servers.

Applying the configuration to the master server does not propagate to the slave. If you want, you need to set on both.

You need to set them previously running pt-osc or the online DDL. They are global parameters.

Once the operation finishes, you can rollback.

1 Like
5

First of, if you have mysql8, such a simple thing can be done with algorithm=INSTANT (with a few limitation that don’t seem to apply to your case).

Otherwise, at that size, ptosc is likely to take the drop_swap foreign key method (since you left it to auto).
If ever it chose rebuild_constraints, there is a major delay to rebuild FK that point to the table altered.
[PT-1327] LP #1542291: pt-osc rebuild constraint can be faster since 5.6 - Percona JIRA (which may get released along PT-1919 in 3.3.1)

FYI, you set --preserve-triggers so I suspect you have some; if so DO NOT USE 3.3.0 as you would loose your BEFORE triggers on that table. [PT-1919] drop_swap can drop triggers - Percona JIRA

1 Like
6

We are using MariaDB 10.3

1 Like