delete caused all kinds of problems

[Kenneth Vogt]

I have a transaction table. Records are added regularly, perhaps 50,000 per day. When it got to 1,000,000 records, I decided to purge the 500,000 oldest. So I ran this simple command:

DELETE FROM am_log WHERE id<500000

Then the fun began. It ran for 40 minutes. It caused timeouts for other operations calling the database. When it was done, the table was no longer functional as am_log.myi was corrupted. After a long rebuild, it was back.

I don't want to have this experience again.

So, how best to approach keeping this table a managable size and in good shape? I could run a cron every night and knock off 50K records or so. I could do it every hour and purge ~4k. If I do one of those, how often do I need to optimize the table? How long will the optimize process take? What happens when the table is called by some function while it is being optimized?

_____________
Kenneth Vogt