[development] Database queries on drupal.org

Fri Jul 28 14:39:35 UTC 2006

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.

> 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:
> 
> 927.011    0.005    192603    cache_get    SELECT data, created, 
> headers, expire FROM cache WHERE cid = S
> 905.582    0.001    973355    drupal_lookup_path    SELECT dst FROM 
> url_alias WHERE src = S
> 883.059    0.098    8986    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
> 388.902    0.014    28330    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
> 186.896    2.967    63    pager_query    SELECT DISTINCT(n.nid), 
> n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS 
> last_post, l.comment_count FROM node n INNER JOIN users u ON n.uid = 
> u.uid INNER JOIN node_comment_statistics l ON n.nid = l.nid WHERE 
> n.status = D
> 180.948    0.007    26472    cache_set    INSERT INTO cache (cid, data, 
> created, expire, headers) VALUES (S)
> 157.065    0.022    6983    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
> 
> I am not sure why there are two node_load queries which look identical 
> otherwise. Probably these fields need to be merged.

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 are the first few queries by average query time, columns as above.
> 
> 155.447    3.048    51    pager_query    SELECT DISTINCT(n.nid), 
> n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS 
> last_post, l.comment_count FROM node n INNER JOIN 
> node_comment_statistics l ON n.nid = l.nid INNER JOIN users u ON n.uid = 
> u.uid LEFT JOIN comments c ON n.nid = c.nid AND (c.status = D
> 186.896    2.967    63    pager_query    SELECT DISTINCT(n.nid), 
> n.title, n.type, n.changed, n.uid, u.name, l.last_comment_timestamp AS 
> last_post, l.comment_count FROM node n INNER JOIN users u ON n.uid = 
> u.uid INNER JOIN node_comment_statistics l ON n.nid = l.nid WHERE 
> n.status = D
> 94.058    1.844    51    pager_query    SELECT COUNT(DISTINCT(n.nid)) 
> FROM node n LEFT JOIN comments c ON n.nid = c.nid AND (c.status = D
> 109.691    1.155    95    pager_query    SELECT DISTINCT m.*, r.*, 
> u.name, u.uid FROM cvs_messages m INNER JOIN cvs_repositories r ON m.rid 
> = r.rid INNER JOIN users u ON m.uid = u.uid ORDER BY m.created DESC LIMIT D
> 1.03    1.03    1    pager_query    SELECT a.aid, a.timestamp, a.url, 
> a.uid, u.name FROM accesslog a LEFT JOIN users u ON a.uid = u.uid WHERE 
> a.path LIKE S ORDER BY a.timestamp DESC LIMIT D
> 2.536    0.845    3    comment_edit    SELECT c.*, u.uid, u.name AS 
> registered_name, u.data FROM comments c INNER JOIN users u ON c.uid = 
> u.uid WHERE c.cid = D
> 0.614    0.614    1    eval    select message, count(message) as count 
> from watchdog where type = S and uid = D
> 0.434    0.434    1    aggregator_page_sources    SELECT f.fid, f.title, 
> f.description, f.image, MAX(i.timestamp) AS last FROM aggregator_feed f 
> LEFT JOIN aggregator_item i ON f.fid = i.fid GROUP BY f.fid, f.title, 
> f.description, f.image ORDER BY last DESC, f.title
> 0.429    0.429    1    pager_query    SELECT q.qid, q.query, q.function, 
> t.*, COUNT(t.qid) AS count, SUM(t.time) AS total_time, AVG(t.time) AS 
> average, STDDEV(t.time) AS stddev FROM devel_queries q INNER JOIN 
> devel_times t ON q.qid = t.qid GROUP BY t.qid ORDER BY total_time DESC 
> LIMIT D

1343.749	3.262	412	pager_query	SELECT DISTINCT(n.nid), n.title, n.type, 
n.changed, n.uid, u.name, l.last_comment_timestamp AS last_post, 
l.comment_count FROM node n INNER JOIN users u ON n.uid = u.uid INNER 
JOIN node_comment_statistics l ON n.nid = l.nid WHERE n.status = D
1045.001	2.969	352	pager_query	SELECT DISTINCT(n.nid), n.title, n.type, 
n.changed, n.uid, u.name, l.last_comment_timestamp AS last_post, 
l.comment_count FROM node n INNER JOIN node_comment_statistics l ON 
n.nid = l.nid INNER JOIN users u ON n.uid = u.uid LEFT JOIN comments c 
ON n.nid = c.nid AND (c.status = D
628.091	1.784	352	pager_query	SELECT COUNT(DISTINCT(n.nid)) FROM node n 
LEFT JOIN comments c ON n.nid = c.nid AND (c.status = D
1091.909	1.327	823	pager_query	SELECT DISTINCT m.*, r.*, u.name, u.uid 
FROM cvs_messages m INNER JOIN cvs_repositories r ON m.rid = r.rid INNER 
JOIN users u ON m.uid = u.uid ORDER BY m.created DESC LIMIT D
19.668	1.093	18	eval	select message, count(message) as count from 
watchdog where type = S and uid = D
147.989	1.08	137	cache_get	DELETE FROM cache WHERE expire != D
1.03	1.03	1	pager_query	SELECT a.aid, a.timestamp, a.url, a.uid, u.name 
FROM accesslog a LEFT JOIN users u ON a.uid = u.uid WHERE a.path LIKE S 
ORDER BY a.timestamp DESC LIMIT D
10.087	0.56	18	eval	select message, count(message) as count from 
watchdog where type = S and uid != D
136.43	0.493	277	pager_query	SELECT COUNT(DISTINCT(n.nid)) FROM node n 
INNER JOIN project_issues p ON p.nid = n.nid INNER JOIN node_revisions r 
ON r.vid = n.vid INNER JOIN users u ON p.assigned = u.uid LEFT JOIN 
project_comments c ON c.nid = p.nid WHERE n.status = D
132.688	0.479	277	pager_query	SELECT DISTINCT(n.nid) FROM node n INNER 
JOIN project_issues p ON p.nid = n.nid INNER JOIN node_revisions r ON 
r.vid = n.vid INNER JOIN users u ON p.assigned = u.uid LEFT JOIN 
project_comments c ON c.nid = p.nid WHERE n.status = D
1247.877	0.473	2640	do_search	CREATE TEMPORARY TABLE temp_search_sids 
SELECT i.type, i.sid, SUM(i.score * t.count) AS relevance, COUNT(*) AS 
matches FROM search_index i INNER JOIN search_total t ON i.word = t.word 
INNER JOIN node n ON n.nid = i.sid INNER JOIN users u ON n.uid = u.uid 
WHERE n.status = D
0.933	0.466	2	aggregator_page_sources	SELECT f.fid, f.title, 
f.description, f.image, MAX(i.timestamp) AS last FROM aggregator_feed f 
LEFT JOIN aggregator_item i ON f.fid = i.fid GROUP BY f.fid, f.title, 
f.description, f.image ORDER BY last DESC, f.title
0.429	0.429	1	pager_query	SELECT q.qid, q.query, q.function, t.*, 
COUNT(t.qid) AS count, SUM(t.time) AS total_time, AVG(t.time) AS 
average, STDDEV(t.time) AS stddev FROM devel_queries q INNER JOIN 
devel_times t ON q.qid = t.qid GROUP BY t.qid ORDER BY total_time DESC 
LIMIT D
9.767	0.425	23	do_search	CREATE TEMPORARY TABLE temp_search_sids SELECT 
i.type, i.sid, SUM(i.score * t.count) AS relevance, COUNT(*) AS matches 
FROM search_index i INNER JOIN search_total t ON i.word = t.word INNER 
JOIN node n ON n.nid = i.sid INNER JOIN term_node tn ON n.nid = tn.nid 
INNER JOIN users u ON n.uid = u.uid WHERE n.status = D
6.014	0.401	15	pager_query	SELECT uid, access FROM users WHERE uid > D
0.372	0.372	1	pager_query	SELECT COUNT(*) FROM accesslog a LEFT JOIN 
users u ON a.uid = u.uid WHERE a.path LIKE S
96.123	0.364	264	forum_get_forums	SELECT r.tid, COUNT(n.nid) AS 
topic_count, SUM(l.comment_count) AS comment_count FROM node n INNER 
JOIN node_comment_statistics l ON n.nid = l.nid INNER JOIN term_node r 
ON n.nid = r.nid WHERE n.status = D
42.274	0.282	150	cache_clear_all	DELETE FROM cache WHERE cid = S

> The queries have been collected on every tenth page view, so you should 
> multiply the count and total time values by ten for real values.

I will make the collected data available to interested parties.

Cheers,
     Gerhard