Skip to:
Content

bbPress.org

Opened 6 years ago

Closed 6 years ago

Last modified 3 months ago

#2353 closed defect (duplicate)

bbp_update_forum_reply_count on a 1.8MM post bbPress forum takes ~6-10 seconds

Reported by: vibol Owned by: johnjamesjacoby
Milestone: Priority: normal
Severity: normal Version: 2.2.3
Component: General - Performance Keywords: needs-patch
Cc: UmeshSingla

Description

This can likely use some optimization.

I haven't dug in enough to come up with a solution but I believe this function is what causes the massive query attached. Perhaps these aggregates should be placed in a separate table and incremented or decremented according to the posts added/removed. At the numbers I'm working with, eventual consistency of the aggregate numbers is acceptable.

Scenarios affected: Add new post, Reply to post, Trash post, Delete post.

Action execution times: ~6-10s depending on freshness of data in memory and size of forum.

System: 12-core, 64GB RAM

Function in question: bbp_update_forum_reply_count

Actual query: see attached.

Attachments (1)

bbp_update_forum_reply_count_query_log.txt (413.6 KB) - added by vibol 6 years ago.

Download all attachments as: .zip

Change History (12)

#1 @johnjamesjacoby
6 years ago

  • Keywords needs-patch added
  • Milestone changed from Awaiting Review to 2.5

Moving to 2.5 so we can address this with a few other performance issues. Thanks for attaching the log.

#2 @johnjamesjacoby
6 years ago

  • Milestone changed from 2.5 to 2.6

Bump to 2.6.

#3 @johnjamesjacoby
6 years ago

  • Component changed from General to Performance Improvements

#4 @johnjamesjacoby
6 years ago

  • Owner set to johnjamesjacoby

#5 @netweb
6 years ago

Some quick stats based on 1 forum with 827 Topics and 1,640 replies ( @vibol's above info is ~22,500 topics in 1 forum)

bbp_update_forum_reply_count (\includes\forums\functions.php#1519)

Current: Query took 0.0327 sec (Each of the 827 topic id's in this forum omitted for readability)

SELECT COUNT(ID) 
FROM wp_posts 
WHERE post_parent 
IN ( 103020,103016,103013,103010,103006... ) 
AND post_status = 'publish' AND post_type = 'reply';

Alternate: Query took 0.0141 sec

<?php
$reply_count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(ID) FROM {$wpdb->posts} WHERE post_parent IN ( SELECT ID  FROM `wp_posts` WHERE `post_parent` = $forum_id   ) AND post_status = '%s' AND post_type = '%s';", bbp_get_public_status_id(), bbp_get_reply_post_type() ) );

SQL

SELECT COUNT(ID)
FROM wp_posts 
WHERE post_parent 
IN ( 
    SELECT ID  FROM `wp_posts` WHERE `post_parent` = 96480  
) 
AND post_status = 'publish' AND post_type = 'reply'

This conservatively halves the query time though because we are not using bbp_forum_query_topic_ids we are including 'any' reply including those that are NOT public.

Pretty much just thinking out loud here...

#6 @netweb
6 years ago

Related: #2533

#7 @netweb
6 years ago

Extending this ticket to include 'all the forum things' around bbp_update_forum()

  • bbp_update_forum_last_topic_id
  • bbp_update_forum_last_reply_id
  • bbp_update_forum_last_active_id
  • bbp_update_forum_last_active_time
  • bbp_update_forum_subforum_count
  • bbp_update_forum_reply_count
  • bbp_update_forum_topic_count
  • bbp_update_forum_topic_count_hidden

This ticket was mentioned in IRC in #bbpress-dev by netweb. View the logs.


6 years ago

#9 @netweb
6 years ago

  • Milestone 2.6 deleted
  • Resolution set to duplicate
  • Status changed from new to closed

Closing this ticket in favor of #1799 as it already has a work in progress patch for bbp_update_forum_reply_count

This ticket was mentioned in IRC in #bbpress-dev by sc0ttkclark. View the logs.


6 years ago

#11 @UmeshSingla
3 months ago

  • Cc UmeshSingla added
Note: See TracTickets for help on using tickets.