Zabbix监控系统基于MySQL PostgreSQL数据库优化哪些事

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

包含以下内容：

1. 数据库中最大的表

2. 进入到Zabbix的值最大的监控项（最新）

3. 数据库中最大的分区表

4. 找到占用空间最多的主机和监控项

一、最大的表

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

' 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.tables
WHERE table_schema = "zabbix"
ORDER BY round(((data_length + index_length) / 1024 / 1024 / 1024),2) DESC
LIMIT 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 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;

二、MySQL PostgreSQL数据库优化，最近5分钟内值最大的监控项

MySQL ‘history_log’：

SELECT SUM(LENGTH(value)) AS 'chars',
CONCAT('history.php?itemids%5B0%5D=', 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’:

SELECT SUM(LENGTH(value)) AS 'chars',
CONCAT('history.php?itemids%5B0%5D=', 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’:

SELECT SUM(LENGTH(value)) AS 'chars',
CONCAT('history.php?itemids%5B0%5D=', 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’:

SELECT CONCAT('history.php?itemids%5B0%5D=', 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’:

SELECT CONCAT('history.php?itemids%5B0%5D=', 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’:

SELECT CONCAT('history.php?itemids%5B0%5D=', 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;

三、MySQL PostgreSQL数据库优化，哪些主机占用了最多的空间

MySQL ‘history_text’:

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’:

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’:

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’:

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’:

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’:

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;

四、从分区表层面分析（Mysql）

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

cd /var/lib/mysql/zabbix
ls -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

2、根据上面的内容，我们可以拿到分区的名字“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.hostid
ORDER BY 4 DESC
LIMIT 10;

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

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

五、如何释放磁盘空间（Mysql）

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

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

SHOW CREATE TABLE history\G

重建表分区：

ALTER TABLE history REBUILD PARTITION p202101160000;

六、如何释放磁盘空间（PostgreSQL）

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

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天内没有出现最后一次自动清理，那就糟糕了。我们必须做一个不同的配置。可以通过以下方式提高自动清理的优先级：

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