[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 |
Attachments: | cache.png getting.png hosts.txt internal.png items.txt perfomance.png pg_settings.txt pg_stat_all_tables.txt pg_stat_user_tables.txt pg_stat_user_tables.txt postgresql-zabbix-db.conf.txt zabbix_server.conf.txt |
Description |
Steps to reproduce:
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: |
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:
And Zabbix Server configuration file with database configuration file. Regards, |
Comment by Evgeny Molchanov [ 2019 Jul 18 ] |
Add all need info. |