Community mailing list archives

community@mail.odoo.com

Re: POS v8 start time

by
Mario Arias Badila
- 12/30/2014 13:34:54
Hi Alexis, great analysis !
Is there any transcript about that IRC session ?  Would be very interesting for those of us that could not join the discussion.


Back in topic..  those load times are even worse when using POS remotely...
Tests for one client with 10K products and 5K customers, takes between three and four minutes remotely, and more than one minute locally on a similar dedicated server.

Any improvements are definitely welcomed...


Regards,
-Mario

On Tue, Dec 30, 2014 at 5:58 AM, Bevan Weiss <kaizen__@hotmail.com> wrote:

The large blind WHERE product_template.id IN (…) list also seems to be taking up to 11seconds.

I wonder if a WHERE product_template.id BETWEEN 12234 AND 12433 would be more efficient

 

Would it be possible for you to do a quick test against your DB for these two queries?

 

I’m intrigued that the IDs in the IN aren’t entirely sequentially ordered.  I assume there is a non-ID sort order coming into the original list.

 

The other item to look at might be the auto_join attribute for the relational fields in product.product to the product.template table.

https://doc.odoo.com/trunk/server/06_misc_auto_join/

 

I guess if this would work appropriately, then it might allow the entire thing to be reduced down to use a JOIN instead of the individual SELECT from product_template queries.

 

 

 

Regards,

Bevan

 

From: alexis [mailto:alexis@via.ecp.fr]
Sent: Tuesday, 30 December 2014 9:17 PM
To: Community
Subject: POS v8 start time

 

Dear OpenERP community friends,

 

I am about to deploy in production a POS on v8 with 10 000 products and 

58 000 partners. With the current codebase, the POS takes 1 min 15 sec 

to start on an Ubuntu 14.04 LTS with Core i7 3.4 Ghz and 8 Gb of RAM.

 

Following the IRC meeting on POS on December 19, I have been 

investigating the POS start time. Frederic Van Der Essen suggested that 

a lot of time was spent in the pricelist, and that I should not 

configure a pricelist on the pos.config (it would then use the Sale 

price of the product), which is in fact not possible on v8 because the 

pricelist is a required field on pos.config.

 

I made some measures, and I found out that it only spends 4 seconds (out 

of 1 min 15s) to compute the pricelists, so the pricelist code is really 

optimized now. I made a patch to by-pass the pricelists (4 lignes of 

code) and I only win the 4 seconds, not more.

 

Then, I set :

log_statement = 'all'

in /etc/postgresql/9.3/main/postgresql.conf and looked at the SQL 

requests in /var/log/postgresql/postgresql-9.3-main.log

 

By looking at the SQL requests, I first figured out there was a LOT of 

SQL requests on ir_translation for product.product, even when my user 

has lang=English. As my customer only uses French, I developped a small 

module "product_no_translation" (cf 

https://github.com/akretion/odoo-usability/tree/8.0/product_no_translation) 

that sets translate=False on the fields of product.product + 

product.template, and I saved 10 seconds on the start time -> 1 min 5 

sec.

 

Now, when I activate firebug in Firefox to get the exact load time of 

each function, I get:

22 sec for the search_read of res.partner

42 sec for the search_read of product.product

 

When I look at the SQL requests when loading products, I found out that 

it first starts to load each field of product.product/product.template 

that are declared in odoo/addons/point_of_sale/static/src/js/models.js 

line 270, and that's quite fast because each SELECT request loads a high 

number of IDS, for example:

 

2014-12-30 09:11:25 CET LOG:  statement:  SELECT 

product_template."list_price",product_template."id" FROM 

"product_template"

                    WHERE product_template.id IN (12288, 12289, 12290, 

12291, 12292, 12293, 12294, 12295, 12296, 12297, 12298, 12299, 12300, 

12301, 12302, 12303, 12304, 12305, 12306, 12307, 12308, 12309, 12310, 

12311, 12312, 12313, 12314, 12315, 12316, 12317, 12318, 12319, 12320, 

12321, 12322, 12323, 12324, 12325, 12326, 12327, 12328, 12329, 12330, 

12331, 12332, 12333, 12334, 12335, 12336, 12337, 12338, 12339, 12340, 

12341, 12342, 12343, 12344, 12345, 12346, 12347, 12348, 12349, 12350, 

12351, 12352, 12353, 12354, 12355, 12356, 12357, 12358, 12359, 12360, 

12361, 12362, 12363, 12364, 12365, 12366, 12367, 12368, 12369, 12370, 

12371, 12372, 12373, 12374, 12375, 12376, 12377, 12378, 12379, 12380, 

12381, 12382, 12383, 12384, 12385, 12386, 12387, 12388, 12389, 12390, 

12391, 12392, 12393, 12394, 12395, 12396, 12397, 12398, 12399, 12400, 

12401, 12402, 12403, 12404, 12405, 12406, 12407, 12408, 12409, 12410, 

12411, 12412, 12413, 12414, 12415, 12416, 12417, 12418, 12419, 12420, 

12421, 12422, 12423, 12424, 12425, 12426, 12427, 12428, 12429, 12430, 

12431, 12432, 12433, 12234, 12235, 12236, 12237, 12238, 12239, 12240, 

12241, 12242, 12243, 12244, 12245, 12246, 12247, 12248, 12249, 12250, 

12251, 12252, 12253, 12254, 12255, 12256, 12257, 12258, 12259, 12260, 

12261, 12262, 12263, 12264, 12265, 12266, 12267, 12268, 12269, 12270, 

12271, 12272, 12273, 12274, 12275, 12276, 12277, 12278, 12279, 12280, 

12281, 12282, 12283, 12284, 12285, 12286, 12287) AND 

((("product_template"."company_id" = 17)  OR  

"product_template"."company_id" IS NULL ))

                    ORDER BY name

which seems OK. But, after that

 

 

BUT, after those high-volume SELECT requests, I have one SELECT request 

for each product.template that loads all the fields of the object 

product.template. As those SELECT requests only get 1 template by 1 

template, it takes 21 seconds to do that on my 9200 product templates 

available in POS ! Here is the SELECT request that is repeated 9200 

times:

 

 

2014-12-30 09:11:36 CET LOG:  statement:  SELECT 

product_template."warranty",product_template."track_all",product_template."uos_id",product_template."exclude_from_intrastat",product_template."mo_price",product_template."track_production",product_template."color",product_template."message_last_post",product_template."write_uid",product_template."mes_type",product_template."uom_id",product_template."mass_request_type_id",product_template."description_purchase",product_template."list_price",product_template."expense_pdt",product_template."id",product_template."income_pdt",product_template."uos_coeff",product_template."create_uid",product_template."weight",product_template."purchase_ok",product_template."product_manager",product_template."track_outgoing",product_template."company_id",product_template."name",product_template."state",product_template."loc_rack",product_template."origin_country_id",product_template."uom_po_id",product_template."manufacturer_id",product_template."type",product_template."track_incoming",product_template."is

_accessory_cost",product_template."description",product_template."must_print_barcode",product_template."weight_net",product_template."volume",product_template."tax_receipt_ok",product_template."donation",product_template."write_date",product_template."description_sale",product_template."active",product_template."collection_id",product_template."book_more_info",product_template."loc_row",product_template."intrastat_id",product_template."sale_ok",product_template."rental",product_template."sale_delay",product_template."loc_case",product_template."produce_delay",product_template."available_in_pos",product_template."categ_id",product_template."table_of_contents",product_template."pos_categ_id",product_template."create_date",product_template."mass",product_template."to_weight",product_template."author_label" 

FROM "product_template"

                     WHERE product_template.id IN (9754) AND (TRUE)

                     ORDER BY name

 

So, if we find a way (in the ORM ? in the code ?) to avoid those SELECT 

request on each product_template, we would reduce the POS start time by 

30% !

 

Eventually, I managed to reduce POS start time by a few seconds by not 

loading a few unused fields :

- I removed "state_id" on res.partner (models.js line 171), which 

reduced start time by a few seconds, because it avoids a lot of requets 

on ir_translation for the name of the state.

- I removed 'list_price', 'uos_id', 'uos_coeff', 'mes_type' for 

product.product (models.js line 270) because they are not used in the JS 

code of POS, and I earned a few more seconds.

 

With these changes, the start time of POS is now 1 min 0 sec. If you 

have other experiences on POS start time on v8 and other ideas to reduce 

it, don't hesitate to share them here !

 

Regards,

 

Alexis de Lattre

_______________________________________________
Mailing-List: https://www.odoo.com/groups/community-59
Post to: mailto:community@mail.odoo.com
Unsubscribe: https://www.odoo.com/groups?unsubscribe

_______________________________________________
Mailing-List: https://www.odoo.com/groups/community-59
Post to: mailto:community@mail.odoo.com
Unsubscribe: https://www.odoo.com/groups?unsubscribe