Support » Fixing WordPress » Astounding performance hit with large number of rows in wp_users

  • While working on my companies install of wordpress I noticed that comments now have a “user must be registered and logged in” status. Interested I investigated further and liked what I saw.

    so I grabbed our user database to use as a sample recordset and imported them all into wp_users.

    with 25,000 users in the wp_users table every page takes over 5 seconds to render and the apache process spikes up to 50%, the advanced posts form and the authors & users page both render a complete list of uses which takes even longer (30+ seconds).

    I have not investigated this for more than 10 minutes, I have only just noticed the problem and will post again when I have narrowed down the possible causes.

    PS, when all the users were removed from the table everything was once again snappy.

Viewing 10 replies - 1 through 10 (of 10 total)
  • Well, the cause is obviously the large number of user, is it not?

    Thread Starter womby

    (@womby)

    Well of course, but would it not be usual to expect a site that requires users to register before they can comment to have a large number of users registered 😉 .

    Edit: Further investigation reveals that the Staticize plugin does not help, the delay is happening before the plugin acts.

    Well, the solution is if you have a number of users larger than x, then don’t use this feature. 😛

    Thread Starter womby

    (@womby)

    Well of course, but, there is no reason the number of users in wp_users should cause the image upload page to take a long time to render, the most it needs to do is check if I have permission to upload.

    To check if you have permission, WP has to go through, at worst case, all 25000 users.

    EDIT: or maybe not. I don’t know exactly how it’s implemented.
    So, yeah, that’ll be great if you could point out exactly the cause.

    Thread Starter womby

    (@womby)

    found it,

    if ( !update_user_cache() && !strstr($_SERVER["PHP_SELF"], "install.php") ) {
    if ( strstr($_SERVER["PHP_SELF"], "wp-admin") )
    $link = "install.php";
    else
    $link = "wp-admin/install.php";
    die("It doesn"t look like you"ve installed WP yet. Try running <a href="$link">install.php</a>.");
    }
    $wpdb->show_errors();

    commented that part out of wp-settings and boom site was rendering in 0.09 seconds.

    EDIT: I am guessing that “update_user_cache” is the offending item.

    I suppose so. Here’s a bit of what’s in that function.
    if ( $users = $wpdb->get_results("SELECT * FROM $wpdb->users WHERE user_level > 0") )

    Hi Alphaoide,

    try adding an index to the wp_users table and user_level column.
    i.e. ALTER TABLE wp_users ADD INDEX ( user_level )
    there is no index on that column so MySQL has todo a complete talbe scan which takes awhile. 🙂

    Regards
    adsworth

    Thread Starter womby

    (@womby)

    looking through the code the update_user_cache populates the array cache_userdata with, well, all the data about all the users on the system.

    then later when ever any user information is needed by the blog that array is checked first, so far I have not found an instance where an un-populated array will cause a failure, in all cases a secondary method of getting the data is present.

    I suspect that this is a problem that can be pushed into 1.6 for resolution.

    EDIT: I Just tested with my blog (only 5 users on that) and with the entry commented out it performed 2 extra sql queries and the time taken to render the page was no different, I am sure if I had thousands of page loads a second it would make a difference though, this deserves further study.

    I would suggest a 1.6 resolution of an on-use cache with a fixed limit.
    So once you lookup the user, cache the info for a certain time. Limit the list to a reasonable number and do a least recently used replace.

Viewing 10 replies - 1 through 10 (of 10 total)
  • The topic ‘Astounding performance hit with large number of rows in wp_users’ is closed to new replies.