Hi there! Today i had a look at drupal.org's slow query log after I found out it is collecting data. Note that the slow query log now also contains queries that don't use an index, no matter how fast. There were 2 queries responsible for about 2/3 of the entries. One is the query from drupal_is_denied, which is run on each bootstrap. This query has been improved for Drupal 5, but I think it coul dbe further approved by doing away with the two LOWER()s in there in case you just check an IP. The other one was the harmless looking query: SELECT * FROM files f INNER JOIN file_revisions r ON f.fid = r.fid WHERE r.vid = 38515 ORDER BY f.fid; an EXPLAIN revealed that it did not use any index. The reason is that we have defined its index as PRIMARY KEY (fid, vid). Chris Johnson explained: vid is a constant, and we are ordering the resulting fids. But in a composite key, MySQL cannot use (at least in version 4.x) any of the components of the composite key if the first component is not used. So what I did is to switch the keys around: alter table file_revisions drop primary key, add primary key (vid, fid); Somebody might want to roll a patch or Drupal5. Cheers, Gerhard
One is the query from drupal_is_denied, which is run on each bootstrap. This query has been improved for Drupal 5, but I think it coul dbe further approved by doing away with the two LOWER()s in there in case you just check an IP.
i'm pretty sure you refer to this issue: http://drupal.org/node/83738 on the same topic, the main node_load() query takes on average 18ms on groups site which seems too high given that groups isn't all that big a site yet. this is a *very* frequent query. the SQL is: SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, 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 = <nid> running EXPLAIN on it suggests that the JOIN with node_revisions is not optimized. Perhaps someone else can look at EXPLAIN for this and suggest a fix?
Moshe Weitzman wrote:
One is the query from drupal_is_denied, which is run on each bootstrap. This query has been improved for Drupal 5, but I think it coul dbe further approved by doing away with the two LOWER()s in there in case you just check an IP.
i'm pretty sure you refer to this issue: http://drupal.org/node/83738
No, I don't. "My" LOWER is in drupal_is_denied.
on the same topic, the main node_load() query takes on average 18ms on groups site which seems too high given that groups isn't all that big a site yet. this is a *very* frequent query. the SQL is:
SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, 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 = <nid>
running EXPLAIN on it suggests that the JOIN with node_revisions is not optimized. Perhaps someone else can look at EXPLAIN for this and suggest a fix?
He, I just sent you a private mail on that matter. I forgot to attach the log, it is in your $HOME on drupal2. The same query works fine on d.o, but it has a slighly different key structure: PRIMARY KEY (`vid`), KEY `nid` (`nid`), KEY `uid` (`uid`) Whereas on g.d.o it is: PRIMARY KEY (`nid`,`vid`), KEY `uid` (`uid`) I had assumed that the difference would be due to the age of drupal.org, but d.o actually has the schema that you get on a newly installed site. So I guess you change the index and that shoul dbe all that is needed. Cheers, Gerhard
running EXPLAIN on it suggests that the JOIN with node_revisions is not optimized. Perhaps someone else can look at EXPLAIN for this and suggest a fix?
It's too bad Karoly is not here to tear into this recommendation as well.
Khalid - In regard to your recent response on the 'I unsubscribe' thread, you earn more of my respect with each balanced post you place on the mailing list. By listening here for about two years and working with Drupal heavily for the past year I've learned enough to be helpful on the list and in issue queues, but I must say I do hold back most of the time, not wanting to raise the ire of some of the stronger personalities in the community. Scott
On 1/3/07, Scott McLewin <drupal@mclewin.com> wrote:
running EXPLAIN on it suggests that the JOIN with node_revisions is not optimized. Perhaps someone else can look at EXPLAIN for this and suggest a fix?
It's too bad Karoly is not here to tear into this recommendation as well.
Khalid - In regard to your recent response on the 'I unsubscribe' thread, you earn more of my respect with each balanced post you place on the mailing list. By listening here for about two years and working with Drupal heavily for the past year I've learned enough to be helpful on the list and in issue queues, but I must say I do hold back most of the time, not wanting to raise the ire of some of the stronger personalities in the community.
Please don't hold back. We do need feedback and discussion. One concern (and it IS valid) is a high signal to noise ratio. I'll use the Documentation list as an example: lots of talk, few contributors actually adding / editing handbook pages. Same goes for here: I know I "left" the list for some time (back in 2002~ish) because I hit a wall in terms of my contribution ability -- I didn't code enough to move the actually *codebase* forward, which is the focus of THIS list. I came back when I was able to contribute more fully. So: respect signal to noise, use issues / patches, but don't feel intimidated. Oh, and we like code (or at least, testing) :P -- Boris Mann Vancouver 778-896-2747 San Francisco 415-367-3595 Skype borismann http://www.bryght.com
Boris Mann wrote:
Please don't hold back. We do need feedback and discussion. One concern (and it IS valid) is a high signal to noise
I'll hold back less and less now that I'm pretty comfortable with the guts of Drupal and carry quite a bit more confidence in my answers (and hopefully without the arrogance that I find off-putting when I encounter it). My development partner and I have been deeply impressed by the abstraction points in the system as we've built a site on top of Drupal.
Same goes for here: I know I "left" the list for some time (back in 2002~ish) because I hit a wall in terms of my contribution ability -- I didn't code enough to move the actually *codebase* forward, which is the focus of THIS list. I came back when I was able to contribute more fully.
So: respect signal to noise, use issues / patches, but don't feel intimidated. Oh, and we like code (or at least, testing)
Understood and recognized. While I submit patches at a reasonable clip I am confident that I'm not winning any patch quantity competitions. :) I see a reflection of my own inspiration to help in Earnie (the one lambasted by Karoly) and get rather disenchanted at how a few people treat the new blood flowing into Drupal. I'm stating this here because I care about the platform and it's overall reputation. The "I unsubscribe" style rant is unfortunate and, IMHO, worse than suggesting an improvement without putting code behind that specific improvement. Back to making signal. Scott
Has it been 6 months yet? I think not, but I'm going to say this anyway. While I don't mean to excuse any truly obnoxious behavior, please be aware that there are a wide variety of personalities, from a wide variety of cultures, speaking a wide variety of languages before English on this list. What that means is, something that might be considered confrontational or insulting in one culture might be the norm -- considered perfectly civil -- in another.
On 03 Jan 2007, at 17:14, Moshe Weitzman wrote:
on the same topic, the main node_load() query takes on average 18ms on groups site which seems too high given that groups isn't all that big a site yet. this is a *very* frequent query. the SQL is:
SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, 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 = <nid>
mysql> EXPLAIN SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, 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 = 2; +----+-------------+-------+--------+---------------------+--------- +---------+--------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------------+--------- +---------+--------------+------+-------------+ | 1 | SIMPLE | n | ref | PRIMARY,vid,uid,nid | PRIMARY | 4 | const | 1 | Using where | | 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | drupal.n.uid | 1 | Using where | | 1 | SIMPLE | r | eq_ref | PRIMARY | PRIMARY | 4 | drupal.n.vid | 1 | | +----+-------------+-------+--------+---------------------+--------- +---------+--------------+------+-------------+ I tested this query on my localhost running CVS HEAD, and it is using keys/indices. I've only a dozen nodes on my test site which can make a difference. It would be interesting to compare this EXPLAIN with one from groups.drupal.org. (I'm currently on the train so I can't do that right now.) -- Dries Buytaert :: http://www.buytaert.net/
On 03 Jan 2007, at 16:22, Gerhard Killesreiter wrote:
The other one was the harmless looking query:
SELECT * FROM files f INNER JOIN file_revisions r ON f.fid = r.fid WHERE r.vid = 38515 ORDER BY f.fid;
an EXPLAIN revealed that it did not use any index. The reason is that we have defined its index as PRIMARY KEY (fid, vid).
Chris Johnson explained:
vid is a constant, and we are ordering the resulting fids. But in a composite key, MySQL cannot use (at least in version 4.x) any of the components of the composite key if the first component is not used.
I just tested and can confirm that this is the case using MySQL 4.1.
So what I did is to switch the keys around:
alter table file_revisions drop primary key, add primary key (vid, fid);
Oh, yeah, this needs fixing in Drupal 5. I suggest we start by creating an issue for it. Good job, Gerhard and thanks for the help Chris! Is the slow query log database specific or does it monitor across all databases? I believe it is the former but I can't verify that as I'm on the train. We'll probably want to profile groups.drupal.org and scratch.drupal.org as well. Chances are we find more bad queries. We still have a little bit of time to fix bad queries in Drupal 5. (Dealing with SQL updates in branches -- after the final Drupal 5.0 release -- is often a pain, so I'd prefer to fix before releasing Drupal 5.0 final.) Either way, I'd recommend that we all spend some time looking at our slow query logs -- especially if you're already on Drupal 5. Note that we added a handy MySQL status page to Drupal 5. Go to "Home › Administer › Logs › Status report > MySQL version number" or jump directly to: http://example.com/?q=admin/logs/status/sql Thanks, -- Dries Buytaert :: http://www.buytaert.net/
Dries Buytaert wrote:
On 03 Jan 2007, at 16:22, Gerhard Killesreiter wrote:
The other one was the harmless looking query:
SELECT * FROM files f INNER JOIN file_revisions r ON f.fid = r.fid WHERE r.vid = 38515 ORDER BY f.fid;
an EXPLAIN revealed that it did not use any index. The reason is that we have defined its index as PRIMARY KEY (fid, vid).
Chris Johnson explained:
vid is a constant, and we are ordering the resulting fids. But in a composite key, MySQL cannot use (at least in version 4.x) any of the components of the composite key if the first component is not used.
I just tested and can confirm that this is the case using MySQL 4.1.
drupaldb still has 4.0, we should change that to 4.1.
So what I did is to switch the keys around:
alter table file_revisions drop primary key, add primary key (vid, fid);
Oh, yeah, this needs fixing in Drupal 5. I suggest we start by creating an issue for it. Good job, Gerhard and thanks for the help Chris!
Is the slow query log database specific or does it monitor across all databases?
All databases. I have now switched logging off, but I have 20MB of logfile to offer to trusted developers. I can extract site specific queries, if requested. I already made the groups.d.o log available to Moshe.
I believe it is the former but I can't verify that as I'm on the train. We'll probably want to profile groups.drupal.org and scratch.drupal.org as well. Chances are we find more bad queries.
We did already get one on groups. Should be easy to fix there.
We still have a little bit of time to fix bad queries in Drupal 5. (Dealing with SQL updates in branches -- after the final Drupal 5.0 release -- is often a pain, so I'd prefer to fix before releasing Drupal 5.0 final.) Either way, I'd recommend that we all spend some time looking at our slow query logs -- especially if you're already on Drupal 5.
Such as groups.d.o. :) Cheers, Gerhard
participants (6)
-
Boris Mann -
Chris Johnson -
Dries Buytaert -
Gerhard Killesreiter -
Moshe Weitzman -
Scott McLewin