Hello group!!

kindly ask you for your advise/support, our server gets saturated when Pentaho is active (tomcat7 user) bringing the 4 cores at 100% and starting to lock the DB connections, therefore other ODOO actions start to not be granted and we have to stop tomcat7 service. The biggest report return ~200 lines. Reports of ~40 lines take in average +1 minute to be returned in a LAN.

Is hard to believe that small reports that might have little intensive read only queries in not that big DB and with not that many concurrent connections can really harm like this in a server that has a good capacity.

The DB connections get a lot of time to return which at the end becomes on not returning and locking the DB:

Challenge:
how to determine which Pentaho SQL query is running behind the one exposed in pg_activity:

PostgreSQL 9.3.14 - SAS2 - postgres@localhost:5432/postgres - Ref.: 2s
  Size:    8.82G -     0.00B/s        | TPS:         126
  Mem.:   15.80% -     3.70G/23.50G   | IO Max:     5410/s
  Swap:    0.00% -     0.00B/0.00B    | Read :      0.00B/s -      0/s
  Load:    6.43 5.64 5.03             | Write:      0.00B/s -      0/s
                                                                         PAUSE                                                                              
PID    DATABASE              USER           CLIENT   CPU% MEM%   READ/s  WRITE/s     TIME+  W  IOW  Query                                                   
44652  db              odoo             None    0.0 10.7    0.00B    0.00B  00:53.99  N    N  SELECT "ir_config_parameter"."key","ir_config_parameter".
 "create_date","ir_config_parameter"."create_uid","ir_config_parameter"."value","ir_config_parameter"."id","ir_config_parameter"."write_date","ir_config_para
 eter"."write_uid" FROM "ir_config_parameter" WHERE "ir_config_parameter".id IN (16) AND ((FALSE OR "ir_config_parameter"."id" IS NULL) OR (TRUE OR "ir_confi
 _parameter"."id" IS NULL)) ORDER BY "ir_config_parameter"."id"

46347  db              odoo             None    0.0 14.2    0.00B    0.00B  0.023171  N    N  SELECT "sale_order_line"."id","sale_order_line"."order_id
 " FROM "sale_order_line" LEFT JOIN "system_system" as "sale_order_line__system_id" ON ("sale_order_line"."system_id" = "sale_order_line__system_id"."id") WH
 RE "sale_order_line".id IN (506118, 506151, 506152, 506153, 506154, 506155, 506156, 506158, 506159, 506160, 506161) AND (("sale_order_line"."company_id" IS
 ULL OR ("sale_order_line"."company_id" in (1))) AND (FALSE OR ("sale_order_line"."order_id" in (71500,71499,71498,71497,71496,71493,71492,71491,71490,71489,
 1488,71487,71486,71485,71484,71483,71482,71481,71480,71475,71474,71473,71472,71471,71470,71469,71468,71467,71466,71465,71464,71463,71462,71461,71460,71459,7
 458,71457,71456,71455,71454,71453,71452,71451,71450,71449,71448,71447,71446,71445,71444,71443,71442,71441,71440,71439,71436,71435,71429,71428,71426,71425,71
 24,71423,71422,71421,71420,71419,71418,71417,71416,71415,71414,71413,71412,71411,71410,71409,71406,71405,71404,71403,71402,71401,71400,71399,71398,71 47686  db              odoo             None    0.0  8.3    0.00B    0.00B  0.015813  N    N  SELECT "res_partner".id FROM "res_partner" WHERE (("res_p
 artner"."active" = true) AND "res_partner"."branch_id" IS NULL ) ORDER BY "res_partner"."display_name"



Further configuration :

Odoo-server:

limit_memory_hard = 6984354560 limit_memory_soft = 5747483648 limit_request = 8192 limit_time_cpu = 480000 limit_time_real = 960000 list_db = True log_db = False log_db_level = warning log_handler = :INFO log_level = info logfile = /var/log/odoo/odoo-server.log logrotate = True longpolling_port = 8072 max_cron_threads = 2 osv_memory_age_limit = 1.0 osv_memory_count_limit = False pg_path = None pidfile = False proxy_mode = True reportgz = False secure_cert_file = server.cert secure_pkey_file = server.pkey

without_demo = False workers = 9 xmlrpc = True xmlrpc_interface = 0.0.0.0 xmlrpc_port = 8069 xmlrpcs = True xmlrpcs_interface = 127.0.0.1 xmlrpcs_port = 8071

Postgresql:

# - Connection Settings -

listen_addresses = '*'       
port = 5432               
max_connections = 110

listen_addresses = '*'       
port = 5432           
max_connections = 110           
unix_socket_directories = '/var/run/postgresql'
shared_buffers = 5GB            # min 128kB
                    # (change requires restart)
                    # (change requires restart)
work_mem = 10MB                # min 64kB


effective_cache_size = 11GB                            #   open_sync wal_buffers = 1MB            # min 32kB, -1 sets based on shared_buffers checkpoint_segments = 10        # in logfile segments, min 1, 16MB each checkpoint_completion_target = 0.9    # checkpoint target duration, 0.0 - 1.0 track_counts = on autovacuum = on            # Enable autovacuum subprocess?  'on'
                    # requires track_counts to also be on.
                    # their durations, > 0 logs only
                    # actions running at least this number
                    # of milliseconds.
autovacuum_max_workers = 3        # max number of autovacuum subprocesses
                    # (change requires restart)
autovacuum_naptime = 1min        # time between autovacuum runs autovacuum_vacuum_threshold = 80    # min number of row updates before
                    # vacuum autovacuum_analyze_threshold = 100    # min number of row updates before
                    # analyze autovacuum_vacuum_scale_factor = 0.2    # fraction of table size before vacuum autovacuum_analyze_scale_factor = 0.1    # fraction of table size before analyze datestyle = 'iso, dmy'
timezone = 'localtime'
lc_messages = 'en_GB.UTF-8'            # locale for system error message lc_monetary = 'en_GB.UTF-8'            # locale for monetary formatting lc_numeric = 'en_GB.UTF-8'            # locale for number formatting lc_time = 'en_GB.UTF-8'                # locale for time formatting

default_text_search_config = 'pg_catalog.english'

Environment:

- average ~90 users connected

- The biggest table: mail_message: ~3M rows

- Server: 4 cores, 24GB RAM, 40% free disk space

Challenge:
how to determine which Pentaho SQL query is running behind the one exposed in pg_activity:

PostgreSQL 9.3.14 - SAS2 - postgres@localhost:5432/postgres - Ref.: 2s
  Size:    8.82G -     0.00B/s        | TPS:         126
  Mem.:   15.80% -     3.70G/23.50G   | IO Max:     5410/s
  Swap:    0.00% -     0.00B/0.00B    | Read :      0.00B/s -      0/s
  Load:    6.43 5.64 5.03             | Write:      0.00B/s -      0/s
                                                                         PAUSE                                                                              
PID    DATABASE              USER           CLIENT   CPU% MEM%   READ/s  WRITE/s     TIME+  W  IOW  Query                                                   
44652  db              odoo             None    0.0 10.7    0.00B    0.00B  00:53.99  N    N  SELECT "ir_config_parameter"."key","ir_config_parameter".
 "create_date","ir_config_parameter"."create_uid","ir_config_parameter"."value","ir_config_parameter"."id","ir_config_parameter"."write_date","ir_config_para
 eter"."write_uid" FROM "ir_config_parameter" WHERE "ir_config_parameter".id IN (16) AND ((FALSE OR "ir_config_parameter"."id" IS NULL) OR (TRUE OR "ir_confi
 _parameter"."id" IS NULL)) ORDER BY "ir_config_parameter"."id"

46347  db              odoo             None    0.0 14.2    0.00B    0.00B  0.023171  N    N  SELECT "sale_order_line"."id","sale_order_line"."order_id
 " FROM "sale_order_line" LEFT JOIN "system_system" as "sale_order_line__system_id" ON ("sale_order_line"."system_id" = "sale_order_line__system_id"."id") WH
 RE "sale_order_line".id IN (506118, 506151, 506152, 506153, 506154, 506155, 506156, 506158, 506159, 506160, 506161) AND (("sale_order_line"."company_id" IS
 ULL OR ("sale_order_line"."company_id" in (1))) AND (FALSE OR ("sale_order_line"."order_id" in (71500,71499,71498,71497,71496,71493,71492,71491,71490,71489,
 1488,71487,71486,71485,71484,71483,71482,71481,71480,71475,71474,71473,71472,71471,71470,71469,71468,71467,71466,71465,71464,71463,71462,71461,71460,71459,7
 458,71457,71456,71455,71454,71453,71452,71451,71450,71449,71448,71447,71446,71445,71444,71443,71442,71441,71440,71439,71436,71435,71429,71428,71426,71425,71
 24,71423,71422,71421,71420,71419,71418,71417,71416,71415,71414,71413,71412,71411,71410,71409,71406,71405,71404,71403,71402,71401,71400,71399,71398,71 47686  db              odoo             None    0.0  8.3    0.00B    0.00B  0.015813  N    N  SELECT "res_partner".id FROM "res_partner" WHERE (("res_p
 artner"."active" = true) AND "res_partner"."branch_id" IS NULL ) ORDER BY "res_partner"."display_name"

Thank you!!!