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

  • Filter
  • Time
  • Show
Clear All
new posts

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

    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.


  • #2

    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


      • #4

        As per documentation, http://www.percona.com/doc/percona-toolkit/2.1/pt-table-sync .html

        "pt-table-sync can run in one of two ways: with --replicate or without. The default is to run without --replicate which causes pt-table-sync to automatically find differences efficiently with one of several algorithms (see "ALGORITHMS"). Alternatively, the value of --replicate, if specified, causes pt-table-sync to use the differences already found by having previously ran pt-table-checksum with its own --replicate option.

        When you run pt-table-sync, it will run checksum before syncing and it can take time when difference are more. So I guess, first you should run pt-table-checksum with --replicate option and analyze the result. If there are too many differences than rebuild the slave is best option but if there are few differences than after checksum run pt-table-sync with --replicate option.

        If you are trying again with your options, Please share here "innodb status" when the blocking happens. It can be helpful for troubleshooting.

        As pt-table-sync is read/write tool, I would suggest you should take a backup of data before doing anything and read documentation of both the tools will be helpful too.

        http://www.percona.com/doc/percona-toolkit/2.1/pt-table-chec ksum.html
        http://www.percona.com/doc/percona-toolkit/2.1/pt-table-sync .html