slug permalinks can lead to db problems
-
Hey guys,
I have a busy bbpress board (http://boards.weddingbee.com) that uses slug-based permalinks. The database has been slow lately, so I started optimizing tonight. While scrolling through the queries in the slow query log, I saw many log entries like this:
SELECT topic_id FROM bb_topics WHERE topic_slug = 'gerbera-daisieswhere-can-i-order-them-wholesale';Let me back up. The default install for both the old school 0.83 and the new 0.91 has URLs like this:
* http://boards.weddingbee.com/topic.php?id=999
However, I set my bbpress to use “slugs”, so that topic redirects to:
* http://boards.weddingbee.com/topic/need-advice-re-florist-mess-up
The problem is that a slug-based URL requires a database query to translate the ‘slug’ (need-advice-re-florist-mess-up) into a topic_id (999). However, the topic_slug field in the bb_topics table is not indexed. in the bbpress database. Thus, the database needs to scan the entire bb_topics table to find the topic_id!
In short, if you want to use slug permalinks, you should go in and index the topic_slug in your database, or your database will be slow once you have lots of topics. You may also want to index tag (in bb_tags) and forum_slug (in bb_forums). I saw a substantial improvement in performance upon adding these indices via phpmyadmin.
Anyone else run into this issue?
-Bob
P.S. In a perfect world, bbpress might automatically index these fields if the keymaster selects “slug” or “name-based” permalinks.
-
Thanks for your thoughtful response, sam! That sounds great to me!
FYI, I also came across this query repeatedly in my slow query log:
SELECT SQL_CALC_FOUND_ROWS * FROM bb_topics WHERE topic_status = 0 AND forum_id = 1 AND topic_sticky = 0 ORDER BY topic_time DESC LIMIT 40, 20;topic_sticky isn’t indexed (in either 0.83 or 0.91, I checked both). Should it be? It’s a tinyint(1), so indexing it should cost very little while improving performance a lot, I think?
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!
- The topic ‘slug permalinks can lead to db problems’ is closed to new replies.
