How do I prevent ibdata1 from continually growing by over 1GB per hour?

Question

Like most DBAs I have two copies of my entire system, one running on a commercial website provider and one on my development system. Both are identical: the same options, the same software level, and exactly the same database contents.

On my development system the ibdata1 file is 397MB.

On the production system the ibdata1 file keeps growing until it has occupied every byte on the site. It is now 23GB, well over 50% of the 40GB I have as part of my contract. I cannot just keep paying for more disk space to satisfy MariaDBs design decision to dump information into this file even though I have set the separate file for each table option.

I shut down the server, deleted the ibdata1 file, restarted the server, reloaded the databases and still the ibdata1 file keeps growing. How do I stop this?

My entire site is down until I can fix this problem, so this is an emergency.

Answer 1

• Tables that were built while innodb_file_per_table = 0 will live in ibdata1. (You imply that this was never the case, but double check how you did the reload.)
• Certain temp tables and other activities temporarily use space in ibdata1.
• ibdata1 only grows; never shrinks. But space is reused, so it can stay at a constant size.
• (This seems like the most likely explanation.) Big modifications grow the disk space for a single table, but won't shrink it. (This applies whether it is in ibdata1 or a separate file.) Example: UPDATE big_table ... that changes most rows. Similarly, with DELETE. Rethink such actions; there are often better ways, perhaps involving a change to the schema. (If this bullet item is relevant, start a separate Question with the specifics.)
• A variant on the previous item: When multiple connections are concurrently making changes to the same table, the "history list" maintains various versions of each row that is being modified. Such is not cleaned up until COMMIT.
• This may be an example of the problem ballooning: Multiple connections increment a "Likes" counter in a main table that many other connections use. For this, I recommend moving t the counter to a separate table and separate transaction.
• Similarly "Normalization" should be done before the main transaction. My blog shows a way to batch Normalizations