Contributors mailing list archives
Re: Odoo/PostgreSQL optimizationby
Open for Small Business, Graeme Gellatly
But really, nothing else for it but good old fashioned profiling on the Odoo side.
But products is hard work. Despite all that, I doubt postgres is the main part of your issues.
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.
assuming they aren't sharding, then turning on constraint_exclusion is pointless but harmless
default_statistics target is really really low and is highly likely to give suboptimal plans on any reasonably sized table.
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.
Maintenance work mem is just massive, 1GB is a generous amount
In general, thats a pretty unusual config.
In my opinionNobody turns fsync off for production for example, and statistics targets are normally quite a bit higher.
Ordering comes from another table, lots of joins, looks at translations etc.Then of course you've got a network between.
On Fri, Nov 10, 2017 at 6:02 PM, Maxime Chambreuil <email@example.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/secTiming buffered disk reads: 456 MB in 3.02 seconds = 151.16 MB/secKernel config of PostgreSQL:kernel.sched_migration_cost_ns = 5000000kernel.sched_autogroup_enabled = 0kernel.sem = 250 32000 100 128kernel.shmall = 4194304kernel.shmmax = 17179869184kernel.shmmni = 4096fs.file-max = 262140vm.vfs_cache_pressure = 50vm.min_free_kbytes = 65536net.core.rmem_default = 33554432net.core.rmem_max = 33554432net.core.wmem_default = 33554432net.core.wmem_max = 33554432net.ipv4.tcp_rmem = 10240 87380 33554432net.ipv4.tcp_wmem = 10240 87380 33554432net.ipv4.tcp_no_metrics_save = 1net.ipv4.tcp_window_scaling = 1net.ipv4.tcp_timestamps = 1net.ipv4.tcp_sack = 1net.core.netdev_max_backlog = 5000net.ipv4.tcp_mem = 786432 1048576 26777216net.ipv4.ip_local_port_range = 1024 65535net.ipv4.tcp_max_tw_buckets = 360000As the postgres user:$ ulimit -acore file size (blocks, -c) 0data seg size (kbytes, -d) unlimitedscheduling priority (-e) 0file size (blocks, -f) unlimitedpending signals (-i) 257588max locked memory (kbytes, -l) 64max memory size (kbytes, -m) unlimitedopen files (-n) 65535pipe size (512 bytes, -p) 8POSIX message queues (bytes, -q) 819200real-time priority (-r) 0stack size (kbytes, -s) 8192cpu time (seconds, -t) unlimitedmax user processes (-u) 257588virtual memory (kbytes, -v) unlimitedfile locks (-x) unlimitedodoo.conf extractlimit_memory_soft = 2412592128limit_memory_hard = 4560075776limit_time_cpu = 86400limit_time_real = 172800max_cron_threads = 1proxy_mode = Trueworkers = 6postgresql.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.