1. Mailing Lists
  2. Contributors
  3. Re: Postgres Optimisation

Contributors mailing list archives

contributors@odoo-community.org

Browse archives

Re: Postgres Optimisation

RE: Postgres Optimisation

Avatar

Re: Postgres Optimisation

by
MoaHub, Graeme Gellatly
- 19/11/2015 22:40:21
Thats 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.

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).

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.

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


Reference