Many Users Causes Export Page to Fail

[filosofo]

On a non-MS installation with over 10,000 users, the call to wp_dropdown_users on line 139 of wp-admin/export.php causes the page to time out as it attempts to query all 10,000 to figure out which are authors and load them into a dropdown. The result is that export is impossible.

I think perhaps a better solution would be an autosuggest text box for selecting the authors to filter by. If I can get some agreement about that, I'll write up a patch.

[scribu]

+1. I suggested something similar in other places: #15085

[Gabriel Reguly]

Replying to scribu:

+1. I suggested something similar in other places: #15085

+1. Sounds sensible.

[nacin]

For 3.1: need to only select authors, not all users. marking highest priority.

For future: autosuggest seems like a good idea

[nacin]

Apparently our query generated here has changed and has lost some serious performance. This affects the posts page too.

[duck_]

Also crashing on the posts list (edit.php), because of author dropdown in quick edit, and on single post edit page (post.php), because of the authors meta box.

Example fatal error:

Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 32 bytes) in /home/sites/trunk/wp-includes/wp-db.php on line 1399
 
Call Stack:
    0.0008     833296   1. {main}() /home/sites/trunk/wp-admin/edit.php:0
    0.2207   33044032   2. WP_Posts_List_Table->inline_edit() /home/sites/trunk/wp-admin/edit.php:250
    0.2218   33055688   3. wp_dropdown_users() /home/sites/trunk/wp-admin/includes/class-wp-posts-list-table.php:778
    0.2218   33066776   4. get_users() /home/sites/trunk/wp-includes/user.php:970
    0.2218   33069136   5. WP_User_Query->__construct() /home/sites/trunk/wp-includes/user.php:598
    0.2219   33073800   6. WP_User_Query->query() /home/sites/trunk/wp-includes/user.php:394
    0.3063   40401976   7. cache_users() /home/sites/trunk/wp-includes/user.php:522
    0.4526   62365728   8. _fill_many_users() /home/sites/trunk/wp-includes/pluggable.php:155
    0.4587   63220632   9. get_user_metavalues() /home/sites/trunk/wp-includes/user.php:1098
    0.4686   67508744  10. update_meta_cache() /home/sites/trunk/wp-includes/user.php:1041
    0.5072   71219944  11. wpdb->get_results() /home/sites/trunk/wp-includes/meta.php:327

As you can see this is being caused by trying to cache every single user matched by the query (approximately 6,500 of them). I tried just removing the call to cache_users in WP_User_Query->query() as an experiment, and although there was no longer a fatal error it was still crippled by the 6,500 database queries to build each user object and still used ~226MB memory (not looked into where).

For comparison 3.0.3 performs two queries, one to get the relevant user IDs (see get_editable_user_ids, now deprecated) and then another to get whole user row for each ID (wp_dropdown_users using the include parameter). So the second query has a huge IN condition as part of the WHERE clause and the browser struggles to render such a massive select dropdown (good reason for autosuggest I guess), but no fatal error (~65.114MB memory usage in testing with 8,500+ users).

Example patch 15786.export.diff to only include users with posts in the export dropdowns, but I don't think it will scale for a large number of different post authors with the current state of wp_dropdown_users and get_users/WP_User_Query. Should wp_dropdown_users just not even use get_users/WP_User_Query?

I also noticed that the user dropdowns when editing a post now show all users, not just those who have a user_level != 0 (that's 3.0.3 behaviour, again see get_editable_user_ids).

Couple of related changesets: [15539], [15542]

[scribu]

WP_User_Query already has a parameter for returning only the user ids.

However, what it would need is a 'with_posts' parameter that only fetches users with posts.

[scribu]

Looked at 15786.export.diff and I think it's the best solution for now.

I'm guessing it should be applied to all places where the dropdown is used.

[duck_]

Replying to scribu:

WP_User_Query already has a parameter for returning only the user ids.

Indeed, however the issue still stands as the full user object is pulled in at some point anyway, e.g. in calling wp_dropdown_users with an include array. Unless you get all the IDs via WP_User_Query and then use that in an individual query like wp_dropdown_users used to do.

However, what it would need is a 'with_posts' parameter that only fetches users with posts.

Sounds good.

[scribu]

Indeed, however the issue still stands as the full user object is pulled in at some point anyway, e.g. in calling wp_dropdown_users with an include array.

The full user objects were fetched in 3.0 as well, so with the current patch it should perform the same.

[scribu]

Revert to straight HTML + raw query

[scribu]

Don't JOIN posts & users; filter by post type

[scribu]

(In [16972]) Revert to raw SQL query and HTML in export author dropdowns. Fixes #15786

[filosofo]

Should I open another ticket for post authors?

[scribu]

For those wondering, we avoid a JOIN to allow the user tables to be in a separate database from the post tables.

filosofo: yes, please.

[scribu]

Actually, should probably re-open #14572

[scribu]

(In [16975]) Use wp_dropdown_users() in export.php again, due to [16974]. See #15786