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

Contributors mailing list archives

contributors@odoo-community.org

Browse archives

Re: Odoo/PostgreSQL optimization

Avatar

Re: Odoo/PostgreSQL optimization

by
Vauxoo, Nhomar Hernández
- 10/11/2017 06:38:52
Hola Maxime.

Several things must be done to check why is this so slow.

Inline some topics (but mostly I need access to measure detailed things we have customers like that).


--
Nhomar Hernandez
CEO Vauxoo.
Twitter: @nhomar
Odoo Gold Partner
Skype: nhomar00 (Envia mail previo no lo superviso siempre).
Móvil Venezuela:
+58 4144110269 (Telegram)
Móvil México:
+52 1 4773933942 (Whatsapp)

On Thu, Nov 9, 2017 at 11: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.

​Move ASAP to postgresql 9.x​

​From here.

- Multiple performance enhancements for specific types of queries, including elimination of unnecessary joins. This helps optimize some automatically-generated queries, such as those produced by object-relational mappers (ORMs).


Performance is deteriorating with time and especially during office hours when employees are working.

​Normal (it happens to us currently) Some tips.

1.- Try to avoid generate unecessary reports more than once (the PDF ones in v8.0 are heavy).
2.- You need to measure what are the tasks blocking, frequently there are reports and views being processed behind scenes even if you closed the tab.
​3.- Block with ACL more complex views if possible (mainly the reports on views) in order to ensure they are used only for the ones that need them.
4.- Try to make asyncronous the more used ones and send them by email.
​5.- Measure measure measure, To solve this issues we try to have a mirror environment​s to test properly the specific clicks that delay.
6.- Attachmentsw are being saved properly in the filessytem or in the database?

 

I would like your input to improve performance based on the info below. If you need more, please let me know.

​Not to much info yet to give proper tips, to fix performance we must measure.​

Odoo and PostgreSQL runs on 2 separate servers, each with 64GB of memory and 8 CPU.


​This is a bad Idea, you are including the lag on the I/O over the network (I supose they are in the same network) they have gigaethernet? Solid states disks? raid? there is more necessary information there, but the I/O is a killing topic.

What we do is have a proper DRP and have everything in the same server (and more with v8.0).

They are not Virtual servers right?​

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

​A proper RAID can help you with this. Our biggest server have 1GBPS reading/writting to disk, here it looks like you have an unique solid disk.​

 

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

​This can be improved I think but not my strong point.​

 

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

​I think you should put down this numbers increase them will give worst sense of slow, it is better try to go and kill the limits with max 5 mins (this will deliver an error but will not block the server, then attack in a testing environment the ​problem directly with no workers to measure.

 
max_cron_threads = 1
proxy_mode = True
workers = 6

​Increase the workers.​

 

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.

​This report does not work on that size, make it asyncronous (I have modules v8.0 ready for that), contact us to share them with you.​

 

Outside office hours, waiting time is divided by 2.

​I need the nginx configuration also to give more tips.​

 

If you have any suggestions, it would be very much appreciated.


​We deliver a presentation on OEXP about how we attack this problems, you can see it there, there is some tools that can help you there.

 

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