Skip to:
Content

BuddyPress.org

Opened 7 years ago

Closed 3 years ago

#4647 closed defect (bug) (wontfix)

Copy to tmp table

Reported by: elpix Owned by:
Milestone: Priority: normal
Severity: normal Version:
Component: Forums Keywords:
Cc:

Description

Hi all,

I ask your help beacause I’ve already tried everything that I knew before to make a query work in few seconds

I’m on WP 3.4.2, and BP 1.5.5

Before, everything was working well, but one day, access to our forum became very very long (170 s average).
After many tests, I think that it’s a cache problem.

Here is two queries that make me think that :

SELECT t.*, g.id AS object_id, g.name AS object_name, g.slug AS object_slug
FROM wp_bb_topics AS t
INNER JOIN wp_bb_posts p ON p.topic_id=t.topic_id AND p.post_time=t.topic_start_time
JOIN wp_bp_groups AS g
LEFT JOIN wp_bp_groups_groupmeta AS gm ON g.id = gm.group_id
WHERE (gm.meta_key = 'forum_id' AND gm.meta_value = t.forum_id) AND g.STATUS = 'public' AND t.topic_status = '0'
ORDER BY t.topic_time
LIMIT 20

And the same without the “LIMIT”

SELECT t.*, g.id AS object_id, g.name AS object_name, g.slug AS object_slug
FROM wp_bb_topics AS t
INNER JOIN wp_bb_posts p ON p.topic_id=t.topic_id AND p.post_time=t.topic_start_time
JOIN wp_bp_groups AS g
LEFT JOIN wp_bp_groups_groupmeta AS gm ON g.id = gm.group_id
WHERE (gm.meta_key = 'forum_id' AND gm.meta_value = t.forum_id) AND g.STATUS = 'public' AND t.topic_status = '0'
ORDER BY t.topic_time

The first (with LIMIT) take 150 secondes (At the beginning, it was working well).
But the second (without LIMIT) takes 1.5 secones !!!!

When I’m in PHPmyADMIN, the “show full processlist” tell “copy to tmp table” during the 150 secondes.

As a precision !

  • In wp_bb_topics : 20.000 rows
  • In wp_bb_group_groupmeta : 75.000 rows

Is there a cache limit ? I’ve reache with my DB (20.000 topics) ?
Where I’ve to modified (I think in my.cnf), and what is the value to put (I’ve make a lot of test, without good results)?

We tried to improve the buffers (all we can see in “show variable global”) but no results.

Change History (3)

#1 @elpix
7 years ago

  • Severity changed from critical to blocker

#2 @boonebgorges
7 years ago

  • Keywords needs-patch added
  • Milestone changed from Awaiting Review to Future Release
  • Severity changed from blocker to normal

It would be very nice to fix this kind of problem, but it's unlikely that the core dev team will be able to put much time into it. Your problem is related to the bbPress 1.x BP integration, while BP core is moving toward bbPress 2.x as the recommended forum solution in BP 1.7+. As such, we're not eager to sink lots of dev time into what is soon to be a legacy system.

If you can suggest some improvements in the format of the query (off the top of my head, I would suggest splitting it into a couple different queries, rather than doing a massive join against groupmeta, which is likely to be slow as you note), it would be most welcome.

#3 @lakrisgubben
3 years ago

  • Keywords needs-patch removed
  • Milestone Future Release deleted
  • Resolution set to wontfix
  • Status changed from new to closed

Since bbPress 1.x was retired/deprecated in BuddyPress 1.7 in favour of bbPress 2.x and a decision has been made that "The retired component will continue to work, but no new features will be incorporated by the BuddyPress team." (https://codex.buddypress.org/legacy/getting-started/using-bbpress-2-2-with-buddypress/#approach-1-migrate-bp-discussion-forums-to-bbpress-preferred) I'm closing this as wontfix.

Note: See TracTickets for help on using tickets.