Archive for June, 2011|Monthly archive page

Site maintenance

Some of you may have noticed bcaching was out of commission for a lengthy period from Thursday night until Friday morning. This was in order to complete as much of the database reorganization (a.k.a. phase III) as possible.

When cache logs were migrated to the new database (mongodb) using the same layout as before (a single list of logs by cache log id with a secondary index on cache id), it caused similar slowdowns as before (on mysql). It’s just too much data and the indexes are too large for the limited resources we have available.

The reorg – to a list of cache documents, each containing all of its related logs – was going painfully slow for about a week and a half when I finally decided to shut the site down for an extended period and use all the server resources to try to just get it done.  It was going reasonably well (maybe 85% complete) until around 5:30 in the morning when mongo decided it needed to allocate more space in the filesystem, even though there was more than adequate space already freed up from dropping the old cache logs table. The problem was likely due to fragmentation within the database files. A “repairdatabase” would have solved it by defragmenting and really freeing up the unused space, but like mysql, mongodb (1.8) requires free disk space to create a new clean copy of the database before it deletes the old one. I didn’t have the space. Luckily mongodb has excellent backup and restore functions that allows backups to be done to and from a separate server so that’s what I did. Incidentally, the next release (2.0) will support an in-place repairdatabase function.

So after the database was restored, I brought the system back up with partially migrated cache logs. You may encounter a few caches that have no logs, but don’t be alarmed. They will reappear over the next few days.

Update 6/19/2011: The cache logs migration is complete. Finally!

MongoDB data conversion phase II complete

The data conversion from MySQL to MongoDB is finally done. Phase I was to move everything except cache logs and was completed as part of the upgrade to 0.8. Phase II was to move the cache logs out of the old database and into the new database.

Since there is not enough disk space to support two full copies of the logs (for 32 million logs, 8-11 GB per copy depending on indexes) I planned to free up disk space out of the old database periodically. That turned out to be impossible since it was too expensive to delete logs from MySQL and freeing up space requires making a new copy of the data before the old one is deleted… did I mention I don’t have enough disk space?

Instead I migrated about a third of the cache logs (about 10 million) then dumped the rest to an off-line archive. Then I took the MySQL DB offline, freed up the space, and started loading the archived logs into MongoDB.

Now for something a little more technical…

In the MySQL database, cache logs were stored in a single table with the cache log ID as the primary key and a secondary index on cache ID + log time. That structure requires index storage in the 2+ GB size. That’s a lot of index data to traverse, especially when looking up logs for many caches at once (as would be the case for the synchronization API). With limited memory resources it also means that very little of the index can be cached in memory so MOST cache log lookups will require quite a lot of disk seeks.

But, since cache logs are always accessed with the cache ID available, a much more efficient structure would be to group all cache logs in a “table” organized by cache ID. For the current 1.5 million cache records, the index storage would be less than 200 MB.

Unlike MySQL, MongoDB allows for unstructured data storage and it would be easy to do this as a table of cache documents (one for each cache ID), then each document would contain an array of all the related cache logs. For cache log retrieval, it would require a single index lookup per cache, followed by (approximately) one disk seek to retrieve ALL cache logs. The downside to this structure is that when adding additional logs to a cache document, the document will grow and may have to be moved (on disk) if there is not enough free space in the document’s current location. Even so, this structure has a strong enough advantage with the significantly reduced index size and will free up memory resources for other indexes and data.

That WAS my original plan, but it was taking so long to extract the logs from MySQL by cache ID I ended up migrating the data using the SAME structure, organized by cache LOG ID. As a result, the performance under the new database is only marginally better than before.

So now there is a “Phase III” where the old structure will be converted and migrated to the new structure.

Stay tuned.