WordPress.org

Make WordPress Core

Opened 5 years ago

Closed 5 years ago

Last modified 3 months ago

#15170 closed defect (bug) (fixed)

Network admin pages don't scale

Reported by: ryan Owned by: ryan
Milestone: 3.1 Priority: normal
Severity: normal Version: 3.1
Component: Multisite Keywords: ongoing-project
Focuses: Cc:

Description (last modified by ryan)

network/sites.php and network/users.php do queries like:

SELECT COUNT(wp_trunk_users.ID) FROM wp_trunk_users WHERE 1=1

SELECT COUNT( blog_id ) FROM wp_trunk_blogs WHERE site_id = '1'ORDER BY wp_trunk_blogs.blog_id ASC

These are very slow on large networks. get_blog_count() and get_user_count() would mitigate this somewhat, although they too can cause problem on large networks.

Perhaps users.php and sites.php should consult get_user_count() and get_blog_count() and not do a query if the count is over a certain threshold. The tables would default to being empty and a search would have to be performed to get results.

Further, a large network flag may be needed so that functions like get_blog_count() and get_user_count() can avoid ever running COUNT queries and instead rely on the network administrator to perform counts and populate the count site options via a separate job.

Attachments (3)

15170.diff (3.9 KB) - added by ryan 5 years ago.
15170.2.diff (1.7 KB) - added by ryan 5 years ago.
Show latest x sites with no paging if on a large network
network-themes.png (161.5 KB) - added by ryan 5 years ago.
Screenshot

Download all attachments as: .zip

Change History (32)

comment:1 @ryan5 years ago

  • Description modified (diff)

comment:2 @scribu5 years ago

Related: #15053

@ryan5 years ago

comment:3 @ryan5 years ago

(In [15875]) Update network-wide active user and blog counts via a cron job to avoid costly count queries. see #15170

comment:4 @jane5 years ago

  • Keywords iceberg added
  • Owner set to ryan
  • Status changed from new to assigned

Applying new tag, "iceberg," for tickets that will have multiple commits over time before they are expected to be closed as finished.

comment:5 @jane5 years ago

  • Keywords ongoing-project added; iceberg removed

Who am I kidding? The (tip of the) iceberg tag sounds cool and funny to those of us in here all the time, but new contributors will be confused. New tag will be ongoing-project.

@ryan5 years ago

Show latest x sites with no paging if on a large network

comment:6 @ryan5 years ago

Patch changes sites.php to show the latest x registered sites without paging to avoid expensive COUNT queries. I wanted to show nothing so we wouldn't need to do a query that is probably throw away, but showing nothing results in the table markup not being output which will break AJAX search requests which expect the table to already be there. So, this is a compromise.

@ryan5 years ago

Screenshot

comment:7 @ryan5 years ago

(In [16137]) For large networks, show the latest registered blogs without paging to avoid expensive count queries. see #15170

comment:8 @ryan5 years ago

$query .= " AND ( {$wpdb->blogs}.domain LIKE '%{$like_s}%' OR {$wpdb->blogs}.path LIKE '%{$like_s}%' ) ";

That is a killer query on large sites. We should probably lose the %s and saerch for $like_s.domain for subdomain installs and domain/$like_s for subdir installs.

comment:9 @ryan5 years ago

(In [16157]) Default to id since registered doesn't have an index. see #15170

comment:10 @ryan5 years ago

(In [16160]) Show latest x users with no paging if on a large network. see #15170

comment:11 @ryan5 years ago

(In [16161]) Lose wildcards in site search. Maybe bring it back as an option. see #15170

comment:12 @ryan5 years ago

(In [16166]) Drop Sites search action dropdown. Determine the search action from the search string. see #15170

comment:13 @ryan5 years ago

(In [16167]) Make IP query threshold more restrictive. see #15170

comment:14 @ryan5 years ago

(In [16169]) Allow trailing wildcard site searches by appending *. see #15170

comment:15 @ryan5 years ago

(In [16170]) Allow trailing wildcard user searches by appending *. see #15170

comment:16 @TobiasBg5 years ago

$wild = 'true';

should be

$wild = true;

for data type consistency, in line 451 of wp-includes/user.php in [16170].

comment:17 @ryan5 years ago

(In [16181]) Use bool not string. Props TobiasBg. see #15170

comment:18 follow-up: @scribu5 years ago

[16170] broke comment searches.

comment:19 @ryan5 years ago

(In [16262]) Drop display_name from user search columns. It doesn't have an index. see #15170

comment:20 in reply to: ↑ 18 @nacin5 years ago

Replying to scribu:

[16170] broke comment searches.

Sounds like we should override get_search_sql() in WP_User_Query. Nearing the point where WP_Object_Query can probably be killed off.

comment:22 @scribu5 years ago

(In [16351]) Split get_search_sql(). See #15170. See #15032

comment:23 @ryan5 years ago

(In [16489]) Site search performance tweaks. Use subquery for registration log queries to allow cross DB queries. Lose order by id by default. Search only by blog_id for numeric search strings. see #15170

comment:24 @ryan5 years ago

That loses some flexibility but greatly improves performance. WIll look at a better fix later.

comment:25 @designsimply5 years ago

Trailing wildcard searches were added in [16170]. Could we also allow wildcard user searches such as *gmail.com?

comment:26 @designsimply5 years ago

or *@gmail.com even... @ocean90 ;)

comment:27 @PeteMall5 years ago

Also need to update contextual help for this.

comment:28 @ryan5 years ago

  • Resolution set to fixed
  • Status changed from assigned to closed

comment:29 @slackbot3 months ago

This ticket was mentioned in Slack in #core by boren. View the logs.

Note: See TracTickets for help on using tickets.