Quantidade excessiva de “select count (*)” do Zabbix para o banco de dados MySQL

Question

Eu tenho alguns problemas com o meu Zabbix Server, pois é constantemente lento. Então eu ativei o realce de consulta lenta no syslog na configuração do Zabbix Server. Por que está constantemente selecionando count (*) nessas tabelas? (Eles são enormes!). Existe uma maneira de contornar esse comportamento do 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"

Edit: Já que eu não pude resolver os problemas (e ninguém também), eu joguei as tabelas e purguei os dados dentro delas. O Zabbix está (é claro) rodando muito mais rápido agora.

    

Answer 1

Essas tabelas possuem algum campo de índice (por exemplo, uma chave primária)? se não, adicionar um índice deve acelerar as coisas.

Outra opção é ajustar o MySQL para fazer melhor uso da RAM. E talvez instale mais RAM no servidor - o mysql pode realmente voar quando você dá muita memória, mas fica mais lento quando RAM insuficiente significa que ele não tem a oportunidade de armazenar em cache respostas a consultas freqüentes ou manter tabelas e / ou índices inteiramente na memória.

Por fim, um proxy SQL em cache entre o Zabbix e o Mysql pode ajudar (ao preço de, ocasionalmente, obter dados obsoletos do cache) - faz anos desde que usei um, por isso não posso recomendar nenhum software específico. Tente pesquisar por mysql proxy cache .