MySQL: Number of on-disk temporary tables created per second is high (over 10 for 5m)
Hi all I would like to ask You if You have some recommandation how to resolve daily zabix warnning
version of mariadb : 10.3.39-MariaDB MariaDB Server linux version : Linux version 3.10.0-1160.83.1.el7.x86_64
I increased varible sort_buffer_size to 16777216, I enabled slow_query_log but it idn't help me , Do you have any idea pls ?
thank You Brano
Answer Answered by Ian Gilfillan in this comment.
The warning is because there are a large number of queries that require on-disk temporary tables to be created, which may not be ideal. You said you increased the sort_buffer_size (did you have a specific reason for trying this?) and enabled the slow query log, but it didn't help you. The slow query log by default should show you the problematic queries, which is a start in identifying what is going on. It could be poorly-indexed queries as well as non-optimal settings. Take a look at the description at tmp_table_size to get an idea of the ratio of tmp tables to tmp tables on disk. It may help to increase tmp_table_size and/or max_heap_table_size.
