Rich Lafferty's LiveJournal (mendel) wrote,
Rich Lafferty's LiveJournal

  • Mood:

mysql argh

Fun MySQL problems at work today. An application update introduced a handful of regularly-run but poorly optimized queries — full table scans, temporary tables, the works — on a box that was already pretty heavily taxed. The end result was a run queue of 20-60 when things were bad, alternating with inexplicable periods of idleness. Cranked up temporary table and cache sizes to no avail; everything got a little bit faster, but context switches went through the roof and things were still getting hung up.

The problem was lock contention — we're still on MyISAM and occasionally a query would obtain a read lock on a table and hold it for 30-120 seconds. In that period a query would request a write lock, and after that some other queries would request read locks. But MySQL prioritizes write locks over read, so all of the queries requesting read locks had to wait for the writing process to get its lock and finish its write before they could proceed. Get a few of the problem queries in that queue and everything would repeat.

Luckily I remembered seeing a switch for that behavior — setting low-priority-updates prioritizes read locks over write. The writing process still has to wait for the pathological reader to release its lock, but in the meantime other processes get concurrent read locks and go about their business. Load average down to 4-8, which is pretty much all application, and context switching down to levels below what we saw before the update. Yay! I wish I'd made that change ages ago.

I seriously need to start planning an InnoDB migration soon (even with Oracle's purchase of Innobase).

  • New Year's resolution

    I'm going to post this on my zen blog later this week, but right now I want to post it somewhere and I'm too tired to compose a post over…

  • how's this work again

    So uh hi there everyone, long time no see? So I've got this theory where I think it'd be good for me to just write about stuff that's going on here,…

  • o hai lj.

    I should totally start using this again.

  • Post a new comment


    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded