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.
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.
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.
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 ?
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)