统计Zabbix数据库最近24小时内每个监控项写入history表的行数，数值型和浮点型两张表合计后按行数降序排列，用来快速找出最占数据库的监控项。

SQL语句

SELECT hst.host, i.name, i.delay, i.history,
       SUM(x.cnt) AS rows_1d
FROM (
    SELECT itemid, COUNT(*) AS cnt FROM history_uint
     WHERE clock > UNIX_TIMESTAMP() - 86400 GROUP BY itemid
    UNION ALL
    SELECT itemid, COUNT(*) AS cnt FROM history
     WHERE clock > UNIX_TIMESTAMP() - 86400 GROUP BY itemid
) x
JOIN items i   ON i.itemid   = x.itemid
JOIN hosts hst ON hst.hostid = i.hostid
GROUP BY i.itemid, hst.host, i.name, i.delay, i.history
ORDER BY rows_1d DESC
LIMIT 100;

标签: none