5

I am using MySQL, and the temporary table engine is configured as InnoDB.

We are currently facing a serious issue in production. Our business has many large analytical queries, especially multi-table JOIN queries. When these queries generate temporary tables and the temporary tables spill to disk, they can quickly consume a large amount of disk space.

This has started to affect normal business access to regular tables, which is a major risk for our system.

Is there any way in MySQL to limit the maximum disk space used by on-disk temporary tables? Ideally, once a query exceeds this limit, MySQL should automatically kill or abort that large query, so that normal business traffic is not impacted.

I would like to know whether MySQL provides any built-in configuration, resource control mechanism, or recommended workaround for this scenario.

Improve this question
New contributor
Kylinzzc is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

1 Answer 1

Reset to default
3

You can reduce the impact of runaway queries by placing the temporary tablespaces on a separate file system mounted at the innodb_temp_tablespaces_dir path. Presumably, queries that cannot allocate enough space for their temporary tables will be terminated automatically. This will likely affect other sessions that use the temporary tablespaces simultaneously, but those that don't should not be affected.

Improve this answer
1
  • Thanks, this is a practical workaround. My concern is that it still needs a separate device/partition, and the limit is only at the filesystem level. If one large query fills the temp tablespace, other queries needing temp space may still be affected. I was hoping for a native per-query/session temp space quota, so only the query exceeding the limit is aborted. Commented yesterday

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.