Stack Overflow
Stack Overflow

Questions tagged [pt-online-schema-change]

pt-online-schema-change is a command-line tool which emulates the way that MySQL alters tables internally, but it works on a copy of the table you wish to alter. This means that the original table is not locked, and clients may continue to read and change data in it.

Pt-online-schema-change is part of Percona Toolkit - a collection of advanced command-line tools for performing a variety of MySQL and system tasks that are too difficult or complex to perform manually.

pt-online-schema-change emulates the way that MySQL alters tables internally, but it works on a copy of the table you wish to alter. This means that the original table is not locked, and clients may continue to read and change data in it.

25 questions
6
votes
1 answer

Alter a large mysql table 700 gb

The production table I want to alter is around 700 Gb. I want to remove a FK from that table. Mysql version is 5.1.61-log. I found following options: pt-online-schema-change: alters a table’s structure without blocking reads or writes. This looks…
Jitendra Chandani
  • 375
  • 1
  • 12
4
votes
2 answers

Why is Percona pt-online-schema-change performing so badly?

We've been using Percona OSC for a while now to make changes to our mysql schema without locking the tables and it has worked great, typically adding a new column or index to "large" innodb tables (~3.8 million rows) within a couple of…
SharkEgg
  • 41
  • 3
3
votes
1 answer

What does pt-online-schema-change tool do if it aborts?

I am planning to use pt-online-schema-change tool for a table alter on a production server in a replication environment. Wanted to understand what steps are executed when pt-online-schema-change tool aborts due to server tool. Is it possible to…
user3778226
  • 41
  • 1
  • 5
2
votes
1 answer

what happens to binlog when alter a table using pt-online-schema-change or gh-ost

I was wondering what happens to the binlog when run an alter using pt-online-schema-change or gh-ost? for the pt-online-schema-change I have read that it copies the table and use some triggers to apply the changes. I don't know if it create a table…
joe gates
  • 459
  • 1
  • 5
  • 15
2
votes
1 answer

Use pt-online-schema-change to add new column and populate it

I need to add a new column to existing MySQL table and populate with value calculated from another column. Is there a way to do this using pt-online-schema-change? Thanks
AlexV
  • 3,836
  • 7
  • 31
  • 37
1
vote
3 answers

MySQL - pt-online-schema-change effect on db performance

I want to use pt-online-schema-change to change the schema of a big table (~100M records), does this tool effect the performance of MySql while its running?
Ran
  • 3,455
  • 12
  • 47
  • 60
1
vote
1 answer

pt-online-schema-change: Unknown option: bin-log

I am running the command pt-online-schema-change --bin-log --host=127.0.0.1 --user=xxx --password=xxx D=xxx,t=xxx,P=8306 --alter "CONVERT TO CHARACTER SET utf8mb4" --execute, however it outputs: "Unknown option: bin-log". There is a --bin-log option…
callum
  • 57
  • 6
1
vote
1 answer

You do not have the PROCESS privilege at /usr/bin/pt-online-schema-change line 4456, line 1

I'm experiment with the pt-online-schema-change tool with a MySQL 5.7 database hosted by AWS RDS. I have gotten past a few different configuration issues and when I run a dry-run everything looks good but I get this error immediately when I try and…
JD D
  • 7,398
  • 2
  • 34
  • 53
1
vote
0 answers

Unable to run online DDL in mysql

I'm running ddl changes on 130GB table using pt-online-schema-change. I didn't make the ddl changes and cancelled in between. Then , I googled about the error I got and after that tried with another recommended method. Next time, I tried with…
Channa
  • 742
  • 17
  • 28
1
vote
2 answers

pt-online-schema-change breaks AWS DMS Replication

Currently using AWS DMS to replicate data from our Aurora MySQL database to S3. This results in a low-latency data lake we can use to get lineage of all changes occurring and build additional data pipelines off of. However, when making a change via…
jardis
  • 687
  • 1
  • 8
  • 16
1
vote
0 answers

Running pt-osc on RDS instance to alter a table with 1.9 billion records

I have an RDS instance running MySQL 5.5.46 which has a table with a primary key of int that it is currently at 1.9 billion records and approaching the 2.1 billion limit and ~425GB in size. I'm attempting to use pt-osc to alter the column to a…
Ryan
  • 13
  • 5
1
vote
1 answer

Cannot connect to MySQL: DBI connect(';host=1;mysql_read_default_group=client','dbops_usr',...) failed

pt-online schema unable to connect slave servers to monitor replica lags, Using below command and getting errors. pt-online-schema-change D=Percona,t=abcd_data_Jan,h=dbops.com,u=dbops_usr \ --ask-pass \ --max-lag 120 \ --check-slave-lag 1 \ …
Bharath
  • 21
  • 3
1
vote
0 answers

Deadlock Encountered when using pt-online-schema-change

I am running pt-online-schema-change, creating an index on a column in that table. pt-online-schema-change D=pt_osc,t=pt_osc_Test,h=abcd-test.com,u=pt-osc --ask-pass --alter 'add index idx_CREATED_DATE(CREATED_DATE)' --alter-foreign-keys-method=auto…
Bharath
  • 21
  • 3
1
vote
0 answers

pt-online-schema-change sudden jumps in row count after alter

I've recently been doing some database maintenance & stumbled upon something rather odd. During a standard procedure cleaning up i found that the row count before running pt-online-schema-change was much lower than after running…
danniehansenweb
  • 465
  • 4
  • 14
1
vote
1 answer

Can I use pt-online-schema-change to change a primary key?

I'm considering using pt-online-schema-change to ALTER a primary key (changing it from one column, to a composite primary index) of a table. Are there any limitations to doing this? The documentation states In most cases the tool will refuse to…
Ztyx
  • 14,100
  • 15
  • 78
  • 114
1
2