Re: [PATCH]: Speed up deletion of multiple files

On Wed, May 23, 2012 at 7:47 PM, Bert Huijben <bert_at_qqmail.nl> wrote:
>
>> -----Original Message-----
>> From: Paul Burba [mailto:ptburba_at_gmail.com]
>> Sent: donderdag 24 mei 2012 0:43
>> To: Subversion Development
>> Subject: [PATCH]: Speed up deletion of multiple files
>>
>> On one CollabNet's forums a user reported that a single delete command
>> of 2000+ WC files took well over three hours to complete with 1.7.5
>> (see
>> http://subversion.open.collab.net/ds/viewMessage.do?dsForumId=4&dsM
>> essageId=456214).
>>
>> I'm able to replicate similar behavior with a partial checkout of
>> ^/subversion/tags
>
> Which Sqlite version did you use?
>
> If your Sqlite is below 3.7.9 I would recommend updating Sqlite first.

You're spot-on Bert: I bumped my build from 3.7.7 to 3.7.12 and my
test deletion of 2463 files completes in average of 4.9s over 4 runs;
about as fast as I was seeing with my patch and 3.7.7.

Out of curiosity I reapplied my patch to see if it made any
difference. Unsurprisingly it was slower, averaging 6.2s over 4 runs.

> (What results do you see from the wc-queries-test result on trunk?)

Ah yes, that did fail.

> This query appears to use the indexes for me on 3.7.12 without the patch, and looking at the buildbots also on 3.7.9.
> (Which doesn't say that it can't be improved further... But if the optimizer already handles this we don't have to write dirty queries)

/me breathes a sigh of relief at that.

-- 
Paul T. Burba
CollabNet, Inc. -- www.collab.net -- Enterprise Cloud Development
Skype: ptburba
> I'll check my results tomorrow, but this sounds exactly like stefan2's problem last Saturday, which disappeared when he upgraded his Sqlite. (It took an hour before his upgrade, and deleting 16k files was just seconds later)
>
> My testcase of deleting 2000 files took about 12 seconds last Saturday. After that I haven't done measurements on that case, but I expect many improvements in other places.
> (And I ran the tests on a very fast network drive instead of a local harddisk)
>
>        Bert
>
>>
>> My test WC:
>>
>>   WC Size: 437 MB 21,012 Files, 2,717 Folders
>>   wc.db Size: 18,108,000 bytes
>>   Deletion Targets: 2642 files
>>
>> Using 1.7.5 this takes almost 27 minutes on my machine:
>>
>>   C:\SVN\sandbox\subversion-tags>timethis svn delete -q --targets
>> del-target.2463.txt
>>
>>   TimeThis :  Command Line :  svn delete -q --targets del-target.2463.txt
>>   TimeThis :    Start Time :  Fri May 18 11:27:04 2012
>>
>>
>>   TimeThis :  Command Line :  svn delete -q --targets del-target.2463.txt
>>   TimeThis :    Start Time :  Fri May 18 11:27:04 2012
>>   TimeThis :      End Time :  Fri May 18 11:53:58 2012
>>   TimeThis :  Elapsed Time :  00:26:53.758
>>
>> trunk_at_1341851 is significantly faster, taking only 16 minutes:
>>
>>   C:\SVN\sandbox\subversion-tags>timethis svn delete -q --targets
>> del-target.2463.txt
>>
>>   TimeThis :  Command Line :  svn delete -q --targets del-target.2463.txt
>>   TimeThis :    Start Time :  Wed May 23 10:56:41 2012
>>
>>
>>   TimeThis :  Command Line :  svn delete -q --targets del-target.2463.txt
>>   TimeThis :    Start Time :  Wed May 23 10:56:41 2012
>>   TimeThis :      End Time :  Wed May 23 11:13:17 2012
>>   TimeThis :  Elapsed Time :  00:16:35.873
>>
>> Using optimizations similar to what Bert used in r1341848 and creating
>> a new , the attach patch cuts this down to just over 4 seconds:
>>
>>   C:\SVN\sandbox\subversion-tags>timethis svn delete -q --targets
>> del-target.2463.txt
>>
>>   TimeThis :  Command Line :  svn delete -q --targets del-target.2463.txt
>>   TimeThis :    Start Time :  Wed May 23 17:41:12 2012
>>
>>
>>   TimeThis :  Command Line :  svn delete -q --targets del-target.2463.txt
>>   TimeThis :    Start Time :  Wed May 23 17:41:12 2012
>>   TimeThis :      End Time :  Wed May 23 17:41:16 2012
>>   TimeThis :  Elapsed Time :  00:00:04.116
>>
>> ...So, WCNG gurus, does this look ok?
>>
>> [[[
>> Speed up WC deletions.
>>
>> * subversion/libsvn_wc/wc-queries.sql
>>   (STMT_DELETE_NODES_ABOVE_DEPTH_RECURSIVE,
>>    STMT_INSERT_DELETE_FROM_NODE_RECURSIVE): Make the OR operation
>> the
>>    outer operation by duplicating some cheap tests.
>>
>>   (STMT_INSERT_DELETE_LIST_RECURSIVE,
>>    STMT_INSERT_DELETE_LIST): Split old STMT_INSERT_DELETE_LIST into two
>>    new versions, one recursive, and one not.
>>
>> * subversion/libsvn_wc/wc_db.c
>>   (delete_node): Use the faster non-recursive query when operating on a
>> file.
>> ]]]
>>
>> --
>> Paul T. Burba
>> CollabNet, Inc. -- www.collab.net -- Enterprise Cloud Development
>> Skype: ptburba