从 Zabbix 到 MySQL 数据库的“select count(*)”数量过多

Question

我的 Zabbix Server 遇到一些问题，因为它总是很慢。因此，我在 Zabbix Server 配置中的 syslog 中启用了慢速查询突出显示。为什么它不断地在这些表上执行 select count(*) ？ （它们很大！）。有没有办法规避Zabbix的这种行为？

zabbix_server[13501]: slow query: 129.014900 sec, "select count(*) from trends"
zabbix_server[13501]: slow query: 178.554334 sec, "select count(*) from trends"
zabbix_server[13501]: slow query: 25.102330 sec, "select count(*) from history_str"
zabbix_server[13505]: slow query: 754.050522 sec, "select count(*) from history"
zabbix_server[13509]: slow query: 180.450058 sec, "select count(*) from trends"
zabbix_server[13512]: slow query: 122.668146 sec, "select count(*) from trends"
zabbix_server[13512]: slow query: 172.661542 sec, "select count(*) from trends"
zabbix_server[13512]: slow query: 31.664581 sec, "select count(*) from history_str"
zabbix_server[13515]: slow query: 210.105187 sec, "select count(*) from trends"
zabbix_server[13516]: slow query: 27.483541 sec, "select count(*) from history_str"
zabbix_server[13516]: slow query: 93.289657 sec, "select count(*) from history_str"
zabbix_server[13518]: slow query: 102.836916 sec, "select count(*) from trends"
zabbix_server[13522]: slow query: 130.317530 sec, "select count(*) from trends"
zabbix_server[13524]: slow query: 29.246892 sec, "select count(*) from history_str"
zabbix_server[13524]: slow query: 31.702153 sec, "select count(*) from history_str"
zabbix_server[13525]: slow query: 34.628497 sec, "select count(*) from history_str"
zabbix_server[13529]: slow query: 106.031498 sec, "select count(*) from trends"
zabbix_server[13529]: slow query: 160.210204 sec, "select count(*) from trends"
zabbix_server[13532]: slow query: 171.634335 sec, "select count(*) from trends"
zabbix_server[13534]: slow query: 75.451523 sec, "select count(*) from history_str"
zabbix_server[13536]: slow query: 132.403058 sec, "select count(*) from history_str"
zabbix_server[13536]: slow query: 25.296489 sec, "select count(*) from history_str"
zabbix_server[13539]: slow query: 1138.864792 sec, "select count(*) from history"
zabbix_server[13542]: slow query: 1001.632530 sec, "select count(*) from history"
zabbix_server[13542]: slow query: 619.885084 sec, "select count(*) from history"
zabbix_server[13549]: slow query: 22.035550 sec, "select count(*) from history_str"
zabbix_server[13550]: slow query: 25.015439 sec, "select count(*) from history_str"
zabbix_server[13550]: slow query: 51.222695 sec, "select count(*) from history_str"
zabbix_server[13552]: slow query: 106.272890 sec, "select count(*) from trends"
zabbix_server[13552]: slow query: 57.781271 sec, "select count(*) from history_str"
zabbix_server[13558]: slow query: 195.948071 sec, "select count(*) from trends"
zabbix_server[13558]: slow query: 35.949829 sec, "select count(*) from history_str"
zabbix_server[13561]: slow query: 27.496382 sec, "select count(*) from history_str"
zabbix_server[13561]: slow query: 59.612290 sec, "select count(*) from history_str"
zabbix_server[13562]: slow query: 266.540841 sec, "select count(*) from trends"
zabbix_server[13562]: slow query: 815.081735 sec, "select count(*) from history"
zabbix_server[13564]: slow query: 739.742027 sec, "select count(*) from history"
zabbix_server[13565]: slow query: 25.792558 sec, "select count(*) from history_str"
zabbix_server[13565]: slow query: 662.055581 sec, "select count(*) from history"
zabbix_server[13566]: slow query: 166.917401 sec, "select count(*) from trends"
zabbix_server[13569]: slow query: 133.917164 sec, "select count(*) from trends"
zabbix_server[13569]: slow query: 375.701664 sec, "select count(*) from trends"
zabbix_server[13576]: slow query: 30.821537 sec, "select count(*) from history_str"
zabbix_server[13576]: slow query: 323.511372 sec, "select count(*) from trends"
zabbix_server[13576]: slow query: 739.250067 sec, "select count(*) from history"
zabbix_server[13576]: slow query: 98.566847 sec, "select count(*) from trends"
zabbix_server[13577]: slow query: 118.234232 sec, "select count(*) from trends"
zabbix_server[13577]: slow query: 29.402386 sec, "select count(*) from history_str"
zabbix_server[13582]: slow query: 1633.032010 sec, "select count(*) from history"
zabbix_server[13584]: slow query: 39.793458 sec, "select count(*) from history_str"
zabbix_server[13587]: slow query: 27.135422 sec, "select count(*) from history_str"
zabbix_server[13595]: slow query: 35.727087 sec, "select count(*) from history_str"
zabbix_server[13600]: slow query: 134.139426 sec, "select count(*) from trends"
zabbix_server[13601]: slow query: 27.672915 sec, "select count(*) from history_str"
zabbix_server[13602]: slow query: 273.831060 sec, "select count(*) from trends"
zabbix_server[13607]: slow query: 60.470353 sec, "select count(*) from history_str"
zabbix_server[13610]: slow query: 135.439930 sec, "select count(*) from trends"
zabbix_server[13610]: slow query: 179.540891 sec, "select count(*) from trends"
zabbix_server[13613]: slow query: 117.768700 sec, "select count(*) from trends"
zabbix_server[13613]: slow query: 165.944011 sec, "select count(*) from trends"
zabbix_server[13613]: slow query: 44.678274 sec, "select count(*) from history_str"
zabbix_server[13615]: slow query: 33.423619 sec, "select count(*) from history_str"
zabbix_server[13617]: slow query: 19.533471 sec, "select count(*) from history_str"
zabbix_server[13617]: slow query: 411.857382 sec, "select count(*) from trends"
zabbix_server[13621]: slow query: 118.574077 sec, "select count(*) from trends"
zabbix_server[13622]: slow query: 21.323747 sec, "select count(*) from history_str"
zabbix_server[13625]: slow query: 156.002532 sec, "select count(*) from trends"

编辑：由于我无法解决问题（而且没有人解决不了），我转储了表并清除了其中的数据。 Zabbix（当然）现在运行得更快。

Answer 1

这些表是否有任何索引字段（例如主键）？如果没有，添加索引应该会加快速度。

另一种选择是调整 Mysql 以更好地利用 RAM。也许在服务器中安装更多的 RAM - 当你给它大量内存时，mysql 确实可以运行，但是当 RAM 不足意味着它没有机会缓存​​频繁查询的答案，或者保留表和/或索引时，它的速度就会变慢完全在记忆中。

最后，Zabbix 和 Mysql 之间的缓存 SQL 代理可能会有所帮助（但代价是偶尔会从缓存中获取过时的数据）——我已经好几年没有使用过它了，所以不能推荐任何特定的软件。尝试用谷歌搜索mysql proxy cache.