Since upgrading Zabbix to 6.0 (and porting the DB from a Ubuntu 14/Maria10.2 box to an Alma 8/Maria10.5 box), my Zabbix server is reporting regular bursts of the following error.
"MySQL: Number of on-disk temporary tables created per second is high"
Now and then in conjunction with the following error:
"MySQL: Number of internal temporary tables created per second is high"
They occur roughly for a minute or two every 1-2 hours, but not at precise intervals, and they don't tie into the time of the Housekeeping process running.
The box has plenty of spare memory in it's InnoDB buffer cache, and there's no other evidence I can find of swapping to disk occurring. I have doubled the size of the sort_buffer_size twice from the old box as I understand this can be a cause of temporary tables being generated. I believe my sort buffer is still quite small at 8M but my system isn't listing a single sort_merge_pass, so I'm reluctant to keep blindly increasing this value as I understand it can have other knock on effects.
Zabbix's own documentation helpfully states that I should look for queries in need of optimisation
Primary keys have also been added to the History tables.
Can anybody advise if my sort_buffer_size is still too small (we're not a massive install, the box looks after about 1000 hosts), and/or suggest next steps in troubleshooting this error? It's not causing any problems to my knowledge, but it is triggering numerous alerts a day.
Comment