Edgewall Software
Modify

Opened 5 years ago

Closed 5 years ago

#11955 closed defect (fixed)

CachedRepository._next_prev_rev is very slow if node_change table is too large on mysql

Reported by: Jun Omae Owned by: Jun Omae
Priority: normal Milestone: 1.0.5
Component: version control/changeset view Version: 0.12-stable
Severity: normal Keywords: svn cache mysql
Cc: Branch:
Release Notes:

Improve retrieving next/previous revisions with cached Subversion repository on MySQL database.

API Changes:

Description

Changeset view shows next and previous revisions to use node_change table if repository is cached. It it very slow if node_change table on mysql is too large (5,000,000+ rows).

It would improve the performance to use MAX/MIN instead of ORDER BY with LIMIT 1. In other database backends, it would keep the same performance.

direction argument ORDER BY with LIMIT 1 MAX/MIN
< 38.74 s 0.00s
> 7.81 s 0.00s
mysql> SELECT COUNT(repos) FROM node_change;
+--------------+
| COUNT(repos) |
+--------------+
|      5016444 |
+--------------+
1 row in set (0.72 sec)

mysql> EXPLAIN SELECT MAX(rev) FROM node_change WHERE repos=10 AND rev<'0000000034';
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT rev FROM node_change WHERE repos=10 AND rev<'0000000034' ORDER BY rev DESC LIMIT 1;
+----+-------------+-------------+------+-----------------------------------+---------------------------+---------+-------+--------+--------------------------+
| id | select_type | table       | type | possible_keys                     | key                       | key_len | ref   | rows   | Extra                    |
+----+-------------+-------------+------+-----------------------------------+---------------------------+---------+-------+--------+--------------------------+
|  1 | SIMPLE      | node_change | ref  | PRIMARY,node_change_repos_rev_idx | node_change_repos_rev_idx | 4       | const | 287492 | Using where; Using index |
+----+-------------+-------------+------+-----------------------------------+---------------------------+---------+-------+--------+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(rev) FROM node_change WHERE repos=10 AND rev<'0000000034';
+------------+
| MAX(rev)   |
+------------+
| 0000000033 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT rev FROM node_change WHERE repos=10 AND rev<'0000000034' ORDER BY rev DESC LIMIT 1;
+------------+
| rev        |
+------------+
| 0000000033 |
+------------+
1 row in set (38.74 sec)

mysql> EXPLAIN SELECT MIN(rev) FROM node_change WHERE repos=10 AND rev>'0000000034';
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT rev FROM node_change WHERE repos=10 AND rev>'0000000034' ORDER BY rev LIMIT 1;
+----+-------------+-------------+------+-----------------------------------+---------------------------+---------+-------+--------+--------------------------+
| id | select_type | table       | type | possible_keys                     | key                       | key_len | ref   | rows   | Extra                    |
+----+-------------+-------------+------+-----------------------------------+---------------------------+---------+-------+--------+--------------------------+
|  1 | SIMPLE      | node_change | ref  | PRIMARY,node_change_repos_rev_idx | node_change_repos_rev_idx | 4       | const | 287492 | Using where; Using index |
+----+-------------+-------------+------+-----------------------------------+---------------------------+---------+-------+--------+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT MIN(rev) FROM node_change WHERE repos=10 AND rev>'0000000034';
+------------+
| MIN(rev)   |
+------------+
| 0000000035 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT rev FROM node_change WHERE repos=10 AND rev>'0000000034' ORDER BY rev LIMIT 1;
+------------+
| rev        |
+------------+
| 0000000035 |
+------------+
1 row in set (7.81 sec)

Attachments (1)

t11955_0.12.diff (2.7 KB ) - added by Jun Omae 5 years ago.
Patch for 0.12-stable

Download all attachments as: .zip

Change History (3)

by Jun Omae, 5 years ago

Attachment: t11955_0.12.diff added

Patch for 0.12-stable

comment:1 by Jun Omae, 5 years ago

Owner: set to Jun Omae
Status: newassigned

Proposed changes in [c8b4720afd/jomae.git].

If someone has the same issue on 0.12-stable with MySQL, please try t11955_0.12.diff.

comment:2 by Jun Omae, 5 years ago

Release Notes: modified (diff)
Resolution: fixed
Status: assignedclosed

Committed in [13763] and merged to trunk in [13764].

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Jun Omae.
The resolution will be deleted. Next status will be 'reopened'.
to as closed The owner will be changed from Jun Omae to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.