IT博客汇 | 清理Zabbix旧数据

清理Zabbix旧数据

夜行人发表于 2017-03-15 07:15:23

问题

磁盘空间不够了，要删除一些历史数据，后面换T级别的硬盘吧

解决过程

备份

硬盘有价，数据无价，在开始删除前，请先做个完整备份，万一以后要查呢。

/usr/bin/pg_dump -U ${zabbix_user} ${zabbix_dbname} | bzip2 -c > zabbix_pgsql_backup_20170309.dump.bz2

统计各个表大小

登入PostgreSQL

PGPASSWORD=xxxx  /usr/bin/psql -U zabbix

统计大小

SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a;

删除方法一

无论表有没有做分区，都可以使用此方法

执行下面的SQL

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');

删除后，手动执行下回收空间

# trends_uint为表名
VACUUM (VERBOSE,ANALYZE) trends_uint;

此法存在问题，就是删除时间比较长，容易引起锁表，并导致Zabbix无法使用

查询是否锁表

SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED;

更多请参考：burner1024/zabbix-sql

删除方法二

此方法适用于表做了分区，我们采用删除分区的方式，特点是快，就好像删除文件一样快，我怀疑PostgreSQL是不是一个分区就存一个文件

# 显示Schema的搜索路径
SHOW search_path;

# 从上面的输出可以看出，默认没有partitions，我们加入，如果不加入，在drop table的时候，就会提示does not exist
SET search_path TO "$user",partitions,public;

# 可以drop了，CASCADE的作用是把关联的触发条件删除，这触发条件，其实就是什么数据应该插入此分区的，现在已经是2017了，自然不会有2015的数据需要插入，所以可以放心删除
drop table history_uint_2015_01_11 CASCADE;

此法真是快到没朋友，最后，简单粗暴，根据上面统计出的表大小结果，我们来批量生成SQL，此处删除2015年的

grep _2015_ zabbix_table_size.txt |awk -F\| '{print "drop table" $3 "CASCADE;"}'

其他参考资料：

从下面的参考资料可以看出，自动创建分区，连删除分区，都是自动的