0

I have a question about MySQL replication: can I set the slave DBs to only poll the master DB for information once every 10 seconds?

Our MySQL master DB is under fairly heavy load. By default, MySQL replicates in almost real time, which means that each slave DB is polling the master DB for changes many times in one second. I don't want the master DB to be bogged down by the update / delete load as well as the constant requests of slaves to get the latest changes.

In our situation, we don't need the slave DB to be synced in real-time to the master. The slaves can each afford to be 10 seconds behind the master and the clients who are using the slave DBs won't even notice. Is there a way I can set the slaves to poll the master every 10 seconds instead of in real-time?

Note: setting the MASTER_DELAY=N setting will not work

The MASTER_DELAY setting tells the slave to intentionally stay N seconds behind the master. However, it doesn't affect how often the slave polls the master for changes. In my case, setting the MASTER_DELAY setting won't do any good bc I want the slaves to poll the master less, not more.

  • So you basically need an asynchronous replication? Or a variable/dynamic polling interval? – hot2use Aug 30 '17 at 5:59
  • Not necessarily either. I need to be able to set the polling interval to whatever I want it to be. – jtcotton63 Aug 30 '17 at 17:33
1

I suggest it is not a real problem. I have worked with a Master that had about 20 slaves, each pulling upwards of a million queries per day. The polling and network issues were minor.

But if you must... Have a job that does this forever:

repeat forever
    START SLAVE;
    sleep 0.1 sec
    STOP SLAVE;
    sleep 10 sec
end-loop

To make it more robust, adjust the sleeps if Seconds_behind_master gets "too high".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.