Issue Problem and Question to ibdata1

[KlausZ]

Server operating system version

AlmaLinux 8.10

Plesk version and microupdate number

Plesk 18.0.80 Update7

Hello, I’m having an issue on an AlmaLinux 8.10 server with 10 subscriptions (10 domains, some of which use WordPress) where the disk space is getting smaller every day. I’m already getting warning messages, so I took a look: the ibdata1 file in /var/mysql is 66 GB. The databases for the individual domains are actually stored in their own databases. So what’s causing the ibdata1 file to grow so much? Can I view it directly on the server using phpMyAdmin? And most importantly, how can I reduce the size of the file again?

 

[Kaspar]

The data in the ibdata1 file cannot be viewed with phpMyAdmin (or any other database manager). It's the raw system tablespace file. Does it grow bigger in size every day?

The most common reason ibdata1 balloons to tens of GB is long-running or uncommitted transactions holding back the purge of undo logs. For example a backup process, a stuck WordPress plugin, or replication lag can all cause this. Every row change generates undo data that can't be reclaimed until the oldest open transaction commits, so undo logs (living inside ibdata1) can grow indefinitely.

The painful part is that InnoDB never shrinks ibdata1 on its own. Once it grows, it stays that size even if the data inside is deleted or purged. So if you are running out of disk space the only solution is to:

1. Dump all databases (mysqldump --all-databases)
2. Stop MySQL/MariaDB
3. Delete the ibdata1 and the ib_logfile* files
4. Restart the service (it recreates a fresh, small ibdata1)
5. Reimport all the dumps

But, it's worth investigating why it grows so much in the first place. Checking SHOW ENGINE INNODB STATUS for the "History list length" (a proxy for pending undo/purge work) can help diagnose why it grew, so it doesn't happen again. Chronic long transactions are worth hunting down before doing the rebuild.