su postgres
psql -d zabbix
-- Количество данных с разбивкой по хостам / метрикам / таблицам хранения
(SELECT hosts.name as "Hostname", items.name as "Item", COUNT(*) as "CNT", 'history' as "Table"
FROM history, items, hosts
WHERE items.itemid=history.itemid AND hosts.hostid=items.hostid
GROUP BY hosts.name, items.name
UNION ALL
SELECT hosts.name as "Hostname", items.name as "Item", COUNT(*) as "CNT", 'history_log' as "Table"
FROM history_log, items, hosts
WHERE items.itemid=history_log.itemid AND hosts.hostid=items.hostid
GROUP BY hosts.name, items.name
UNION ALL
SELECT hosts.name as "Hostname", items.name as "Item", COUNT(*) as "CNT", 'history_str' as "Table"
FROM history_str, items, hosts
WHERE items.itemid=history_str.itemid AND hosts.hostid=items.hostid
GROUP BY hosts.name, items.name
UNION ALL
SELECT hosts.name as "Hostname", items.name as "Item", COUNT(*) as "CNT", 'history_text' as "Table"
FROM history_text, items, hosts
WHERE items.itemid=history_text.itemid AND hosts.hostid=items.hostid
GROUP BY hosts.name, items.name
UNION ALL
SELECT hosts.name as "Hostname", items.name as "Item", COUNT(*) as "CNT", 'history_uint' as "Table"
FROM history_uint, items, hosts
WHERE items.itemid=history_uint.itemid AND hosts.hostid=items.hostid
GROUP BY hosts.name, items.name
UNION ALL
SELECT hosts.name as "Hostname", items.name as "Item", COUNT(*) as "CNT", 'trends' as "Table"
FROM trends, items, hosts
WHERE items.itemid=trends.itemid AND hosts.hostid=items.hostid
GROUP BY hosts.name, items.name
UNION ALL
SELECT hosts.name as "Hostname", items.name as "Item", COUNT(*) as "CNT", 'trends_uint' as "Table"
FROM trends_uint, items, hosts
WHERE items.itemid=trends_uint.itemid AND hosts.hostid=items.hostid
GROUP BY hosts.name, items.name)
ORDER BY "CNT" DESC
LIMIT 1000;
-- Количество данных с разбивкой по таблицам (по типам хранимых данных)
(SELECT 'history' AS "Table", COUNT(*) AS "CNT" FROM history
UNION ALL
SELECT 'history_uint' AS "Table", COUNT(*) AS "CNT" FROM history_uint
UNION ALL
SELECT 'history_str' AS "Table", COUNT(*) AS "CNT" FROM history_str
UNION ALL
SELECT 'history_log' AS "Table", COUNT(*) AS "CNT" FROM history_log
UNION ALL
SELECT 'history_text' AS "Table", COUNT(*) AS "CNT" FROM history_text
UNION ALL
SELECT 'trends' AS "Table", COUNT(*) AS "CNT" FROM trends
UNION ALL
SELECT 'trends_uint' AS "Table", COUNT(*) AS "CNT" FROM trends_uint)
ORDER BY "CNT" DESC;
Описание таблиц:
history - числовой (с плавающей точкой)
history_uint - числовой (целое положительное)
history_str - символ (но фактически строка)
history_log - журнал (лог)
history_text - текст (в т.ч. многострочный)
trends - динамика изменений для типа числовой (с плавающей точкой)
trends_uint - динамика изменений для типа числовой (целое положительное)
su postgres
vacuumdb -U postgres --quiet --verbose --analyze --dbname=zabbix
vacuumdb --full --dbname=zabbix
su postgres
psql -d zabbix
time sudo -u postgres psql -A -R ' : ' -P 'footer=off' zabbix < delete-old-data.pg.sql
-- keep 1 week of history and 3 months of trends
\set history_interval 90
\set trends_interval 180
DELETE FROM alerts where age(to_timestamp(alerts.clock)) > (:history_interval * interval '1 day');
DELETE FROM acknowledges where age(to_timestamp(acknowledges.clock)) > (:history_interval * interval '1 day');
DELETE FROM events where age(to_timestamp(events.clock)) > (:history_interval * interval '1 day');
DELETE FROM history where age(to_timestamp(history.clock)) > (:history_interval * interval '1 day');
DELETE FROM history_uint where age(to_timestamp(history_uint.clock)) > (:history_interval * interval '1 day') ;
DELETE FROM history_str where age(to_timestamp(history_str.clock)) > (:history_interval * interval '1 day') ;
DELETE FROM history_text where age(to_timestamp(history_text.clock)) > (:history_interval * interval '1 day') ;
DELETE FROM history_log where age(to_timestamp(history_log.clock)) > (:history_interval * interval '1 day') ;
DELETE FROM trends where age(to_timestamp(trends.clock)) > (:trends_interval * interval '1 day');
DELETE FROM trends_uint where age(to_timestamp(trends_uint.clock)) > (:trends_interval * interval '1 day') ;
time zabbix_server -R housekeeper_execute