Zabbix系统中哪些会占用大量的磁盘空间?

原创

Zabbix中国 2021-09-29 15:29:16 ©著作权

文章标签 mysql postgresql php unix 数据 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者Zabbix中国的原创作品，请联系作者获取转载授权，否则将追究法律责任

Zabbix系统中哪些会占用大量的磁盘空间?_postgresql

感谢译者余伟男，宏时数据Zabbix技术经理。

本篇文章列出了在Zabbix中，哪些会占用大量的磁盘空间以及哪些监控项和主机对象消耗磁盘空间最多。

包含以下内容：

数据库中最大的表
进入到Zabbix的值最大的监控项（最新）
数据库中最大的分区表
找到占用空间最多的主机和监控项

●

最大的表

一般来说，在Zabbix的库中，最占空间的表以大小依次排序为：

history history_uint	history_str history_text history_log	events

' history_uint '表存储整数类型的数据。' history '存储十进制的数据。

' history_str '， ' history_text '， ' history_log '存储文本类型的数据。

“events”表中记录了问题事件、内部事件、代理自动注册事件、自动发现的记录。

用sql语句检查哪些表占用了最多的空间。

Mysql：

SELECT table_name,       table_rows,       data_length,       index_length,       round(((data_length + index_length) / 1024 / 1024 / 1024),2) "Size in GB"FROM information_schema.tablesWHERE table_schema = "zabbix"ORDER BY round(((data_length + index_length) / 1024 / 1024 / 1024),2) DESCLIMIT 8;

PostgreSQL:

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 tableFROM (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;

●

最近5分钟内值最大的监控项

MySQL ‘history_log’：

SELECT SUM(LENGTH(value)) AS 'chars',CONCAT('history.php?itemids%5B0%5D=', itemid ,'&action=showlatest' ) AS 'URL'FROM history_logWHERE clock > UNIX_TIMESTAMP(NOW() - INTERVAL 5 MINUTE)GROUP BY itemid ORDER BY SUM(LENGTH(value)) DESC LIMIT 5;

MySQL ‘history_text’:

SELECT SUM(LENGTH(value)) AS 'chars',CONCAT('history.php?itemids%5B0%5D=', itemid ,'&action=showlatest' ) AS 'URL'FROM history_textWHERE clock > UNIX_TIMESTAMP(NOW() - INTERVAL 5 MINUTE)GROUP BY itemid ORDER BY SUM(LENGTH(value)) DESC LIMIT 5;

MySQL ‘history_str’:

SELECT SUM(LENGTH(value)) AS 'chars',CONCAT('history.php?itemids%5B0%5D=', itemid ,'&action=showlatest' ) AS 'URL'FROM history_strWHERE clock > UNIX_TIMESTAMP(NOW() - INTERVAL 5 MINUTE)GROUP BY itemid ORDER BY SUM(LENGTH(value)) DESC LIMIT 5;

PostgreSQL ‘history_text’:

SELECT CONCAT('history.php?itemids%5B0%5D=', itemid ,'&action=showlatest' ) AS URL,SUM(LENGTH(value)) FROM history_textWHERE clock > EXTRACT(epoch FROM NOW()-INTERVAL '5 MINUTE')GROUP BY itemid ORDER BY SUM(LENGTH(value)) DESC LIMIT 5;

PostgreSQL ‘history_log’:

SELECT CONCAT('history.php?itemids%5B0%5D=', itemid ,'&action=showlatest' ) AS URL,SUM(LENGTH(value)) FROM history_logWHERE clock > EXTRACT(epoch FROM NOW()-INTERVAL '5 MINUTE')GROUP BY itemid ORDER BY SUM(LENGTH(value)) DESC LIMIT 5;

PostgreSQL ‘history_str’:

SELECT CONCAT('history.php?itemids%5B0%5D=', itemid ,'&action=showlatest' ) AS URL,SUM(LENGTH(value)) FROM history_strWHERE clock > EXTRACT(epoch FROM NOW()-INTERVAL '5 MINUTE')GROUP BY itemid ORDER BY SUM(LENGTH(value)) DESC LIMIT 5;

●

哪些主机占用了最多的空间

MySQL ‘history_text’:

SELECT SUM(LENGTH(history_text.value)) AS 'chars', hosts.name AS 'name'FROM history_textJOIN items ON (items.itemid=history_text.itemid)JOIN hosts ON (hosts.hostid=items.hostid)WHERE history_text.clock > UNIX_TIMESTAMP(NOW() - INTERVAL 5 MINUTE)GROUP BY hosts.name ORDER BY SUM(LENGTH(history_text.value)) DESC LIMIT 5;

MySQL ‘history_log’:

SELECT SUM(LENGTH(history_log.value)) AS 'chars', hosts.name AS 'name'FROM history_logJOIN items ON (items.itemid=history_log.itemid)JOIN hosts ON (hosts.hostid=items.hostid)WHERE history_log.clock > UNIX_TIMESTAMP(NOW() - INTERVAL 5 MINUTE)GROUP BY hosts.name ORDER BY SUM(LENGTH(history_log.value)) DESC LIMIT 5;

MySQL ‘history_str’:

SELECT SUM(LENGTH(history_str.value)) AS 'chars', hosts.name AS 'name'FROM history_strJOIN items ON (items.itemid=history_str.itemid)JOIN hosts ON (hosts.hostid=items.hostid)WHERE history_str.clock > UNIX_TIMESTAMP(NOW() - INTERVAL 5 MINUTE)GROUP BY hosts.name ORDER BY SUM(LENGTH(history_str.value)) DESC LIMIT 5;

PostgreSQL ‘history_text’:

SELECT SUM(LENGTH(history_text.value)) AS "chars", hosts.name AS "name"FROM history_textJOIN items ON (items.itemid=history_text.itemid)JOIN hosts ON (hosts.hostid=items.hostid)WHERE history_text.clock > EXTRACT(epoch FROM NOW()-INTERVAL '5 MINUTE')GROUP BY hosts.name ORDER BY SUM(LENGTH(history_text.value)) DESC LIMIT 5;

PostgreSQL ‘history_log’:

SELECT SUM(LENGTH(history_log.value)) AS "chars", hosts.name AS "name"FROM history_logJOIN items ON (items.itemid=history_log.itemid)JOIN hosts ON (hosts.hostid=items.hostid)WHERE history_log.clock > EXTRACT(epoch FROM NOW()-INTERVAL '5 MINUTE')GROUP BY hosts.name ORDER BY SUM(LENGTH(history_log.value)) DESC LIMIT 5;

PostgreSQL ‘history_str’:

SELECT SUM(LENGTH(history_str.value)) AS "chars", hosts.name AS "name"FROM history_strJOIN items ON (items.itemid=history_str.itemid)JOIN hosts ON (hosts.hostid=items.hostid)WHERE history_str.clock > EXTRACT(epoch FROM NOW()-INTERVAL '5 MINUTE')GROUP BY hosts.name ORDER BY SUM(LENGTH(history_str.value)) DESC LIMIT 5;

●

从分区表层面分析（Mysql）

如果你将Mysql作为你的数据库并且做了表分区，可以列出占用空间最大的分区：

cd /var/lib/mysql/zabbixls -lh history_log#*

将会输出如下内容:

-rw-r-----. 1 mysql mysql  44M Jan 24 20:23 history_log#p#p2021_02w.ibd-rw-r-----. 1 mysql mysql  24M Jan 24 21:20 history_log#p#p2021_03w.ibd-rw-r-----. 1 mysql mysql 128K Jan 11 00:59 history_log#p#p2021_04w.ibd

根据上面的内容，我们可以拿到分区的名字“p2021_02w”，并在下面的sql语句中使用，用来分析这个分区表：

SELECT ho.hostid, ho.name, count(*) AS records, (count(*)* (SELECT AVG_ROW_LENGTH FROM information_schema.tables WHERE TABLE_NAME = 'history_log' and TABLE_SCHEMA = 'zabbix')/1024/1024) AS 'Total size average (Mb)', sum(length(history_log.value))/1024/1024 + sum(length(history_log.clock))/1024/1024 +sum(length(history_log.ns))/1024/1024 + sum(length(history_log.itemid))/1024/1024 AS 'history_log Column Size (Mb)'FROM history_log PARTITION (p2021_02w)LEFT OUTER JOIN items i on history_log.itemid = i.itemid LEFT OUTER JOIN hosts ho on i.hostid = ho.hostid WHERE ho.status IN (0,1)GROUP BY ho.hostidORDER BY 4 DESCLIMIT 10;

可以在列出时重现类似的场景：

ls -lh history_text#*ls -lh history_str#*

●

如何释放磁盘空间（Mysql）

在前端页面删除主机不会释放MySQL上的空间。它将在表中创建空行，以便插入新数据。如果您想真正释放磁盘空间，我们可以重建分区。

首先列出所有可能的分区名称：

SHOW CREATE TABLE history\G

重建表分区：

ALTER TABLE history REBUILD PARTITION p202101160000;

●

如何释放磁盘空间（PostgreSQL）

在PostgreSQL上，有一个进程负责清空表。如果想确定最近已进行清理，可以执行一下语句：

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuumFROM pg_stat_all_tablesWHERE n_dead_tup > 0ORDER BY n_dead_tup DESC;

在输出中，我们要注意‘n_dead_tup’，它表示一个死元组。

如果最近10天内没有出现最后一次自动清理，那就糟糕了。我们必须做一个不同的配置。可以通过以下方式提高自动清理的优先级：

vacuum_cost_page_miss = 10vacuum_cost_page_dirty = 20autovacuum_vacuum_threshold = 50autovacuum_vacuum_scale_factor = 0.01autovacuum_vacuum_cost_delay = 20msautovacuum_vacuum_cost_limit = 3000autovacuum_max_workers = 6