Housekeeper not deleting data (Zabbix 3.2 Postgres)

[eryunt]

Hi all,
I have problems with Housekeeper not deleting data.
My Zabbix Server is Version 3.2 (Postgres) and when Housekeeper is executed, in server logs appear the following errors:

PGRES_FATAL_ERROR:ERRORE: Cannot run UPDATE or DELETE on table history_uint because it does not have a PRIMARY KEY.
HINT: Add a PRIMARY KEY to the table

My DB is growing every day and these are the currend dimensions of my tables:

relation | total_size
---------------------+------------
public.history_uint | 188 GB
public.history | 124 GB
public.history_text | 20 GB
public.trends_uint | 720 MB
public.trends | 388 MB
(5 rows)


Also trying to manually delete rows in these tables prompts the same error...
How can I solve this situation?

Thanks in advance!

[doctorbal82]

eryunt ,

history* tables do not have primary key; that is the intended design by the Zabbix developers.

Hence this is unusual seeing this error.

Have you made any database changes? What PostgreSQL version on which OS are you using? Have you recently done an upgrade or postgres DB dumb anywhere?

As far as I know the history_uint table schema has not changed in 3.0, 3.2, 3.4, 4.0, 4.2 and is intentionally designed (for now) to not have primary keys; here is the schema:

CREATE TABLE history_uint (
    itemid bigint NOT NULL,
    clock integer DEFAULT '0' NOT NULL,
    value numeric(20) DEFAULT '0' NOT NULL,
    ns integer DEFAULT '0' NOT NULL
);
CREATE INDEX history_uint_1 ON history_uint (itemid,clock);

There is actually an open feature request in Zabbix JIRA tracking to add primary keys as some users have mentioned performance improvements - https://support.zabbix.com/browse/ZBXNEXT-3089.

From brief initial search your issue was also seen by another user but no solution posted - https://www.zabbix.com/forum/zabbix-...g-primary-keys.

The solution as I see it based on your log output (FATAL postgres error is not good) would be to add the PRIMARY KEY. BUT be very careful with that as future upgrades might break this. It would be best to understand the root cause.

ALTER TABLE history_uint ADD PRIMARY KEY (`itemid`,`clock`);

Side note - once you have figured this out consider using partitioning for your database. I wrote something in github around this (https://github.com/Doctorbal/zabbix-...s-partitioning) but there are also excellent options in the Zabbix Share (https://share.zabbix.com/databases/db_postgresql).

[eryunt]

Thanks for the reply,
I am using Postgres 9.4 and Ubuntu 16.04 Server.
No changes to DB made by me.
Also tried VACUUM but no result.
The only way to reduce the dimension of the DB seems to be to add a Primary Key.
By the way, It's a really strange problem...

[doctorbal82]

It IS a really strange problem hence why I am surprised this comes up. Let me know if adding the Primary Key helps resolve some aspects for the housekeeper and upgrading.

I would suggest to first replicate this in DEV, turn off Zabbix housekeeper completely and upgrade Zabbix to the latest version for 3.2 as well as add the primary keys and carefully look at the postgresql logs to see any other anomalies that might occur with this change.

Good luck!

[stevereaver]

Seems you and I have exactly the same issue. housekeeper never finishes and I have massive history (399GB) and history_uint (133GB) tables. I also had very large indexes but was able to reduce those with pg_repack -x. I'd like to run pg_repack on history* tables however they need to have primary key set. Zabbix 4.0.12 still does not have primary key is the schema, so I guess I will be adding and crossing my fingers!

[stevereaver]

I have not tried to delete a row per se, however I did run the following script and it seemed to complete.

psql -qAt -c "DELETE FROM trends WHERE to_timestamp(clock) < NOW()-INTERVAL '365 days';" zabbix
echo "Delete trends_units"
psql -qAt -c "DELETE FROM trends_uint WHERE to_timestamp(clock) < NOW()-INTERVAL '365 days';" zabbix
echo "Delete events"
psql -qAt -c "DELETE FROM events WHERE to_timestamp(clock) < NOW()-INTERVAL '365 days';" zabbix
echo "Delete alerts"
psql -qAt -c "DELETE FROM alerts WHERE to_timestamp(clock) < NOW()-INTERVAL '365 days';" zabbix
echo "Delete history"
psql -qAt -c "DELETE FROM history WHERE to_timestamp(clock) < NOW()-INTERVAL '365 days';" zabbix
echo "Delete history_unit"
psql -qAt -c "DELETE FROM history_uint WHERE to_timestamp(clock) < NOW()-INTERVAL '365 days';" zabbix
echo "Delete history_log"
psql -qAt -c "DELETE FROM history_log WHERE to_timestamp(clock) < NOW()-INTERVAL '365 days';" zabbix
echo "Delete history_str"
psql -qAt -c "DELETE FROM history_str WHERE to_timestamp(clock) < NOW()-INTERVAL '365 days';" zabbix
echo "Delete history_text"
psql -qAt -c "DELETE FROM history_text WHERE to_timestamp(clock) < NOW()-INTERVAL '365 days';" zabbix

I'm don't know if that helps. I only have very basic DBA's experience so I'm not sure about this at the moment.

[stevereaver]

I left the housekeeper process running for around 24 hours and overnight it seems to have sorted itself out. It's is now successfully removing history and trends greater that 365 days. And I have set to run every minute and it completes in a few seconds. However there are still MANY dead tuples in the History* tables. Which I cannot repack because there is no primary key.

tablename          | livetuples | deadtuples 
----------------------------+------------+------------
history_str                |    6453551 |     234151
history                    | 1516631139 |   46419901
history_text               |   19796925 |      35966
history_uint               | 1262696933 |   66304241