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
Raphael Makonnen
- 10/11/2017 14:43:30 
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
> 
> 
>

Reference