[timskii]

The memcached information is useful to know. And based on what I’ve found below, will logically make an absolutely huge difference. Closer to “requires memcached” on a busy forum.

You see, I’ve done some more tests. And BBPress seems to do a lot of simple queries to check information that generally doesn’t change.

I’ve hooked up _ck_’s excellent BB-Benchmark, and started looking for patterns. I assume this picks up everything in 1.0.

As a general rule, page rendering (after queries) is very slightly slower with 1.0. That can probably be explained simply by twice the volume (in bytes) of files typically being executed from 0.9.4 to 1.0. Only about 10ms difference. So, not an issue.

The crux of the problem is the volume of queries. The fastest query execution is 1ms – measured, it seems, to within 0.1ms – although I’m unsure of that accuracy. This may be due to the way the database is hosted – mySQL is on a separate machine, which is logically going to impose a delay in getting results back.

1.0’s slowest query is faster than 0.9.4. But 1.0 executes vastly more queries: It’s almost inevitable that 40 queries will takes longer to execute than 10 queries, because there is such a significant overhead associated with “running a query”, regardless of its complexity.

Here are examples of what I found:

On the front page, forum views, and tag views, many pairs of queries are being run that look like:

SELECT * FROM wp_users WHERE ID = ‘n’

SELECT meta_key, meta_value FROM wp_usermeta WHERE user_id = ‘n’ /* WP_Users::append_meta */

My first thought was that my templates or plugins were broken. But disabling everything, and switching to the default theme, still causes all these pairs of queries to be executed.

The only reason I can see for the first query is to extract the display name for each of the last post authors. I can’t see any requirement for the second query, unless you were trying to augment the name with some extra information, like a title.

The topic table contains a last poster field, but it cannot contain the display name, presumably because the display name can be changed on a whim, while the old username was unchanging. If you have a lot of active posters (rather than a handful), a 20-post-per-page view could easily require 40 individual queries, just to check a piece of information that probably has not changed.

Now, we can argue that there should be some element of caching of the display name in the topics table. I wouldn’t have a problem with, for example, always seeing the name the user displayed when they posted, rather than the name they are currently using. But there’s also a compromise position, where the last poster name is only checked against wp_users at intervals.

Profile views repeat this a lot:

SELECT meta_key, meta_value FROM bb_meta WHERE object_type = ‘bb_post’ AND object_id = n /* bb_append_meta */

And I’m only displaying recent replies.

Turning to a topic page. I’ve looked at the first 20 posts in a 260+ post-long topic. A real mix of users, some who have added a lot of custom data (I allow quite a lot to be added, which is stored in wp_usermeta). This is painful: 30ms total query time on 0.9.4, 120ms on 1.0. 14 queries plays 79 queries.

Again, the key weakness is duplication of ostensibly similar queries. Line after line of:

SELECT meta_key, meta_value FROM bb_meta WHERE object_type = ‘bb_post’ AND object_id = n /* bb_append_meta */

or

SELECT * FROM wp_users WHERE ID = ‘n’

or

SELECT post_id FROM bb_posts WHERE topic_id = n AND post_status = 0 ORDER BY post_id ASC LIMIT 1

In contrast, 0.9.4 manages to decide all the IDs it needs, throws them all into one query, and presumably lets the PHP split out the results. The wp_usermeta data continues to be pulled out in such a manner:

SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (n,n,n,n…) /* WP_Users::append_meta */

In 3ms. Bargain.

Keep in mind, you won’t see this pattern if your forum contains 20 posts by you. You must have the variety of posters, that tends to only be found on larger forums.

My gut feeling is that some of the loops aren’t terribly well optimized. Or not well optimized for remote databases. Or not well optimized for operation without a secondary cache. Something’s instinctively not right.