Re: Slow Updates , High CPU 100%

[Rick James]

Not normal.

Possible problem... If a SELECT is taking 0.5s and it started just before the UPDATE started, then the UPDATE will wait because it needs a write lock on that MyISAM table.

Set long_query_time = 0.5 (if you version is new enough to allow floating values; else set it to 1).

Suggest you do SHOW PROCESSLIST frequently. Look for any queries (including SELECT) taking more than a second, any queries running when the UPDATE says "Locked", and any queries that show up frequently.

All it would take is some SELECT doing a table scan to both peg the CPU and block the UPDATE. Perhaps all it will take to fix the problem is an INDEX to make the naughty SELECT run fast.

Switching to InnoDB might or might not help. UPDATE and SELECT can run simultaneously, but perhaps not if the SELECT is doing a table scan.