Many2one auto-completion too slow with many resources

We're investigating this... for the record, here is a set of queries for populating a test database with 100k products for example:

insert into product_template (id, name, supply_method, standard_price, mes_type, uom_id, uom_po_id, type, procure_method, cost_method, categ_id, sale_ok, purchase_ok) select generate_series, ('Product ' || generate_series::text), 'buy', 30.0, 'fixed', 4, 4, 'service', 'make_to_stock', 'standard', 1, true, true from generate_series(1100000,1200000);
insert into product_product (name_template, product_tmpl_id, valuation, active) select ('Product '||generate_series::text), generate_series, '', true from generate_series(1100000,1200000);
update product_product set active=true;

The last update was unnecessary of course, the following is sufficient on a test database - adapt the boundaries of the generate_series call to get the desired number of products:

insert into product_template (id, name, supply_method, standard_price, mes_type, uom_id, uom_po_id, type, procure_method, cost_method, categ_id, sale_ok, purchase_ok) select generate_series, ('Product ' || generate_series::text), 'buy', 30.0, 'fixed', 4, 4, 'service', 'make_to_stock', 'standard', 1, true, true from generate_series(1100000,1200000);
insert into product_product (name_template, product_tmpl_id, valuation, active) select ('Product '||generate_series::text), generate_series, '', true from generate_series(1100000,1200000);

So turns out the name_get does get slower as the number of products increases (seems to cap out at ~1s here), but there is a bad interaction between typing just fast and slow enough that each keystroke triggers a "long" name_get which launches a bunch of name_get •queued• on the server (at least on pg 8.4 on OSX), meaning each request seems to wait for the previous one (and some) then take its 1s of execution. The second request triggers the "waiting" gears which remain in place until the last name_get is done which can take a pretty long time: http://i.imgur.com/5kB9H.png

1. The web client really should cancel a name_get when the next one (on the same field) is triggered
2. We need to find out why the server queues the requests instead of executing them in parallel

dammit, just realized 2. is because the standalone openerp-web server is single-threaded by default (to avoid mixing multiple request logs), so if you're using that, it can only accept() a single RPC connection at a time.

And indeed, launching a multithreaded web client, the behavior becomes significantly closer to expectations: http://i.imgur.com/OOMZQ.png

(nb: using werkzeug's development server spawns a new thread for each new connection, so a given request is more expensive, hence the first request taking 2.1s I'd expect)

Changed name_get handling so it cancels previous name_get requests automatically, although it seems the single-threaded server will *still* complete its request, so the gain is not really significant there.

Use the embedded client, or the stand-alone client in multi-threaded mode (or put it behind a production-ready WSGI server, that's not really the job werkzeug's development server was built for)

Hello Xavier,

It's really not the heart of teh problem and I might be wrong, but some
times ago, I've seen something like for dev mode, server would be
mono-threaded to be simpler to debug. Is is multi-process by default now?
Did you force it to be multi-process here?

On Tue, Jan 24, 2012 at 10:03 AM, Xavier (Open ERP) <
<email address hidden>> wrote:

> So turns out the name_get does get slower as the number of products
> increases (seems to cap out at ~1s here), but there is a bad interaction
> between typing just fast and slow enough that each keystroke triggers a
> "long" name_get which launches a bunch of name_get •queued• on the
> server (at least on pg 8.4 on OSX), meaning each request seems to wait
> for the previous one (and some) then take its 1s of execution. The
> second request triggers the "waiting" gears which remain in place until
> the last name_get is done which can take a pretty long time:
> http://i.imgur.com/5kB9H.png
>
> 1. The web client really should cancel a name_get when the next one (on
> the same field) is triggered
> 2. We need to find out why the server queues the requests instead of
> executing them in parallel
>
> --
> You received this bug notification because you are a member of OpenERP
> Drivers, which is subscribed to OpenERP Web.
> https://bugs.launchpad.net/bugs/920884
>
> Title:
> Many2one auto-completion too slow with many resources
>
> Status in OpenERP Web:
> In Progress
>
> Bug description:
> Hello,
>
> On the new webclient, about the autocomplete feature when we type a
> name in a many2one.
>
> As instance, on a database with ~12000 products
> - we create new sale order line
> - we start typing a product name
> - the client starts polling to search the products
> - when we have finished to type the product's name, the gears starts
> (and blocks)
> - we can see the list of proposed products growing and changing many
> times (it's like if i could see the proposition for each new letter typed)
> and we have to wait around 10 seconds before to have the selection list and
> no more be stucked
>
> - now, if instead of typing the name in the text box, I write in a
> text editor and copy-paste it in the box, I get a result
> instantaneously.
>
> Without further investigation, my feeling is that the autocomplete
> polls for a auto-complete selection at each char typed.
>
> It works perfectly with a few products, but with a real products
> database, I think it needs some adjustments, as instance, call for the
> autocomplete only after after a delay when we stop typing, but I'm not
> an expert of such web interfaces, I bet on you to find the best
> solution ;-)
>
> Thanks & Best Regards
> Guewen
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/openerp-web/+bug/920884/+subscriptions
>

Hello,

Thanks for your so quick responses and correction :-)

We are using the embedded client so we'll benefit from your change.

Guewen

> Is is multi-process by default now? Did you force it to be multi-process here?

It is still mono-threaded by default, but it has a `—multi-threaded` flag which launches Werkzeug's development server with the `threaded=True` parameter.

All right, it's what we were doing; but we never tested too much to see if
it worked. Thanks for clarifying.

On Tue, Jan 24, 2012 at 1:36 PM, Xavier (Open ERP) <
<email address hidden>> wrote:

> > Is is multi-process by default now? Did you force it to be multi-
> process here?
>
> It is still mono-threaded by default, but it has a `—multi-threaded`
> flag which launches Werkzeug's development server with the
> `threaded=True` parameter.
>
> --
> You received this bug notification because you are a member of OpenERP
> Drivers, which is subscribed to OpenERP Web.
> https://bugs.launchpad.net/bugs/920884
>
> Title:
> Many2one auto-completion too slow with many resources
>
> Status in OpenERP Web:
> Fix Released
>
> Bug description:
> Hello,
>
> On the new webclient, about the autocomplete feature when we type a
> name in a many2one.
>
> As instance, on a database with ~12000 products
> - we create new sale order line
> - we start typing a product name
> - the client starts polling to search the products
> - when we have finished to type the product's name, the gears starts
> (and blocks)
> - we can see the list of proposed products growing and changing many
> times (it's like if i could see the proposition for each new letter typed)
> and we have to wait around 10 seconds before to have the selection list and
> no more be stucked
>
> - now, if instead of typing the name in the text box, I write in a
> text editor and copy-paste it in the box, I get a result
> instantaneously.
>
> Without further investigation, my feeling is that the autocomplete
> polls for a auto-complete selection at each char typed.
>
> It works perfectly with a few products, but with a real products
> database, I think it needs some adjustments, as instance, call for the
> autocomplete only after after a delay when we stop typing, but I'm not
> an expert of such web interfaces, I bet on you to find the best
> solution ;-)
>
> Thanks & Best Regards
> Guewen
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/openerp-web/+bug/920884/+subscriptions
>