Database v7
Well, it’s been a busy past couple days, but I have some great news to report with regards to CIA. The new database backend I’ve been hacking at for the past couple weeks is now in place, and the performance is great.
So, to back up a bit… CIA uses MySQL. It has for almost 2 years now. There are many things CIA does that SQL works great for: storing the smaller bits of metadata on each project/author, tracking the association graphs, and quickly pulling up overview statistics for the site’s front page. SQL does not, however, work well for storing the actual commit messages. I’ll save you the boring details of why, but suffice it to say that this has been bogging CIA down in nearly every way possible. The stats_messages table was eating about 2.5 GB of disk space, the indices were causing mysqld to use most of my RAM, it was using unacceptable amounts of my server’s limited I/O bandwidth, and there was no efficient way to clean out old messages. CIA needed a new storage option.
The new v7 database schema CIA is on now has completely abandoned the stats_messages table. I developed a custom file format for the messages. Each stats target has a file on disk with two ring buffers. A fixed size (currently 4 kB) index buffer provides IDs for recent messages. These IDs are actually offsets into a second larger ring buffer. This second buffer starts out empty, but can grow up to a fixed maximum, currently 256 kB. At the seek offset corresponding to each valid message ID, the content buffer contains a frame marker that lets it detect invalid or expired IDs. The messages themselves, rather than being stored as normal XML, are encoded as a binary representation of SAX events. This makes them quick to load, and lets the format maintain commonly used strings in a dictionary. This provides about the same compression ratio as gzip’ed XML, but it doesn’t have the backup problems that an already-compressed database would present.
I spent Saturday night (in a hack session with Christian and Paul) finalizing the database backend itself and writing a converter tool. Sunday, the converter ran.. and it ran.. and it eventually finished Monday morning after converting over 1.5 million commit messages! After doing a dump/load to shrink the InnoDB tablespace, I had successfully replaced a 2.5 GB impossibly slow SQL table with a zippy collection of small ringbuffer files, totalling under 500 MB. CPU usage with the new backend is about the same, but I/O load is much lower.
Google AdSense
If you look way at the bottom-left of any CIA page, you’ll now see a familiar block of text advertisements. Consider this a trial run. CIA’s web layout is currently such that there really isn’t a great place for advertisements, so they’re really kind of in the middle of nowhere at the moment.
Yes, this marks CIA’s descent into the depths of capitalistic hell I’m sure. The reality is that CIA costs a lot to run. I’ve been paying $40/month for its current hosting. We’ve had a lot of great donations which I am really greatful for. Now that I’m out of college and have a real job, I can pay for the server without begging for $10 here and $5 there.
But yet, the ads are an experiment. CIA receives a huge amount of web traffic. Granted most of this is generated by RSS aggregators and web spiders, but even the comparatively small human-generated slice of the pie is pretty big. If it’s possible to support CIA’s hosting just by adding some unobtrusive text ads, I think this would be beneficial for everybody involved.
Ideally, once I have time to tweak CIA’s layout, there will be a better place for the ads. If they do well enough, I might even be able to get a real dedicated server rather than this Linode box. At the rate CIA is growing, I’m going to need one sooner or later.
I want to emphasize that I will only be placing text ads on the site, and only where they won’t interfere with the display of the site’s real content. Site usability is still my #1 concern, but I need to plan for a future that includes steadily rising server requirements.
Future Work
So, what’s next on the plate for CIA?
Well, database v7 is actually just a small piece of a larger database overhaul I have planned for CIA. I’d like to eventually get rid of MySQL. As nice as MySQL is, I’m really not benefitting from the use of a client/server database. My utopian database v8 format would look something like this:
- Messages themselves stored as an indexed ringbuffer of SAX events, same as v7
- Smaller metadata items stored either in SQLite or a very simple special-purpose format
- Larger metadata items (like images) stored in individual files
- Counters replaced with fidtool. Fast Interval Databases (FIDs) are another backend format I’ve been working on. They store event timestamps, and efficiently perform interval queries against them. This would let me finally replace the “today”, “yesterday”, “this week”, and such with sliding intervals. For example, with fidtool CIA could keep a running total of commits received in the last 24 hours. I also have some cool plans for activity graphs with google-maps-like AJAX scrolling and zooming.
- Catalog data cached in SQLite. This should eliminate the uber-queries that are currently necessary to index all the children of a particular stats target. This should finally let me re-enable the project and author index pages.
Well, except for a few new capabilities database v8 will offer, it’s mostly optimization work. Since database v7 seems to have the server running smoothly again, there are a lot of other projects I’d like to poke at:
- A web frontend for maintaining rulesets and requesting capabilities (finally!)
- Replacing metadata keys, which exist only for historical reasons really, with a traditional username/password login
- Further splitting the monolithic server process. I’d like to eventually be able to run the web frontend and XML-RPC server as separate processes, and even to run them directly from apache using mod_python. This should improve server response times, and potentially make maintenance easier.
- Web site layout tweaks. CIA has grown up a lot since the current layout was designed, it would be nice to revisit it to improve usability. Particularly, I’d like to make it scale better to large numbers of projects, authors, and stored messages
- I’ve toyed with the idea of replacing CIA’s little documentation system with an external wiki. As it is, the doc browser is really a lot like a read-only wiki in the way it formats documents. With a real wiki, users could more easily contribute their own client scripts, tips and tricks, suggestions, complaints, and documentation.
- On a similar note, would it be possible to ditch the majority of CIA’s web frontend and somehow embed it into a wiki or some other content framework?
- Web 2.0 tags, ‘cuz they’re trendy. To really make CIA web-2.0-compliant, I need a page that lists all projects where font size is proportional to activity.
I’m sure I’ve forgotten a few things.. I’ve had a lot of crazy ideas swimming around in my head recently. If I’m not careful, a few of them might get implemented.
Weekend
Thursday night, David flew into town for a job interview with VMware. We don’t know yet whether he got the job, but we’re quite hopeful. Either way, we managed to finish off his day-long interview with the traditional video games and dinner. More video games at my apartment later that night, then Saturday morning we wandered San Francisco a bit with Scott and Carrie. We tried a couple coffee shops in North Beach, and I ended up with some muchly yummy cappucino and biscotti.
His flight back home from SJC was at 5:00 Saturday, so we didn’t have a huge amount of time to spend here. Still, it was a lot more exploration than I got to do when I flew in for my interview earlier this year. It was goodbye for only a week or so. Wednesday I’m coming back to Colorado for thanksgiving, and I plan on spending the weekend in Boulder. Yay!