FabulousEntertainment

I use a login MyISAM table to record all logins to the site (IP address, user agent, username, etc) which is pretty handy for spotting scammers and people with multiple accounts.

Anyway, the problem is that this table gets very large, very quickly. My solution had simply been to run a delete every month to remove older entries. But this led to two problems:

1. The table was locked for ages while the delete happened.
2. The table was then left fragmented (which prevents concurrent inserts on my configuration)

My first thought was to use DELETE … LIMIT 100; in a loop so that the table wouldn’t be locked for too long. However that was still not an ideal solution as it was still using a lot of resource and fragmenting the table.

What I finally settled on was using a new login table for each month and then using a MyISAM merge table to join them all together. Then, I can simply drop the old tables as they go out of date and reconfigure the merge table.

I can now delete millions of rows instantly with no performance impact or fragmentation. Result!

This entry was posted on Sunday, June 22nd, 2008 at 11:23 pm and is filed under Technical. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.