Not the answer you need?
Register and ask your own question!

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

utdrmacutdrmac EntrantInactive User Role Beginner
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?

Comments

  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    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.
  • utdrmacutdrmac Entrant Inactive User Role Beginner
    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</pre>
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.