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
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.
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).
--
Cédric Krier
B2CK SPRL
Rue de Rotterdam, 4
4000 Liège
Belgium
Tel: +32 472 54 46 59
Email/Jabber: [email protected]
Website: http://www.b2ck.com/
pgpAczpUm1hC7.pgp
Description: PGP signature
