Lussumo Community Downtime

Mark

You may have noticed a slow-down on Lussumo.com over the last couple of days, and yesterday I took the community forum down for some database tuning & application optimization.

It turns out that I had forgotten to apply all of the indexes & optimizations to this database through the years that we've been online. The growth of our community, combined with poor indexing caused a couple of the tables to begin to lock. The LUM_User and LUM_UserDiscussionWatch tables in particular were locking. These tables are updated frequently with login information and discussion tracking information respectively. Because the tables were MyISAM type, all records would be locked when an update was applied to just a single row - this meant that all 9000+ user records would get locked whenever anyone's "DateLastActive" field was updated, and all 90,000+ records in the LUM_UserDiscussionWatch table would get locked whenever anyone even looked at a single discussion (and the record of their view of that discussion was recorded).

To fix both of these issues, I changed their table types to InnoDB so that only the affected row should become locked when updates are applied.

I also analyzed the Discussions & Comments queries, which are (obviously) the most actively run queries in the application. The comments query was extremely slow. After running EXPLAIN on the query, I found that it was indexed incorrectly. For some reason the LUM_Comment table was using both the CommentID and the DiscussionID columns as it's primary key. I removed the DiscussionID as a primary key and added it as a simple index. This allows the query to not scan the entire LUM_Comment table when performing the join to LUM_Discussion. I also found that the LUM_UserBlock table had no indexes at all, so I added those and was able to further reduce the query time. Here is a list of the changes that I made to the database for anyone who might be interested:

ALTER TABLE `community`.`LUM_Comment` DROP PRIMARY KEY,
 ADD PRIMARY KEY  USING BTREE(`CommentID`),
 ADD INDEX `comment_discussion`(`DiscussionID`);

ALTER TABLE LUM_UserBlock ADD INDEX (BlockingUserID);
ALTER TABLE LUM_UserBlock ADD INDEX (BlockedUserID);

ALTER TABLE LUM_User ENGINE=InnoDB;
ALTER TABLE LUM_UserDiscussionWatch ENGINE=InnoDB;

I do not know if these changes will solve all of our problems, but they are certainly a good start. I'll be watching the database load today to see how the server performs with the new changes in place.

Regardless, it's time to start thinking about a serious caching solution - I'll be researching that as well.

ps. Thanks to Damien (Dinoboff) and Dave (Wallphone) for jumping in and offering some assistance at my blog.