Community mailing list archives

community@mail.odoo.com

Re: POS v8 start time

by Frédéric van der Essen<fvdessen@gmail.com> - 12/31/2014 11:47:51
Hi,
Those performances are indeed not good enough. I'll experiment with caching the products/partners in IndexedDB/WebSQL.
- Frederic. 

On Tue, Dec 30, 2014 at 10:47 AM, alexis <alexis@via.ecp.fr> wrote:
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