Comments on: MySQL: Disk Space Exhaustion for Implicit Temporary Tables

By: Corrado Pandiani

Corrado Pandiani — Mon, 15 Mar 2021 12:13:33 +0000

In reply to Muhammad Omair. Hi Muhammad, yes, the memory is not reclaimed. So, having persistent connections could lead after some time to more memory usage. When using persistent connection you should refresh them from time to time, this way you can reduce memory consumption. You can set for example a timeout on your connection pooler. How frequqntly should you refresh the connections? Not easy to say, it depends on your workload and available memory. You may monitor your server for some time and evaluated the memory usage. Usually refeshing connections once every few hours is good enough. Please have a look aslo at the new dedicated storage engine on MySQL 8.0 for managing imlicit temporary tables: https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html#internal-temporary-tables-engines

By: Muhammad Omair

Muhammad Omair — Thu, 11 Mar 2021 20:58:25 +0000

@Corrado excellent post. I had one question regarding the intrinsic tmp table creation in memory. Are intrinsic tmp tables created in memory by an sql query not dropped after its completion when it is using persistent db connections? And is the memory not reclaimed/freed up after query completion that had created intrinsic tmp tables in memory during its execution?

By: Marc M

Marc M — Mon, 07 Dec 2020 08:16:16 +0000

In reply to Corrado Pandiani. Noted! Thanks again! We also consider moving to Aurora for our next infrastructure iteration. Any pice of advice on that?

By: Corrado Pandiani

Corrado Pandiani — Tue, 01 Dec 2020 22:18:34 +0000

In reply to Marc M. Hi Marc M, I'm glad that you found my article useful. I'd like to let you know that MySQL 8.0 finally solved this issue since a new temporary tablespace was implemented to menage implicit temporary tables. The same issue shouldn't happen any more. Thank you

By: Marc M

Marc M — Tue, 01 Dec 2020 16:13:59 +0000

This post is awesome. It explains, in depth, a problem we have had last week on our production server (RDS, MySQL, InnoDB, with CPU at 100% and 100GB of disk disappearing in 2 hours).
It has been of such great help that I had to leave this message : warm thanks to the author!

By: Corrado Pandiani

Corrado Pandiani — Tue, 19 Nov 2019 14:24:16 +0000

In reply to Gennady. Hi gennady, first of all sorry for the very late response. I suppose that the problem is related to the queries you are running on the table. When you delete records from a table you are generating "free space". The free space can be resused by new INSERTs or for adjacent record enlargements because of UPDATEs. But not all the time this is possible. If you have a lot of fragmentation with very small emtpy blocks they cannot be reused. As long as you run your queries you can generate more and more free space in the table. From time to time you need to run manually OPTIMIZE TABLE. In fact MySQL doesn't have an automatica garbage collector.

By: Gennady

Gennady — Tue, 20 Aug 2019 19:12:37 +0000

Good post! Thank you.
I have a question about usual InnoDB tables growth though. We have InnoDB tables of a size about 60Gb, and when we migrated from 5.7 to 8.0, we started noticing that the tables gain size, without releasing it. Each 7 days they grow 5-10% of their size. And every week we run OPTIMIZE to reclaim that space back. What can cause such constant growth?

By: Adam Mulla

Adam Mulla — Mon, 22 Jul 2019 11:09:25 +0000

Thanks…Good stuff on important topic