How a MySQL Table with No Data Occupies GBs of Disk Space

Background

In a production environment, we observed that many ibd files occupied significant disk space, yet corresponding tables appeared empty. Initial speculation suggested archived tables without defragmentation caused the issue. However, binlog analysis revealed no massive DELETE operations. Further investigation using the general log showed that an application backup script—executed nightly via INSERT INTO ... SELECT * FROM large_table—triggered transaction rollbacks due to exceeding max_binlog_cache_size. This prevented tablespace reclamation.

Problem Reproduction

1. ​Create Test Data:

-- Generate a 10M-row table  
CREATE TABLE `sbtest1` (...) ENGINE=InnoDB;  
INSERT INTO `sbtest1` SELECT * FROM sysbench.sbtest;  

-- Verify table size  
SELECT name, FILE_SIZE/1024/1024/1024 AS GB  
FROM information_schema.INNODB_TABLESPACES  
WHERE name = 'test/sbtest1';  
-- Output: ~2.27 GB  

‍

​2. Simulate Backup Workflow:

SET GLOBAL max_binlog_cache_size = 1 * 1024 * 1024 * 1024; -- 1GB  
CREATE TABLE `t1` LIKE `sbtest1`;  
INSERT INTO `t1` SELECT * FROM `sbtest1`;  
-- Error: Transaction exceeds max_binlog_cache_size  

SELECT COUNT(*) FROM `t1`; -- Returns 0  
SELECT FILE_SIZE/1024/1024/1024 AS GB FROM ... -- Still ~1.34 GB  

‍

Root Cause

The INSERT ... SELECT operation generated a massive transaction that exceeded max_binlog_cache_size, triggering a rollback. However, InnoDB had already allocated disk space for the transaction’s temporary data, which was not released after the rollback.

Solutions

1. Temporary Fix:​​ Increase max_binlog_cache_size to accommodate large transactions

SET GLOBAL max_binlog_cache_size = 4 * 1024 * 1024 * 1024; -- 4GB (for 64-bit systems)  

‍

2. Long-Term Optimization:​​

1. Partition data by date (e.g., t1_202310) instead of full-table backups.
2. Use tools like pt-archiver for incremental backups to reduce memory pressure.

Key Takeaways

• Avoid oversized transactions to prevent cache exhaustion.
• Regularly monitor INNODB_TABLESPACES for unexpected space usage.
• Follow best practices for backup strategies to minimize resource contention.

‍