3

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

3 Answers 3

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

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

1
  • 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."
    – Bohyun
    Feb 5, 2021 at 8:46
6

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.