I'm trying to understand why Mariadb (v10.4.12) is writing most of the temp tables to disk.
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.