Difference between revisions of "Zabbix Cleanup"

From UVOO Tech Wiki
Jump to navigation Jump to search
(Created page with "https://github.com/azmelanar/zabbix-database-cleanup/blob/master/orphaned/clean.sql https://github.com/burner1024/zabbix-sql ``` delete from history where itemid not in (sel...")
 
 
(28 intermediate revisions by the same user not shown)
Line 1: Line 1:
 +
Disable autovacuum on large tables
 +
```
 +
ALTER TABLE history SET (autovacuum_enabled = false);
 +
ALTER TABLE history_uint SET (autovacuum_enabled = false);
 +
ALTER TABLE history_str SET (autovacuum_enabled = false);
 +
ALTER TABLE history_text SET (autovacuum_enabled = false);
 +
ALTER TABLE history_log SET (autovacuum_enabled = false);
 +
```
 +
 +
Checks
 +
```
 +
SELECT relname, n_dead_tup FROM pg_stat_user_tables WHERE relname like 'history%';
 +
 +
SELECT
 +
      relname AS "table_name",
 +
      pg_size_pretty(pg_table_size(C.oid)) AS "table_size"
 +
FROM
 +
      pg_class C
 +
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 +
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r')
 +
ORDER BY pg_table_size(C.oid)
 +
DESC LIMIT 1;
 +
 +
SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables;
 +
 +
SELECT reloptions FROM pg_class WHERE relname='my_table';
 +
 +
 +
select * from pg_stat_progress_vacuum;
 +
 +
SELECT pg_size_pretty( pg_total_relation_size('history_uint') );
 +
 +
SELECT *
 +
FROM pg_settings
 +
WHERE name LIKE 'autovacuum%';
 +
```
 +
- https://medium.com/coding-blocks/optimizing-storage-and-managing-cleanup-in-postgresql-c2fe56d4cf5
 +
- https://dba.stackexchange.com/questions/44657/how-much-time-will-a-vacuum-autovacuum-operation-take
 +
- https://www.datadoghq.com/blog/postgresql-vacuum-monitoring/
 +
 +
# Script
 +
 +
.env
 +
```
 +
export PGPASSWORD=foo
 +
export PGUSER=admin
 +
export PGDATABASE=demodb
 +
export PGHOST=postgres1.postgres-prod.svc.cluster.local
 +
```
 +
 +
run-cleanup.sh
 +
```
 +
. .env
 +
nohup ./vacuum-reindex.sh &
 +
```
 +
 +
vacuum-reindex.sh
 +
```
 +
#!/bin/bash
 +
set -eu
 +
 +
db=zabbix
 +
 +
# tables=( "history_uint" "history" "history_text" "history_str" "history_log" "trends" "trends_uint" )
 +
tables=( "history_uint" "history" "history_text" "history_str" "history_log" )
 +
 +
 +
timestamp() {
 +
  #date +"%T"
 +
  ts=$(date "+%Y%m%d-%H%M%S")
 +
  echo $ts
 +
}
 +
 +
 +
delete_table_records(){
 +
  table=$1
 +
  echo I: Starting delete of table $table records.
 +
  psql ${db} -c "DELETE FROM ${table}"
 +
  echo I: Ending delete of table $table records.
 +
}
 +
 +
 +
delete(){
 +
  table=$1
 +
  days_before=$2
 +
  echo I: Starting delete of table $table items before ${days_before} in past.
 +
  timestamp
 +
  echo "DELETE FROM ${table} WHERE clock < extract(epoch from (SELECT NOW() - INTERVAL '${days_before} DAY'))"
 +
  time psql ${db} -c "DELETE FROM ${table} WHERE clock < extract(epoch from (SELECT NOW() - INTERVAL '${days_before} DAY'))"
 +
  timestamp
 +
  echo I: Ending delete of table $table before ${days_before} in past.
 +
}
 +
 +
 +
vacuum(){
 +
  table=$1
 +
  echo I: Starting vacuum of table $table.
 +
  timestamp
 +
  # psql ${db} -c "VACUUM(ANALYZE, VERBOSE) ${table}"
 +
  # psql ${db} -c "VACUUM(FULL, VERBOSE) ${table}"
 +
  time psql ${db} -c "VACUUM(ANALYZE, VERBOSE) ${table}"
 +
  timestamp
 +
  echo I: Ending vacuum of table $table.
 +
}
 +
 +
 +
reindex(){
 +
  table=$1
 +
  echo I: Starting reindex concurrently of table $table.
 +
  timestamp
 +
  time psql ${db} -c "REINDEX TABLE CONCURRENTLY ${table}"
 +
  timestamp
 +
  echo I: Ending reindex concurrently of table $table.
 +
}
 +
 +
 +
vaccum_full_reindexmain(){
 +
  time psql ${db} -c "VACUUM FULL VERBOSE"
 +
  time psql ${db} -c "REINDEX VERBOSE DATABASE ${db}"
 +
}
 +
 +
 +
vaccum_reindex_db(){
 +
  echo start
 +
  # timestamp
 +
  psql ${db} -c "VACUUM VERBOSE"
 +
  # psql ${db} -c "REINDEX CONCURRENTLY VERBOSE DATABASE ${db}"
 +
}
 +
 +
remove_disabled_item_history(){
 +
 +
        declare -a queries=(
 +
                "delete from history where itemid not in (select itemid from items where status='0');"
 +
                "delete from history where itemid not in (select itemid from items where status='0');"
 +
                "delete from history_uint where itemid not in (select itemid from items where status='0');"
 +
                "delete from history_str where itemid not in (select itemid from items where status='0');"
 +
                "delete from history_text where itemid not in (select itemid from items where status='0');"
 +
                "delete from history_log where itemid not in (select itemid from items where status='0');"
 +
                "delete from trends where itemid not in (select itemid from items where status='0');"
 +
                "delete from trends_uint where itemid not in (select itemid from items where status='0');"
 +
        )
 +
 +
        for query in "${queries[@]}"
 +
        do
 +
                echo "$query"
 +
    time psql ${db} -c "$query"
 +
        done
 +
 +
}
 +
 +
 +
main(){
 +
  # remove_disabled_item_history
 +
  echo Start table optimize.
 +
  timestamp
 +
  for table in "${tables[@]}"; do
 +
    echo $table
 +
    delete $table 14
 +
    vacuum $table
 +
    # reindex $table
 +
    # delete_table_records $table
 +
  done
 +
  timestamp
 +
  echo End table optimize.
 +
}
 +
 +
 +
main
 +
# vaccum_reindex_db
 +
```
 +
 +
 +
 +
# Other Stuff/Notes
 +
```
 +
DELETE FROM history_uint WHERE clock < extract(epoch from (SELECT NOW() - INTERVAL '35 DAY'));
 +
DELETE FROM history WHERE clock < extract(epoch from (SELECT NOW() - INTERVAL '35 DAY'));
 +
 +
DELETE FROM history_str WHERE clock < extract(epoch from (SELECT NOW() - INTERVAL '35 DAY'));
 +
DELETE FROM history_text WHERE clock < extract(epoch from (SELECT NOW() - INTERVAL '35 DAY'));
 +
DELETE FROM history_log WHERE clock < extract(epoch from (SELECT NOW() - INTERVAL '35 DAY'));
 +
 +
other options
 +
trends_uint
 +
trends
 +
events
 +
alerts
 +
```
 +
 +
orphans
 +
```
 +
select count(*) from item_tag where itemid not in (select itemid from items);
 +
delete from item_tag where itemid not in (select itemid from items);
 +
```
 +
 +
 
https://github.com/azmelanar/zabbix-database-cleanup/blob/master/orphaned/clean.sql
 
https://github.com/azmelanar/zabbix-database-cleanup/blob/master/orphaned/clean.sql
  
 
https://github.com/burner1024/zabbix-sql
 
https://github.com/burner1024/zabbix-sql
 +
 +
https://www.zabbix.com/documentation/2.2/manual/api/reference/item/object
 +
```
 +
Status of the item.
 +
 +
Possible values:
 +
0 - (default) enabled item;
 +
1 - disabled item.
 +
```
  
 
```
 
```
Line 84: Line 289:
 
DELETE FROM trends_uint WHERE itemid NOT IN (SELECT itemid FROM items);
 
DELETE FROM trends_uint WHERE itemid NOT IN (SELECT itemid FROM items);
 
```
 
```
 +
 +
MYSQL
 +
```
 +
DELETE FROM history_uint WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 35 DAY));
 +
DELETE FROM history WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 35 DAY));
 +
DELETE FROM trends_uint WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 35 DAY));
 +
DELETE FROM trends WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 35 DAY));
 +
DELETE FROM events WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 35 DAY));
 +
DELETE FROM alerts WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 35 DAY));
 +
```
 +
 +
 +
# Option 2
 +
- https://github.com/mattiasgeniar/zabbix-orphaned-data-cleanup/blob/master/cleanup.sql
 +
 +
```
 +
-- Delete orphaned alerts entries
 +
DELETE FROM alerts WHERE NOT EXISTS (SELECT 1 FROM actions WHERE alerts.actionid = actions.actionid);
 +
DELETE FROM alerts WHERE NOT EXISTS (SELECT 1 FROM events WHERE alerts.eventid = events.eventid);
 +
DELETE FROM alerts WHERE NOT userid IN (SELECT userid FROM users);
 +
DELETE FROM alerts WHERE NOT mediatypeid IN (SELECT mediatypeid FROM media_type);
 +
 +
-- Delete orphaned application entries that no longer map back to a host
 +
DELETE FROM applications WHERE NOT hostid IN (SELECT hostid FROM hosts);
 +
 +
-- Delete orphaned auditlog details (such as logins)
 +
DELETE FROM auditlog_details WHERE NOT auditid IN (SELECT auditid FROM auditlog);
 +
DELETE FROM auditlog WHERE NOT userid IN (SELECT userid FROM users);
 +
 +
-- Delete orphaned conditions
 +
DELETE FROM conditions WHERE NOT actionid IN (SELECT actionid FROM actions);
 +
 +
-- Delete orphaned functions
 +
DELETE FROM functions WHERE NOT itemid IN (SELECT itemid FROM items);
 +
DELETE FROM functions WHERE NOT triggerid IN (SELECT triggerid FROM triggers);
 +
 +
-- Delete orphaned graph items
 +
DELETE FROM graphs_items WHERE NOT graphid IN (SELECT graphid FROM graphs);
 +
DELETE FROM graphs_items WHERE NOT itemid IN (SELECT itemid FROM items);
 +
 +
-- Delete orphaned host macro's
 +
DELETE FROM hostmacro WHERE NOT hostid IN (SELECT hostid FROM hosts);
 +
 +
-- Delete orphaned item data
 +
DELETE FROM items WHERE hostid NOT IN (SELECT hostid FROM hosts);
 +
DELETE FROM items_applications WHERE applicationid NOT IN (SELECT applicationid FROM applications);
 +
DELETE FROM items_applications WHERE itemid NOT IN (SELECT itemid FROM items);
 +
 +
-- Delete orphaned HTTP check data
 +
DELETE FROM httpstep WHERE NOT httptestid IN (SELECT httptestid FROM httptest);
 +
DELETE FROM httpstepitem WHERE NOT httpstepid IN (SELECT httpstepid FROM httpstep);
 +
DELETE FROM httpstepitem WHERE NOT itemid IN (SELECT itemid FROM items);
 +
DELETE FROM httptest WHERE applicationid NOT IN (SELECT applicationid FROM applications);
 +
 +
-- Delete orphaned maintenance data
 +
DELETE FROM maintenances_groups WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
 +
DELETE FROM maintenances_groups WHERE groupid NOT IN (SELECT groupid FROM groups);
 +
DELETE FROM maintenances_hosts WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
 +
DELETE FROM maintenances_hosts WHERE hostid NOT IN (SELECT hostid FROM hosts);
 +
DELETE FROM maintenances_windows WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
 +
DELETE FROM maintenances_windows WHERE timeperiodid NOT IN (SELECT timeperiodid FROM timeperiods);
 +
 +
-- Delete orphaned mappings
 +
DELETE FROM mappings WHERE NOT valuemapid IN (SELECT valuemapid FROM valuemaps);
 +
 +
-- Delete orphaned media items
 +
DELETE FROM media WHERE NOT userid IN (SELECT userid FROM users);
 +
DELETE FROM media WHERE NOT mediatypeid IN (SELECT mediatypeid FROM media_type);
 +
DELETE FROM rights WHERE NOT groupid IN (SELECT usrgrpid FROM usrgrp);
 +
DELETE FROM rights WHERE NOT id IN (SELECT groupid FROM groups);
 +
DELETE FROM sessions WHERE NOT userid IN (SELECT userid FROM users);
 +
 +
-- Delete orphaned screens
 +
DELETE FROM screens_items WHERE screenid NOT IN (SELECT screenid FROM screens);
 +
 +
-- Delete orphaned events & triggers
 +
DELETE FROM trigger_depends WHERE triggerid_down NOT IN (SELECT triggerid FROM triggers);
 +
DELETE FROM trigger_depends WHERE triggerid_up NOT IN (SELECT triggerid FROM triggers);
 +
 +
-- Delete records in the history/trends table for items that no longer exist
 +
DELETE FROM history WHERE NOT EXISTS (SELECT 1 FROM items WHERE history.itemid = items.itemid);
 +
DELETE FROM history_uint WHERE NOT EXISTS (SELECT 1 FROM items WHERE history_uint.itemid = items.itemid);
 +
DELETE FROM history_log WHERE NOT EXISTS (SELECT 1 FROM items WHERE history_log.itemid = items.itemid);
 +
DELETE FROM history_str WHERE NOT EXISTS (SELECT 1 FROM items WHERE history_str.itemid = items.itemid);
 +
DELETE FROM history_text WHERE NOT EXISTS (SELECT 1 FROM items WHERE history_text.itemid = items.itemid);
 +
 +
DELETE FROM trends WHERE NOT EXISTS (SELECT 1 FROM items WHERE trends.itemid = items.itemid);
 +
DELETE FROM trends_uint WHERE NOT EXISTS (SELECT 1 FROM items WHERE trends_uint.itemid = items.itemid);
 +
 +
-- Delete records in the events table for triggers/items that no longer exist
 +
DELETE FROM events WHERE source = 0 AND object = 0 AND objectid NOT IN (SELECT triggerid FROM triggers);
 +
DELETE FROM events WHERE source = 3 AND object = 0 AND objectid NOT IN (SELECT triggerid FROM triggers);
 +
DELETE FROM events WHERE source = 3 AND object = 4 AND objectid NOT IN (SELECT itemid FROM items);
 +
 +
-- Delete all orphaned acknowledge entries
 +
DELETE FROM acknowledges WHERE NOT eventid IN (SELECT eventid FROM events);
 +
DELETE FROM acknowledges WHERE NOT userid IN (SELECT userid FROM users);
 +
DELETE FROM acknowledges WHERE eventid IN (SELECT eventid FROM events WHERE (source = 0 OR source=3) AND object = 0 AND objectid NOT IN (SELECT triggerid FROM triggers));
 +
DELETE FROM acknowledges WHERE eventid IN (SELECT eventid FROM events WHERE source=3 AND object = 4 AND objectid NOT IN (SELECT itemid FROM items));
 +
 +
```
 +
 +
 +
NOTES
 +
 +
- https://www.zabbix.com/forum/zabbix-for-large-environments/425296-removing-old-data-postgresql
 +
  - psotgres housekeeper might not work
 +
 +
 +
 +
https://www.zabbix.com/forum/zabbix-troubleshooting-and-problems/373410-zabbix-housekeeper-processes-more-than-75-busy
 +
```
 +
#!/bin/sh
 +
( flock -n 9 || exit 1
 +
psql -U postgres zabbix -c 'DELETE FROM trends_uint t WHERE ctid IN ( SELECT t.ctid FROM trends_uint t LEFT JOIN items i ON i.itemid = t.itemid WHERE to_timestamp(t.clock) < (current_date - ((i.trends)::interval)) LIMIT 10000);'
 +
psql -U postgres zabbix -c 'DELETE FROM history_str h WHERE ctid IN ( SELECT h.ctid FROM history_str h LEFT JOIN items i ON i.itemid = h.itemid WHERE to_timestamp(h.clock) < (current_date - ((i.history)::interval)) LIMIT 10000);'
 +
psql -U postgres zabbix -c 'DELETE FROM history_text h WHERE ctid IN ( SELECT h.ctid FROM history_text h LEFT JOIN items i ON i.itemid = h.itemid WHERE to_timestamp(h.clock) < (current_date - ((i.history)::interval)) LIMIT 100000);'
 +
psql -U postgres zabbix -c 'DELETE FROM history h WHERE ctid IN ( SELECT h.ctid FROM history h LEFT JOIN items i ON i.itemid = h.itemid WHERE to_timestamp(h.clock) < (current_date - ((i.history)::interval)) LIMIT 300000);'
 +
psql -U postgres zabbix -c 'DELETE FROM history_uint h WHERE ctid IN ( SELECT h.ctid FROM history_uint h LEFT JOIN items i ON i.itemid = h.itemid WHERE to_timestamp(h.clock) < (current_date - ((i.history)::interval)) LIMIT 500000);'
 +
) 9>/var/lock/00-zabbix-history-purge.lock
 +
```
 +
 +
 +
# Drop and recreate all large tables and vacuum reindex
 +
```
 +
DROP TABLE public.history_str;
 +
DROP TABLE public.history;
 +
DROP TABLE public.history_uint;
 +
DROP TABLE public.history_text;
 +
DROP TABLE public.history_log;
 +
 +
 +
 +
--
 +
-- Name: history_str; Type: TABLE; Schema: public; Owner: zabbix
 +
--
 +
 +
CREATE TABLE public.history_str (
 +
    itemid bigint NOT NULL,
 +
    clock integer DEFAULT 0 NOT NULL,
 +
    value character varying(255) DEFAULT ''::character varying NOT NULL,
 +
    ns integer DEFAULT 0 NOT NULL
 +
);
 +
 +
 +
ALTER TABLE public.history_str OWNER TO zabbix;
 +
 +
--
 +
-- Name: history; Type: TABLE; Schema: public; Owner: zabbix
 +
--
 +
 +
CREATE TABLE public.history (
 +
    itemid bigint NOT NULL,
 +
    clock integer DEFAULT 0 NOT NULL,
 +
    value numeric(16,4) DEFAULT 0.0000 NOT NULL,
 +
    ns integer DEFAULT 0 NOT NULL
 +
);
 +
 +
 +
ALTER TABLE public.history OWNER TO zabbix;
 +
 +
--
 +
-- Name: history_log; Type: TABLE; Schema: public; Owner: zabbix
 +
--
 +
 +
CREATE TABLE public.history_log (
 +
    itemid bigint NOT NULL,
 +
    clock integer DEFAULT 0 NOT NULL,
 +
    "timestamp" integer DEFAULT 0 NOT NULL,
 +
    source character varying(64) DEFAULT ''::character varying NOT NULL,
 +
    severity integer DEFAULT 0 NOT NULL,
 +
    value text DEFAULT ''::text NOT NULL,
 +
    logeventid integer DEFAULT 0 NOT NULL,
 +
    ns integer DEFAULT 0 NOT NULL
 +
);
 +
 +
 +
ALTER TABLE public.history_log OWNER TO zabbix;
 +
 +
 +
--
 +
-- Name: history_text; Type: TABLE; Schema: public; Owner: zabbix
 +
--
 +
 +
CREATE TABLE public.history_text (
 +
    itemid bigint NOT NULL,
 +
    clock integer DEFAULT 0 NOT NULL,
 +
    value text DEFAULT ''::text NOT NULL,
 +
    ns integer DEFAULT 0 NOT NULL
 +
);
 +
 +
 +
ALTER TABLE public.history_text OWNER TO zabbix;
 +
 +
--
 +
-- Name: history_uint; Type: TABLE; Schema: public; Owner: zabbix
 +
--
 +
 +
CREATE TABLE public.history_uint (
 +
    itemid bigint NOT NULL,
 +
    clock integer DEFAULT 0 NOT NULL,
 +
    value numeric(20,0) DEFAULT '0'::numeric NOT NULL,
 +
    ns integer DEFAULT 0 NOT NULL
 +
);
 +
 +
 +
ALTER TABLE public.history_uint OWNER TO zabbix;
 +
 +
 +
-- Name: history_1; Type: INDEX; Schema: public; Owner: zabbix
 +
CREATE INDEX history_1 ON public.history USING btree (itemid, clock);
 +
-- Name: history_log_1; Type: INDEX; Schema: public; Owner: zabbix
 +
CREATE INDEX history_log_1 ON public.history_log USING btree (itemid, clock);
 +
-- Name: history_str_1; Type: INDEX; Schema: public; Owner: zabbix
 +
CREATE INDEX history_str_1 ON public.history_str USING btree (itemid, clock);
 +
-- Name: history_text_1; Type: INDEX; Schema: public; Owner: zabbix
 +
CREATE INDEX history_text_1 ON public.history_text USING btree (itemid, clock);
 +
-- Name: history_uint_1; Type: INDEX; Schema: public; Owner: zabbix
 +
CREATE INDEX history_uint_1 ON public.history_uint USING btree (itemid, clock);
 +
 +
VACUUM FULL VERBOSE;
 +
REINDEX DATABASE zabbix;
 +
```
 +
 +
Drop readd trends
 +
```
 +
DROP TABLE trends;
 +
DROP TABLE trends_uint;
 +
 +
CREATE TABLE public.trends (
 +
    itemid bigint NOT NULL,
 +
    clock integer DEFAULT 0 NOT NULL,
 +
    num integer DEFAULT 0 NOT NULL,
 +
    value_min double precision DEFAULT '0'::double precision NOT NULL,
 +
    value_avg double precision DEFAULT '0'::double precision NOT NULL,
 +
    value_max double precision DEFAULT '0'::double precision NOT NULL
 +
);
 +
 +
ALTER TABLE public.trends OWNER TO zabbix;
 +
 +
CREATE TABLE public.trends_uint (
 +
    itemid bigint NOT NULL,
 +
    clock integer DEFAULT 0 NOT NULL,
 +
    num integer DEFAULT 0 NOT NULL,
 +
    value_min numeric(20,0) DEFAULT '0'::numeric NOT NULL,
 +
    value_avg numeric(20,0) DEFAULT '0'::numeric NOT NULL,
 +
    value_max numeric(20,0) DEFAULT '0'::numeric NOT NULL
 +
);
 +
 +
ALTER TABLE public.trends_uint OWNER TO zabbix;
 +
 +
ALTER TABLE ONLY public.trends
 +
    ADD CONSTRAINT trends_pkey PRIMARY KEY (itemid, clock);
 +
 +
 +
ALTER TABLE ONLY public.trends_uint
 +
    ADD CONSTRAINT trends_uint_pkey PRIMARY KEY (itemid, clock);
 +
 +
```
 +
 +
Run
 +
```
 +
psql zabbix < drop-create-vacuum-reindex-zabbix.sql
 +
```
 +
 +
 +
Some stats
 +
```
 +
select * from pg_stat_progress_create_index;
 +
select * from pg_stat_progress_vacuum;
 +
```
 +
- https://www.postgresql.org/docs/current/progress-reporting.html

Latest revision as of 17:06, 3 February 2023

Disable autovacuum on large tables 

ALTER TABLE history SET (autovacuum_enabled = false);
ALTER TABLE history_uint SET (autovacuum_enabled = false);
ALTER TABLE history_str SET (autovacuum_enabled = false);
ALTER TABLE history_text SET (autovacuum_enabled = false);
ALTER TABLE history_log SET (autovacuum_enabled = false);

Checks 

SELECT relname, n_dead_tup FROM pg_stat_user_tables WHERE relname like 'history%';

SELECT
       relname AS "table_name",
       pg_size_pretty(pg_table_size(C.oid)) AS "table_size"
FROM
       pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname !~ '^pg_toast' AND relkind IN ('r')
ORDER BY pg_table_size(C.oid)
DESC LIMIT 1;

SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables;

SELECT reloptions FROM pg_class WHERE relname='my_table';


select * from pg_stat_progress_vacuum;

SELECT pg_size_pretty( pg_total_relation_size('history_uint') );

SELECT *
FROM pg_settings 
WHERE name LIKE 'autovacuum%';

Script

.env 

export PGPASSWORD=foo
export PGUSER=admin
export PGDATABASE=demodb
export PGHOST=postgres1.postgres-prod.svc.cluster.local

run-cleanup.sh 

. .env
nohup ./vacuum-reindex.sh &

vacuum-reindex.sh 

#!/bin/bash
set -eu

db=zabbix

# tables=( "history_uint" "history" "history_text" "history_str" "history_log" "trends" "trends_uint" )
tables=( "history_uint" "history" "history_text" "history_str" "history_log" )


timestamp() {
  #date +"%T"
  ts=$(date "+%Y%m%d-%H%M%S")
  echo $ts
}


delete_table_records(){
  table=$1
  echo I: Starting delete of table $table records.
  psql ${db} -c "DELETE FROM ${table}"
  echo I: Ending delete of table $table records.
}


delete(){
  table=$1
  days_before=$2
  echo I: Starting delete of table $table items before ${days_before} in past.
  timestamp
  echo "DELETE FROM ${table} WHERE clock < extract(epoch from (SELECT NOW() - INTERVAL '${days_before} DAY'))"
  time psql ${db} -c "DELETE FROM ${table} WHERE clock < extract(epoch from (SELECT NOW() - INTERVAL '${days_before} DAY'))"
  timestamp
  echo I: Ending delete of table $table before ${days_before} in past.
}


vacuum(){
  table=$1
  echo I: Starting vacuum of table $table.
  timestamp
  # psql ${db} -c "VACUUM(ANALYZE, VERBOSE) ${table}"
  # psql ${db} -c "VACUUM(FULL, VERBOSE) ${table}"
  time psql ${db} -c "VACUUM(ANALYZE, VERBOSE) ${table}"
  timestamp
  echo I: Ending vacuum of table $table.
}


reindex(){
  table=$1
  echo I: Starting reindex concurrently of table $table.
  timestamp
  time psql ${db} -c "REINDEX TABLE CONCURRENTLY ${table}"
  timestamp
  echo I: Ending reindex concurrently of table $table.
}


vaccum_full_reindexmain(){
  time psql ${db} -c "VACUUM FULL VERBOSE"
  time psql ${db} -c "REINDEX VERBOSE DATABASE ${db}"
}


vaccum_reindex_db(){
  echo start
  # timestamp
  psql ${db} -c "VACUUM VERBOSE"
  # psql ${db} -c "REINDEX CONCURRENTLY VERBOSE DATABASE ${db}"
}

remove_disabled_item_history(){

        declare -a queries=(
                "delete from history where itemid not in (select itemid from items where status='0');"
                "delete from history where itemid not in (select itemid from items where status='0');"
                "delete from history_uint where itemid not in (select itemid from items where status='0');"
                "delete from history_str where itemid not in (select itemid from items where status='0');"
                "delete from history_text where itemid not in (select itemid from items where status='0');"
                "delete from history_log where itemid not in (select itemid from items where status='0');"
                "delete from trends where itemid not in (select itemid from items where status='0');"
                "delete from trends_uint where itemid not in (select itemid from items where status='0');"
        )

        for query in "${queries[@]}"
        do
                 echo "$query"
     time psql ${db} -c "$query"
        done

}


main(){
  # remove_disabled_item_history
  echo Start table optimize.
  timestamp
  for table in "${tables[@]}"; do
    echo $table
    delete $table 14
    vacuum $table
    # reindex $table
    # delete_table_records $table
  done
  timestamp
  echo End table optimize.
}


main
# vaccum_reindex_db

Other Stuff/Notes

DELETE FROM history_uint WHERE clock < extract(epoch from (SELECT NOW() - INTERVAL '35 DAY'));
DELETE FROM history WHERE clock < extract(epoch from (SELECT NOW() - INTERVAL '35 DAY'));

DELETE FROM history_str WHERE clock < extract(epoch from (SELECT NOW() - INTERVAL '35 DAY'));
DELETE FROM history_text WHERE clock < extract(epoch from (SELECT NOW() - INTERVAL '35 DAY'));
DELETE FROM history_log WHERE clock < extract(epoch from (SELECT NOW() - INTERVAL '35 DAY'));

other options
trends_uint
trends
events
alerts

orphans 

select count(*) from item_tag where itemid not in (select itemid from items);
delete from item_tag where itemid not in (select itemid from items);

https://github.com/azmelanar/zabbix-database-cleanup/blob/master/orphaned/clean.sql

https://github.com/burner1024/zabbix-sql

https://www.zabbix.com/documentation/2.2/manual/api/reference/item/object 

Status of the item.

Possible values:
0 - (default) enabled item;
1 - disabled item.

delete from history where itemid not in (select itemid from items where status='0');
delete from history_uint where itemid not in (select itemid from items where status='0');
delete from history_str where itemid not in (select itemid from items where status='0');
delete from history_text where itemid not in (select itemid from items where status='0');
delete from history_log where itemid not in (select itemid from items where status='0');

delete from trends where itemid not in (select itemid from items where status='0');
delete from trends_uint where itemid not in (select itemid from items where status='0');

DELETE FROM auditlog WHERE NOT userid IN (SELECT userid FROM users);

-- Delete orphaned conditions 
DELETE FROM conditions WHERE NOT actionid IN (SELECT actionid FROM actions);

-- Delete orphaned functions 
DELETE FROM functions WHERE NOT itemid IN (SELECT itemid FROM items);
DELETE FROM functions WHERE NOT triggerid IN (SELECT triggerid FROM triggers);

-- Delete orphaned graph items 
DELETE FROM graphs_items WHERE NOT graphid IN (SELECT graphid FROM graphs);
DELETE FROM graphs_items WHERE NOT itemid IN (SELECT itemid FROM items);

-- Delete orphaed host_profiles 
DELETE FROM hosts_profiles WHERE NOT hostid IN (SELECT hostid FROM hosts);
DELETE FROM hosts_profiles_ext WHERE NOT hostid IN (SELECT hostid FROM hosts);

-- Delete orphaned host macro's 
DELETE FROM hostmacro WHERE NOT hostid IN (SELECT hostid FROM hosts);

-- Delete orphaned item data 
DELETE FROM items WHERE hostid NOT IN (SELECT hostid FROM hosts);
DELETE FROM items_applications WHERE applicationid NOT IN (SELECT applicationid FROM applications);
DELETE FROM items_applications WHERE itemid NOT IN (SELECT itemid FROM items);

-- Delete orphaned HTTP check data 
DELETE FROM httpstep WHERE NOT httptestid IN (SELECT httptestid FROM httptest);
DELETE FROM httpstepitem WHERE NOT httpstepid IN (SELECT httpstepid FROM httpstep);
DELETE FROM httpstepitem WHERE NOT itemid IN (SELECT itemid FROM items);
DELETE FROM httptest WHERE applicationid NOT IN (SELECT applicationid FROM applications);

-- Delete orphaned maintenance data 
DELETE FROM maintenances_groups WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
DELETE FROM maintenances_groups WHERE groupid NOT IN (SELECT groupid FROM groups);
DELETE FROM maintenances_hosts WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
DELETE FROM maintenances_hosts WHERE hostid NOT IN (SELECT hostid FROM hosts);
DELETE FROM maintenances_windows WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
DELETE FROM maintenances_windows WHERE timeperiodid NOT IN (SELECT timeperiodid FROM timeperiods);

-- Delete orphaned mappings 
DELETE FROM mappings WHERE NOT valuemapid IN (SELECT valuemapid FROM valuemaps);

-- Delete orphaned media / user items 
DELETE FROM media WHERE NOT userid IN (SELECT userid FROM users);
DELETE FROM media WHERE NOT mediatypeid IN (SELECT mediatypeid FROM media_type);
DELETE FROM rights WHERE NOT groupid IN (SELECT usrgrpid FROM usrgrp);
DELETE FROM rights WHERE NOT id IN (SELECT groupid FROM groups);
DELETE FROM sessions WHERE NOT userid IN (SELECT userid FROM users);
DELETE FROM user_history WHERE NOT userid IN (SELECT userid FROM users);

-- Screens 
DELETE FROM screens_items WHERE screenid NOT IN (SELECT screenid FROM screens);

-- Events & triggers 
DELETE FROM trigger_depends WHERE triggerid_down NOT IN (SELECT triggerid FROM triggers);
DELETE FROM trigger_depends WHERE triggerid_up NOT IN (SELECT triggerid FROM triggers);

-- Count the amount of records in the history/trends table for items that no longer exist
DELETE FROM history WHERE itemid NOT IN (SELECT itemid FROM items);
DELETE FROM history_uint WHERE itemid NOT IN (SELECT itemid FROM items);
DELETE FROM history_log WHERE itemid NOT IN (SELECT itemid FROM items);
DELETE FROM history_str WHERE itemid NOT IN (SELECT itemid FROM items);
DELETE FROM history_sync WHERE itemid NOT IN (SELECT itemid FROM items);
DELETE FROM history_text WHERE itemid NOT IN (SELECT itemid FROM items);

DELETE FROM trends WHERE itemid NOT IN (SELECT itemid FROM items);
DELETE FROM trends_uint WHERE itemid NOT IN (SELECT itemid FROM items);

MYSQL 

DELETE FROM history_uint WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 35 DAY));
DELETE FROM history WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 35 DAY));
DELETE FROM trends_uint WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 35 DAY));
DELETE FROM trends WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 35 DAY));
DELETE FROM events WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 35 DAY));
DELETE FROM alerts WHERE clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 35 DAY));

Option 2

-- Delete orphaned alerts entries
DELETE FROM alerts WHERE NOT EXISTS (SELECT 1 FROM actions WHERE alerts.actionid = actions.actionid);
DELETE FROM alerts WHERE NOT EXISTS (SELECT 1 FROM events WHERE alerts.eventid = events.eventid);
DELETE FROM alerts WHERE NOT userid IN (SELECT userid FROM users);
DELETE FROM alerts WHERE NOT mediatypeid IN (SELECT mediatypeid FROM media_type);

-- Delete orphaned application entries that no longer map back to a host
DELETE FROM applications WHERE NOT hostid IN (SELECT hostid FROM hosts);

-- Delete orphaned auditlog details (such as logins)
DELETE FROM auditlog_details WHERE NOT auditid IN (SELECT auditid FROM auditlog);
DELETE FROM auditlog WHERE NOT userid IN (SELECT userid FROM users);

-- Delete orphaned conditions
DELETE FROM conditions WHERE NOT actionid IN (SELECT actionid FROM actions);

-- Delete orphaned functions
DELETE FROM functions WHERE NOT itemid IN (SELECT itemid FROM items);
DELETE FROM functions WHERE NOT triggerid IN (SELECT triggerid FROM triggers);

-- Delete orphaned graph items
DELETE FROM graphs_items WHERE NOT graphid IN (SELECT graphid FROM graphs);
DELETE FROM graphs_items WHERE NOT itemid IN (SELECT itemid FROM items);

-- Delete orphaned host macro's
DELETE FROM hostmacro WHERE NOT hostid IN (SELECT hostid FROM hosts);

-- Delete orphaned item data
DELETE FROM items WHERE hostid NOT IN (SELECT hostid FROM hosts);
DELETE FROM items_applications WHERE applicationid NOT IN (SELECT applicationid FROM applications);
DELETE FROM items_applications WHERE itemid NOT IN (SELECT itemid FROM items);

-- Delete orphaned HTTP check data
DELETE FROM httpstep WHERE NOT httptestid IN (SELECT httptestid FROM httptest);
DELETE FROM httpstepitem WHERE NOT httpstepid IN (SELECT httpstepid FROM httpstep);
DELETE FROM httpstepitem WHERE NOT itemid IN (SELECT itemid FROM items);
DELETE FROM httptest WHERE applicationid NOT IN (SELECT applicationid FROM applications);

-- Delete orphaned maintenance data
DELETE FROM maintenances_groups WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
DELETE FROM maintenances_groups WHERE groupid NOT IN (SELECT groupid FROM groups);
DELETE FROM maintenances_hosts WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
DELETE FROM maintenances_hosts WHERE hostid NOT IN (SELECT hostid FROM hosts);
DELETE FROM maintenances_windows WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
DELETE FROM maintenances_windows WHERE timeperiodid NOT IN (SELECT timeperiodid FROM timeperiods);

-- Delete orphaned mappings
DELETE FROM mappings WHERE NOT valuemapid IN (SELECT valuemapid FROM valuemaps);

-- Delete orphaned media items
DELETE FROM media WHERE NOT userid IN (SELECT userid FROM users);
DELETE FROM media WHERE NOT mediatypeid IN (SELECT mediatypeid FROM media_type);
DELETE FROM rights WHERE NOT groupid IN (SELECT usrgrpid FROM usrgrp);
DELETE FROM rights WHERE NOT id IN (SELECT groupid FROM groups);
DELETE FROM sessions WHERE NOT userid IN (SELECT userid FROM users);

-- Delete orphaned screens
DELETE FROM screens_items WHERE screenid NOT IN (SELECT screenid FROM screens);

-- Delete orphaned events & triggers
DELETE FROM trigger_depends WHERE triggerid_down NOT IN (SELECT triggerid FROM triggers);
DELETE FROM trigger_depends WHERE triggerid_up NOT IN (SELECT triggerid FROM triggers);

-- Delete records in the history/trends table for items that no longer exist
DELETE FROM history WHERE NOT EXISTS (SELECT 1 FROM items WHERE history.itemid = items.itemid);
DELETE FROM history_uint WHERE NOT EXISTS (SELECT 1 FROM items WHERE history_uint.itemid = items.itemid);
DELETE FROM history_log WHERE NOT EXISTS (SELECT 1 FROM items WHERE history_log.itemid = items.itemid);
DELETE FROM history_str WHERE NOT EXISTS (SELECT 1 FROM items WHERE history_str.itemid = items.itemid);
DELETE FROM history_text WHERE NOT EXISTS (SELECT 1 FROM items WHERE history_text.itemid = items.itemid);

DELETE FROM trends WHERE NOT EXISTS (SELECT 1 FROM items WHERE trends.itemid = items.itemid);
DELETE FROM trends_uint WHERE NOT EXISTS (SELECT 1 FROM items WHERE trends_uint.itemid = items.itemid);

-- Delete records in the events table for triggers/items that no longer exist
DELETE FROM events WHERE source = 0 AND object = 0 AND objectid NOT IN (SELECT triggerid FROM triggers);
DELETE FROM events WHERE source = 3 AND object = 0 AND objectid NOT IN (SELECT triggerid FROM triggers);
DELETE FROM events WHERE source = 3 AND object = 4 AND objectid NOT IN (SELECT itemid FROM items);

-- Delete all orphaned acknowledge entries
DELETE FROM acknowledges WHERE NOT eventid IN (SELECT eventid FROM events);
DELETE FROM acknowledges WHERE NOT userid IN (SELECT userid FROM users);
DELETE FROM acknowledges WHERE eventid IN (SELECT eventid FROM events WHERE (source = 0 OR source=3) AND object = 0 AND objectid NOT IN (SELECT triggerid FROM triggers));
DELETE FROM acknowledges WHERE eventid IN (SELECT eventid FROM events WHERE source=3 AND object = 4 AND objectid NOT IN (SELECT itemid FROM items));

NOTES

https://www.zabbix.com/forum/zabbix-troubleshooting-and-problems/373410-zabbix-housekeeper-processes-more-than-75-busy 

#!/bin/sh
( flock -n 9 || exit 1
psql -U postgres zabbix -c 'DELETE FROM trends_uint t WHERE ctid IN ( SELECT t.ctid FROM trends_uint t LEFT JOIN items i ON i.itemid = t.itemid WHERE to_timestamp(t.clock) < (current_date - ((i.trends)::interval)) LIMIT 10000);'
psql -U postgres zabbix -c 'DELETE FROM history_str h WHERE ctid IN ( SELECT h.ctid FROM history_str h LEFT JOIN items i ON i.itemid = h.itemid WHERE to_timestamp(h.clock) < (current_date - ((i.history)::interval)) LIMIT 10000);'
psql -U postgres zabbix -c 'DELETE FROM history_text h WHERE ctid IN ( SELECT h.ctid FROM history_text h LEFT JOIN items i ON i.itemid = h.itemid WHERE to_timestamp(h.clock) < (current_date - ((i.history)::interval)) LIMIT 100000);'
psql -U postgres zabbix -c 'DELETE FROM history h WHERE ctid IN ( SELECT h.ctid FROM history h LEFT JOIN items i ON i.itemid = h.itemid WHERE to_timestamp(h.clock) < (current_date - ((i.history)::interval)) LIMIT 300000);'
psql -U postgres zabbix -c 'DELETE FROM history_uint h WHERE ctid IN ( SELECT h.ctid FROM history_uint h LEFT JOIN items i ON i.itemid = h.itemid WHERE to_timestamp(h.clock) < (current_date - ((i.history)::interval)) LIMIT 500000);'
) 9>/var/lock/00-zabbix-history-purge.lock

Drop and recreate all large tables and vacuum reindex

DROP TABLE public.history_str;
DROP TABLE public.history;
DROP TABLE public.history_uint;
DROP TABLE public.history_text;
DROP TABLE public.history_log;



--
-- Name: history_str; Type: TABLE; Schema: public; Owner: zabbix
--

CREATE TABLE public.history_str (
    itemid bigint NOT NULL,
    clock integer DEFAULT 0 NOT NULL,
    value character varying(255) DEFAULT ''::character varying NOT NULL,
    ns integer DEFAULT 0 NOT NULL
);


ALTER TABLE public.history_str OWNER TO zabbix;

--
-- Name: history; Type: TABLE; Schema: public; Owner: zabbix
--

CREATE TABLE public.history (
    itemid bigint NOT NULL,
    clock integer DEFAULT 0 NOT NULL,
    value numeric(16,4) DEFAULT 0.0000 NOT NULL,
    ns integer DEFAULT 0 NOT NULL
);


ALTER TABLE public.history OWNER TO zabbix;

--
-- Name: history_log; Type: TABLE; Schema: public; Owner: zabbix
--

CREATE TABLE public.history_log (
    itemid bigint NOT NULL,
    clock integer DEFAULT 0 NOT NULL,
    "timestamp" integer DEFAULT 0 NOT NULL,
    source character varying(64) DEFAULT ''::character varying NOT NULL,
    severity integer DEFAULT 0 NOT NULL,
    value text DEFAULT ''::text NOT NULL,
    logeventid integer DEFAULT 0 NOT NULL,
    ns integer DEFAULT 0 NOT NULL
);


ALTER TABLE public.history_log OWNER TO zabbix;


--
-- Name: history_text; Type: TABLE; Schema: public; Owner: zabbix
--

CREATE TABLE public.history_text (
    itemid bigint NOT NULL,
    clock integer DEFAULT 0 NOT NULL,
    value text DEFAULT ''::text NOT NULL,
    ns integer DEFAULT 0 NOT NULL
);


ALTER TABLE public.history_text OWNER TO zabbix;

--
-- Name: history_uint; Type: TABLE; Schema: public; Owner: zabbix
--

CREATE TABLE public.history_uint (
    itemid bigint NOT NULL,
    clock integer DEFAULT 0 NOT NULL,
    value numeric(20,0) DEFAULT '0'::numeric NOT NULL,
    ns integer DEFAULT 0 NOT NULL
);


ALTER TABLE public.history_uint OWNER TO zabbix;


-- Name: history_1; Type: INDEX; Schema: public; Owner: zabbix
CREATE INDEX history_1 ON public.history USING btree (itemid, clock);
-- Name: history_log_1; Type: INDEX; Schema: public; Owner: zabbix
CREATE INDEX history_log_1 ON public.history_log USING btree (itemid, clock);
-- Name: history_str_1; Type: INDEX; Schema: public; Owner: zabbix
CREATE INDEX history_str_1 ON public.history_str USING btree (itemid, clock);
-- Name: history_text_1; Type: INDEX; Schema: public; Owner: zabbix
CREATE INDEX history_text_1 ON public.history_text USING btree (itemid, clock);
-- Name: history_uint_1; Type: INDEX; Schema: public; Owner: zabbix
CREATE INDEX history_uint_1 ON public.history_uint USING btree (itemid, clock);

VACUUM FULL VERBOSE;
REINDEX DATABASE zabbix;

Drop readd trends 

DROP TABLE trends;
DROP TABLE trends_uint;

CREATE TABLE public.trends (
    itemid bigint NOT NULL,
    clock integer DEFAULT 0 NOT NULL,
    num integer DEFAULT 0 NOT NULL,
    value_min double precision DEFAULT '0'::double precision NOT NULL,
    value_avg double precision DEFAULT '0'::double precision NOT NULL,
    value_max double precision DEFAULT '0'::double precision NOT NULL
);

ALTER TABLE public.trends OWNER TO zabbix;

CREATE TABLE public.trends_uint (
    itemid bigint NOT NULL,
    clock integer DEFAULT 0 NOT NULL,
    num integer DEFAULT 0 NOT NULL,
    value_min numeric(20,0) DEFAULT '0'::numeric NOT NULL,
    value_avg numeric(20,0) DEFAULT '0'::numeric NOT NULL,
    value_max numeric(20,0) DEFAULT '0'::numeric NOT NULL
);

ALTER TABLE public.trends_uint OWNER TO zabbix;

ALTER TABLE ONLY public.trends
    ADD CONSTRAINT trends_pkey PRIMARY KEY (itemid, clock);


ALTER TABLE ONLY public.trends_uint
    ADD CONSTRAINT trends_uint_pkey PRIMARY KEY (itemid, clock);

Run 

psql zabbix < drop-create-vacuum-reindex-zabbix.sql

Some stats 

select * from pg_stat_progress_create_index;
select * from pg_stat_progress_vacuum;
Retrieved from "https://tech.uvoo.io/index.php?title=Zabbix_Cleanup&oldid=3759"