_user_relationships_generate_query() check for oneway relationships is slow [#1183040]

When urt.is_oneway <> 0 is used, MySQL is forced to use ALL on the table join, making it an order of magnitude slower. As a rough benchmark, removing that condition makes a 1s query 1ms, and reduces the load time of /relationships by about 4 (!) seconds. On sites with many relationships, but few (or one) relationship type, this could be optimized better.

Here's the EXPLAIN output:

The current query:

EXPLAIN EXTENDED SELECT COUNT(DISTINCT rid) AS count FROM user_relationships ur INNER JOIN user_relationship_types urt USING ( rtid ) WHERE (ur.requester_id = 43 OR ((ur.approved <> 1 OR urt.is_oneway <> 0) AND ur.requestee_id = 43)) AND ur.approved = 1

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra	
1	SIMPLE	urt	ALL	PRIMARY	NULL	NULL	NULL	1	100.00	
1	SIMPLE	ur	ref	PRIMARY,requester_id,requestee_id,rtid,approved	rtid	4	grammy365_drupal_blizzard.urt.rtid	36801	100.00	Using where

And a modified query, assuming a single SELECT is used beforehand to get the value of urt.is_oneway:

EXPLAIN EXTENDED SELECT COUNT(DISTINCT rid) AS count FROM user_relationships ur INNER JOIN user_relationship_types urt USING ( rtid ) WHERE (ur.requester_id = 43 OR ((ur.approved <> 1 OR 0 <> 0) AND ur.requestee_id = 43)) AND ur.approved = 1

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra	
1	SIMPLE	urt	index	PRIMARY	name	768	NULL	1	100.00	Using index
1	SIMPLE	ur	ref	PRIMARY,requester_id,rtid,approved	PRIMARY	4	const	94	100.00	Using where

Comment	File	Size	Author
#11	1183040.11-optimize-oneway-queries.patch	3.8 KB	mrf
#8	1183040.8-optimize-oneway-queries.patch	3.91 KB	mrf
#6	1183040.6-optimize_oneway_queries_7.x.patch	12.4 KB	deviantintegral
#4	1183040.4-optimize_oneway_queries_7.x.patch	8.76 KB	deviantintegral
#1	1183040.1-optimize-oneway-queries.patch	8.17 KB	deviantintegral

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

deviantintegral CreditAttribution: deviantintegral commented 13 June 2011 at 19:29

Status:

Active

» Needs review

File	Size
1183040.1-optimize-oneway-queries.patch	8.17 KB

Here's a patch that fixes the indentation of the switch block, and only checks for oneway relationships that are actually configured. All tests currently pass. I'll post benchmarks soon, but on my localhost this brings loading /relationships down from ~2.4s to around 1.1s for a user with many relationships.

Comment #2

deviantintegral CreditAttribution: deviantintegral commented 13 June 2011 at 20:09

Results are a little less impressive for a different user account with a more representative number of relationships, but here's the ab results:

Before the patch:

$ ab -n 50 -c 1 -C <snipped> http://grammy365.lan:8888/relationships
This is ApacheBench, Version 2.0.41-dev <$Revision: 1.121.2.12 $> apache-2.0
Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright (c) 2006 The Apache Software Foundation, http://www.apache.org/

Benchmarking grammy365.lan (be patient).....done


Server Software:        Apache
Server Hostname:        grammy365.lan
Server Port:            8888

Document Path:          /relationships
Document Length:        92065 bytes

Concurrency Level:      1
Time taken for tests:   110.75511 seconds
Complete requests:      50
Failed requests:        6
   (Connect: 0, Length: 6, Exceptions: 0)
Write errors:           0
Total transferred:      4617940 bytes
HTML transferred:       4603240 bytes
Requests per second:    0.45 [#/sec] (mean)
Time per request:       2201.510 [ms] (mean)
Time per request:       2201.510 [ms] (mean, across all concurrent requests)
Transfer rate:          40.96 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.0      0       0
Processing:  2179 2200  24.3   2192    2295
Waiting:     2129 2149  24.2   2141    2245
Total:       2179 2200  24.3   2192    2295

Percentage of the requests served within a certain time (ms)
  50%   2192
  66%   2197
  75%   2205
  80%   2213
  90%   2230
  95%   2254
  98%   2295
  99%   2295
 100%   2295 (longest request)

After the patch:

This is ApacheBench, Version 2.0.41-dev <$Revision: 1.121.2.12 $> apache-2.0
Copyright (c) 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright (c) 2006 The Apache Software Foundation, http://www.apache.org/

Benchmarking grammy365.lan (be patient).....done


Server Software:        Apache
Server Hostname:        grammy365.lan
Server Port:            8888

Document Path:          /relationships
Document Length:        92065 bytes

Concurrency Level:      1
Time taken for tests:   86.103636 seconds
Complete requests:      50
Failed requests:        5
   (Connect: 0, Length: 5, Exceptions: 0)
Write errors:           0
Total transferred:      4617945 bytes
HTML transferred:       4603245 bytes
Requests per second:    0.58 [#/sec] (mean)
Time per request:       1722.073 [ms] (mean)
Time per request:       1722.073 [ms] (mean, across all concurrent requests)
Transfer rate:          52.37 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.0      0       0
Processing:  1696 1721  49.2   1708    1969
Waiting:     1644 1669  48.1   1656    1914
Total:       1696 1721  49.2   1708    1969

Percentage of the requests served within a certain time (ms)
  50%   1708
  66%   1710
  75%   1714
  80%   1721
  90%   1743
  95%   1810
  98%   1969
  99%   1969
 100%   1969 (longest request)

Comment #3

Berdir

German

Switzerland

CreditAttribution: Berdir commented 18 June 2011 at 14:38

Can you provide a patch against 7.x-1.x? I currently only commit patches for 7.x.

Comment #4

deviantintegral CreditAttribution: deviantintegral commented 20 June 2011 at 14:44

Version:

6.x-1.x-dev

» 7.x-1.x-dev

File	Size
1183040.4-optimize_oneway_queries_7.x.patch	8.76 KB

Here it is!

Comment #5

Berdir

German

Switzerland

CreditAttribution: Berdir commented 7 July 2011 at 08:56

Status:

Needs review

» Needs work

+++ b/user_relationships.moduleundefined
@@ -78,6 +78,13 @@ function _user_relationships_generate_query($param = array(), $options = array()
+
+  $oneway_relationship_types = array();
+  $oneway_result = db_query("SELECT rtid FROM {user_relationship_types} WHERE is_oneway <> 0");
+  foreach ($oneway_result as $result) {
+    $oneway_relationship_types[] = $result->rtid;
+  }

The list of relationship types returned by user_relationships_types_load() is statically cached. Instead of doing another query, it is probably faster to simply get all relationship types from there and then select those which are not oneway is probably faster. Also, some comments would be nice to see why we are doing this.

Powered by Dreditor.

Comment #6

deviantintegral CreditAttribution: deviantintegral commented 7 July 2011 at 14:32

Status:

Needs work

» Needs review

File	Size
1183040.6-optimize_oneway_queries_7.x.patch	12.4 KB

Here's a patch that uses user_relationships_types_load() and adds some additional comments.

Comment #7

Berdir

German

Switzerland

CreditAttribution: Berdir commented 7 July 2011 at 19:07

Version:	7.x-1.x-dev	» 6.x-1.x-dev
Status:	Needs review	» Patch (to be ported)

Thanks, commited.

Two notes:

- Many maintainers don't like multiple commits in a single patch, it quickly gets hard to review (with dreditor) and can be confusing. I merged latest commit into the previous one now.
- Remember to add your username to the commit message (Issue #xxx by deviantintegral: ...)

Marking as patch to be ported and back to 6.x-1.x (Although I can not guarantee you that anything will happen with a 6.x patch..)

Comment #8

mrf CreditAttribution: mrf commented 7 January 2012 at 00:12

Status:

Patch (to be ported)

» Needs review

File	Size
1183040.8-optimize-oneway-queries.patch	3.91 KB

So the first patch no longer applied and I manually updated it for 6.x dev. I included the types_load optimization from #6 as well. I am now getting mysql errors when visiting user profile pages and wanted to get another set of eyes on this to see if I'm missing something obvious.