Why Database Size Grows After Import: Causes and Solutions

After importing a database from a .sql file, the table size increased from 13GB to 55GB mainly due to InnoDB table fragmentation, Docker and virtual machine specifics, and MySQL configuration differences. This problem occurs when moving databases between different environments when the space occupied by tables is not properly optimized.

Table of Contents

• Main reasons for database size increase
• Role of InnoDB fragmentation
• Differences between Docker and virtual machine
• Performance issues
• Database size optimization solutions
• Step-by-step reduction guide

Main reasons for database size increase

A 4-5 times increase in database size after import is a common problem when transferring MySQL between different environments. According to research from Database Administrators Stack Exchange, there are several key factors:

InnoDB table fragmentation

• Data import through Bitrix24 or other tools is performed suboptimally
• Physical data location on disk becomes inefficient
• Filled table pages contain unused space

MySQL configuration differences

• Even with the same MySQL version (5.7.44-48), configuration parameters may differ
• Different settings for innodb_file_per_table, innodb_buffer_pool_size
• Differences in logging and recovery parameters

Environment-specific characteristics

• Docker containers have I/O operation limitations
• Virtual machines may use a different file system
• Different mount point and file system settings

Role of InnoDB fragmentation

The InnoDB storage engine is the primary cause of database size increase. As explained in the official MySQL documentation, when innodb_file_per_table is enabled (by default), each table is stored in a separate .ibd file.

Fragmentation mechanisms:

• Internal fragmentation: Table pages contain unused space
• External fragmentation: Physical location of data fragments on disk is suboptimal
• Index fragmentation: Indexes may occupy more space than necessary

Important: Even with identical table structure and data, the physical representation of this data in InnoDB can vary significantly depending on the import and recovery operation history.

As shown in practice on Server Fault, fragmentation intensifies particularly when importing large volumes of data in a single operation.

Differences between Docker and virtual machine

Transferring a database from a Docker container to a virtual machine introduces several architectural differences that can affect database size and performance:

Docker limitations:

• I/O operations may be limited
• File system may be based on overlayfs
• Different locking and synchronization settings

Virtual machine:

• Direct access to disk resources
• Different file system settings (ext4, XFS, etc.)
• Different OS caching parameters

Key difference: In Docker containers, the file system may handle write operations differently than on a virtual machine, leading to different table space allocation.

As noted on Stack Overflow, when working with large databases in Docker, there may be specific issues with space management.

Performance issues

Increasing the database size to 55GB directly impacts query performance:

Memory issues:

• 55GB database doesn’t fit entirely in the InnoDB buffer pool
• MySQL constantly performs disk read operations
• Indexes are not fully loaded into memory

I/O operations:

• Queries hang for up to 1 minute due to disk operation waits
• Increased response time even for simple queries
• Conflicts between reading and writing

According to research from Database Administrators Stack Exchange, database size shouldn’t significantly impact performance provided that indexes fit in memory. In your case, 55GB significantly exceeds typical buffer pool sizes.

Database size optimization solutions

To reduce database size to optimal values, several effective methods exist:

Table rebuilding:

ALTER TABLE your_table ENGINE=InnoDB;

Fragmentation optimization:

OPTIMIZE TABLE your_table;

Fragmentation analysis:

SELECT table_schema, table_name, data_free 
FROM information_schema.tables 
WHERE table_schema = 'your_database';

Moving to separate tablespace:

ALTER TABLE your_table TABLESPACE new_ts;

Important: OPTIMIZE TABLE and ALTER TABLE operations require free disk space, temporarily increasing disk space usage.

Step-by-step reduction guide

Step 1: Analyze current state

-- Check fragmentation
SELECT 
    table_name,
    data_length,
    index_length,
    data_free,
    (data_free / 1024 / 1024) as free_space_mb
FROM information_schema.tables 
WHERE table_schema = 'your_database'
ORDER BY data_free DESC;

Step 2: Stop application

• Suspend applications using the database
• Ensure new connections are denied

Step 3: Optimize tables

-- For each table with high data_free value
OPTIMIZE TABLE large_table1, large_table2, ...;

Step 4: Configure MySQL parameters

# my.cnf
innodb_buffer_pool_size = 16G
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

Step 5: Monitor results

-- Check size after optimization
SELECT 
    table_name,
    ROUND(data_length / 1024 / 1024 / 1024, 2) as size_gb,
    ROUND(index_length / 1024 / 1024 / 1024, 2) as index_gb
FROM information_schema.tables 
WHERE table_schema = 'your_database';

Step 6: Regular maintenance

• Schedule periodic execution of OPTIMIZE TABLE
• Monitor fragmentation through scripts
• Plan maintenance during low traffic periods

Sources

1. Why MySQL Database is Bigger After Dump/Import? - Database Administrators Stack Exchange
2. InnoDB File Space Management - MySQL 5.7 Reference Manual
3. MySQL tables grow very large even though purger is enabled - IBM Support
4. MySQL .ibd file is too big - Database Administrators Stack Exchange
5. How to Allow Use of Larger Database Sizes in MySQL Docker Container on MacOS - Stack Overflow
6. Database size twice as large after mysql dump and re-import - Database Administrators Stack Exchange

Conclusion

The increase in database size from 13GB to 55GB after import is a result of InnoDB fragmentation and differences in operation between Docker and virtual machines. Key takeaways:

1. Fragmentation - the primary cause of size increase, especially when importing large volumes of data
2. Performance directly depends on the ratio of database size to InnoDB buffer pool
3. Optimization through OPTIMIZE TABLE and MySQL parameter configuration allows restoring optimal size
4. Prevention requires regular fragmentation monitoring and proper MySQL configuration
5. Transition between environments requires special attention to file system parameters and I/O operations

To solve the current problem, it is recommended to perform step-by-step table optimization and configure MySQL parameters for your infrastructure. Regular database maintenance will prevent similar issues in the future.