* Betr.: " Re: [tryton-dev] Performance and MPTT-Update" (Wed, 9 Mar 2011 15:58:17 +0100):
> On 09/03/11 15:29 +0100, Mathias Behrle wrote: > > Hi all, > > > > while installing account charts for our customers we encountered heavy > > performance problems, that we could track down to be caused by multiple > > MPTT updates inside a transaction. > > > > I put together a little show case with a script and a server patch to be > > found at http://codereview.appspot.com/4239075 > > > > It just creates a nested account chart of only views, that mimics the > > behavior of module account (naturally we will be hit by those problems on > > any hierarchical model using parent/children e.g. locations, ...). On my > > (not so powerful) hardware I got the following results for just 2000 > > records: > > > > Running with mptt update (current layout): > > ========================================== > > > > total creation time: 2:20:29.985710 > > min creation time: 0:00:00.024825 > > max creation time: 0:00:22.139077 > > > > > > Running without mptt update: > > ============================ > > (uncommented ##1) > > > > total creation time: 0:00:37.847233 > > min creation time: 0:00:00.013649 > > max creation time: 0:00:00.470916 > > > > > > Running with delayed mptt update: > > ================================= > > (uncommented ##2) > > > > total creation time: 0:00:47.834367 > > min creation time: 0:00:00.013451 > > max creation time: 0:00:00.485578 > > Did you check if you are not always in this bad case: > > > http://hg.tryton.org/trytond/file/28c6cc9fe39f/trytond/model/modelsql.py#l2076 I checked once again and as expected this is not the case. > Also, I'm wondering if we should not replace the child_ids computation by a > subqueries. > > > > > The numbers show clearly that we need some smarter MPTT update handling. The > > included server patch gave us at least the possibilty to control the MPTT > > update with minimal invasive coding by context handling, running without > > problems in production. Probably better would be the extension of the > > signatures of create, write and delete with named argument > > tree_update=True. Since it is very useful to have such control I am asking > > for inclusion. > > > > Furthermore we should evaluate the possibilities to delay the MPTT update > > till the end of any encapsulating transaction. The idea is to run the MPTT > > update only at the end of the first of perhaps multiple nested transaction. > > > > Comments and thoughts welcome. > > I don't find that delaying or postpone the update of MPTT is a good idea. > Because the system is build on the assumption that it can use MPTT for search > at any time, so inside the same transaction you could use it just after > modification of the tree. > > I think it will be better to work on having the create method working for > creation of many records. This won't solve the problem for kind of more complex transactions with creates, deletes, writes... But it would be indeed a step forward. > By the way, MPTT is a pre-computation (or a cache mechanism) that creates an > overload at CRUD operations but speed much more the search (like an index). I think there has to be a tradeoff between the early overload and later gain in speed. Currently it looks for me, that the pre-computation slows more down than it yields a performance gain. Let's take a look at a delete operation in a somewhat bigger nested chart. Every time you are deleting an element from the tree, you are hitting the rebuild of the tree. Provided you want to delete several items, each time you have the delay of the MPTT update. For me it feels, that it would be better to build the MPTT tree on demand. How often will you need an immediate search on the tree compared to the simple display of the tree? The MPTT implementation in its current implementation is for me a very costly "index" and I have the impression it costs much more than it gains. Could you shed some light on when the tree is finally used in searches? -- Mathias Behrle MBSolutions Gilgenmatten 10 A D-79114 Freiburg Tel: +49(761)471023 Fax: +49(761)4770816 http://m9s.biz UStIdNr: DE 142009020 PGP/GnuPG key availabable from any keyserver, ID: 0x8405BBF6
signature.asc
Description: PGP signature
