Contributors mailing list archives
contributors@odoo-community.org
Browse archives
Re: Postgres Optimisation
byWith kind regards,
Mit freundlichen Grüßen,
Con un cordial saludo,
Cordialement,
с сердечным приветом,
เรื่องที่เกี่ยวกับชนิด,
與親切的問候,
CEO/General Manager LisAndi Co., Ltd.
LisAndi Co. Ltd., Phuket, Thailand (lisandi.com)
--------------------------------------------------
This email may contain confidential and/or privileged information. If you are not the intended recipient (or have received this email by mistake), please notify the sender immediately and destroy this email. Any unauthorized copying, disclosure or distribution of the material in this email is strictly prohibited. Email transmission security and error-free status cannot be guaranteed as information could be intercepted, corrupted, destroyed, delayed, incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message which may arise as a result of email transmissionThats a fairly blunt way of determining indexes and the thing is they aren't free. Aside from taking space, one bad index can cause the planner to choose a very suboptimal plan. That said those ones look OK even if a few are a bit useless, but it misses the most important indexes.If it is a small implementation then maybe you run on one server, in which case really you don't need to do anything. Defaults are pretty much fine. Or else just run pgtune, I don't think its been updated since 8.4 but still gives decent defaults for 9+. Maybe flick default stats target up to 0.9 if you are lazy and don't want to tune tables too much and you have lots more reads than writes (again monitor IO logs). It'll cost some space and write time, but is usually worth it. Use SSD's or have enough RAM to keep the whole database in there and finetuning won't even really matter. That gets you 80% of the way there, most won't need that last 20.
I find the opposite, so many useless indexes that need removing. It frustrates so much the official addons adding indexes where they are really unnecessary. Currencies, companies or countries tables are good examples. The indexes on those tables take more space than the actual table. When would the query planner ever use that? It is only 1 page, maybe 2 so pointless slow down on writes, the planner will always choose seq scan. Or organisations that don't have multi currency, or rarely use a second currency/have a dominant currency. How many pointless index writes is currency_id every time on those tables. Even worse, that one can cause really stupid plans (company_id being another offender). Just add it to a composite. Don't even get me started on the ir_translation tables. How many times that table ruins perfectly good queries and makes them slow as anything because of the way Odoo constructs queries for translatable fields and the way that table is indexed, or the Odoo query generator searching for NULL entries as well as the requested one requiring hash joins or extra filtering where the column is required because of how it reads the default record rules.
Another is btree indexes on commonly searched text fields, generally replace/enhance them all with pg_trgm GIN or GIST indexes (sometimes this needs postgres customization if using unaccent) depending on whether static or dynamic data. Most other indexes usually need composites if running multicompany (but depends on how the load is distributed by company), otherwise you end up with a really stupid query plan where you rescan a massive list of results. Also there are a few good composites which really speed things up regardless on big tables with a common query plan (like account.move.line), but that is just basic admin activity, as your data changes so do your index needs, not a deployment thing. And if your deployment is small you don't really need to know that, and if it is bigger, there is no one size fits all answer. It is an ongoing maintenance task. I'd say most of the time the problem is bad python (usually mine), creating poor queries, on a table(s) that has grown to need good index usage, rather than missing indexes.
Just monitor the logs using a log aggregator for common queries which accumulates both the number of queries, average time of query and total time. Also for indexes it is worth checking which ones are getting hit and when, and hopefully if you know your data, why. Also the opposite, which ones are never getting used and just costing write time. FWIW in my biggest implementation with a full tuned server as best I can, over 50% of database time is DELETE FROM wkf_.... But that is beside the point, the original post was about settings (which really don't make a lot of difference with a few specialized exceptions), not so much database maintenance (which makes orders of magnitude difference).
But to tune I take a few log files from busy periods, change a copy of the server, and replay those queries to simulate load and measure whether its worth it. For most postgres settings it really isn't, at least for me (DB is 5GB on disk, roughly 50/50 split between indexes and data, attachments in filesystem, so still pretty small really, easily held in RAM).
But most production installs for any decent amount of data/users will be running a separate postgres instance, probably with one or more streaming rep servers. In which case, it is time to learn postgres in the context of your own environment or hire someone. You don't want to be doing that stuff off someone’s blog post or wiki without understanding it.On Fri, Nov 20, 2015 at 7:53 AM, Ray Carnes <ray.carnes@bistasolutions.com> wrote:The only thing I’ve ever needed to do specific to Odoo was to create indices on some tables where they are missing. A major culprit is Community modules where indexing isn’t part of the module implementation.
I run this SQL to work out which tables are being joined to by foreign keys that aren’t indexed:
SELECT conrelid::regclass, attname
FROM pg_constraint
JOIN pg_class c ON (c.oid = conrelid)
JOIN pg_class fc ON (fc.oid = confrelid)
JOIN pg_attribute ON (attrelid = conrelid AND attnum = ANY(conkey))
WHERE contype = 'f'
AND confrelid != 'res_users'::regclass
AND c.reltuples > 1 AND fc.reltuples > 1
AND NOT EXISTS (
SELECT 1 FROM pg_index
WHERE indrelid = conrelid AND indkey[0] = conkey[1]);
It comes from this post https://gist.github.com/florentx/10875212
On a v9 system with all App installed, this SQL returns the following missing indices:
ir_model_fields | serialization_field_id
ir_module_module_dependency | module_id
ir_ui_view | model_data_id
ir_act_report_xml | ir_values_id
ir_act_window | search_view_id
ir_act_window | view_id
ir_values | model_id
res_country | currency_id
res_country_state | country_id
res_partner | commercial_partner_id
res_partner | country_id
res_partner | state_id
res_users | partner_id
account_financial_html_report_line | parent_id
purchase_order_line | partner_id
purchase_order_line | currency_id
This is a LOT less than the last time I ran this, back in v8, where it would return almost 100 tables.
Ray.
From: David Arnold [mailto:dar@devco.co]
Sent: Wednesday, November 18, 2015 8:53 PM
To: Contributors <contributors@odoo-community.org>
Subject: Postgres Optimisation
Hi
is anyone interested in an open discussion about PSQL optimisation?
Knowledge about PostgreSQL Configuration and Deployment is mostly privately held or simply very scars in the ecosystem. Many of us go probably with the defaults in their daily operations. This however is probably perceived as an uncomfortable black box and one would wish to have more knowledge dispersal in the odoo world to raise the overall deployment quality and establish best practices.
Here is an invitation to join this ad-hoc initiative to do something about this and gather, collect and consolidate the available odoo-specific knowledge. I chose and suggest the following approach:
The 212 config options of postgres9.4 are clustered into 34 (sub)categories, which I copied here for convenience. A comprehensive list of options you can find here: https://goo.gl/DPfDwK
If you A) know something about any of those categories, please consider replying to this email with a odoo-specific short description, like so:
"category X can be used to do Y. It is noteworthy altering some default values in the context of odoo deployment because Z"
AND/XOR
If you have B) some odoo-contextualized knowledge about a specific parameter (of the 212, see linked spreadsheet) please feel free to comment in the sheet iteself.
Throughout gathering phase I would consolidate Knowledge in the excel sheet and once, ideally, gathered sufficient Input, I would then write up a shared authorship blog post about the topic.
Some hints and general odoo optimizaiton stuff can be found here: http://de.slideshare.net/openobject/performance2014-35689113
PostgreSQL guides:
Best Regards and thanks for your interest
David
Autovacuum
Version and Platform Compatibility / Previous PostgreSQL Versions
Client Connection Defaults / Locale and Formatting
Client Connection Defaults / Other Defaults
Client Connection Defaults / Shared Library Preloading
Client Connection Defaults / Statement Behavior
File Locations
Connections and Authentication / Connection Settings
Connections and Authentication / Security and Authentication
Error Handling
Lock Management
Query Tuning / Genetic Query Optimizer
Query Tuning / Other Planner Options
Query Tuning / Planner Cost Constants
Query Tuning / Planner Method Configuration
Replication / Master Server
Replication / Sending Servers
Replication / Standby Servers
Reporting and Logging / What to Log
Reporting and Logging / When to Log
Reporting and Logging / Where to Log
Resource Usage / Asynchronous Behavior
Resource Usage / Background Writer
Statistics / Query and Index Statistics Collector
Resource Usage / Cost-Based Vacuum Delay
Resource Usage / Disk
Resource Usage / Kernel Resources
Resource Usage / Memory
Developer Options
Version and Platform Compatibility / Other Platforms and Clients
Statistics / Monitoring
Write-Ahead Log / Archiving
Write-Ahead Log / Checkpoints
Write-Ahead Log / Settings
_______________________________________________
Mailing-List: http://odoo-community.org/groups/contributors-15
Post to: mailto:contributors@odoo-community.org
Unsubscribe: http://odoo-community.org/groups?unsubscribe_______________________________________________
Mailing-List: http://odoo-community.org/groups/contributors-15
Post to: mailto:contributors@odoo-community.org
Unsubscribe: http://odoo-community.org/groups?unsubscribe_______________________________________________
Mailing-List: http://odoo-community.org/groups/contributors-15
Post to: mailto:contributors@odoo-community.org
Unsubscribe: http://odoo-community.org/groups?unsubscribe
