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

Contributors mailing list archives

contributors@odoo-community.org

Browse archives

Re: Odoo/PostgreSQL optimization

Re: Odoo/PostgreSQL optimization

Avatar

Re: Odoo/PostgreSQL optimization

by
MoaHub, Graeme Gellatly
- 10/11/2017 06:51:33
In general, thats a pretty unusual config.

In my opinion
Nobody turns fsync off for production for example, and statistics targets are normally quite a bit higher.
Maintenance work mem is just massive, 1GB is a generous amount
Work mem as well is very generous for an Odoo server, and remember that number gets allocated and multiplied by the amount of complexity in the query (simplification), it could possibly be consuming all the server.
default_statistics target is really really low and is highly likely to give suboptimal plans on any reasonably sized table.
assuming they aren't sharding, then turning on constraint_exclusion is pointless but harmless

But anyway, they are clearly tracking and logging queries, that would be the place to start on the postgres side.  Find slow queries analyze them, fix them.

But products is hard work.  Despite all that, I doubt postgres is the main part of your issues. 

Ordering comes from another table, lots of joins, looks at translations etc.
Then of course you've got a network between.
But really, nothing else for it but good old fashioned profiling on the Odoo side.

On Fri, Nov 10, 2017 at 6:02 PM, Maxime Chambreuil <mchambreuil@opensourceintegrators.com> 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


Reference