Why did MySQL database size grow from 13GB to 55GB after import?

Sharp MySQL Database Size Increase from 13GB to 55GB After SQL Import

A sharp increase in MySQL database size from 13GB to 55GB after importing from an SQL file is typically related to technical features of InnoDB, especially when changing environments. The main reasons include differences in InnoDB page sizes, data fragmentation, storage settings, and space allocation strategies between the source and target systems.

Contents

• Primary reasons for database size increase
• InnoDB page size differences
• Fragmentation and space allocation strategy
• Storage settings and file structure
• Solutions and optimization
• Diagnostics and monitoring
• Conclusion

Primary reasons for database size increase

The more than 4x increase in database size with identical data and table structure is related to fundamental features of MySQL InnoDB. As noted in research, InnoDB creates data pages and index pages of 16K each, and during insert, update, delete, commit, and rollback operations, data fragmentation occurs [source].

In your case when moving from Docker to a virtual machine via Bitrix24, several factors could have influenced the size:

1. Different InnoDB page size settings - if the original system used 8K pages while the target uses 16K pages, this could double storage requirements
2. Different fragmentation strategies - the original database might have had high fragmentation that was “reset” during export-import with reallocated space
3. Different innodb_file_per_table values - this setting determines whether tables are stored in separate files or shared table spaces

InnoDB page size differences

One of the most significant reasons could be the difference in InnoDB page sizes between environments. As noted in the MySQL documentation, MySQL 5.6 and later versions support different page sizes without recompilation [source].

Important points:

• Standard InnoDB page size: 16K
• Available options: 4K, 8K, 16K
• Page size affects storage efficiency and performance

In your case, if the original system used 4K or 8K pages while the target uses 16K pages, this explains the sharp increase in size. Even with the same amount of data, different page sizes lead to different numbers of pages to store the same data, especially with variable-length fields and indexes.

Fragmentation and space allocation strategy

Data fragmentation is a key concept that explains the size difference. As experts explain, during insert, update, and delete operations, you get partially filled pages [source].

InnoDB features:

• InnoDB reserves a percentage of pages for future growth so that B-tree pages can be allocated continuously
• The ability to change the percentage of reserved pages allows fine-tuning of InnoDB to solve fragmentation or inefficient storage usage problems [source]
• When you restore a database using mysqldump, you rebuild tables from scratch and get a less fragmented version of the database [source]

In your scenario:

1. The original database (13GB) had a certain level of fragmentation
2. During export via Bitrix24 and import into a new environment
3. Tables were recreated from scratch but with a new space allocation strategy
4. The new system allocated more reserved space for future index growth
5. As a result, the database became 55GB instead of 13GB

Storage settings and file structure

The innodb_file_per_table setting is critically important for understanding the size difference. This setting determines whether tables are stored in separate .ibd files or in shared table spaces.

Possible scenarios:

-- Check current setting
SHOW VARIABLES LIKE 'innodb_file_per_table';

-- If = 1 (ON) - tables in separate files
-- If = 0 (OFF) - tables in shared table spaces

Differences in your case:

1. In Docker: possibly used innodb_file_per_table = 0

   • All tables in one large data file
   • Efficient space usage with fragmentation
   • Size 13GB

2. In VM: possibly innodb_file_per_table = 1

   • Each table in a separate .ibd file
   • More overhead for metadata
   • Size 55GB

As noted in discussions, MySQL doesn’t rebuild the entire data file as soon as you delete a row, for obvious performance reasons [source]. As a result, when restoring in a different environment with different settings, space reallocation occurs.

Solutions and optimization

To reduce database size and improve performance, you can take the following steps:

1. Table optimization

-- Analyze fragmentation
ANALYZE TABLE your_table;

-- Optimization (defragmentation)
OPTIMIZE TABLE your_table;

2. Changing InnoDB page size

If the issue is page size, you can rebuild the database with the desired size:

-- Check current page size
SHOW VARIABLES LIKE 'innodb_page_size';

-- If different, you'll need export-import with new size

3. Configuring reserved space

-- Reduce percentage of reserved space
SET GLOBAL innodb_file_per_table = 1;
SET GLOBAL innodb_buffer_pool_size = 4G; -- 25-30% of RAM

4. Rebuilding tables from scratch

As experts recommend, to reduce InnoDB size, you need to export data and import it back [source]. This effectively removes fragmentation.

-- Export structure
mysqldump -u user -p --no-data database > structure.sql

-- Export data
mysqldump -u user -p --no-create-info database > data.sql

-- Import in new environment
mysql -u user -p database < structure.sql
mysql -u user -p database < data.sql

Diagnostics and monitoring

To understand the reasons for size increase and optimize performance, you need to conduct diagnostics:

1. Check page sizes

-- InnoDB page sizes
SHOW VARIABLES LIKE 'innodb_page_size';

-- Table file sizes
SELECT table_name, data_length, index_length 
FROM information_schema.tables 
WHERE table_schema = 'your_database';

2. Analyze fragmentation

-- Fragmentation information
SELECT table_name, data_free, table_rows 
FROM information_schema.tables 
WHERE table_schema = 'your_database';

3. Check storage settings

-- Key InnoDB settings
SHOW VARIABLES LIKE 'innodb_%';

4. Performance monitoring

Since you’re experiencing performance issues (queries up to 1 minute), you need to check:

-- Buffer pool size
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- Buffer pool usage
SHOW STATUS LIKE 'Innodb_buffer_pool%';

Conclusion

The sharp increase in MySQL database size from 13GB to 55GB after import is related to a combination of technical factors related to InnoDB features when changing environments. The main takeaways:

1. Difference in InnoDB page sizes between Docker and VM - the most likely cause of more than 4x size increase
2. Data fragmentation in the original database when restored in a new environment led to space reallocation
3. Storage settings (innodb_file_per_table, reserved space) significantly affect the final size
4. Performance decreased due to suboptimal buffer pool configuration and high disk load

To solve the problem, it’s recommended to check and unify InnoDB settings between environments, perform table optimization, and if necessary, perform a fresh export-import with correct parameters. It’s important to understand that when changing environments, MySQL requires configuration review to maintain performance and space efficiency.

Sources

1. Different MySQL Datafile Sizes After Restoration - Database Administrators Stack Exchange
2. MySQL :: MySQL 8.0 Reference Manual :: 17.11.2 File Space Management
3. mysql - why does mysqldump restore to a smaller size? - Database Administrators Stack Exchange
4. How do you remove fragmentation from InnoDB tables? - Database Administrators Stack Exchange
5. Overview of fragmented MySQL InnoDB tables - lefred blog
6. different database size after export and import - HeidiSQL Forum
7. mysql - Innodb page size setting - Stack Overflow
8. r/mysql on Reddit: Mysql.ibd file is huge(40 GB) whereas actual tables are 100 MB only