MySQL

Great Example of Pluggable Storage Engine Win

At the MySQL reception last night at OSCON, which was an astounding success (> 140 people I believe), I ran into one of the LiveJournal crew who told me about a success story they have had with one of the pluggable storage engines that is usually skimmed over and rarely discussed: the ARCHIVE storage engine. According to the LiveJournal employee (I left his business card back at the hotel so I can’t remember his name…) they switched from using MyISAM to ARCHIVE for their Apache server logging and noticed a 400% performance improvement (from a disk I/O perspective) and at least a 20% reduction in storage size.

This little story highlights the main point I try to get across during my tutorials and presentations on performance tuning: that you should take advantage of the MySQL storage engine architecture by using the storage engine best suited for the job. In this case, using the ARCHIVE storage engine, which is designed for extremely fast INSERT operations and low disk overhead along with decent data compression, was the right tool for the job.

Anyone else out there using ARCHIVE in production? What has been your experience? I talked with Brian Aker about the LiveJournal case and while he was happy about the performance improvement, he remarked that typical compression ratios for ARCHIVE are much higher than 20%. I’ll try and ping my LiveJournal contact to get an exact compression ratio from him and perhaps a sample of their data to see if there is anything we can do to increase the ratio.