Contributors mailing list archives

contributors@odoo-community.org

Browse archives

Avatar

Odoo/PostgreSQL optimization

by
Open Source Integrators, Maxime Chambreuil
- 10/11/2017 05:47:53
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

Follow-Ups