2

I have a master server, where I want modify operations such as UPDATEs to be LOW_PRIORITY so that they don't block SELECTs etc. This is better for end-user visible behaviour.

I have a slave server, where I do batch processing, and I don't care about prioritising SELECTs over UPDATEs. In fact, because the slave consumes the UPDATEs and executes them as LOW_PRIORITY, and the replication is single-threaded, what happens is that the batch processing tends to delay the replication.

Ideally on this slave server I would tell MySQL to prioritise the UPDATEs above SELECTs, so that the two servers stayed in sync, at the cost of delaying my batch operations a bit.

Edward Hibbert
  • 31
  • 1
  • 4

1 Answers1

0

Start the slaves with the --skip-innodb, --low-priority-updates, and --delay-key-write=ALL options to get speed improvements on the slave end. In this case, the slave uses nontransactional MyISAM tables instead of InnoDB tables to get more speed by eliminating transactional overhead.

Reference: MySQL Manual

Wasif
  • 330
  • 1
  • 9
  • I think this would exacerbate the problem I see of replication lagging because update ops are delayed by read ones. An option of --high-priority-updates might help, but I don't think that exists. As for the other two, doesn't this leave the slave data vulnerable to data corruption? For example, what happens if it crashes in the middle of a transaction? – Edward Hibbert Dec 02 '12 at 18:14
  • Skipping innodb (--skip-innodb) is not compulsory. I misunderstood --low-priority-updates. What if we start master with --low-priority-updates and don't run UPDATE statements on master with LOW_PRIORITY. As a normal slave, UPDATEs will have priority over SELECT. Delaying writes may create a long write queue. I won't recommend doing anything with master. – Wasif Dec 04 '12 at 05:24
  • SELECTs have low priority as compared to UPDATEs. On master, do SELECT HIGH_PRIORITY and on slave keep them a normal SELECT. – Wasif Dec 04 '12 at 05:45