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).

3 responses to “mysql argh”

  1. InnoDB is GPL, so while Oracle might scale back any developement, it’s not going to disapear. New dev will happen at MySQL AB and in the community.

    So I wouldn’t worry much.

  2. I don’t have any reason to think that MySQL will fork InnoDB. They didn’t have the resources to buy Innobase, and similarly won’t be in a position to poach key people from them now that they’re owned by Oracle. Any development would have to come from existing or new inside resources, and there’s nothing in the GPL that makes maintaining that level of development easy. Even if people can get up to speed something has to give.

    The problem is that MySQL AB is now in an awkward position for their revenue-generating products: the client libraries and support. Until now they’ve been pricing their client libraries as if they were the database, even though the database; that’s a lot harder to do when it’s clear to customers that large chunks of what they seem to be buying are owned by a competitor.

    If MySQL AB is going to separate themselves from dependence on Innobase Oy it’s going to be by prioritizing BDB and working with Sleepycat. I don’t know if they’ll do that, or just hope they don’t anger their new partner; I don’t think Oracle would buy Innobase to kill MySQL, I think they bought it to put themselves in a very good bargaining position with a competitor that now needs them.