Difference between revisions of "Zabbix Cleanup"
Jump to navigation
Jump to search
| Line 414: | Line 414: | ||
# Drop and recreate all large tables and vacuum reindex | # Drop and recreate all large tables and vacuum reindex | ||
``` | ``` | ||
| − | DROP TABLE public.history_str | + | DROP TABLE public.history_str; |
| − | DROP TABLE public.history | + | DROP TABLE public.history; |
| − | DROP TABLE public.history_uint | + | DROP TABLE public.history_uint; |
| − | DROP TABLE public.history_text | + | DROP TABLE public.history_text; |
| − | DROP TABLE public.history_log | + | DROP TABLE public.history_log; |
Revision as of 16:51, 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%';
- 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/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-for-large-environments/425296-removing-old-data-postgresql
- psotgres housekeeper might not work
#!/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;
Run
psql zabbix < drop-create-vacuum-reindex-zabbix.sql
Some stats
select * from pg_stat_progress_create_index; select * from pg_stat_progress_vacuum;