[Clicknathan]

Hello,

I’ve recently set up a BBPress forum for non-profit Bike Pittsburgh and their host, Pair Networks, shut down their database because of the load BBPress was putting on the server. Pair has since reinstated the db, but I was wondering if anyone could offer any insight as to how I might trim down the size of the queries that BBPress is putting through. A little background:

1. I used this method to convert a WordPress XDForum into BBPress. I know the author of that post mentions that the method he uses might not be the most efficient, however at this point (after the conversion), I wouldn’t think that BBPress is using any of this info any more, correct?
2. We’re running BBPress v. 0.9.0.2
3. We’re using the following plugins: Akismet, Use Display Name, BBPress Private Messaging, Post Count Plus, Forum Last Poster, and Images Allowed

The information from the host was this:

With that said, we wanted to provide a follow up in terms of the nature of the problem itself. The problem is that the database is running some very inefficient queries, trying to sort through 2.5 million rows and are returning very few to no results. These queries take a long time to run and are called “slow queries”. As you gain in traffic, the more of these slow queries that are run, the higher the load on the server, and therefore the more affect you have on other customers.

I’ve included some examples of these queries below for your review:

# Time: 080930 20:55:01

# User@Host: bikepgh[bikepgh] @ [209.68.4.180]

# Query_time: 54 Lock_time: 0 Rows_sent: 10 Rows_examined: 2469001

SELECT forum_id, topic_id FROM bb_topics WHERE topic_last_post_id IN

(SELECT MAX

(topic_last_post_id) FROM bb_topics WHERE topic_status=0 GROUP BY

forum_id);

# Time: 080930 20:56:05

# User@Host: bikepgh[bikepgh] @ [209.68.4.180]

# Query_time: 71 Lock_time: 0 Rows_sent: 10 Rows_examined: 2469001

SELECT forum_id, topic_id FROM bb_topics WHERE topic_last_post_id IN

(SELECT MAX

(topic_last_post_id) FROM bb_topics WHERE topic_status=0 GROUP BY

forum_id);

# Time: 080930 20:56:43

# User@Host: bikepgh[bikepgh] @ [209.68.4.180]

# Query_time: 67 Lock_time: 0 Rows_sent: 10 Rows_examined: 2469001

SELECT forum_id, topic_id FROM bb_topics WHERE topic_last_post_id IN

(SELECT MAX

(topic_last_post_id) FROM bb_topics WHERE topic_status=0 GROUP BY

forum_id);

# Time: 080930 20:57:38

# User@Host: bikepgh[bikepgh] @ [209.68.4.180]

# Query_time: 52 Lock_time: 0 Rows_sent: 10 Rows_examined: 2469001

SELECT forum_id, topic_id FROM bb_topics WHERE topic_last_post_id IN

(SELECT MAX

(topic_last_post_id) FROM bb_topics WHERE topic_status=0 GROUP BY

forum_id);

# Time: 080930 21:20:46

# User@Host: bikepgh[bikepgh] @ [209.68.4.180]

# Query_time: 263 Lock_time: 0 Rows_sent: 10 Rows_examined: 2469001

SELECT forum_id, topic_id FROM bb_topics WHERE topic_last_post_id IN

(SELECT MAX

(topic_last_post_id) FROM bb_topics WHERE topic_status=0 GROUP BY

forum_id);

# Time: 080930 21:22:35

# User@Host: bikepgh[bikepgh] @ [209.68.4.180]

# Query_time: 281 Lock_time: 0 Rows_sent: 0 Rows_examined: 1952752

SELECT forum_id, topic_id FROM bb_topics WHERE topic_last_post_id IN

(SELECT MAX

(topic_last_post_id) FROM bb_topics WHERE topic_status=0 GROUP BY

forum_id);

As you can see, these queries are taking almost 300 seconds to complete

(query_time). This is an extremely high time, as a query on a shared server

should take under 10 seconds to complete.

I hope this helps provide some needed details for you. If we can be of any

further assistance, please do not hesitate to ask.

Any help as to what I might do in PHPMyAdmin or otherwise to help lower the load on the db would be VERY MUCH appreciated!

[chrishajer]

There have been some relevant discussions here:

https://bbpress.org/forums/topic/bbpress-0901-gt-30-queries-per-page

https://bbpress.org/forums/topic/server-overload

https://bbpress.org/forums/topic/fulltext-searching-very-slow

https://bbpress.org/forums/topic/how-to-evaluate-server-speed

Looking at the front page of your forum, I see a total of 3074 threads and 22,121 posts. Is there more than meets the eye here to come up with 2.5 million rows of data? Or maybe there’s not 2.5 millions rows in bb_topics, it’s just because of the subquery that it looks at that many records (like it’s something I don’t understand about the way MySQL displays the number or rows searched)?

Did the amount of data grow exponentially after the conversion from xdforums? Maybe there were 20K rows in xdforums, but something with the conversion didn’t go well and it duplicated a bunch of data? How many rows were there in comparable tables before and after conversion?

Something doesn’t add up.

Also, are all those queries the same, and if so, is that query just trying to determine the last poster, from the “Forum Last Poster” plugin? If so, have you tried just turning that plugin off? If that works, then maybe just optimizing the query for your installation would work and you could turn it back on after modifying the plugin.

Of all the plugins, the only other one I could see affecting the queries like that would be the Post Count Plus. That would be the second one I turn off and check for a performance difference.

[Sam Bauers]

I think that query is generated by the forum-last-poster plugin.

If it is the one that is showing up over and over again, then get rid of it.