MySQL IDB File grows huge despite having little data

Question

We have recently migrated to a new database server with mysql 8 (from 5.7 previously). We now have a table that stores an int, a datetime and a mediumtext column that grows unproportionally huge. So much so that the server's complete hard drive space was depleted.

The problem is the table only contains about 100 rows of data and the idb file after an optimize table is 7 Mb. after a week running the table grew to over 700GB.

The table does receive a lot of deletes+inserts. We had the same table on the old server for years without issue.

i tried

innodb_purge_threads = 32
innodb_max_purge_lag = 2000000

But the table continues to grow at about 20Mb per Minute without any obvious reason. Am I missing any special new mysql feature?

Or will I need to have my apps run an optimize table every couple minutes now. It seems the database should be handling this issue by itself instead of needing to rely on such hacks from applications.

Thanks in advance for any pointers!

The MySQL Server is Version 8.0.18 running on Windows 2019 with 32GB of RAM. Nothing else runs on this server and the database server is an upgrade from an older version that had a third of this specs. The table structure is identical and ran for years without issue on the old server.

Below is the table structure:

The following is the Show Create Table
CREATE TABLE `automationjobstate` (
  `AutomationJobID` int(10) unsigned NOT NULL,
  `StateTimestamp` datetime NOT NULL,
  `StateData` mediumtext,
  PRIMARY KEY (`AutomationJobID`),
  CONSTRAINT `automationjobstate_ibfk_1` FOREIGN KEY (`AutomationJobID`) REFERENCES `automationjobs` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

How do I attach the results from SHOW VARIABLES? It says its to many characters.

Answer 1

Some 30 hours now have past and the table in question (i.e. its idb file) has less disk space occupied than expected. The application that uses the table had been offline for a couple of hours, too, and hence, the table has been shrunk again. It still shows its growing behaviour once the application is coming back online again.

It appears now, that there is a purge system within mysql that does not quite keep up with usage of that table - despite the older 5.7 MySQL on much older hardware didn't have that issue.

I suppose that this realization is the answer to my original question and that I will have to tweak the innoDB purge parameters to find optimal settings, or maybe trigger Optimize Table from the Application every once in a while.