MariaDB: how to reduce ibdata file size

Question

I found that My MariaDB's ibdata file keep increasing. So, I've searched for this, and found that innodb_file_per_table should be set as 1. but, my DBMS's configuration has already set as 1;

Why ibdata file size keep increasing and what else should I do for this.

These below are my dbms info.

DBMS: MariaDB
engine: InnoDB Engine
version: 10.3
** my.cnf **
innodb_file_per_table=ON
transaction-isolation=READ-COMMITTED

Answer 1

The first thought that came to mind was "Wow !!! You must have rather big transactions."

According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace, these are the main issues that cause ibdata1 to grow:

• Lots of Transactional Changes
• Very Long Transactions
• Lagging Purge Thread

The undo logs inside ibdata1 will hold lots of info to support transaction isolation levels. Since you are using READ-COMMITTED, this growth can be more significant.

I have discussed this before in the DBA StackExchange

• Aug 21, 2015 : Transactional DDL workflow for MySQL
• Jun 16, 2014 : MySQL Index creation failing on table is full
• Apr 23, 2013 : How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?
• ... and more

SUGGESTION #1

Since you are using MariaDB, please look into configuring the undo logs outside of the system tablespace. Please read the MariaDB docs on innodb_undo_directory and innodb_undo_tablespaces and go from there.

SUGGESTION #2

You may have to mysqldump are your data and load it into a new installation of MariaDB. See my posts in How do I shrink the innodb file ibdata1 without dumping all databases? for other ideas. Either way, this will take some work !!!

• Bohyun: Very informative answer and clarified the issue. I think I need to restart database to change innodb_undo parameters. but still I have some questions. 1. Is there recommended size of innodb_max_undo_log_size? 2. I'm gonna use mysqldump to export database, do I have to "reinstall MariaDB"? or "just remove ib* files and then reload *.sql files which I exported."

Answer 2

This topic has also been discussed in MDEV-21952. In the upcoming MariaDB Server 10.6.13 release, MDEV-29593 should improve reuse of undo log pages. Starting with MariaDB Server 10.11 (the newest long-term-support release series), thanks to MDEV-19229 you can change the value of innodb_undo_tablespaces after SET GLOBAL innodb_fast_shutdown=0; SHUTDOWN;.

Tables that reside in the InnoDB system tablespace should be reported by the following query:

SELECT name FROM information_schema.innodb_sys_tables WHERE space=0;

Last but not least, MDEV-27734 set innodb_change_buffering=none by default not only because it can make the system tablespace grow uncontrollably, but also because of various corruption issues that have affected MySQL and MariaDB alike.

Edit: The recently released MariaDB Server 11.2.1 includes MDEV-14795, which allows the InnoDB system tablespace to be shrunk on startup if innodb_data_file_path includes the attribute :autoshrink. This could be useful in combination with the two changes in MariaDB Server 11.0: MDEV-29986, which made innodb_undo_tablespaces=3 the new default, and MDEV-29694, which removed the InnoDB change buffer.

Answer 3

(Rolando's answer addresses ways for that file to grow; I'll address the question of whether tables are in ibdata1 and you don't realize it.)

Was this always set? innodb_file_per_table=ON -- I ask because it only applies to tables that were created after it is turned ON.

Do SHOW TABLE STATUS in each directory. Observe Data_free. Some likely values you will see:

• 0 -- Small table
• 4M, 5M, 6M, 7M -- medium or large table
• some number, possibly large, that is repeated for many tables. -- probably created with innodb_file_per_table=OFF; that is, living in ibdata1.

The above test is inconclusive but relatively quick to do.

There is a version-dependent way to use information_schema to discover file-per-table; I don't happen to know the table and column for MariaDB 10.3.