WordPress.org

Ready to get started?Download WordPress

Forums

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

  1. hooopla
    Member
    Posted 8 years ago #

    I've gone from 0 to just over 2000 posts on my site in just under two months. My initial render time was about 0.10 seconds and it's now over 2.0 seconds. This is worrying and I need to understand more about why performance is degrading so quickly.

    The way I understand "the loop", WP is pulling the 10 most recent posts, then stopping and displaying a page with a navigation menu. (I'm using Scriptygoddess' WP Paginate plugin for the nav menu. It displays the total number of pages, which I assumes it calculates from a table's row count.)

    If that's how the loop works, then why would it make any difference to render time how many records there are?

    I know there are caching plugins, etc., that can help here. At the moment, though, I just want to understand what the problem is. I'm hoping to build this database at a rate of 15,000 entries a year, but I'm not a programmer and don't want to have to hack my way to acceptable performance. Should I be looking at a different platform (e.g., Drupal or some other "enterprise" CMS)? Can WP manage a database of that size efficiently?

    I'd appreciate any thoughts or advice.

  2. Root
    Member
    Posted 8 years ago #

    Here is a line of thought. The 10 most recent posts are not necessarily stored at the top of the pile in mysql.

  3. hooopla
    Member
    Posted 8 years ago #

    Here is a line of thought. The 10 most recent posts are not necessarily stored at the top of the pile in mysql.

    I don't know how WP's records are stored, but -- just thinking out loud, here -- wouldn't you need your data accessible in date order if your application (i.e., a blog) is going to display records in date order?

    I don't know anything about MySQL but I know other platforms let you create indexes on different fields, so that you get fast access no matter what physical order the records are in. I'm thinking MySQL -- being a powerhouse DBMS -- must support that kind of thing.

  4. Denis de Bernardy
    Member
    Posted 8 years ago #

    Well, as far as I can tell, WordPress is better than most tools when it comes to performance. You might want to upgrade to the very latest of WP, and to get the WP-Cache plugin. Both will significantly increase the performance of your site.

    Also, check your MySQL database. [Long pause] I lack words to tell you how much I hate MySQL, its pathetic features, and its pathetic performance. I hate it! I hate it! I hate it! Ahem... Make sure it is properly indexed and optimized.

    In particular, I recall there is an optimize table feature somewhere in phpMyAdmin. And you'll want to index fields that you access (or use to order by) the most.

  5. Root
    Member
    Posted 8 years ago #

    Accessible by date order does not mean that they are added to the table top. More likely the bottom I would think. This has to be a mysql tweaking thing as D de B suggests.

  6. hooopla
    Member
    Posted 8 years ago #

    Thanks, Denis and Root.

    I'm trying to take a long view of this issue. The latest version of WP might improve performance a bit (I'm still running 1.5.1) but I'm guessing that that improvement will be quickly offset by the increasing size of the database.

    I will look at the WP-Cache plugin. The way I understand caching, though, you lose the ability to display random data (e.g., quotes and images) since the page is built and then cached. Repeat visits during the cache interval will mean you'd see the same "random" components. Or am I wrong about that?

    Yes, there is an optimize table feature in phpMyAdmin. I've never used it because I don't really understand it. But I'll investigate.

    Your comment about wanting to index the fields used most surprises me -- and maybe that's just because I don't understand how WP interacts with MySQL. I'd have thought that WP would "instruct" MySQL to create the indexes it needs -- not the user!

    Obviously, I have a lot to learn about this. Thanks again.

  7. lambic
    Member
    Posted 8 years ago #

    It doesn't matter how well indexed a table is, the more data that is in it the slower it will be. That's just a fact of life in databases. You might be able to tune things up in MySQL, and the WP people might be able to tune their SQL, but MySQL isn't the fastest RDBMS in the world by any stretch.

  8. hooopla
    Member
    Posted 8 years ago #

    It doesn't matter how well indexed a table is, the more data that is in it the slower it will be.

    When you say "more data", are you talking about the size of the database or the number of records?

    Each post on my site is lightweight: a Title, Categories, Source (a small custom field), and the Body is a few sentences of text -- no links other than the Title, no images, no comments, no trackbacks. Compared to most blogs, an entry on mine is tiny. I'm guessing that the size of the database is relatively small even though the number of records is high.

  9. lambic
    Member
    Posted 8 years ago #

    Size means number of rows, number of columns, and volume of data, they all play a part. Number of rows is probably the most important though.

  10. Denis de Bernardy
    Member
    Posted 8 years ago #

    Well, a "real" DB would optimize stored procedures along the lines of what you say, and more. But MySQL is everything but a real DB. In MySQL you get, er... tables and records and er... lemme see... er... and that's about it. Even the SELECT statement doesn't work properly.

    That to say, you'll want to create a couple of indexes. As a rule, anything that you query often (e.g. a date field, etc.), do not hesitate to index it. When using a real DB, indexing is hardly useful until you join tables with hundreds of thousands of records. MySQL, however, will take forever to make even the simplest join on 10,000 records if there is no proper index.

    Also note that the optimizer of MySQL is rotten. If you're managing a lot of records, you might want to insert a AND 1 = 0 in the loop's where clause via the relevant plugin hook, so as to rewrite the query entirely via the the_posts hook. Indeed, and in spite of php's reputably poor performance, I found that it was sometimes worthwhile to work around MySQL's ludicrous performance by querying a couple of bits myself, in order to "manually" optimize the query.

  11. hooopla
    Member
    Posted 8 years ago #

    Thanks, lambic. Not good news, but what I needed to know.

  12. hooopla
    Member
    Posted 8 years ago #

    Thanks, Denis. I honestly had never heard MySQL criticised on those points before. Do you have any thoughts about whether PostgreSQL or SQLite are better performers for a large database?

  13. lambic
    Member
    Posted 8 years ago #

    Postgres is a better RDBMS, but it is less supported.

  14. Denis de Bernardy
    Member
    Posted 8 years ago #

    PGSQL is a bit better, for the little I played with it. I've never tried SQLite. But, it won't make much difference if you're going to stick to open source software. Most open source devs are people who have no clue of how to use stored procedures, triggers or SQL functions, so you're essentially comparing simple select, insert, update and delete statements that devs bind together using php. Whereas performance-wise, you should be comparing how the DB handles a bunch of immensily complex stored procedures.

  15. lambic
    Member
    Posted 8 years ago #

    Umm, why the obsession with stored procedures? For a simple application like a blog, straight-forward queries should be fine, and in many cases more efficient than using procedures.

    Even in Oracle I wouldn't use stored procedures to return my data in a blog app.

  16. Denis de Bernardy
    Member
    Posted 8 years ago #

    For one thing, because a stored procedure is compiled, so will run faster. Also, each time the stored procedure is run, a gen alg in the SQL engine investigates further optimization until it finds the optimal sequence that leads to the result set.

  17. Kafkaesqui

    Posted 8 years ago #

    <stir type="pot">How exactly would stored procedures help the majority of WP users who are running it off MySQL versions significantly below 5.0?</stir>

  18. Denis de Bernardy
    Member
    Posted 8 years ago #

    Well, in wordpress' case, it wouldn't; moreover, MySQL 3 compatibility is so central to WP that were WP to go for a PGSQL version, I'd guess the devs would continue using $wpdb. i was just answering lambic's question. :)

  19. lambic
    Member
    Posted 8 years ago #

    Returning result sets using stored procedures will not be faster than a tuned piece of SQL on any decent RDBMS. The time it takes to parse a piece of SQL is insignificant compared to fetch times.

    There are all sorts of things that make an RDBMS like Oracle faster than MySQL, but stored procedures isn't one of them.

  20. Denis de Bernardy
    Member
    Posted 8 years ago #

    Yes, but beyond the parse time, your RDBMS (of which the R is a fallacy in MySQL's case) will then do a sequence of actions in order to find the result set, and different strategies lead to shorter or faster queries. On the one side, you do a query, and optimize the code accordingly. On the other, you do a sp, and optimize the code in a like manner. The difference is what happens immediately after: Whereas the select statement will be processed exactly the same way next time you run it, the optimizer will inspect where it could have gained time with the sp so as to run it faster the next time. The same kind of gen algs run your email spam filter: You'll get the most of it by training them.

  21. lambic
    Member
    Posted 8 years ago #

    I'm not sure which RDBMS you're talking about. My expertise is only in Oracle, and I can tell you that what you're saying is completely incorrect in that case. The Oracle optimizer treats SQL the same, whether it came from a stored procedure or an external call. In both cases it will "learn" to be better over time.

  22. Denis de Bernardy
    Member
    Posted 8 years ago #

    I toy with the MS gadget most of the time. :)

  23. darkcanuck
    Member
    Posted 8 years ago #

    Folks, take a step back for a second to consider the problem rather than engaging in a my-db-is-better-than-your-db argument.

    There are 2 possible causes for the slowdown on hooopla's site:

    1) Poor scaling in the posts loop due to query ineffeciency, badly structured db schema or poor db engine performance.

    2) Poor scaling in other queries executed by WordPress.

    While there may be some cause to examine the first, the likely culprit is the second one. Does the site list links for categories or archives? Those queries are more complicated than you think and will degrade in performance as the database grows in size. How many plugins are in use on the site? Many plugins run inefficient queries and add unnecessary processing time.

    If you really want to know what's going on, try turning on SAVEQUERIES and dump the value of $wpdb->queries at the bottom of the page. You'll see what WordPress is trying to do and how long each query takes to execute.

  24. Mark (podz)
    Support Maven
    Posted 8 years ago #

    Is there any mileage in someone creating some sort of diagnostic plugin for this execution stuff ?

  25. darkcanuck
    Member
    Posted 8 years ago #

    Podz, I don't think you can set SAVEQUERIES from a plugin early enough to catch *all* queries. I can look into making a quick plugin.

    For what it's worth, if you want to do diagnostics, add the following to your index.php (before the wp-blog-header.php include):
    define('SAVEQUERIES', 1);

    And add this code somewhere in your site's footer:
    echo "
    <div class=\"stats\">
    {$wpdb->num_queries} queries, ";
    timer_stop(1);
    echo " seconds.
    </div>
    ";

    if (SAVEQUERIES)
    {
    echo "<!--";
    print_r($wpdb->queries);
    echo "-->";
    }

  26. Laughinglizard
    Member
    Posted 8 years ago #

    Hoopla,
    I personally think your problem is outside of the core WordPress code and might be some inconsistencies in your MySql database rather than the inability of WordPress itself or the RDBMS.
    If you prefer and if you cannot find any good answer soon, please feel free to contact me directly at mark at wltc dot net and I can try to find a solution for you.

  27. Neil Mickelson
    Member
    Posted 8 years ago #

    I know that I would LOVE to see a "diagnostics" plugin for WordPress...I have no idea what's available in the code for diagnosing problems, and being able to get access to this would be just phenomenal. Bonus points for making the output use a JavaScript popup window so it doesn't ruin my blog layout. <grin>

  28. darkcanuck
    Member
    Posted 8 years ago #

    Here's a quick plugin to do the above: jeromes-query-diagnostics

    Note that the plugin can't set the SAVEQUERIES constant itself, you have to do it manually (see plugin file for details). No JS required.

  29. hooopla
    Member
    Posted 8 years ago #

    Thank you, darkcanuck and LaughingLizard. I appreciate your help very much.

    I'm going to set up some tests tonight and tomorrow to see if one of the plugins I use is causing this. I'll also add that snippet to my front page and see where the queries are taking so much time. Several people now have suggested that this isn't typical behaviour for WP core, and my host runs such a finely-tuned server that it's frightening... ;-)

    PS: I'm with podz and mickelsn1 re: a plugin. I'm sure there are lots of people like me with a need to identify performance problems but without the skills to figure them out at the command prompt.

  30. hooopla
    Member
    Posted 8 years ago #

    Here's a quick plugin to do the above: jeromes-query-diagnostics

    Jeez, what took you so long! :-)

Topic Closed

This topic has been closed to new replies.

About this Topic