# Adding new columns on 350 B size table by using pt-osc utility **URL:** \ **Category:** Percona Toolkit\ **Tags:** percona\ **Created:** [April 9, 2021, 6:44pm UTC](https://forums.percona.com/t/adding-new-columns-on-350-b-size-table-by-using-pt-osc-utility/10038 "2021-04-09T18:44:55Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![vasu1](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vasu1/32/3519_2.png) [@vasu1](https://forums.percona.com/u/vasu1)\ **Post date:** [April 9, 2021, 6:44pm UTC](https://forums.percona.com/t/adding-new-columns-on-350-b-size-table-by-using-pt-osc-utility/10038/1 "2021-04-09T18:44:56Z")
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. ---
**Author:** ![vinicius.grippa](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vinicius.grippa/32/1246_2.png) [@vinicius.grippa](https://forums.percona.com/u/vinicius.grippa)\ **Post date:** [April 11, 2021, 4:18pm UTC](https://forums.percona.com/t/adding-new-columns-on-350-b-size-table-by-using-pt-osc-utility/10038/2 "2021-04-11T16:18:45Z")
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](https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/)] ---
**Author:** ![vasu1](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vasu1/32/3519_2.png) [@vasu1](https://forums.percona.com/u/vasu1)\ **Post date:** [April 12, 2021, 1:26am UTC](https://forums.percona.com/t/adding-new-columns-on-350-b-size-table-by-using-pt-osc-utility/10038/3 "2021-04-12T01:26:33Z")
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 ? ---
**Author:** ![vinicius.grippa](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vinicius.grippa/32/1246_2.png) [@vinicius.grippa](https://forums.percona.com/u/vinicius.grippa)\ **Post date:** [April 13, 2021, 4:11pm UTC](https://forums.percona.com/t/adding-new-columns-on-350-b-size-table-by-using-pt-osc-utility/10038/4 "2021-04-13T16:11:06Z")
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. ---
**Author:** ![bob](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@bob](https://forums.percona.com/u/bob)\ **Post date:** [April 16, 2021, 12:35pm UTC](https://forums.percona.com/t/adding-new-columns-on-350-b-size-table-by-using-pt-osc-utility/10038/5 "2021-04-16T12:35:01Z")
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](https://jira.percona.com/browse/PT-1327) (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](https://jira.percona.com/browse/PT-1919) ---
**Author:** ![vasu1](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vasu1/32/3519_2.png) [@vasu1](https://forums.percona.com/u/vasu1)\ **Post date:** [May 15, 2021, 1:28pm UTC](https://forums.percona.com/t/adding-new-columns-on-350-b-size-table-by-using-pt-osc-utility/10038/6 "2021-05-15T13:28:19Z")
We are using MariaDB 10.3