Question :
I have a nagios check for mariadb 10.4 server running on Debian9, WARNING – 59.92% of 670 tables were created on disk | pct_tmp_table_on_disk=59.92%;50;75 pct_tmp_table_on_disk_now=77.46%. However, there’s little to no activity on the server, slow query log doesn’t registered anything.
In my.cnf I have tmp_table_size = 32M and max_heap_table_size = 32M.
The tmpdir is /tmp and i see nothing there as well.
I am not sure if the check is wrong or is there anything that i’m missing…Any suggestion would be appreciated
Answer :
“slow query log doesn’t registered anything” — Decrease long_query_time down to 1 or less. (Even 0 if you get desperate.)
Temp tables may be in RAM or on disk, depending on several things. Please provide SHOW CREATE TABLE for any tables that might involve temp tables.
The prime candidate for going straight to disk (and ignoring the two ...table_size settings) is when there is any kind of TEXT or BLOB column in the query. Another case is that it will fill up the 32M you have configured, then copy it to disk to continue.
“/tmp and i see nothing there” — How are you looking? The code probably creates the disk table, then “rm’s” it immediately. After that it proceeds to use it. If a crash occurs, the file is already gone, so there is no trash left behind. (Magic!)