Hi,
I still feel 1.8.3 subversion as a step backwards in terms of speed,
compared to the pre-sqlite era (and it seems there are some problems
with sqlite 3.8 (even with 3.8.1), so the following was made with 3.7.17).
My wc.db is 3.1G, completely fits into (and is in) memory, so sqlite
disk IOs are not the limiting factor (even a full table scan doesn't
touch the disks). I mostly do commits, propsets and gets, adds and
removes, and few updates.
The combined wall times of top sqlite SQL commands taking more than one
second (combined) are following, measured in seconds, from a lot of
different sessions from the past week.
I hope this will help making subversion faster, by placing some indexes,
or doing some other optimizations for large working copies like mine.
570.643166 UPDATE nodes SET revision = ?3 WHERE wc_id = ?1 AND
local_relpath = ?2 AND op_depth = 0
426.308679 SELECT local_relpath, nodes.repos_id, nodes.repos_path,
presence, kind, revision, depth, file_external, lock_token,
lock_owner, lock_comment, lock_date FROM nodes LEFT OUTER JOIN lock ON
nodes.repos_id = lock.repos_id AND nodes.repos_path =
lock.repos_relpath WHERE wc_id = ?1 AND parent_relpath = ?2 AND op_depth
= 0
285.95045 SELECT local_relpath, kind FROM nodes WHERE wc_id = ?1 AND
parent_relpath = ?2 AND op_depth = ?3 AND presence !=
'base-deleted' AND file_external is NULL
276.615108 UPDATE nodes SET inherited_props = ?3 WHERE (wc_id = ?1 AND
local_relpath = ?2 AND op_depth = 0)
270.842042 SELECT repos_id, repos_path, presence, kind, revision,
checksum, translated_size, changed_revision, changed_date,
changed_author, depth, symlink_target, last_mod_time, properties,
file_external FROM nodes WHERE wc_id = ?1 AND local_relpath = ?2 AND
op_depth = 0
135.406228 DELETE FROM nodes WHERE wc_id = ?1 AND (((local_relpath) >
(CASE (?2) WHEN '' THEN '' ELSE (?2) || '/' END)) AND ((local_relpath) <
CASE (?2) WHEN '' THEN X'FFFF' ELSE (?2) || '0' END)) AND op_depth = 0
119.258439 RELEASE SAVEPOINT svn
112.633499 SELECT IFNULL((SELECT properties FROM actual_node
a WHERE a.wc_id = ?1 AND A.local_relpath =
n.local_relpath), properties), local_relpath, depth FROM
nodes_current n WHERE wc_id = ?1 AND local_relpath = ?2 AND kind =
'dir' AND presence IN ('normal', 'incomplete') UNION ALL SELECT
IFNULL((SELECT properties FROM actual_node a WHERE
a.wc_id = ?1 AND A.local_relpath = n.local_relpath), properties),
local_relpath, depth FROM nodes_current n WHERE wc_id = ?1 AND
(((local_relpath) > (CASE (?2) WHEN '' THEN '' ELSE (?2) || '/' END))
AND ((local_relpath) < CASE (?2) WHEN '' THEN X'FFFF' ELSE (?2) || '0'
END)) AND kind = 'dir' AND presence IN ('normal', 'incomplete')
94.476561 SELECT local_relpath, repos_path FROM nodes WHERE wc_id = ?1
AND (((local_relpath) > (CASE (?2) WHEN '' THEN '' ELSE (?2) || '/'
END)) AND ((local_relpath) < CASE (?2) WHEN '' THEN X'FFFF' ELSE (?2) ||
'0' END)) AND op_depth = 0 AND (inherited_props not null)
94.134614 SELECT op_depth, nodes.repos_id, nodes.repos_path, presence,
kind, revision, checksum, translated_size, changed_revision,
changed_date, changed_author, depth, symlink_target, last_mod_time,
properties, lock_token, lock_owner, lock_comment, lock_date,
local_relpath, moved_here, moved_to, file_external FROM nodes LEFT OUTER
JOIN lock ON nodes.repos_id = lock.repos_id AND nodes.repos_path =
lock.repos_relpath AND op_depth = 0 WHERE wc_id = ?1 AND parent_relpath
= ?2
78.117183 DELETE FROM nodes WHERE wc_id = ?1 AND (((local_relpath) >
(CASE (?2) WHEN '' THEN '' ELSE (?2) || '/' END)) AND ((local_relpath) <
CASE (?2) WHEN '' THEN X'FFFF' ELSE (?2) || '0' END)) AND presence =
'base-deleted' AND op_depth > 0 AND op_depth = (SELECT MIN(op_depth)
FROM nodes n WHERE n.wc_id =
?1 AND n.local_relpath =
nodes.local_relpath AND op_depth > 0)
66.691045 SELECT root, uuid FROM repository WHERE id = ?1
64.433394 DELETE FROM wc_lock WHERE wc_id = ?1 AND local_dir_relpath = ?2
57.043226 SELECT op_depth, repos_id, repos_path, presence, kind,
revision, checksum, translated_size, changed_revision, changed_date,
changed_author, depth, symlink_target, last_mod_time, properties,
moved_here, inherited_props, moved_to FROM nodes WHERE wc_id = ?1 AND
local_relpath = ?2 ORDER BY op_depth DESC
54.335927 INSERT INTO nodes ( wc_id, local_relpath, op_depth,
parent_relpath, presence, kind) SELECT wc_id, local_relpath, ?4 ,
parent_relpath, 'base-deleted', kind FROM nodes WHERE wc_id =
?1 AND (local_relpath = ?2 OR (((local_relpath) > (CASE (?2)
WHEN '' THEN '' ELSE (?2) || '/' END)) AND ((local_relpath) < CASE (?2)
WHEN '' THEN X'FFFF' ELSE (?2) || '0' END))) AND op_depth = ?3 AND
presence NOT IN ('base-deleted', 'not-present', 'excluded',
'server-excluded') AND file_external IS NULL
52.65289 UPDATE nodes SET translated_size = ?3, last_mod_time = ?4 WHERE
wc_id = ?1 AND local_relpath = ?2 AND op_depth = (SELECT MAX(op_depth)
FROM nodes WHERE wc_id = ?1 AND local_relpath = ?2)
47.496993 INSERT OR REPLACE INTO nodes ( wc_id, local_relpath,
op_depth, parent_relpath, repos_id, repos_path, revision, presence,
depth, kind, changed_revision, changed_date, changed_author, checksum,
properties, translated_size, last_mod_time, dav_cache, symlink_target,
file_external, moved_to, moved_here, inherited_props) VALUES (?1, ?2,
?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16,
?17, ?18, ?19, ?20, ?21, ?22, ?23)
30.005897 INSERT INTO wc_lock (wc_id, local_dir_relpath, locked_levels)
VALUES (?1, ?2, ?3)
25.521807 SELECT nodes.repos_id, nodes.repos_path, lock_token FROM nodes
LEFT JOIN lock ON nodes.repos_id = lock.repos_id AND nodes.repos_path
= lock.repos_relpath WHERE wc_id = ?1 AND op_depth = 0 AND
(((local_relpath) > (CASE (?2) WHEN '' THEN '' ELSE (?2) || '/' END))
AND ((local_relpath) < CASE (?2) WHEN '' THEN X'FFFF' ELSE (?2) || '0'
END))
25.367188 SELECT local_relpath, moved_to, op_depth, kind FROM nodes
WHERE wc_id = ?1 AND (local_relpath = ?2 OR (((local_relpath) > (CASE
(?2) WHEN '' THEN '' ELSE (?2) || '/' END)) AND ((local_relpath) < CASE
(?2) WHEN '' THEN X'FFFF' ELSE (?2) || '0' END))) AND op_depth > ?3
AND moved_to IS NOT NULL
21.71736 SELECT properties FROM actual_node WHERE wc_id = ?1 AND
local_relpath = ?2
19.201899 INSERT OR REPLACE INTO nodes ( wc_id, local_relpath,
op_depth, parent_relpath, repos_id, repos_path, revision, presence,
depth, kind, changed_revision, changed_date, changed_author, checksum,
properties, dav_cache, symlink_target, inherited_props, file_external )
VALUES (?1, ?2, 0, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12,
?13, ?14, ?15, ?16, ?17, (SELECT file_external FROM
nodes WHERE wc_id = ?1 AND local_relpath =
?2 AND op_depth = 0))
18.593748 INSERT INTO delete_list(local_relpath) SELECT local_relpath
FROM nodes AS n WHERE wc_id = ?1 AND (local_relpath = ?2 OR
(((local_relpath) > (CASE (?2) WHEN '' THEN '' ELSE (?2) || '/' END))
AND ((local_relpath) < CASE (?2) WHEN '' THEN X'FFFF' ELSE (?2) || '0'
END))) AND op_depth >= ?3 AND op_depth = (SELECT MAX(s.op_depth)
FROM nodes AS s WHERE s.wc_id = ?1
AND s.local_relpath = n.local_relpath) AND presence NOT IN
('base-deleted', 'not-present', 'excluded', 'server-excluded') AND
file_external IS NULL
17.72543 DELETE FROM work_queue WHERE id = ?1
13.600698 INSERT INTO actual_node (wc_id, local_relpath, parent_relpath,
properties) VALUES (?1, ?2, ?3, ?4)
12.764808 INSERT INTO work_queue (work) VALUES (?1)
12.046104 SELECT properties, presence FROM nodes WHERE wc_id = ?1 AND
local_relpath = ?2 ORDER BY op_depth DESC
8.382276 SELECT id, work FROM work_queue ORDER BY id LIMIT 1
8.140105 DELETE FROM actual_node WHERE wc_id = ?1 AND local_relpath = ?2
8.132293 SELECT size FROM pristine WHERE checksum = ?1 LIMIT 1
7.695312 SELECT local_relpath FROM nodes WHERE wc_id = ?1 AND
(((local_relpath) > (CASE (?2) WHEN '' THEN '' ELSE (?2) || '/' END))
AND ((local_relpath) < CASE (?2) WHEN '' THEN X'FFFF' ELSE (?2) || '0'
END)) AND op_depth = 0 AND presence = 'server-excluded' LIMIT 1
7.414062 DELETE FROM nodes WHERE wc_id = ?1 AND (((local_relpath) >
(CASE (?2) WHEN '' THEN '' ELSE (?2) || '/' END)) AND ((local_relpath) <
CASE (?2) WHEN '' THEN X'FFFF' ELSE (?2) || '0' END)) AND op_depth =
0 AND file_external IS NOT NULL
7.26516 SELECT 1 FROM nodes WHERE wc_id = ?1 AND local_relpath = ?2 LIMIT 1
7.085938 SELECT local_relpath, moved_to, op_depth FROM nodes WHERE wc_id
= ?1 AND (local_relpath = ?2 OR (((local_relpath) > (CASE (?2) WHEN ''
THEN '' ELSE (?2) || '/' END)) AND ((local_relpath) < CASE (?2) WHEN ''
THEN X'FFFF' ELSE (?2) || '0' END))) AND moved_to IS NOT NULL AND
op_depth >= (SELECT MAX(op_depth) FROM nodes o WHERE
o.wc_id = ?1 AND o.local_relpath = ?2)
6.851132 INSERT INTO pristine (checksum, md5_checksum, size, refcount)
VALUES (?1, ?2, ?3, 0)
6.304285 COMMIT TRANSACTION
4.538772 DELETE FROM nodes WHERE wc_id = ?1 AND local_relpath = ?2
4.429404 SAVEPOINT svn
3.859128 SELECT changelist, properties, conflict_data FROM actual_node
WHERE wc_id = ?1 AND local_relpath = ?2
3.101364 SELECT md5_checksum FROM pristine WHERE checksum = ?1
2.921688 SELECT local_dir_relpath, locked_levels FROM wc_lock WHERE
wc_id = ?1 AND ((local_dir_relpath >= ?3 AND local_dir_relpath <=
?2) OR local_dir_relpath = '')
2.796697 SELECT locked_levels FROM wc_lock WHERE wc_id = ?1 AND
local_dir_relpath = ?2
2.757636 BEGIN IMMEDIATE TRANSACTION
2.335788 SELECT local_relpath, changelist, properties, conflict_data
FROM actual_node WHERE wc_id = ?1 AND parent_relpath = ?2
2.257668 UPDATE actual_node SET properties = ?3 WHERE wc_id = ?1 AND
local_relpath = ?2
2.1483 SELECT id FROM repository WHERE root = ?1
1.953 SELECT local_relpath FROM delete_list ORDER BY local_relpath
1.593648 SELECT op_depth, presence, kind, checksum, translated_size,
changed_revision, changed_date, changed_author, depth, symlink_target,
repos_id, repos_path, revision, moved_here, moved_to, last_mod_time,
properties FROM nodes WHERE wc_id = ?1 AND local_relpath = ?2 AND
op_depth > 0 ORDER BY op_depth DESC LIMIT 1
1.52334 SELECT op_depth, nodes.repos_id, nodes.repos_path, presence,
kind, revision, checksum, translated_size, changed_revision,
changed_date, changed_author, depth, symlink_target, last_mod_time,
properties, moved_here, inherited_props, lock_token, lock_owner,
lock_comment, lock_date FROM nodes LEFT OUTER JOIN lock ON
nodes.repos_id = lock.repos_id AND nodes.repos_path =
lock.repos_relpath WHERE wc_id = ?1 AND local_relpath = ?2 ORDER BY
op_depth DESC
1.078127 DELETE FROM nodes WHERE wc_id = ?1 AND (local_relpath =
?2 OR (((local_relpath) > (CASE (?2) WHEN '' THEN '' ELSE (?2) ||
'/' END)) AND ((local_relpath) < CASE (?2) WHEN '' THEN X'FFFF' ELSE
(?2) || '0' END))) AND op_depth >= ?3
1.054687 SELECT local_relpath, moved_to FROM nodes WHERE wc_id = ?1
AND (local_relpath = ?2 OR (((local_relpath) > (CASE (?2) WHEN '' THEN
'' ELSE (?2) || '/' END)) AND ((local_relpath) < CASE (?2) WHEN '' THEN
X'FFFF' ELSE (?2) || '0' END))) AND op_depth >= ?3 AND moved_to IS
NOT NULL AND NOT (((moved_to) > (CASE (?2) WHEN '' THEN '' ELSE (?2) ||
'/' END)) AND ((moved_to) < CASE (?2) WHEN '' THEN X'FFFF' ELSE (?2) ||
'0' END))
1.01556 UPDATE nodes SET presence = ?3, revision = ?4, repos_path = ?5
WHERE wc_id = ?1 AND local_relpath = ?2 AND op_depth = 0
Received on 2013-10-30 10:34:04 CET