WP generated SQL for orderby meta is super slow on very large DBs

  • DuncanMarshall

    (@duncanmarshall)


    9 years, 2 months ago

    tl;dr: How do people with hundreds of of thousands of posts deal with very slow queries?

    I’m converting a very old and large Phorum installation to BBPress, some queries are extremely slow.

    It seems that when a query orders by meta value, this SQL is generated by WordPress:

    SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1  AND wp_posts.post_parent = 9  AND wp_posts.post_type = 'topic' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'closed' OR wp_posts.post_status = 'private' OR wp_posts.post_status = 'hidden') AND (wp_postmeta.meta_key = '_bbp_last_active_time' ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value DESC LIMIT 0, 15

    This query takes 20 seconds on my 750k post wp_posts table, with a powerful AWS DB instance. I’ve already tried DB optimization, and server optimization techniques, and while they have an impact, the query is still taking well over 15 seconds in a best case scenario.

    These orderby meta queries are pretty fundamental to BBPress, and I’m not sure it’s something I can code out without hacking BBPress core, or WordPress core.

    It seems that this is a common problem, and the offered solution is to remove the SQL_CALC_FOUND_ROWS part and achieve pagination in some other way. However, I’ve tried that, and no dice, it’s still slow. I think it’s the JOIN that’s causing the lag.

    I know that this is something that can be dealt with in one way or another, since I see BBPress installations with more posts than mine.

    Any solutions?

    Thanks.

Viewing 9 replies - 1 through 9 (of 9 total)
  • progresst

    (@progresst)

    9 years, 2 months ago

    You can try to build an index on one of the columns you suspect is the most sluggish ie. could benefit from and index using myPHPAdmin or similar tool outside of WP. Joins typically can be very piggy especially multiple JOINS where text or varchar columns are used. I’ve found an index to help tremendously in some cases.

    Thread Starter DuncanMarshall

    (@duncanmarshall)

    9 years, 2 months ago

    Doesn’t that create the risk that WordPress just gets rid of my index next time it updates?

    progresst

    (@progresst)

    9 years, 2 months ago

    If it’s a WP core table yes OR if BBPress changes the table in a future update, yes. I figured since this is a migration it would not matter after the import.

    Thread Starter DuncanMarshall

    (@duncanmarshall)

    9 years, 2 months ago

    I mean it matters in that I don’t want to hand over a site that’s going to break on the next update.

    To be clear, the site is slow with normal use because of the way WP handles these queries; the problem isn’t a slow import.

    There are so many sites out there that have hundreds of thousands if not millions of posts. How are they all coping?

    Dion

    (@diondesigns)

    9 years, 2 months ago

    There are so many sites out there that have hundreds of thousands if not millions of posts. How are they all coping?

    I really doubt there are many WordPress sites with hundreds of thousands of posts. The WordPress DBAL and DB schema pretty much insure a site that large would require a massive dedicated server (such as the one used here). WordPress is overall a great piece of sortware, but by far its weak point is its (poorly designed by today’s standards) DB schema. And as you’ve found, the “db-delta” function run during updates insures no one will ever write a plugin to try to fix the DB schema.

    I’ve found that most sites using WordPress with a discussion board fall into two categories: those who use real discussion board software, and those who regret they chose bbPress over real discussion board software.

    Please give some thought to using a quality standalone board solution with a plugin that integrates WordPress to the board. Your server will thank you because it will not be struggling, and your users will thank you because your site will not be slug-slow.

    Moderator bcworkz

    (@bcworkz)

    9 years, 2 months ago

    I’m inclined to agree with DionD that you should use a package that is designed from the ground up to do what you want and stay away from adaptive solutions that were meant for one thing and tortured into doing something else.

    However, a large WP/BBpress solution is possible. There are some very large sites that are WP based. Running BBpress as well I’m not so sure, but there’s at least one, these forums. There’s several million posts here. Do note that these forums are far from an out of the box implementation, it’s been heavily tweaked. I’ve also no idea what it’s running on, I would expect it to be fairly impressive. You can’t run a sizable site on shared hosting.

    Large sites do need to be actively managed to ensure the DB is working as efficiently as possible. BBpress can be made to work, but you will need to work at it. A dedicated solution also will require work, but I have to think you’ll be better off.

    Thread Starter DuncanMarshall

    (@duncanmarshall)

    9 years, 2 months ago

    I really doubt there are many WordPress sites with hundreds of thousands of posts.

    I guess by “so many” I just meant that there’s at least a few, and they’re big and successful, and obviously not falling over every five minutes: http://bbshowcase.org/forums/view/top100

    Do you guys mean to say that WordPress.org et al are actually kind of doing a stupid thing, and they’d save on their VPS bill if they just accepted that they should be using PHPBB? Are they just constantly kludging BBPress in to working every time they hacks go obsolete?

    Tangentially, why hasn’t WordPress just been made to be scalable yet?

    Moderator bcworkz

    (@bcworkz)

    9 years, 2 months ago

    The choices are not black and white, right or wrong, at least IMO. More like shades of gray. Semantics can exaggerate the differences more than is warranted. WordPress et al are hardly stupid. Bold perhaps. They could have had an easier way of it but stuck with their product. They are not kludging the installation, more like adjusting and improving. I’d expect hardly anything done ever goes obsolete. WP is generally very good at being backwards compatible with properly written code.

    WordPress is scalable. You just have to work a bit harder than some other options. Scaling up to a large site will be work no matter what. Every site is unique, requiring unique adaptations. Could WP be better? Sure, but to really be better would be a major rewrite that would not be backwards compatible. It is the path consciously not taken. No one package is going to be the answer for everyone. For various reasons a lot of site owners decided WP is the answer. Others have not. No one is wrong, but some are better off.

    Dion

    (@diondesigns)

    9 years, 2 months ago

    I guess by “so many” I just meant that there’s at least a few, and they’re big and successful, and obviously not falling over every five minutes: http://bbshowcase.org/forums/view/top100

    Do you guys mean to say that WordPress.org et al are actually kind of doing a stupid thing, and they’d save on their VPS bill if they just accepted that they should be using PHPBB? Are they just constantly kludging BBPress in to working every time they hacks go obsolete?

    By the standards of discussion boards, the sites in your link are not large. HERE is a large site, which uses a heavily-modified version of phpBB2.

    I believe the wordpress.org server was donated by EIG. This is fairly common with large GPL2 open source software. The Drupal, Joomla, and phpBB sites are all hosted on servers that were donated.

    THe following is based on personal experience. A bbPress site with 100,000 posts will require a moderate-sized VPS. A phpBB3 board with 100,000 posts can be run on shared hosting. Kunena (Joomla’s answer to bbPress) requires even more resources than bbPress, but that’s more due to the huge overhead of Joomla than anything else.

    Anyway, good luck with your board conversion!

Viewing 9 replies - 1 through 9 (of 9 total)
  • The topic ‘WP generated SQL for orderby meta is super slow on very large DBs’ is closed to new replies.