SQL Storage usage massive increase

[Dx]

Hello,

We've recently experienced problems with our instances (Mysql 8.0.18 / 8.0.30) in two separate occasions, where a query remains on execute forever and creates a huge implicit temporary table. The first time we didn't immediately notice the issue and the query mananged to double our required storage (several TB increase). We mainly noticed because at some point other queries that created implicit temporary tables started to hang as well. We killed the query which took the db hours to clean up.

The first query contained a recursive cte (but was a select that didn't require any locks), the second one was a very basic query (UPDATE ... SET... WHERE ...). No other queries seem to be running around that would interfere with these queries. Also these issues occured in two completely unrelated parts of our application and the first script hasn't been changed for quite a while. Also we didn't make any other changes that seem relevant (only related to replication, like placing a replica, but well before these issues occured).

So far we are left in de dark as to why this is happening. Did anyone experience something similar or have any advice?

[ms4446]

MySQL's behavior of creating implicit temporary tables can indeed cause a significant increase in storage usage, as you've experienced. There could be a few factors at play here:

1. Query Complexity: Complex queries, especially those involving operations like JOIN, UNION, GROUP BY, ORDER BY, and subqueries, can result in the creation of implicit temporary tables as MySQL attempts to process the query. Recursive CTEs can also create large temporary tables if they produce a large number of rows. Optimizing these queries, or potentially rewriting them to be less complex, could help avoid the creation of these large temporary tables.

2. Temporary Table Storage Engine: In MySQL, temporary tables can be created in memory or on disk. If they're created

[Dx]

1. The first query was indeed slightly more complex with a recursive cte. I already rewrote this query to make it simpler, but I personally don't think the query complexity causes this. The second query was a very simple update query. No joins, unions or anything: update one column in one  row based on a primary key. As far as I know this query shouldn't do anything with regards to tmp tables.

2. We allow reasonably large tmp tables in memory, however these queries started writing to disk (as expected) since they were both in the magnitude of TB by the time we discovered the issue and killed them.

The reason of the disk increase is logical, but I cannot find any reason why these tmp tables are becoming so large (or used in case of the second query).

[ms4446]

I see, thanks for the additional details. It's indeed very unusual for such simple queries to generate TB-sized temporary tables. Your understanding of the situation seems correct - these queries should not normally be generating such large implicit temporary tables. 

Here are some additional factors we might consider:

1. InnoDB Buffer Pool: The size of the InnoDB buffer pool can affect MySQL's temporary table usage. If the buffer pool is too small, MySQL might need to create more temporary tables on disk. However, given that you've already configured MySQL to allow large temporary tables in memory, it's unlikely this is the issue.

2. Data and Indexes: The structure and size of your data, as well as the indexes on your tables, can impact MySQL's query execution and temporary table usage. For the UPDATE query, if the primary key isn't indexed or if the table is exceptionally large, this might result in an inefficient operation that requires a large temporary table.

3. Binary Logging and Replication: You mentioned making changes related to replication. While this was well before the issue occurred, it's still worth considering whether this could be related. In some cases, binary logging (which is used in replication) can result in additional disk usage. Also, note that the tmpdir used for binary logging may be different from the one used for other operations, which could potentially contribute to the disk space issue.

4. Internal Temporary Tables: MySQL sometimes creates internal temporary tables while processing queries. These are not the same as the explicit temporary tables that you can create with the CREATE TEMPORARY TABLE statement. It's possible that one of your queries is triggering the creation of a large internal temporary table. You might be able to get more insight into this by examining the MySQL server's performance schema or status variables.If none of the above seem to apply, it might be helpful to get more information about what's happening when these queries run. The EXPLAIN statement can provide information about the query execution plan, which might help identify why such large temporary tables are being created. Additionally, enabling the slow query log might help identify any other queries that are running slowly or consuming a lot of resources.

If none of the above seem to apply, it might be helpful to get more information about what's happening when these queries run. The EXPLAIN statement can provide information about the query execution plan, which might help identify why such large temporary tables are being created. Additionally, enabling the slow query log might help identify any other queries that are running slowly or consuming a lot of resources.