By p4guru on
Pretty new to drupal so no idea about most of it but there's this query which is really pulling the site down alot. It's a using version 5.19 of vbdrupal and wondering if anyone has suggestions ? Any help is appreciated :)
mysqlsla -lt slow /home/mysql/mysql-slow-queries.log --TOP 1 -x tschema,tcount -ex
Report for slow logs: /home/mysql/mysql-slow-queries.log
119 queries total, 42 unique
Sorted by 't_sum'
Grand Totals: Time 111 s, Lock 0 s, Rows sent 4.38k, Rows Examined 333.99M
______________________________________________________________________ 001 ___
Count : 10 (8.40%)
Time : 109.931309 s total, 10.993131 s avg, 10.820919 s to 11.078777 s max (99.23%)
95% of Time : 98.852532 s total, 10.983615 s avg, 10.820919 s to 11.065214 s max
Lock Time (s) : 1.42 ms total, 142 µs avg, 110 µs to 231 µs max (11.93%)
95% of Lock : 1.189 ms total, 132 µs avg, 110 µs to 201 µs max
Rows sent : 9 avg, 5 to 9 max (1.96%)
Rows examined : 33.32M avg, 33.32M to 33.33M max (99.77%)
Database : vb364
Users :
dbusername@dbserverip : 100.00% (10) of query, 99.16% (118) of all users
Table:#rows : vb3_drupal_download_counter:8.55k vb3_drupal_node:28.54k vb3_drupal_votingapi_vote:2.43k vb3_drupal_files:6.31k vb3_drupal_node_revisions:30.78k
Table schemas :
CREATE TABLE `vb3_drupal_download_counter` (
`nid` int(11) NOT NULL,
`count` int(11) NOT NULL,
UNIQUE KEY `nid` (`nid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CREATE TABLE `vb3_drupal_node` (
`nid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`vid` int(10) unsigned NOT NULL DEFAULT '0',
`type` varchar(32) NOT NULL DEFAULT '',
`title` varchar(255) NOT NULL,
`uid` int(11) NOT NULL DEFAULT '0',
`status` int(11) NOT NULL DEFAULT '1',
`created` int(11) NOT NULL DEFAULT '0',
`changed` int(11) NOT NULL DEFAULT '0',
`comment` int(11) NOT NULL DEFAULT '0',
`promote` int(11) NOT NULL DEFAULT '0',
`moderate` int(11) NOT NULL DEFAULT '0',
`sticky` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`nid`,`vid`),
UNIQUE KEY `vid` (`vid`),
KEY `node_type` (`type`(4)),
KEY `node_title_type` (`title`,`type`(4)),
KEY `status` (`status`),
KEY `uid` (`uid`),
KEY `node_moderate` (`moderate`),
KEY `node_promote_status` (`promote`,`status`),
KEY `node_created` (`created`),
KEY `node_changed` (`changed`),
KEY `node_status_type` (`status`,`type`,`nid`),
KEY `nid` (`nid`)
) ENGINE=MyISAM AUTO_INCREMENT=29315 DEFAULT CHARSET=utf8
CREATE TABLE `vb3_drupal_votingapi_vote` (
`vote_id` int(10) unsigned NOT NULL,
`content_type` varchar(20) DEFAULT NULL,
`content_id` int(10) unsigned DEFAULT NULL,
`value` float DEFAULT NULL,
`value_type` varchar(20) NOT NULL,
`tag` varchar(128) DEFAULT 'vote',
`uid` int(10) unsigned DEFAULT '0',
`timestamp` int(11) DEFAULT NULL,
`hostname` varchar(128) DEFAULT '0',
PRIMARY KEY (`vote_id`),
KEY `content` (`content_type`,`content_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `vb3_drupal_files` (
`fid` int(10) unsigned NOT NULL DEFAULT '0',
`nid` int(10) unsigned NOT NULL DEFAULT '0',
`filename` varchar(255) NOT NULL DEFAULT '',
`filepath` varchar(255) NOT NULL DEFAULT '',
`filemime` varchar(255) NOT NULL DEFAULT '',
`filesize` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`fid`),
KEY `nid` (`nid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `vb3_drupal_node_revisions` (
`nid` int(10) unsigned NOT NULL,
`vid` int(10) unsigned NOT NULL,
`uid` int(11) NOT NULL DEFAULT '0',
`title` varchar(255) NOT NULL,
`body` longtext NOT NULL,
`teaser` longtext NOT NULL,
`log` longtext NOT NULL,
`timestamp` int(11) NOT NULL DEFAULT '0',
`format` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`vid`),
KEY `nid` (`nid`),
KEY `uid` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
EXPLAIN : 22697304 produced, 45422664 read
id: 1
select_type: SIMPLE
table: n
type: ref
possible_keys: PRIMARY,node_type,status,node_moderate,node_created,node_status_type,nid
key: node_status_type
key_len: 102
ref: const,const
rows: 9352
Extra: Using where; Using temporary; Using filesort
id: 1
select_type: SIMPLE
table: f
type: ref
possible_keys: nid
key: nid
key_len: 4
ref: vb364.n.nid
rows: 1
Extra:
id: 1
select_type: SIMPLE
table: nr
type: ref
possible_keys: nid
key: nid
key_len: 4
ref: vb364.n.nid
rows: 1
Extra:
id: 1
select_type: SIMPLE
table: v
type: ALL
possible_keys:
key:
key_len:
ref:
rows: 2427
Extra:
id: 1
select_type: SIMPLE
table: dc
type: eq_ref
possible_keys: nid
key: nid
key_len: 4
ref: vb364.n.nid
rows: 1
Extra:
Query abstract:
SET timestamp=N; SELECT n.nid,MAX(nr.vid) AS vid, n.title, n.created, f.filename, f.filepath, f.filesize,nr.teaser,avg(v.value)/N AS percent,dc.COUNT FROM vb3_drupal_node n INNER JOIN vb3_drupal_node_revisions nr ON nr.nid=n.nid LEFT JOIN vb3_drupal_files f ON f.nid = n.nid LEFT JOIN vb3_drupal_votingapi_vote v ON n.nid = v.content_id LEFT JOIN vb3_drupal_download_counter dc ON n.nid = dc.nid WHERE n.type = 'S' AND n.status = N AND n.moderate=N AND n.created <= UNIX_TIMESTAMP(now()) GROUP BY n.nid ORDER BY n.created DESC LIMIT N, N;
Query sample:
SET timestamp=1265918482;
SELECT n.nid,max(nr.vid) as vid, n.title, n.created, f.filename, f.filepath, f.filesize,nr.teaser,avg(v.value)/20 as percent,dc.count FROM vb3_drupal_node n INNER JOIN vb3_drupal_node_revisions nr ON nr.nid=n.nid LEFT JOIN vb3_drupal_files f ON f.nid = n.nid LEFT JOIN vb3_drupal_votingapi_vote v ON n.nid = v.content_id LEFT JOIN vb3_drupal_download_counter dc ON n.nid = dc.nid WHERE n.type = 'download' AND n.status = 1 AND n.moderate=0 AND n.created <= UNIX_TIMESTAMP(NOW()) GROUP BY n.nid ORDER BY n.created DESC LIMIT 0, 9;
