Skip to content

Instantly share code, notes, and snippets.

@mkhon
Last active September 9, 2022 08:30
Show Gist options
  • Save mkhon/7b840c4fbae23e7cf1acb1738657664e to your computer and use it in GitHub Desktop.
Save mkhon/7b840c4fbae23e7cf1acb1738657664e to your computer and use it in GitHub Desktop.
Force zabbix housekeeping with batched DELETE. Sample use: PGPASSWORD=password ./zabbix_housekeeping.sh history history_str history_text history_log history_uint trends trends_uint"
#!/bin/sh
psql="psql -h localhost -U zabbix zabbix"
interval="90 days"
limit=1000000
housekeeping()
{
t="$1"
echo "--> $t"
while :; do
t1=`date +%s`
result=`cat << EOF | $psql 2>&1
--
-- Generic statement
--
-- WITH rows AS
-- (SELECT itemid, clock FROM $t WHERE age(to_timestamp(clock)) > INTERVAL '$interval' LIMIT $limit)
-- DELETE FROM $t h WHERE EXISTS (SELECT 1 FROM rows WHERE rows.itemid = h.itemid AND rows.clock = h.clock)
--
-- PostgreSQL-optimized query that does not use an index to do DELETE
-- FOR UPDATE SKIP LOCKED is mandatory so that ctid does not change while in transaction
--
WITH rows AS
(SELECT ctid FROM $t WHERE age(to_timestamp(clock)) > INTERVAL '$interval' LIMIT $limit FOR UPDATE SKIP LOCKED)
DELETE FROM $t WHERE ctid IN (TABLE rows)
EOF`
t2=`date +%s`
case "$result" in
DELETE*)
num=${result#DELETE }
echo "Deleted $num records in $(($t2 - $t1)) sec"
if [ "$num" -lt "$limit" ]; then
break
fi
;;
*)
echo $result
break
;;
esac
done
}
for t in $*; do
housekeeping $t
done
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment