MariaDB Maximum Table Size (innodb_file_per_table) - Limits & Optimization

What Is innodb_file_per_table?

innodb_file_per_table is a MariaDB setting that allows each InnoDB table to have its own .ibd file instead of storing all data in a single shared ibdata1 file.

Benefits of innodb_file_per_table:

• Better performance for large databases.
• Easier backup & migration of individual tables.
• Reclaim space when a table is deleted (unlike shared tablespaces).

Maximum Table Size Limits in MariaDB

The maximum table size depends on multiple factors:

InnoDB is recommended for large databases due to better crash recovery and indexing.

Check If innodb_file_per_table Is Enabled

Run:

SHOW VARIABLES LIKE 'innodb_file_per_table';

Expected output (enabled):

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

If OFF, enable it in my.cnf (next step).

Enable innodb_file_per_table (If Not Already Enabled)

Edit my.cnf Configuration File

sudo nano /etc/mysql/my.cnf # Ubuntu/Debian

sudo nano /etc/my.cnf # CentOS/RHEL

[mysqld]
innodb_file_per_table = 1
innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M

Restart MariaDB to Apply Changes

sudo systemctl restart mariadb

Now each InnoDB table will have its own .ibd file.

Convert Existing Tables to innodb_file_per_table

If innodb_file_per_table was previously disabled, convert existing tables:

Optimize Each Table

ALTER TABLE my_table ENGINE=InnoDB;
OPTIMIZE TABLE my_table;

This moves the table to its own .ibd file.

Optimize All Tables in a Database

Run this SQL command:

SELECT CONCAT('OPTIMIZE TABLE ', table_schema, '.', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'your_database';

Copy and execute the generated OPTIMIZE TABLE statements.

Now all tables are converted to their own files.

Increase InnoDB Table Size Limit (If Needed)

If you need larger tables, modify my.cnf:

[mysqld]
innodb_file_per_table = 1
innodb_data_file_path = ibdata1:12M:autoextend
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 2

Restart MariaDB

sudo systemctl restart mariadb

Now MariaDB supports larger tables.

Check Your Table Size

Find the Size of a Single Table

SELECT table_name, ROUND((data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.tables
WHERE table_schema = 'your_database' AND table_name = 'your_table';

This shows how much space your table uses.

Find the Size of All Tables in a Database

SELECT table_name, ROUND((data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.tables
WHERE table_schema = 'your_database';

This helps you monitor large tables.

Troubleshooting Table Size Issues

Table Is Still Growing?

• Check free space:

  df -h

• Clean up unnecessary data:

  DELETE FROM my_table WHERE created_at < NOW() - INTERVAL 1 YEAR;
  OPTIMIZE TABLE my_table;

Table Size Not Reducing After Deleting Rows?

• Run:

  OPTIMIZE TABLE my_table;

  This reclaims disk space.

Summary

Now you can manage large MariaDB tables efficiently!