[development] Database queries on drupal.org

[Gerhard Killesreiter]

Gerhard Killesreiter wrote:
> Gerhard Killesreiter wrote:
> 
>> Since last night, I let the devel module collect query statistics on 
>> drupal.org. We now can see which queries are run very often and which 
>> are just slow.
> 
> 
> I have now stopped the data collecting.

I did a second run of collecting data, this time with my patch for 
better caching applied (http://drupal.org/node/72617). This patch in its 
current form caches certain elements created by forum module and tries 
to invalidate them only when really needed.

>> Here are the first queries by accumulated time.
>> The first column is accumulated time, the second average time/query, 
>> the third the number of times the query was run, the fourth the 
>> function which calls the query, the last the query itself:

> 
> 
> Here again the first six queries:
> 
> 12585.885    0.01    1322443    cache_get    SELECT data, created, 
> headers, expire FROM cache WHERE cid = S
> 7524.278    0.001    6814856    drupal_lookup_path    SELECT dst FROM 
> url_alias WHERE src = S
> 6062.889    0.107    56508    theme_forum_topic_navigation    SELECT 
> n.nid, n.title, n.sticky, l.comment_count, l.last_comment_timestamp FROM 
> node n INNER JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN 
> term_node r ON n.nid = r.nid AND r.tid = D
> 4343.435    0.024    183059    node_load    SELECT n.nid, n.vid, n.type, 
> n.status, n.created, n.changed, n.comment, n.promote, n.moderate, 
> n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, 
> r.log, r.format, u.uid, u.name, u.picture, u.data FROM node n INNER JOIN 
> users u ON u.uid = n.uid INNER JOIN node_revisions r ON r.vid = n.vid 
> WHERE n.nid = D
> 1837.531    0.04    45429    node_load    SELECT n.nid, n.vid, n.type, 
> n.status, n.created, n.changed, n.comment, n.promote, n.moderate, 
> n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, 
> r.log, r.format, u.uid, u.name, u.picture, u.data FROM node n INNER JOIN 
> users u ON u.uid = n.uid INNER JOIN node_revisions r ON r.vid = n.vid 
> WHERE n.nid = S
> 1538.485    0.011    143889    cache_set    INSERT INTO cache (cid, 
> data, created, expire, headers) VALUES (S)

Here the same queries for comparison:


5684.353	0.007	765611	cache_get	SELECT data, created, headers, expire 
FROM cache WHERE cid = S
2848.273	0.001	3187992	drupal_lookup_path	SELECT dst FROM url_alias 
WHERE src = S
2100.561	0.207	10170	theme_forum_topic_navigation	SELECT n.nid, n.title, 
n.sticky, l.comment_count, l.last_comment_timestamp FROM node n INNER 
JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN term_node r 
ON n.nid = r.nid AND r.tid = D
1754.965	0.019	94054	node_load	SELECT n.nid, n.vid, n.type, n.status, 
n.created, n.changed, n.comment, n.promote, n.moderate, n.sticky, 
r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, 
r.format, u.uid, u.name, u.picture, u.data FROM node n INNER JOIN users 
u ON u.uid = n.uid INNER JOIN node_revisions r ON r.vid = n.vid WHERE 
n.nid = D
942.805	0.01	97798	db_lock_table	LOCK TABLES cache WRITE
706.889	0.008	86837	cache_set	INSERT INTO cache (cid, data, created, 
expire, headers) VALUES (S)


Since I did run this second test for less time than the previous one, 
the numbers obviously differ. However, there is a way to compare them: 
We use the drupal_lookup_path as a gauge and assume that per time unit 
the amount of these queries is a constant. I could also look at the 
total number of queries, but I'd have to load the other tables again to 
obtain it. ;)

For the first set of queries there have been about 5 times as many 
lookup path queries than cache_get queries. For the second time this 
factor is only about 4. This was to be expected: More cached stuff needs 
more cache_get queries.

The ratio of lookup path vs theme_forum_topic_navigation queries changed 
from about 120 to about 310, so we have much less queries of the forum 
topic kind and can infer that this kind of caching is rather efficient. 
There are about 3000 cache entries in the database that were generated 
by my patch.

You will also observe that if you look at the time that is spend doing 
this query, we don't gain much...

While we don't execute as many queries, the individual query is more 
expensive by almost a factor of 2. I attribute ths to the fact that the 
query didn't get cached by the MySQL query cache anymore since it is run 
less frequent.

But we didn't want to focus on the query cache anyway. ;)

There is also another way to make this particular query more efficient: 
http://drupal.org/node/76324

Cheers,
	Gerhard