Had a long day of putzing around with FeedDigest. The database problems continued to mount up. While I've spent a lot of time optimizing and keeping it running, the situation still wasn't ideal, and the main bottleneck was a combination of disk I/O and simple math (that is, making 100 radically different temporary tables of up to 10,000 rows of 300 bytes each each minute isn't viable).

I decided to solve the problem two-fold. First, I created an extremely lean 'posts_meta' table to store only the barest of information that FeedDigest needs to be able to sort items into the order the user/digest requires. That includes the id of the post (to link to the 'posts' table), the aggregated time, a hash of the URL, a hash of the title, and such like. Now I have the meta data table down to 26 bytes per row on average.. which is a ten-fold increase, and a massive sigh of relief to the database's dizzy brain.

Secondly, FeedDigest is extremely heavy on the INSERTs and SELECTs. Optimizting a database is easy if it's one way or the other, but FeedDigest's database has a ton of stuff going in (all the posts, feed items, etc) and a ton of stuff going out (to digests). All of the INSERTs were causing SELECTs to lock and slow down the process of building a digest, so I decided to implement a queue.

Due to the database structure, this was easily said than done and has resulted in a whole lot of frowning and scrunched up forehead tonight, but I think it's finally there. Now all of the crawlers throw data into posts_queue (schematically a duplicate of 'posts').. and a queue maintainence program runs every minute and syncs up the data with the active tables.

It all works too, although now it's not I/O causing problems.. it's CPU! Due to some of the structure of FeedDigest, we're now doing about 8 times the amount of raw requests per second to pull off the above.. so it's still way faster than before, but now the CPU is maxing out a lot. Luckily there are a lot of query optimizations I can pull now, since it's finally working.. but it's constant work. Still, it's serving almost three million requests a day, and you can't complain at a single machine that can do that while pulling and managing data in a gargantuan MySQL database!