Reclaiming Leaked Storage from MariaDB InnoDB Data Dictionary & Orphaned Partitions

In high-velocity database environments characterized by continuous automated migrations, extensive table partitioning, and high-frequency temporary table creation, database administrators occasionally face an enigmatic storage anomaly: the sum of all table files on disk is tens of gigabytes smaller than the actual filesystem disk usage reported by df.

Even after dropping old partitioned tables or executing OPTIMIZE TABLE, the operating system filesystem does not reclaim space, or the system tablespace continues expanding uncontrollably.

This condition is typically caused by InnoDB Data Dictionary Bloat and Orphaned Tablespaces. When schema operations (DROP TABLE, ALTER TABLE ... DROP PARTITION) crash midway, suffer abrupt server reboots, or encounter lock timeouts, MariaDB’s internal metadata tables (SYS_TABLES, SYS_INDEXES, SYS_COLUMNS, and SYS_FIELDS) can become desynchronized from the actual .ibd files on disk. Orphaned tablespace IDs continue holding disk allocations, leaking storage and degrading database crash recovery times.

In this deep architectural guide, we dissect MariaDB’s data dictionary mechanics, detect orphaned tablespaces using INFORMATION_SCHEMA, and safely reclaim gigabytes of leaked storage without downtime.

---

How InnoDB Leaks Orphaned Tablespaces

Inside the InnoDB storage subsystem:

1. Every table and partition possesses a unique internal Space ID (space_id).
2. When a table is dropped, InnoDB must:
   • Mark the table deleted in the data dictionary.
   • Flush dirty pages from the Buffer Pool.
   • Call the filesystem unlink() system call to delete the physical .ibd file.
3. If an uncommitted transaction holds a handle to the table or if an abrupt power cycle interrupts the sequence, the dictionary record is deleted, but the physical file remains locked, or the physical file is removed while the data dictionary continues allocating pages in the system tablespace!

[ Failed DROP PARTITION or Mid-DDL Crash ]
                     │
         ┌───────────┴───────────┐
         ▼                       ▼
[ Orphaned .ibd File on Disk ]  [ Orphaned Entry in SYS_TABLESPACES ]
(Holds 20GB space; invisible to (Allocates internal segment IDs;
 MySQL SHOW TABLES commands!)    bloats ibdata1 system tablespace!)

Deploying database systems on bare-metal Dedicated Servers provides the dedicated NVMe storage controllers and battery-backed write caches needed to prevent metadata corruption during unexpected hardware faults.

---

Step 1: Identifying Orphaned Files on the Filesystem

To detect physical .ibd files that exist on the filesystem but have no corresponding table in the database schema:

#!/bin/bash
# Script to scan for orphaned .ibd files
DATADIR="/var/lib/mysql"

mysql -N -B -e "SELECT CONCAT(table_schema, '/', table_name, '.ibd') FROM information_schema.tables WHERE engine='InnoDB'" > /tmp/active_tables.txt

find "$DATADIR" -name "*.ibd" | sed "s|^$DATADIR/||" | while read -r ibd_file; do
    # Skip temporary or system files
    if [[ "$ibd_file" =~ ^(innodb_temp|undo_) ]]; then continue; fi
    
    if ! grep -Fxq "$ibd_file" /tmp/active_tables.txt; then
        FILE_SIZE=$(du -sh "$DATADIR/$ibd_file" | cut -f1)
        echo "[ORPHAN DETECTED] $ibd_file ($FILE_SIZE)"
    fi
done

Sample output:

[ORPHAN DETECTED] production_db/audit_log#P#p202512.ibd (18.4G)
[ORPHAN DETECTED] production_db/#sql-ib142-89214.ibd (12.1G)

Here, over 30 GB of NVMe storage is locked in orphaned files completely hidden from normal SQL queries!

---

Step 2: Safely Adopting and Purging Orphaned Tablespaces

Do not simply run rm -f on active .ibd files without disconnecting the InnoDB engine handle, as doing so will cause MariaDB to crash on the next checkpoint.

Scenario A: Cleaning Up Temporary DDL Leftovers (#sql-ib*)

If orphaned files start with #sql-ib:

# Verify that no active transaction is running
mysql -e "SELECT * FROM information_schema.innodb_trx\G"

# Safely drop or remove the abandoned DDL file
rm -f /var/lib/mysql/production_db/#sql-ib*.ibd

Scenario B: Adopting Orphaned Partition .ibd Files

To reclaim an orphaned table cleanly through the database engine:

-- Recreate a dummy table with identical schema structure
CREATE TABLE production_db.temp_recover (id INT PRIMARY KEY) ENGINE=InnoDB;

-- Discard its tablespace
ALTER TABLE production_db.temp_recover DISCARD TABLESPACE;

-- Copy the orphaned .ibd file over the dummy file
-- (In terminal: cp /var/lib/mysql/production_db/audit_log#P#p202512.ibd /var/lib/mysql/production_db/temp_recover.ibd)

-- Import and cleanly drop
ALTER TABLE production_db.temp_recover IMPORT TABLESPACE;
DROP TABLE production_db.temp_recover;

Dropping the adopted table forces MariaDB to update its data dictionary, clear buffer pool page references, and cleanly delete the file from the operating system.

---

Step 3: Preventing Data Dictionary Fragmentation in my.cnf

To ensure MariaDB automatically cleans up internal temporary dictionary tables and prevents fragmentation:

[mariadb]
# Ensure all user tables use independent tablespaces (never write user tables into ibdata1!)
innodb_file_per_table = 1

# Separate temporary tablespace to prevent ibdata1 bloat
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:20G

# Enable automated cleanup of undo and temporary memory maps
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

Restart MariaDB:

systemctl restart mariadb

---

Reclaim Impact: Before vs After Data Dictionary Audit

Storage Metric	Fragmented / Orphaned State	Post-Audit Cleaned State
Physical Disk Usage	412 GB	364 GB (48 GB Reclaimed!)
Orphaned Tablespace Files	14 files	0 files
Data Dictionary Lookup Latency	12.4 ms	0.8 ms
Server Crash Recovery Time	4 minutes 20 seconds	8.4 Seconds

Hosting your high-volume database nodes on enterprise Dedicated Servers in Pakistan guarantees high-end PCIe Gen4 NVMe arrays, robust storage monitoring, and seamless database health maintenance.