0

Running lsof +L1

COMMAND PID USER FD TYPE DEVICE SIZE/OFF NLINK NODE NAME

mysqld 886068 mysql 521u REG 254,1 194824503296 0 29360306 /tmp/#29360306 (deleted)

Running mysql 3.0.34 in a innodb cluster. Seemingly randomly the disk gets eaten up all 300 gigs of it into a tmp folder, once it reaches full it is released.

Looking for suggestion / how to limit tmp table size?

SET @sys.statement_truncate_len = 1000; select * from sys.statements_with_temp_tables;

Yields on 1 item that is flagged as disk_tmp_tables.

query db exec_count total_latency memory_tmp_tables disk_tmp_tables avg_tmp_tables_per_query tmp_tables_to_disk_pct first_seen last_seen digest SELECT TABLE_NAME FROM information_schema . INNODB_BUFFER_PAGE WHERE TABLE_NAME LIKE ? LIMIT ?, ... 1 439.42 ms 1 1 1 100 2024-02-09 10:14:54.800889 2024-02-09 10:14:54.800889 d24fb8e96673a518f0f536b9516781644ffb7a5079f95490cec24804437f34a9

4
  • Does this answer your question? Mysql 8.0.34 Temp Tables Consumes Entire Disk Feb 9 at 22:22
  • They moved the question from stackoverflow remove the other post.
    – Watson
    Feb 9 at 22:34
  • Let's see and fix the naughty SELECT that caused this. It may be a JOIN without an ON clause.
    – Rick James
    Feb 10 at 16:50
  • It was a mysql view that has a join without an ON clause. The slow query didn't log it. but I was able to catch it in real time and look just look at the current client connections.
    – Watson
    Feb 20 at 17:34

1 Answer 1

1

There's no good way to limit temp table size on disk in MySQL.

Temp tables are created automatically by certain queries. See https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html for a list of such cases.

There's no practical limit to the size of a temp table, because any given query might do a huge Cartesian product or any other operation that needs a lot of temp space. It depends on the datasets you are processing and the type of query.

Also it's common that multiple sessions do such operations concurrently. They each get their own temp tablespace, and each of those tablespaces can grow.

I've seen cases like yours, where the space grows by hundreds of gigabytes suddenly, exhausting all remaining disk space.

MySQL 8.0 uses a pool of temporary tablespaces. As the optimizer creates temp tables that are stored on disk, it uses these tablespaces, and they are expanded as needed to store the temp tables for a given session. These tablespaces are truncated when a session disconnects, but they might keep expanding otherwise. Read https://dev.mysql.com/doc/refman/8.0/en/innodb-temporary-tablespace.html for details.

The best way I know to mitigate temp table size is to identify queries that use temp tables, and try to optimize them so they don't create temp tables.

Another method is to reduce the size of your datasets so the temp tables are smaller. This can be done either by optimizing the query with indexes, so it examines less data, and therefore the amount of data that needs to go into a temp table is reduced.

Or ultimately, archive data and remove it from your database.

Another thing you can try is to configure MySQL Server so the innodb_temp_tablespaces_dir option creates the temp tablespaces on a dedicated disk volume with limited space. But this will cause queries to fail in spectacular ways if they can't create a large temp table that they need.

8
  • "The best way I know to mitigate temp table size is to identify queries that use temp tables, and try to optimize them so they don't create temp tables." Any way to identify the queries associated with these large temp tables easily?
    – Watson
    Feb 9 at 18:56
  • Use EXPLAIN to analyze each of the queries your application runs. In most cases, it says "Using temporary" in the Extra field of the EXPLAIN. But there are a few less common cases (described in the manual I linked to) that use temporary tables even though the EXPLAIN doesn't say so. Basically, it's a meticulous process of analyzing queries and studying the way the optimizer works by reading the manual, and some experimentation. Feb 9 at 19:11
  • System has thousands of queries. statements_with_temp_tables shows only 1 item flagged as disk_tmp_tables = 1 but it doesn't read the full query; SELECT TABLE_NAME FROM information_schema . INNODB_BUFFER_PAGE WHERE TABLE_NAME LIKE ? LIMIT ? Any way to get the full query instead of the "normalized" statement?
    – Watson
    Feb 9 at 19:18
  • If it's a prepared statement, the general query log can be configured to log both the version with placeholders and the query with values. But you shouldn't need the query with values to know which query it is. Feb 9 at 20:21
  • I'll also comment you don't need to analyze every single query your application runs. Many of the queries are undoubtedly simple, and once you learn the cases of queries that may incur temp tables, you can rule out the simple cases quickly. Feb 9 at 20:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.