感谢译者余伟男，宏时数据Zabbix技术经理。
欢迎翻译Zabbix官方博文，将有用资料分享给更多用户！
本篇文章列出了在Zabbix中，哪些会占用大量的磁盘空间以及哪些监控项和主机对象消耗磁盘空间最多。
包含以下内容：
- 数据库中最大的表
- 进入到Zabbix的值最大的监控项（最新）
- 数据库中最大的分区表
- 找到占用空间最多的主机和监控项
1
●
最大的表
一般来说，在Zabbix的库中，最占空间的表以大小依次排序为：
historyhistory_uint | history_strhistory_texthistory_log | events |
|---|
' history_uint '表存储整数类型的数据。' history '存储十进制的数据。
' history_str '， ' history_text '， ' history_log '存储文本类型的数据。
“events”表中记录了问题事件、内部事件、代理自动注册事件、自动发现的记录。
用sql语句检查哪些表占用了最多的空间。
Mysql：
代码语言：javascript复制SELECT table_name,
table_rows,
data_length,
index_length,
round(((data_length index_length) / 1024 / 1024 / 1024),2) "Size in GB"
FROM information_schema.tables
WHERE table_schema = "zabbix"
ORDER BY round(((data_length index_length) / 1024 / 1024 / 1024),2) DESC
LIMIT 8;PostgreSQL:
代码语言：javascript复制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;2
●
最近5分钟内值最大的监控项
MySQL ‘history_log’：
代码语言：javascript复制SELECT SUM(LENGTH(value)) AS 'chars',
CONCAT('history.php?itemids[0]=', itemid ,'&action=showlatest' ) AS 'URL'
FROM history_log
WHERE clock > UNIX_TIMESTAMP(NOW() - INTERVAL 5 MINUTE)
GROUP BY itemid ORDER BY SUM(LENGTH(value)) DESC LIMIT 5;MySQL ‘history_text’:
代码语言：javascript复制SELECT SUM(LENGTH(value)) AS 'chars',
CONCAT('history.php?itemids[0]=', itemid ,'&action=showlatest' ) AS 'URL'
FROM history_text
WHERE clock > UNIX_TIMESTAMP(NOW() - INTERVAL 5 MINUTE)
GROUP BY itemid ORDER BY SUM(LENGTH(value)) DESC LIMIT 5;MySQL ‘history_str’:
代码语言：javascript复制SELECT SUM(LENGTH(value)) AS 'chars',
CONCAT('history.php?itemids[0]=', itemid ,'&action=showlatest' ) AS 'URL'
FROM history_str
WHERE clock > UNIX_TIMESTAMP(NOW() - INTERVAL 5 MINUTE)
GROUP BY itemid ORDER BY SUM(LENGTH(value)) DESC LIMIT 5;PostgreSQL ‘history_text’:
代码语言：javascript复制SELECT CONCAT('history.php?itemids[0]=', itemid ,'&action=showlatest' ) AS URL,
SUM(LENGTH(value)) FROM history_text
WHERE clock > EXTRACT(epoch FROM NOW()-INTERVAL '5 MINUTE')
GROUP BY itemid ORDER BY SUM(LENGTH(value)) DESC LIMIT 5;PostgreSQL ‘history_log’:
代码语言：javascript复制SELECT CONCAT('history.php?itemids[0]=', itemid ,'&action=showlatest' ) AS URL,
SUM(LENGTH(value)) FROM history_log
WHERE clock > EXTRACT(epoch FROM NOW()-INTERVAL '5 MINUTE')
GROUP BY itemid ORDER BY SUM(LENGTH(value)) DESC LIMIT 5;PostgreSQL ‘history_str’:
代码语言：javascript复制SELECT CONCAT('history.php?itemids[0]=', itemid ,'&action=showlatest' ) AS URL,
SUM(LENGTH(value)) FROM history_str
WHERE clock > EXTRACT(epoch FROM NOW()-INTERVAL '5 MINUTE')
GROUP BY itemid ORDER BY SUM(LENGTH(value)) DESC LIMIT 5;3
●
哪些主机占用了最多的空间
MySQL ‘history_text’:
代码语言：javascript复制SELECT SUM(LENGTH(history_text.value)) AS 'chars', hosts.name AS 'name'
FROM history_text
JOIN 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’:
代码语言：javascript复制SELECT SUM(LENGTH(history_log.value)) AS 'chars', hosts.name AS 'name'
FROM history_log
JOIN 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’:
代码语言：javascript复制SELECT SUM(LENGTH(history_str.value)) AS 'chars', hosts.name AS 'name'
FROM history_str
JOIN 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’:
代码语言：javascript复制SELECT SUM(LENGTH(history_text.value)) AS "chars", hosts.name AS "name"
FROM history_text
JOIN 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’:
代码语言：javascript复制SELECT SUM(LENGTH(history_log.value)) AS "chars", hosts.name AS "name"
FROM history_log
JOIN 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’:
代码语言：javascript复制SELECT SUM(LENGTH(history_str.value)) AS "chars", hosts.name AS "name"
FROM history_str
JOIN 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;4
●
从分区表层面分析（Mysql）
如果你将Mysql作为你的数据库并且做了表分区，可以列出占用空间最大的分区：
代码语言：javascript复制cd /var/lib/mysql/zabbix
ls -lh history_log#*将会输出如下内容:
代码语言：javascript复制-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语句中使用，用来分析这个分区表：
代码语言：javascript复制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.hostid
ORDER BY 4 DESC
LIMIT 10;可以在列出时重现类似的场景：
代码语言：javascript复制ls -lh history_text#*
ls -lh history_str#*5
●
如何释放磁盘空间（Mysql）
在前端页面删除主机不会释放MySQL上的空间。它将在表中创建空行，以便插入新数据。如果您想真正释放磁盘空间，我们可以重建分区。
首先列出所有可能的分区名称：
代码语言：javascript复制SHOW CREATE TABLE historyG重建表分区：
代码语言：javascript复制ALTER TABLE history REBUILD PARTITION p202101160000;6
●
如何释放磁盘空间（PostgreSQL）
在PostgreSQL上，有一个进程负责清空表。如果想确定最近已进行清理，可以执行一下语句：
代码语言：javascript复制SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_all_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;在输出中，我们要注意‘n_dead_tup’，它表示一个死元组。
如果最近10天内没有出现最后一次自动清理，那就糟糕了。我们必须做一个不同的配置。可以通过以下方式提高自动清理的优先级：
代码语言：javascript复制vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.01
autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = 3000
autovacuum_max_workers = 6点击“阅读原文”，查看英文官方博文
