WordPress.org

Ready to get started?Download WordPress

Forums

Long, dark night of "the loop"... (56 posts)

  1. AdrianB
    Member
    Posted 9 years ago #

    "If you want to see the WordPress community's own kind of FUD at work, have a look at the answers to this WP support topic"

    http://lightpress.org/post/wordpress-is-fast-mysql-is-slow-fud-at-work

  2. Mark (podz)
    Support Maven
    Posted 9 years ago #

    There is an argument brewing in this thread which has a place on some sort of hardcore coding forum, but not here. The vast majority of people don't have the passion about this.

    BUT

    As someone who answers posts here to help others, jerome's diagnostic plugin will prove VERY useful when posters are seeking help for slow sites.

    jerome++

  3. hooopla
    Member
    Posted 9 years ago #

    As someone who answers posts here to help others, jerome's diagnostic plugin will prove VERY useful when posters are seeking help for slow sites.

    Seconded. I'm setting up a test instance this morning to try and nail this problem and Jerome's plugin will be where I start.

  4. trevorturk
    Member
    Posted 9 years ago #

    All I'm getting right now is:

    <!-- 16 queries. 0.403 seconds. -->

    Which is cool, but might be too little information to track down a source of trouble...?

    But it's very cool. I personally think that speed is one of best things about WP, and any work done to improve/maintain speed is great to see. So, thanks!

  5. lambic
    Member
    Posted 9 years ago #

    Sorry I turned the thread into an argument, I don't like to see mis-information going un-refuted.

    Just to add something productive to this post, here are other things to consider when thinking about performance:

    1. MySQL tuning
    2. Web server tuning
    3. Turn off plugins one by one to see if one is being bad
    4. Ask your service provider if they're having problems
    5. Caching

    I've had a couple of days when response time on my blog has been horrible. Both times it turned out to be a problem on the server that my provider had to sort out.

  6. hooopla
    Member
    Posted 9 years ago #

    Which is cool, but might be too little information to track down a source of trouble...?

    Read the plugin's instructions (i.e., view the plugin using an editor). You need to turn on SAVEQUERIES and then view the output in page source. There's lots of useful information there.

  7. LoneBoat
    Member
    Posted 9 years ago #


  8. LoneBoat
    Member
    Posted 9 years ago #

    Hey darkcanuck, thanks for that quick-n-easy diagnostics code snippet...

  9. darkcanuck
    Member
    Posted 9 years ago #

    (someone's got to make those next/previous page links a little more obvious... and fix the thread feed links)

    trevorturk, make sure you enable SAVEQUERIES as pointed out by hooopla. If it could be turned on in the plugin I would have done it, but alas this is not possible.

    LoneBoat et al, you're very welcome. This plugin doesn't do very much, but I'm glad that you find it useful.

  10. hooopla
    Member
    Posted 9 years ago #

    OK, here's what I've found out using Jerome's diagnotic plugin.

    The WP Paginate plugin appears to be reading every row in the table in order to determine the total number of records -- rather than using a row count. In case anyone's interested in seeing the output from Jerome's Query Diagnostics when this plugin is activated, here it is. (Queries 4, 5, and 6 seem to be looking at every post.) Compare with no plugins activated here. Since what I know about MySQL fits on the head of a pin, I could be totally wrong, but I'll follow up with the plugin author and see what she thinks.

    Thanks again for everyone's help with this problem.

  11. darkcanuck
    Member
    Posted 9 years ago #

    Oh my...

    The query to look at is [3] (as ugly as [4] and [5] look, [3] is the real troublemaker):
    - in the no plugin version, the query is limited to the first 10 posts
    - with plugins activated, the query grabs all posts every time.

    Besides the extra query time, this may cause WP to try to preprocess all of the posts returned.

  12. hooopla
    Member
    Posted 9 years ago #

    - in the no plugin version, the query is limited to the first 10 posts
    - with plugins activated, the query grabs all posts every time.

    I have Options>Reading>Show at most set to "10 posts", so I'm guessing that WP Paginate overrides that setting and grabs all posts in order to do its own calculations. Is that a reasonable interpretation? (I'm asking because I want to communicate as clearly as I can with the plugin author and I don't totally understand what the queries are saying.)

  13. davidchait
    Member
    Posted 9 years ago #

    3 is bad, but 4 and 5 show some nasty things going on as well. Why are there a couple hundred posts being processed every pageload?? One is a comment-count thing (which I hate... maybe it's time for a plugin), the other is something similar but for meta fields.

    -d

  14. trevorturk
    Member
    Posted 9 years ago #

    ah, sorry about missing those instructions. this is a really cool plugin - thanks!

  15. hooopla
    Member
    Posted 9 years ago #

    3 is bad, but 4 and 5 show some nasty things going on as well. Why are there a couple hundred posts being processed every pageload?? One is a comment-count thing (which I hate... maybe it's time for a plugin), the other is something similar but for meta fields.

    Hmmm... I don't even use comments on my site. :-(

    I'm guessing that those posts (actually everything in my database) are being processed to arrive at a total_posts number in order to calculate total_pages and display that in the navigation menu at the bottom of the page (i.e., the "Page: 1 | 2 | 3 | ... | 220" stuff).

    The meta field query I have no clues about. If it's in the only_wp-paginate.txt file but not in the no_plugins.txt file, then I can't guess.

  16. Karel
    Member
    Posted 9 years ago #

    I didn't get the plugin to work. I am working with my adaptation of Classic and I don't find a line "that includes the file wp-blog-header.php", or is that get_header(); ? I placed define ('SAVEQUERIES', true) before that line, between the <?php ?> thingies.

    And I don't find do_action('wp_footer'); but instead something like wp_footer();

    I placed the do_action() before wp_footer, behind, and as a replacement.

    Stumped.

  17. darkcanuck
    Member
    Posted 9 years ago #

    hooopla:

    Upon closer inspection, it looks like queries [2]-[6] are being added by the plugin ([7] onwards seems to match more closely with [2]+ in the non-plugin version). Either the plugin is executing these queries directly or else it is forcing WordPress to do another pass through "the loop". The latter is where I would start.

    The second loop ([7]+) appears more normal and uses your setting of 10 posts per page.

    My guess is that the plugin hijacks the first pass through "the loop" to count the number of posts available. Then it starts a second pass with the settings back to normal for output to the user. If that's all wp-paginate needs, a better approach would be to do a single query to count the posts rather than forcing the loop to do it.

  18. darkcanuck
    Member
    Posted 9 years ago #

    Karel,
    You need to add the SAVEQUERIES line to the *real* index.php, not the one in your theme. Look in your site's root directory (normally where you installed WordPress).

    <code>wp_footer(); does the same thing as <code>do_action('wp_footer'); - I'm just used to the old style so that's my error. I've updated the instructions.

  19. Amit Gupta
    Member
    Posted 9 years ago #

    Denis-de-Bernardy:
    I lack words to tell you how much I hate MySQL, its pathetic features, and its pathetic performance.
    Maybe it does lack some necessary features but there can be no doubt about MySQL's performance, second only to Oracle(yes, both are better than SQL Server which is quite slow as I've seen it & no, I'm not a Microsoft hater, I usually use & love Microsoft products a lot). Here's a Benchmark done in 2002 & that's talking about MySQL 4.0.1 & mind you, a lot of webhosts still have MySQL 4.0.x on their servers, not everyone's got v4.1.x yet!! And in that benchmark, Oracle9i came out as best but it was just slightly better than MySQL which came in second!! So you possibly can't say that MySQL has poor performance, as I know it, MySQL powers a lot of high-traffic websites which get a lot of traffic, much more than hooopla's website I think!!

    If your script doesn't work good, then you are to try to improve it & then also tweak the way you use the RDBMS for maximum performance!! :)

    Though its a point that MySQL does lack a lot of necessary features like Triggers, Stored Procedures, etc. that are a must in a good RDBMS but they are in development in v5 of MySQL. Besides we can hope for good products from MySQL despite it being a free database as it does has a commercial side as well, so the devs there are not volunteers but paid employees!!

    But the main thing that I noticed is that in LightPress, the developer has optimised the SQL a lot, so maybe WordPress can take a leaf out of its book and instead of putting in new features in next few releases, why not focus more on optimising the software so that it runs better than ever? I certainly would like to see WordPress run better & faster than now, in next releases rather than see any new feature(s)!!

  20. Karel
    Member
    Posted 9 years ago #

    Thanks, darkcanuck! Works wonderfully now. My hosting provider seems to have some trouble, but I really wanted to exclude problems on my side.

    BTW, I hacked the code a bit, so there is always the < !-- x queries/y seconds --> output.

  21. cjcollier
    Member
    Posted 9 years ago #

    Create an index on the date posted field :)

    http://dev.mysql.com/doc/mysql/en/create-index.html

  22. davidchait
    Member
    Posted 9 years ago #

    I actually have some custom code I use on my site, that caches queries for common data. I'll have to think if there's any way to make it into a plugin (right now it's custom function calls, as they need to intercept the database query before handing to $wpdb...).

    -d

  23. hooopla
    Member
    Posted 9 years ago #

    Create an index on the date posted field :)

    I'm probably going to do that. Thanks for the link.

  24. angsuman
    Member
    Posted 9 years ago #

    I think you shouldn't index on the date. Rather order it by the id. id's are automatcially indexed and it will be really fast.

  25. darkcanuck
    Member
    Posted 9 years ago #

    angsuman,
    you can have multiple indices into a table and they can be either single or multi-column. In general, a query will be faster if all of the fields used by WHERE clauses are indexed (separately or, even better, all together).

    So if the most significant WHERE clause uses the date, the query will execute much faster if the date is indexed.

    This is a bit of a simplification of course, query optimization is a fairly complex topic.

  26. angsuman
    Member
    Posted 9 years ago #

    @darkcanuck
    I am aware darkcanuck, have long rdbms experience :)

    I was just suggesting him a simple route. Indexes cost space too and many web hosting providers have limited space which includes database too.

    Personally I prefer not to use date based ordering as ID (auto generated) based ordering works just as well and technically more accurate. Primary keys are anyway indexed.

Topic Closed

This topic has been closed to new replies.

About this Topic