Large ibdata1 file

As you are using MySQL, the size of the ibdata1 file grows. MySQL, as well as its forks, such as MariaDB and Percona, has a long-standing bug, which has been reported more than 15 years ago (MySQL bug 1341) and still has not been solved. When some data is saved to ibdata1 and later deleted, the space will not be reclaimed. The only solution is to dump all databases, initialize new database directory and re-import the data. This is a time-consuming operation, which requires a maintenance window.

Historically, ibdata1 included all data about InnoDB tables - the data itself, indexes, undo logs etc. Due to the aforementioned bug, the developers have added ways to move the data elsewhere.

Solution

The most widely used setting is innodb_file_per_table, which moves the data, along with indexes, to individual files. You can turn this on even on an existing database directory. However, the space in ibdata1 will only be freed for other MySQL purposes - the disk space will not be reclaimed.

Usually, this should solve the problem. But if it does not, you need to analyze the ibdata1 with innochecksum or innodb_space. These will show you what kind of data is stored in the file. The most common cause is UNDO_LOG. It is also the only data type, that you can offload elsewhere. UNDO_LOG is used for transactions (it contains data that make rollbacks possible). If you avoid large transactions, you should not run into the problem. By configuring innodb_undo_tablespaces and innodb_undo_directory, you can create a separate storage for UNDO_LOGs. And innodb_undo_log_truncate will ensure, that the space in those files will be properly freed up. Changing these settings is not possible on an existing data directory. Therefore, you need to dump all data, initialize a new directory and re-import the databases.