1. Mailing Lists
  2. Contributors
  3. Re: Odoo/PostgreSQL optimization

Contributors mailing list archives

contributors@odoo-community.org

Browse archives

Proposal for repo OCA/iot 'Internet of Things'

Re: how to ask a running odoo what its addons path is

Avatar

Re: Odoo/PostgreSQL optimization

by
Vauxoo, Moisés López Calderón
- 13/07/2018 17:55:22
FYI There is another fix for this related issue, please review:



El mié., 4 jul. 2018 a las 13:46, Kevin McMenamin (<Kevin.McMenamin@solnet.co.nz>) escribió:

We did a local module for large customers to set all those counted fields to 0 – was causing big delays from a user perspective

 

From: Moises Lopez [mailto:moylop260@vauxoo.com]
Sent: Thursday, 5 July 2018 5:47 AM
To: Contributors <contributors@odoo-community.org>
Subject: Re: Odoo/PostgreSQL optimization

 

FYI  I had a similar issue related with a slow view of products:

 - https://github.com/odoo/odoo/pull/25280

 

Using the module odoo-profiler I found that the method slowest in python and slowest query in PostgreSQL is related with a computed field called: "sales_count".

It is so slow because is calculated using "sale.report" to get just the count of sales.

 

I just have created the PR with the fix.

I think that your issue could be related.

 

 

El lun., 13 nov. 2017 a las 9:02, Maxime Chambreuil (<mchambreuil@opensourceintegrators.com>) escribió:

Thanks guys,

 

Few corrections and additional info:

 

We are using PostgreSQL 9.3, not 8.3. Sorry, my mistake.

 

Both servers are on the same network and here is the ping:

 

root@10.20.194.11:~# ping 10.20.194.12

PING 10.20.194.12 (10.20.194.12) 56(84) bytes of data.

64 bytes from 10.20.194.12: icmp_seq=1 ttl=64 time=0.136 ms

64 bytes from 10.20.194.12: icmp_seq=2 ttl=64 time=0.138 ms

64 bytes from 10.20.194.12: icmp_seq=3 ttl=64 time=0.155 ms

64 bytes from 10.20.194.12: icmp_seq=4 ttl=64 time=0.126 ms

 

$ pip freeze

[...]

psycopg2==2.5.4

[...]

 

Odoo server

load average: 2.29, 2.42, 2.29

 

PostgreSQL server

load average: 1.67, 1.27, 1.16

 

We are using Apache 2 with the attached config files and the following modules:

 

# apachectl -M

Loaded Modules:

 core_module (static)

 so_module (static)

 watchdog_module (static)

 http_module (static)

 log_config_module (static)

 logio_module (static)

 version_module (static)

 unixd_module (static)

 access_compat_module (shared)

 alias_module (shared)

 auth_basic_module (shared)

 authn_core_module (shared)

 authn_file_module (shared)

 authz_core_module (shared)

 authz_host_module (shared)

 authz_user_module (shared)

 autoindex_module (shared)

 deflate_module (shared)

 dir_module (shared)

 env_module (shared)

 filter_module (shared)

 headers_module (shared)

 mime_module (shared)

 mpm_event_module (shared)

 negotiation_module (shared)

 proxy_module (shared)

 proxy_http_module (shared)

 security2_module (shared)

 setenvif_module (shared)

 socache_shmcb_module (shared)

 ssl_module (shared)

 status_module (shared)

 unique_id_module (shared)


 

MAXIME CHAMBREUIL
PROJECT MANAGER/CONSULTANT

O: 1.855.877.2377 EXT. 710
M: 602.427.5632
E: MChambreuil@OpenSourcelntegrators.com

P.O. BOX 940, HIGLEY, AZ 85236

 

 

On Fri, Nov 10, 2017 at 11:02 AM, Jos De Graeve <jos.degraeve@apertoso.be> wrote:

Hello Maxime,

 

If you have a separate machine for the database, ensure that the RTT between the DB and ODOO machines, is less than 0.5 milliseconds.  I have quite good experience with a setup on two machines ( one DB, one odoo ), they have an average RTT of 0,150 ms.  Simply run a ping from your odoo machine to the DB machine to test this.

I agree with Nhomar that you shoud upgrade your postgresql.  Upgrade to 9.6, first thing.  Check that you are not running an old version of the python psycopg2 module ( iirc, v2.5+ is ok )

 

Make sure that you postgresql configuration is properly set.  The default memory parameters are set for low memory usage, not for high performance.

Check these parameters: work_mem - maintenance_work_mem - effective_cache_size - shared_buffers - temp_buffers

 

If possible, consider to upgrade the CPU.  For response times, the single-thread performance is still very important.

 

SSD or nvme disks are very good for DB performance.  I'm not a big fan of RAID controllers.

 

Check your database indexes:

 

With 300k products postgres trigram indexes might significantly speed up name searches: https://www.postgresql.org/docs/9.6/static/pgtrgm.html -  the drawback of these indexes is that they consume a lot of disk space :/

 

Depending on the number of users, i would increase the number of workers, and have them use less memory.  If you tell me that performance is better outside of office hours, this is something to check !!

 

Check if you have iowait on your DB server, this will tell you if your disks can't keep up.  Check the load average on the odoo server: if it is too low, add more workers.

 

Regards,

 


Jos De Graeve - Apertoso business ICT

 

 

Guido Gezellelaan 16 - B-9800 Deinze - Belgium

 

Phone: +32 9 381 64 50

Mobile: +32 475 54 68 80

 

2017-11-10 15:02 GMT+01:00 Michael Telahun <miket@clearict.com>:

Hi Maxime,
 
Others have already given excellent replies, but I wanted to note a
couple of small changes that can give you big rewards:
1. If you are seeing deterioration over time this says to me that
increasing number of records added to db are causing longer search/query
times. Try to determine what your longest running queries are and see if
adding indexes helps.
 
2. Look at network traffic. Specifically, does odoo and the db
communicate on their own network segment separate from all other
traffic? Are the users on the local network or do they work remotely?
 
3. What does top show? If your mem usage, load averages, cpu %, and io
wait times are not showing high loads don't worry about any OS
parameters or hardware. Look at Odoo  postgres.
 
Ok, that was actually 3 things, but I hope you find them useful.
 
Regards,
Mike.
 
On Fri, 2017-11-10 at 05:02 +0000, Maxime Chambreuil wrote:
 
> Hello,
 
> 
 
> 
 
> I have a customer with ~300k products and he has been running Odoo
 
> version 8 with PostgreSQL 8.3 for almost a year now.
 
> 
 
> 
 
> Performance is deteriorating with time and especially during office
 
> hours when employees are working.
 
> 
 
> 
 
> I would like your input to improve performance based on the info
 
> below. If you need more, please let me know.
 
> 
 
> 
 
> Odoo and PostgreSQL runs on 2 separate servers, each with 64GB of
 
> memory and 8 CPU.
 
> 
 
> 
 
> On PostgreSQL:
 
> 
 
> 
 
> # hdparm -t -T /dev/mapper/vg01-root
 
> 
 
> 
 
> /dev/mapper/vg01-root:
 
>  Timing cached reads:   16838 MB in  2.00 seconds = 8425.66 MB/sec
 
>  Timing buffered disk reads: 456 MB in  3.02 seconds = 151.16 MB/sec
 
> 
 
> 
 
> Kernel config of PostgreSQL:
 
> 
 
> 
 
> kernel.sched_migration_cost_ns = 5000000
 
> kernel.sched_autogroup_enabled = 0
 
> kernel.sem = 250 32000 100 128
 
> kernel.shmall = 4194304
 
> 
 
> kernel.shmmax = 17179869184
 
> 
 
> kernel.shmmni = 4096
 
> fs.file-max = 262140
 
> vm.vfs_cache_pressure = 50
 
> vm.min_free_kbytes = 65536
 
> net.core.rmem_default = 33554432
 
> net.core.rmem_max = 33554432
 
> net.core.wmem_default = 33554432
 
> net.core.wmem_max = 33554432
 
> net.ipv4.tcp_rmem = 10240 87380 33554432
 
> net.ipv4.tcp_wmem = 10240 87380 33554432
 
> net.ipv4.tcp_no_metrics_save = 1
 
> net.ipv4.tcp_window_scaling = 1
 
> net.ipv4.tcp_timestamps = 1
 
> net.ipv4.tcp_sack = 1
 
> net.core.netdev_max_backlog = 5000
 
> net.ipv4.tcp_mem = 786432 1048576 26777216
 
> net.ipv4.ip_local_port_range = 1024 65535
 
> net.ipv4.tcp_max_tw_buckets = 360000
 
> 
 
> 
 
> As the postgres user:
 
> 
 
> 
 
> $ ulimit -a
 
> core file size          (blocks, -c) 0
 
> data seg size           (kbytes, -d) unlimited
 
> scheduling priority             (-e) 0
 
> file size               (blocks, -f) unlimited
 
> pending signals                 (-i) 257588
 
> max locked memory       (kbytes, -l) 64
 
> max memory size         (kbytes, -m) unlimited
 
> open files                      (-n) 65535
 
> pipe size            (512 bytes, -p) 8
 
> POSIX message queues     (bytes, -q) 819200
 
> real-time priority              (-r) 0
 
> stack size              (kbytes, -s) 8192
 
> cpu time               (seconds, -t) unlimited
 
> max user processes              (-u) 257588
 
> virtual memory          (kbytes, -v) unlimited
 
> file locks                      (-x) unlimited
 
> 
 
> 
 
> odoo.conf extract
 
> 
 
> 
 
> limit_memory_soft = 2412592128
 
> limit_memory_hard = 4560075776
 
> 
 
> limit_time_cpu = 86400
 
> 
 
> limit_time_real = 172800
 
> 
 
> max_cron_threads = 1
 
> 
 
> proxy_mode = True
 
> workers = 6
 
> 
 
> 
 
> postgresql.conf is attached.
 
> 
 
> 
 
> During office hours, opening the product list (80 records) takes 5
 
> sec. Running sales report (product category by week) takes 20 sec. I
 
> never succeeded to get the inventory valuation report.
 
> 
 
> 
 
> Outside office hours, waiting time is divided by 2.
 
> 
 
> 
 
> If you have any suggestions, it would be very much appreciated.
 
> 
 
> 
 
> Thank you.
 
> 
 
> MAXIME CHAMBREUIL
 
> PROJECT MANAGER/CONSULTANT
 
> O: 1.855.877.2377 EXT. 710
 
> M: 602.427.5632
 
> E: MChambreuil@OpenSourcelntegrators.com
 
 
> P.O. BOX 940, HIGLEY, AZ 85236
 
> 
> 
> 
> 
> 
> _______________________________________________
> Mailing-List: https://odoo-community.org/groups/contributors-15
 
> Post to: mailto:contributors@odoo-community.org
> Unsubscribe: https://odoo-community.org/groups?unsubscribe
> 
> 
> 
 
 

_______________________________________________
Mailing-List: https://odoo-community.org/groups/contributors-15
Post to: mailto:contributors@odoo-community.org
Unsubscribe: https://odoo-community.org/groups?unsubscribe

 

_______________________________________________
Mailing-List: https://odoo-community.org/groups/contributors-15
Post to: mailto:contributors@odoo-community.org
Unsubscribe: https://odoo-community.org/groups?unsubscribe

 

_______________________________________________
Mailing-List: https://odoo-community.org/groups/contributors-15
Post to: mailto:contributors@odoo-community.org
Unsubscribe: https://odoo-community.org/groups?unsubscribe


 

--

Moisés López Calderón

Mobile: (+521) 477-752-22-30

Twitter: @moylop260

hangout: moylop260@vauxoo.com
http://www.vauxoo.com - Odoo Gold Partner

Twitter: @vauxoo

Attention: This email may contain information intended for the sole use of the original recipient. Please respect this when sharing or disclosing this email's contents with any third party. If you believe you have received this email in error, please delete it and notify the sender or postmaster@solnetsolutions.co.nz as soon as possible. The content of this email does not necessarily reflect the views of Solnet Solutions Ltd.

_______________________________________________
Mailing-List: https://odoo-community.org/groups/contributors-15
Post to: mailto:contributors@odoo-community.org
Unsubscribe: https://odoo-community.org/groups?unsubscribe



--
Moisés López Calderón
Mobile: (+521) 477-752-22-30
Twitter: @moylop260
hangout: moylop260@vauxoo.com
http://www.vauxoo.com - Odoo Gold Partner
Twitter: @vauxoo

Reference