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.
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.