[ZBX-16378] PostgreSQL slow query Created: 2019 Jul 15  Updated: 2019 Oct 15  Resolved: 2019 Oct 15

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: 4.2.3
Fix Version/s: None

Type: Problem report Priority: Trivial
Reporter: Evgeny Molchanov Assignee: Edgar Akhmetshin
Resolution: Incomplete Votes: 0
Labels: database, postgresql
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Ubuntu 18.04
PostgreSQL 11.4-1.pgdg18.04+1
Zabbix 4.2.3


Attachments: PNG File cache.png     PNG File getting.png     Text File hosts.txt     PNG File internal.png     Text File items.txt     PNG File perfomance.png     Text File pg_settings.txt     Text File pg_stat_all_tables.txt     Text File pg_stat_user_tables.txt     Text File pg_stat_user_tables.txt     Text File postgresql-zabbix-db.conf.txt     Text File zabbix_server.conf.txt    

 Description   

Steps to reproduce:

  1. login to database
  2. run explain analyze query
explain analyze select i.itemid,i.hostid,i.status,i.type,i.value_type,i.key_,i.snmp_community,i.snmp_oid,i.port,i.snmpv3_securityname,i.snmpv3_securitylevel,i.snmpv3_authpassphrase,i.snmpv3_privpassphrase,i.ipmi_sensor,i.delay,i.trapper_hosts,i.logtimefmt,i.params,i.state,i.authtype,i.username,i.password,i.publickey,i.privatekey,i.flags,i.interfaceid,i.snmpv3_authprotocol,i.snmpv3_privprotocol,i.snmpv3_contextname,i.lastlogsize,i.mtime,i.history,i.trends,i.inventory_link,i.valuemapid,i.units,i.error,i.jmx_endpoint,i.master_itemid,i.timeout,i.url,i.query_fields,i.posts,i.status_codes,i.follow_redirects,i.post_type,i.http_proxy,i.headers,i.retrieve_mode,i.request_method,i.output_format,i.ssl_cert_file,i.ssl_key_file,i.ssl_key_password,i.verify_peer,i.verify_host,i.allow_traps,i.templateid,id.parent_itemid from items i inner join hosts h on i.hostid=h.hostid left join item_discovery id on i.itemid=id.itemid where h.status in (0,1) and i.flags<>2;

Result:

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=22750.62..112925.16 rows=1338692 width=260) (actual time=1205.691..4043.411 rows=1655136 loops=1)
Hash Cond: (i.itemid = id.itemid)
-> Hash Join (cost=125.62..82377.02 rows=1338692 width=252) (actual time=3.567..1865.635 rows=1655136 loops=1)
Hash Cond: (i.hostid = h.hostid)
-> Seq Scan on items i (cost=0.00..77874.82 rows=1666907 width=252) (actual time=0.024..1322.627 rows=1668370 loops=1)
Filter: (flags <> 2)
Rows Removed by Filter: 180842
-> Hash (cost=115.10..115.10 rows=3006 width=8) (actual time=3.474..3.474 rows=3006 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 150kB
-> Seq Scan on hosts h (cost=0.00..115.10 rows=3006 width=8) (actual time=0.025..2.710 rows=3006 loops=1)
Filter: (status = ANY ('{0,1}'::integer[]))
Rows Removed by Filter: 737
-> Hash (cost=16680.20..16680.20 rows=1698515 width=16) (actual time=1190.917..1190.917 rows=1698539 loops=1)
Buckets: 2097152 Batches: 1 Memory Usage: 96004kB
-> Index Only Scan using item_discovery_1 on item_discovery id (cost=0.43..16680.20 rows=1698515 width=16) (actual time=0.094..753.578 rows=1698539 loops=1)
Heap Fetches: 641940
Planning Time: 6.035 ms
Execution Time: 4099.055 ms
(18 rows)

Expected:
query time ~ planning time



 Comments   
Comment by Edgar Akhmetshin [ 2019 Jul 18 ]

Hello Evgeny,

Please provide output from the following SQL queries:

\d hosts
\d items
SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables;

SELECT 
    schemaname
    ,relname
    ,n_live_tup
    ,n_dead_tup
    ,last_autovacuum
FROM pg_stat_all_tables
ORDER BY n_dead_tup
    /(n_live_tup
      * current_setting('autovacuum_vacuum_scale_factor')::float8
      + current_setting('autovacuum_vacuum_threshold')::float8)
     DESC

select name, setting, source, short_desc from pg_settings where name like '%autova%';

Also please attach performance graphs from Monitoring → Graphs → select your Zabbix server showing time period of 1 day:

  • Zabbix cache usage, % free
  • Zabbix data gathering process busy %
  • Zabbix internal process busy %
  • Zabbix server performance

And Zabbix Server configuration file with database configuration file.

Regards,
Edgar

Comment by Evgeny Molchanov [ 2019 Jul 18 ]

Add all need info.

Generated at Thu Mar 28 11:45:14 EET 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.