Struggling

At the moment the server is a bit slow and I know why it is. I was looking through some log files and discovered that some SQL queries became slow. The queries in question were all optimized, indexes in place and so on. Then it struck me, there was a huge amount of unused data that could end up in this queries to be examined. And there was no easy way to get around this unwanted feature.
There was only one solution; clean up the database of this bits from the past. And so I wrote a couple of queries to examine how much data there would be deleted. An astonishing 1.000.000 rss items I keep in my database were orphans. That means items that belong to feeds that are gone or that I just don't read anymore. That's one third of all the items that are stored inside the feeds database, and I was never ever going to look at them again. (Well in theory I could, but over the last five years I have never done that)
So at the moment the items of over 500 RSS feeds are being cleared out and that will take a couple of hours. Next an index rebuild will be done so the indexes are optimal for the table again.
I don't expect this job done before midnight. This site will be visible I hope, but my feeds (http://rss.xiffy.nl/) probably not. I'm deleting in chunks to let mysql catch it's breath once in a while and prioritize other queries for a second.

Comments

Okay, that took a little bit longer than expected. But I freed myself of 45.000 tags and 1M of items. That's 1 Gig of disk space, and some nice performance increase on the server. Now I know this won't happen again. When disabling feeds I will delete the feed items after 30 days or so. Will be tweaking the machine a bit further this evening so if you see weird screens and slow response times; I'm sorry.

previous item: Type O Negative – Haunted next item: plurk update
thank you for watching  Creative Commons License