This query is number 1 in the slow log:

__ 001 _______________________________________________________________________

Count         : 411151 (59%)
Time          : 1301056 s total, 3 s avg, 0 s to 14 s max
95% of Time   : 1169220 s total, 2 s avg, 0 s to 6 s max
Lock Time     : 46 s total, 0 s avg, 0 s to 1 s max
Rows sent     : 10 avg, 1 to 25 max
Rows examined : 421284 avg, 20 to 499662 max
User          : drupal@www5.drupal.org/140.211.166.7 (63%)
Database      : Unknown

SELECT m.*, u.name, u.uid FROM cvs_messages m INNER JOIN users u ON m.uid = u.uid ORDER BY m.created DESC LIMIT N, N;

This query, on large values of N (LIMIT N, M), uses filesort, since the table's primary key is cid, and the sorting is done by 'created'.
Since we know that all commits are added in order, we can sort by cid, for the same result, without the filesort.

See:

mysql> explain  SELECT m.*, u.name, u.uid FROM cvs_messages m INNER JOIN users u ON m.uid = u.uid ORDER BY  m.created DESC LIMIT 5000, 10;
+----+-------------+-------+--------+---------------+---------+---------+---------------+--------+----------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows   | Extra          |
+----+-------------+-------+--------+---------------+---------+---------+---------------+--------+----------------+
|  1 | SIMPLE      | m     | ALL    | uid           | NULL    | NULL    | NULL          | 243630 | Using filesort |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY       | PRIMARY | 4       | drupal6.m.uid |      1 |                |
+----+-------------+-------+--------+---------------+---------+---------+---------------+--------+----------------+
2 rows in set (0.00 sec)
 
mysql> explain  SELECT m.*, u.name, u.uid FROM cvs_messages m INNER JOIN users u ON m.uid = u.uid ORDER BY  m.cid DESC LIMIT 5000, 10;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
|  1 | SIMPLE      | m     | index  | uid           | PRIMARY | 4       | NULL          | 5010 |       |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY       | PRIMARY | 4       | drupal6.m.uid |    1 |       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+
2 rows in set (0.00 sec)

DamZ rightfully claims this is a bit hackish, but given the fact that nobody but Google reads the CVS logs, and that these pages change every commit, which cause Google to scan them even more, I think that it can be safely applied.

With git, we will not be able to make this hack, but let's handle that when we get there.

CommentFileSizeAuthor
yh.patch1.5 KByhager

Comments

gerhard killesreiter’s picture

gerhard killesreiter commented

Running both queries on the slave server with sql_no_cache showed 0.15s for the original and 0.1s (guestimates) for the improved query.

yhager’s picture

yhager commented

> Running both queries on the slave server with sql_no_cache showed 0.15s for the original and 0.1s (guestimates) for the improved query.

Testing this on scratch, I get better results (x2 to x5):

mysql> SELECT SQL_NO_CACHE m.*, u.name, u.uid FROM cvs_messages m INNER JOIN users u ON m.uid = u.uid ORDER BY  m.cid DESC LIMIT 100000, 10;
10 rows in set (0.15 sec)
mysql> SELECT SQL_NO_CACHE m.*, u.name, u.uid FROM cvs_messages m INNER JOIN users u ON m.uid = u.uid ORDER BY  m.created DESC LIMIT 100000, 10;
10 rows in set (0.34 sec)

mysql> SELECT SQL_NO_CACHE m.*, u.name, u.uid FROM cvs_messages m INNER JOIN users u ON m.uid = u.uid ORDER BY  m.created DESC LIMIT 5000, 10;
10 rows in set (0.11 sec)
mysql> SELECT SQL_NO_CACHE m.*, u.name, u.uid FROM cvs_messages m INNER JOIN users u ON m.uid = u.uid ORDER BY  m.cid DESC LIMIT 5000, 10;
10 rows in set (0.02 sec)

Another solution would be not to use limit, but calculate the cid's for the relevant page, and limit the value of cid in a WHERE condition. This will definitely be faster, but a bit more involved (read: hackish).
Do you think we should go that route, or test this first?

dww’s picture

dww
we/he/they
commented
Category: bug » task

I'm wondering why it's worth fixing this at all, since the move to git seems fairly imminent. But, short of "won't fix", I'd much rather do the smallest change to improve things (e.g. the original patch), than get fancier with doing WHERE based on cid and a bunch of extra effort to make that work properly.

And no, google's not the only one who reads these pages. ;) I know I certainly view them frequently. But yes, mostly just pages #1-3, almost never do I go back much further than that...

yhager’s picture

yhager commented

I know this is a throw-away eventually, but we still have time till we scratch cvslog completely.

A general note - I offered my help to the infrastructure team, but I am not up to date on where help is most needed. Please direct me - I am yhager on irc.

dww’s picture

dww
we/he/they
commented
Version: 6.x-1.0-alpha3 » 6.x-1.x-dev
Status: Needs review » Fixed

Reviewed, tested, committed, and deployed on d.o:

http://drupal.org/cvs
http://drupal.org/cvs?page=25061

It's a bit of a hack, but it's not worth spending any more time on this issue at this point.

Thanks, yhager!
-Derek

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.