pt-table-sync - prevent master lockup on busy server?

Polyglot Projects Percona Toolkit
1

We have a simple master->slave set up. Trying to use pt-table-sync to bring slave in sync with master. Can’t use the replication method because we are so far out of sync.

Problem is, the SELECT statements on the master block/lock other INSERT queries coming in from our app.

Is there a way to make pt-t-s wait a second after each Nibble SELECT so that other queries have time to run?

Tried using --buffer-in-mysql, --lock 0, --lock 1, --transaction to no help.

All tables InnoDB.

Ideas?

2

Hi,

Can you please provide exact command line which you are using to run pt-table-sync? AFAIK, --transaction option should definitely help you for locking issue except you are using --lock and --transaction options at the same time.

Please also provide Percona Toolkit Version.

3

Tried all of the following. None helped. While the SELECT statements were running on the master, INSERT statements were piling up and then timing out being unable to get lock within ‘innodb-wait-lock-timeout’ period. v 2.1.1

pt-table-sync h=deb058,P=3309,D=azuredb,t=day_5 h=sus042,P=3309 --no-bin-log --executept-table-sync h=deb058,P=3309,D=azuredb,t=day_5 h=sus042,P=3309 --no-bin-log --execute --transactionpt-table-sync h=deb058,P=3309,D=azuredb,t=day_5 h=sus042,P=3309 --no-bin-log --execute --no-transaction --lock 1pt-table-sync h=deb058,P=3309,D=azuredb,t=day_5 h=sus042,P=3309 --no-bin-log --execute --no-transaction --lock 1pt-table-sync h=deb058,P=3309,D=azuredb,t=day_5 h=sus042,P=3309 --no-bin-log --execute --buffer-in-mysqlpt-table-sync h=deb058,P=3309,D=azuredb,t=day_5 h=sus042,P=3309 --no-bin-log --execute --buffer-in-mysql --lock 0