Hi,
I upgraded from ZABBIX 4.0 LTS to 5.0 LTS and in doing so switched from MySQL to Postgres based on articles advocating the improved performance and better use of disk thanks to the compression. Since doing so though, I have had nothing but problems, The ZABBIX (virtual) hardware and monitored environment is identical but I have been plagued with problems including constant alerts for " Number of waiting connections to high", "PostgreSQL: Active transaction to long (time=1m 2s 400ms)", "Zabbix housekeeper processes more than 75% busy" a bunch of errors in the Postgres logs (see below) and worst of all, I am currently using 100GB more disk than I was before and still growing !
To the best of my knowledge, I followed the install docs to the letter (including running timescale-tune ) and have tried the fix for the postgres logs found here Function pg_last_xlog_receive_location() does not exist in postgres 10 · Issue #907 · DataDog/integrations-core · GitHub but I am sure I have missed something somewhere. For starters, (If I am reading it properly) the ZABBIX server logs suggest that the housekeeper is deleting history row by row (see below) which I don't think is right but I don't know why.
Any help would be gratefully received !
----------
Postgres logs show;
2021-06-15 14:18:38.890 BST [3038208] postgres@postgres ERROR: unrecognized configuration parameter ": p1"
2021-06-15 14:18:38.890 BST [3038208] postgres@postgres STATEMENT: SELECT current_setting(': p1')
2021-06-15 14:18:40.197 BST [3038236] postgres@postgres ERROR: unrecognized configuration parameter ": p1"
2021-06-15 14:18:40.197 BST [3038236] postgres@postgres STATEMENT: SELECT current_setting(': p1')
2021-06-15 14:18:40.584 BST [3038262] postgres@postgres ERROR: unrecognized configuration parameter ": p1"
2021-06-15 14:18:40.584 BST [3038262] postgres@postgres STATEMENT: SELECT current_setting(': p1')
2021-06-15 14:18:54.396 BST [3038651] postgres@postgres ERROR: relation "pg_stat_statements" does not exist at character 64
2021-06-15 14:18:54.396 BST [3038651] postgres@postgres STATEMENT: SELECT round((sum(total_time) / sum(calls))::numeric,2) FROM pg_stat_statements
2021-06-15 14:19:02.808 BST [3038869] postgres@postgres ERROR: could not open directory "pg_xlog": No such file or directory
2021-06-15 14:19:02.808 BST [3038869] postgres@postgres STATEMENT: SELECT count(*) FROM pg_ls_dir('pg_xlog')
2021-06-15 14:19:04.219 BST [3038899] postgres@postgres ERROR: function pg_current_xlog_location() does not exist at character 30
2021-06-15 14:19:04.219 BST [3038899] postgres@postgres HINT: No function matches the given name and argument types. You might need to add explicit type casts.
2021-06-15 14:19:04.219 BST [3038899] postgres@postgres STATEMENT: SELECT pg_xlog_location_diff(pg_current_xlog_location(),' 0/00000000')
ZABBIX Server logs show this every few minutes but otherwise has nothing of interest;
slow query: 216.353397 sec, "delete from history_uint where itemid=469401 and ctid = any(array(select ctid from history_uint where itemid=469401 limit 300))"
My environment has;
- Master server : VMware ESXi 6.5 guest), Ubuntu 20, 8 CPU, 32GB RAM, 10GB OS disk, 350GB PostGres disk (SAN attached with no contention), Postgres 12, TimescaleDB
- 4x Proxy servers VMware ESXi 6.5 guest), Ubuntu 20, 2 CPU, 8GB RAM, 30GB OS disk (SAN attached with no contention)
618 hosts
107492 Items
Required server performance, new values per second 207.01
Master server versions;
postgresql-12/focal-pgdg,now 12.7-1.pgdg20.04+1 amd64 [installed]
postgresql-client-12/focal-pgdg,now 12.7-1.pgdg20.04+1 amd64 [installed]
postgresql-client-common/focal-pgdg,now 226.pgdg20.04+1 all [installed,automatic]
postgresql-common/focal-pgdg,now 226.pgdg20.04+1 all [installed,automatic]
timescaledb-loader-postgresql-12/focal,now 1.7.5~ubuntu20.04 amd64 [installed,automatic]
timescaledb-postgresql-12/focal,now 1.7.5~ubuntu20.04 amd64 [installed]
zabbix-agent/unknown,now 1:5.0.12-1+focal amd64 [installed]
zabbix-apache-conf/unknown,now 1:5.0.12-1+focal all [installed]
zabbix-frontend-php/unknown,now 1:5.0.12-1+focal all [installed]
zabbix-release/unknown,now 1:5.0-1+focal all [installed]
zabbix-server-pgsql/unknown,now 1:5.0.12-1+focal amd64 [installed]
I upgraded from ZABBIX 4.0 LTS to 5.0 LTS and in doing so switched from MySQL to Postgres based on articles advocating the improved performance and better use of disk thanks to the compression. Since doing so though, I have had nothing but problems, The ZABBIX (virtual) hardware and monitored environment is identical but I have been plagued with problems including constant alerts for " Number of waiting connections to high", "PostgreSQL: Active transaction to long (time=1m 2s 400ms)", "Zabbix housekeeper processes more than 75% busy" a bunch of errors in the Postgres logs (see below) and worst of all, I am currently using 100GB more disk than I was before and still growing !
To the best of my knowledge, I followed the install docs to the letter (including running timescale-tune ) and have tried the fix for the postgres logs found here Function pg_last_xlog_receive_location() does not exist in postgres 10 · Issue #907 · DataDog/integrations-core · GitHub but I am sure I have missed something somewhere. For starters, (If I am reading it properly) the ZABBIX server logs suggest that the housekeeper is deleting history row by row (see below) which I don't think is right but I don't know why.
Any help would be gratefully received !
----------
Postgres logs show;
2021-06-15 14:18:38.890 BST [3038208] postgres@postgres ERROR: unrecognized configuration parameter ": p1"
2021-06-15 14:18:38.890 BST [3038208] postgres@postgres STATEMENT: SELECT current_setting(': p1')
2021-06-15 14:18:40.197 BST [3038236] postgres@postgres ERROR: unrecognized configuration parameter ": p1"
2021-06-15 14:18:40.197 BST [3038236] postgres@postgres STATEMENT: SELECT current_setting(': p1')
2021-06-15 14:18:40.584 BST [3038262] postgres@postgres ERROR: unrecognized configuration parameter ": p1"
2021-06-15 14:18:40.584 BST [3038262] postgres@postgres STATEMENT: SELECT current_setting(': p1')
2021-06-15 14:18:54.396 BST [3038651] postgres@postgres ERROR: relation "pg_stat_statements" does not exist at character 64
2021-06-15 14:18:54.396 BST [3038651] postgres@postgres STATEMENT: SELECT round((sum(total_time) / sum(calls))::numeric,2) FROM pg_stat_statements
2021-06-15 14:19:02.808 BST [3038869] postgres@postgres ERROR: could not open directory "pg_xlog": No such file or directory
2021-06-15 14:19:02.808 BST [3038869] postgres@postgres STATEMENT: SELECT count(*) FROM pg_ls_dir('pg_xlog')
2021-06-15 14:19:04.219 BST [3038899] postgres@postgres ERROR: function pg_current_xlog_location() does not exist at character 30
2021-06-15 14:19:04.219 BST [3038899] postgres@postgres HINT: No function matches the given name and argument types. You might need to add explicit type casts.
2021-06-15 14:19:04.219 BST [3038899] postgres@postgres STATEMENT: SELECT pg_xlog_location_diff(pg_current_xlog_location(),' 0/00000000')
ZABBIX Server logs show this every few minutes but otherwise has nothing of interest;
slow query: 216.353397 sec, "delete from history_uint where itemid=469401 and ctid = any(array(select ctid from history_uint where itemid=469401 limit 300))"
My environment has;
- Master server : VMware ESXi 6.5 guest), Ubuntu 20, 8 CPU, 32GB RAM, 10GB OS disk, 350GB PostGres disk (SAN attached with no contention), Postgres 12, TimescaleDB
- 4x Proxy servers VMware ESXi 6.5 guest), Ubuntu 20, 2 CPU, 8GB RAM, 30GB OS disk (SAN attached with no contention)
618 hosts
107492 Items
Required server performance, new values per second 207.01
Master server versions;
postgresql-12/focal-pgdg,now 12.7-1.pgdg20.04+1 amd64 [installed]
postgresql-client-12/focal-pgdg,now 12.7-1.pgdg20.04+1 amd64 [installed]
postgresql-client-common/focal-pgdg,now 226.pgdg20.04+1 all [installed,automatic]
postgresql-common/focal-pgdg,now 226.pgdg20.04+1 all [installed,automatic]
timescaledb-loader-postgresql-12/focal,now 1.7.5~ubuntu20.04 amd64 [installed,automatic]
timescaledb-postgresql-12/focal,now 1.7.5~ubuntu20.04 amd64 [installed]
zabbix-agent/unknown,now 1:5.0.12-1+focal amd64 [installed]
zabbix-apache-conf/unknown,now 1:5.0.12-1+focal all [installed]
zabbix-frontend-php/unknown,now 1:5.0.12-1+focal all [installed]
zabbix-release/unknown,now 1:5.0-1+focal all [installed]
zabbix-server-pgsql/unknown,now 1:5.0.12-1+focal amd64 [installed]
Comment