Ad Widget

Collapse

Housekeeper not deleting data (Zabbix 3.2 Postgres)

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

    Housekeeper not deleting data (Zabbix 3.2 Postgres)

    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!

    #2
    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:

    Code:
    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.

    Code:
    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).

    Comment


      #3
      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...

      Comment


      • doctorbal82
        doctorbal82 commented
        Editing a comment
        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!
    Working...
    X