Feedwhip keeps track of the last two weeks’ worth of changes to your subscriptions. Anything past that gets deleted so that the servers’ hard disks don’t fill up.

Unfortunately, deleting individual items from database tables can be really slow. Once per hour, we were looking in the database at each feed’s items for entries that were old. This was consuming a lot of the database server’s time, so I came up with a better solution.

Now, we store the feed items in rotating tables. There are several of them, and each contains a week’s worth of data. Once per week, a cron job deletes the oldest table, renames all the other tables, and creates a fresh new table. This means that I’m no longer sucking up cycles looking for old items to delete — I know everything in the oldest table is old, and so I drop them all at once.

It also shrinks the size of the table containing the most likely source of items, but at the cost of having to do more than one lookup to find all the items for a given feed. Since most feeds are updated fairly regularly, you probably don’t need to go back more than a week to find the ten most recent items.

It’s my hope that this will lighten the load on the database server and eliminate some of the strange slow behavior I’ve been seeing — such as primary key lookups taking more than a second (they should be more-or-less instant).

If this doesn’t improve things dramatically (or even if it does), I’ve still got more plans on how to improve the performance — the easiest of which is throwing more RAM into the database server.

Advertisements