1

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
RolandoMySQLDBA
  • 171,728
  • 30
  • 293
  • 486
Bohyun
  • 35
  • 3

2 Answers2

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

RolandoMySQLDBA
  • 171,728
  • 30
  • 293
  • 486
  • 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 05 '21 at 08:46
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.

Rick James
  • 66,863
  • 4
  • 38
  • 92