Mysql – Mariadb always writes tmp disk to a file

mariadbmariadb-10.4MySQL

I'm trying to understand why Mariadb (v10.4.12) is writing most of the temp tables to disk.

Created_tmp_disk_tables 196,8k
Created_tmp_tables 367.8k

There's 188G of RAM in the system with ~50% of it unused. So it's not like that should be a problem.

I enabled performance schema and I see that there are queries with small datasets with GROUP BY statements that have 100% rate on writing tmp tables to disk (events_statements_summary_by_digest.SUM_CREATED_TMP_DISK_TABLES === events_statements_summary_by_digest.SUM_CREATED_TMP_TABLES)

For example - one of those queries have:
* 160 rows when grouped
* 2150 rows without grouping
* each row is about ~20 bytes - so it's quite tiny

I have no TEXT fields in the query only some integer fields that are indexed.

Query does have multiple INNER JOINs in it. But all the ON conditions etc are done on integer (PK) fields – no string used anywhere.
One joined table does have TEXT field in the schema but it's not used in the query itself. I can't see how that could influence this.

How could I get more info on why are these tmp tables written to disk?

There are lots more different queries that are behaving same way and I can't understand why is that so.

I'm not putting any schema / query examples here currenlty as I'm interested understanding how to debug this myself and I really don't want to publish any info about production systems schema etc.

Best Answer

Read the manual where it mentions using MEMORY for temp tables and when it will switch to MyISAM, either because of limitations such as TEXT or because of exceeding LEAST(tmp_table_size, max_hash_table_size). Further note that VARCHARs become CHAR for calculating when to spill to disk. (This is fixed in 8.0; I doubt if it is yet in 10.4.) Example: VARCHAR(250) utf8mb4 counts as 1000 bytes.

See EXPLAIN SELECT. If it says Using temporary, that is a clue. However, that phrase is usually on the first line of the EXPLAIN not necessarily on the line that triggered the need.

Each of DISTINCT, GROUP BY, and ORDER BY may need a temp table. If the query involves a JOIN, then it is more likely that one of those will trigger the need for a temp table.

Do not fear temp tables; some queries simply require such. A simple example is any query with GROUP BY something ORDER BY something_else.

Granted, the above does not say whether the temp table is on disk or in memory.

If that does not suffice, please provide a specific case. Show us the query and SHOW CREATE TABLE and EXPLAIN SELECT .... We should be able to help you change so that it does not need a disk-based temp table or explain why it must use such.

To check to see if a particular query uses disk temp table(s), do something like:

SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables';
SELECT ...
SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables';

That is a counter, hence the need to check before and after.

The slowlog might also give you a clue.

Related Question