[Cubicweb] massive deletion performance

[Alexandre Fayolle]

On Monday 17 January 2011 18:55:07 Alexandre Fayolle wrote:
> This mail is mainly a reminder for myself tomorrow when I resume working on
> this, but I'll be happy if anyone cares to share his insight on the topic.
> 
> I believe I'm not the first one to be hit by the dreadful performance when
> massive deletion are performed in CW. The current example I have at hand
> involves deleting ~2000 entities, which will through composite relations
> cause the cascade deletion of ~45000 entities. The first DELETE statement
> takes ~300s on my computer and the commit (during which the 45k related
> entities are deleted) takes ages.

I've made some progress on this today. Here are some figures. The "delete" 
lines give the name of the etype, the number deleted and the time requires to 
execute the DELETE RQL query. The "commit" lines give the time for the commit 
statements. At the end, we have the output of the time shell builtin for the 
script. 

The second commit deletes an additional 1512 YearRangeProfileBox entities and 
44321 TimeSeries entities. 

without patch
=============

delete ScenarioSet 1 0.135946989059
commit 0.0686221122742
delete RiskFactor 108 2.40645980835
delete Scenario 344 68.0118050575
delete SubYearlyBoxAssemblage 72 4.89098191261
delete TSBox 1894 274.777546167
commit 4313.74800014

real    77m49.850s
user    66m50.287s
sys     0m56.360s


with patch
==========

delete ScenarioSet 1 0.182914972305
commit 0.051344871521
delete RiskFactor 108 1.03510499001
delete Scenario 344 62.8357331753
delete SubYearlyBoxAssemblage 72 2.7994761467
delete TSBox 1894 133.116788149
commit 888.476686001

real    18m15.180s
user    16m15.105s
sys     0m9.281s


The patch is available for review in the mq repository, branch "alf" 
([stable] optimize integrity hooks.patch)


-- 
Alexandre Fayolle                              LOGILAB, Paris (France)
Formations Python, CubicWeb, Debian :  http://www.logilab.fr/formations
Développement logiciel sur mesure :      http://www.logilab.fr/services
Informatique scientifique:               http://www.logilab.fr/science
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.cubicweb.org/pipermail/cubicweb/attachments/20110118/03e5c235/attachment-0186.html>