Saturday, December 30, 2006

MyISAM DELETE statements taking forever

On one of my servers today I noticed some DELETE statements that had been running for more than 14000 seconds due to system locks.

 Queries: 180.2M  qps: 1294 Slow:  173.3k         Se/In/Up/De(%):    09/02/33/00
qps now: 2065 Slow qps: 8.1 Threads: 463 ( 225/ 738) 06/01/49/00
Cache Hits: 610.9k Hits/s: 4.3 Hits now: 4.1 Ratio: 3.9% Ratio now: 3.3%
Key Efficiency: 99.2% Bps in/out: 134.7k/ 1.5M Now in/out: 203.9k/ 1.3M

Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
18951229 www fl_db1 14516 Query DELETE FROM user_
19003358 www fl_db1 14273 Query DELETE FROM user_
19014485 www fl_db1 14221 Query DELETE FROM user_
19117224 www fl_db1 13733 Query DELETE FROM user_
21707448 www fl_db1 1474 Query DELETE FROM user_

One common misunderstanding is that when system locks are disabled (using --skip-external-locking), threads shouldn't be in "System lock" state. This confusion exists because we see state of "System lock" even when MyISAM locks are in place for a thread.

Key cache is flushed during the unlocking phase and can take some time. During the flush process, it my understanding that all key blocks that have been changed are written to the index file. So size of the key cache plays an important role here.

What I am investigating is why some of the DELETE records ocassionally take as much as 14000 seconds and how can that be decreased.

So my boss found out the reason for the application issuing excessive DELETE statements. It turned out that within one class our old programmers were using DELETE followed by an INSERT to perform UPDATEs on the table. That is, ofcourse, terrible usage of resources and not to mention its horrible defragmentation effects.

If you ever, for some reason, find yourself in such situation, remember to defragment the tables.

Archival Deletes
Going forward, it would be very nice for MySQL to provide an option (for MyISAM tables) for a DBA to set "archival/status deletes" for a table. What I mean is that whenever a DELETE statement is issued, MyISAM shouldn't actually delete the record but instead mark it as deleted. Then later, those records can be physically deleted periodically, or on demand. I would really love to see something like this to help improve performance of DELETE operations.

One way to "relatively increase" the performance of DELETE statements is to lower its priority by specifying the LOW_PRIORITY attribute.

Read more about table locking issues on MySQL website.

No comments: