mariadb: innodb_file_per_table=On and still have a huge ibdata1



  • I have a mariadb server for TEST on a Linux OS.

    I set innodb_file_per_table=1 in config file and restarted the database. I checked in Database and it was activated. I checked the ibdata1 file and it was 12M.

    Then I created a test database and test data.

    Then I started a 1 hour benchmarking for this table with 10 milion rows and 1000 threads. To my undrestanding all data should be saved in test database (test directory)

    After 1 hour benchmarking the ibdata1 got to 15G, binlog directory to 20G and the test database directory (test.ibd ) got to 3,5G.

    I wanna underestand why the ibdata1 grew too much (actually more than the data in test directory)? What is in this file right now? Will it shrink automatically? How should I deal with this issue?

    Thanks in advance.



  • Caveat: I don't use MariaDB, so I don't have in my recall which versions of MariaDB have some of the features I describe below regarding the system tablespace. MariaDB started as a fork of MySQL and InnoDB in 2010, but both products have been changing gradually since then, so you shouldn't think of MariaDB as compatible with MySQL anymore.

    The system tablespace (ibdata1) file never shrinks. This was reported in the MySQL bug tracker in 2003 ( https://bugs.mysql.com/bug.php?id=1341 ), but it has never changed. The reason is that it's hard to do that without downtime, and the workaround exists to use innodb_file_per_table and other recent features of MySQL.

    Even if you set innodb_file_per_table=1, this does not change tables that were created before you made that configuration change. Tables are not immediately pulled out of the system tablespace. It only applies to tables created after the change.

    But you said you did create a test database and test data subsequently making that configuration change. So it should be in its own tablespace (an .ibd file under the subdirectory for its database). You can check this by examining the tablespace id for your table in the information_schema:

    SELECT name, space FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE name LIKE 'mydatabase/%';
    

    (You should substitute your test database name where I wrote mydatabase.)

    That's how I would query it in MySQL 8.0. I think MariaDB has a similar table: https://mariadb.com/kb/en/information-schema-innodb_sys_tables-table/

    You can check to see what tables are still in the system tablespace:

    SELECT name FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE space = 0;
    

    Besides tables, InnoDB uses the system tablespace for some other operations. Depending on the version of MySQL, it stores:

    • https://dev.mysql.com/doc/refman/8.0/en/innodb-temporary-tablespace.html , but in MySQL 5.7 and later it became normal for temporary tables to be stored in their own tablespaces.
    • https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-logs.html , but in MySQL 5.7 and later it became normal for undo logs to be stored in a separate tablespace, or the global temporary tablespace, or the system tablespace.
    • https://dev.mysql.com/doc/refman/8.0/en/innodb-change-buffer.html is normally in the buffer pool, but is periodically persisted to the system tablespace.
    • https://dev.mysql.com/doc/refman/8.0/en/innodb-doublewrite-buffer.html is quite small, so not likely to be a great factor in increasing the size of the system tablespace. In MySQL 8.0.20 and later, the doublewrite buffer is stored in its own set of files outside the system tablespace.

    The non-table use of the system tablespace is temporary in nature and depends on workload, so it's possible that your benchmark cause the file to expand briefly, but now many of the pages it allocated are empty. The system tablespace still never shrinks, so unfortunately it's stuck at the "high-water mark" of its past use.

    The only way to shrink the system tablespace is still to start over with a new instance of MySQL/MariaDB, initialized with a small system tablespace. Load it with data dumped from your current instance.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2