[bobbyh]

Our bbpress forum does roughly 20-25k pageviews per weekday. We also have a WordPress blog (with caching) that does around 75k pageviews per weekday. The boards and blog are integrated, and share the same database.

The database is the only thing that runs on a (mt) dedicated server with 1GB of RAM (the Extreme package), and its my.cnf is a slightly tweaked version of a my-huge.cnf.

The database was really running ragged a week ago, with a load average between 2.5 and 4.0. The first change I made was described in the first post (indexing topic_slug in bb_topics, tag in bb_tags and forum_slug in bb_forums). That really helped a lot, because on a popular forum, there are apparently enough new topics that the query cache gets invalidated frequently. By making this query a lot more efficient with an index, the load average fell to between 0.4 and 0.7.

Then, after adding the 4-column index, the load average plunged to between 0.03 and 0.20. It’s pretty amazing.

Indices are AWESOME!