Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
Which storage engine you are using? –  mwasif Dec 4 '12 at 5:48
add comment

1 Answer

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

share|improve this answer
    
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 2 '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. –  mwasif Dec 4 '12 at 5:24
    
SELECTs have low priority as compared to UPDATEs. On master, do SELECT HIGH_PRIORITY and on slave keep them a normal SELECT. –  mwasif Dec 4 '12 at 5:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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