Hi, Thanks for the very clear answer, Gordan.
Several new-to-me options there that nicely coincide with a mariadb upgrade to versions that support them. I shall do some reading. Appreciated, thanks. Simon -----Original Message----- From: Gordan Bobic <[email protected]> Sent: 18 February 2026 11:38 To: Simon Avery <[email protected]> Cc: [email protected] Subject: Re: [MariaDB discuss] Shrinking ibdata1 without a full dump - will it ever be possible? [You don't often get email from [email protected]. Learn why this is important at https://aka.ms/LearnAboutSenderIdentification ] On Wed, 18 Feb 2026 at 12:49, Simon Avery via discuss <[email protected]> wrote: > > Hello > > I have 26 legacy and standalone MariaDb 10.11.x servers which have an ibdata1 > file larger than 10Gb. The largest is 276Gb (total DB's on that server are > around 700G) and there's another 3 servers where it’s over 100Gb. I track > rate of growth, and some grow by over 1Gb/day, so a fair bit of our storage > is given over to this problem. > > Our newer servers have been built for some years with > "innodb_undo_tablespaces=3" and that works brilliantly at keeping things > sensible, but we've got a lot of legacy production servers. Half of these > we're migrating from Rocky 8 to Debian 13, including a lift of mariadb 10.11 > to 11.8 by means of moving the mysqldir vmdk from old to new, which is > working well so far, but of course this just brings the problem over to the > new host. > > I'm aware of the traditional fix, which is to do a full sqldump, wipe, add > the above and restore. Unfortunately, many of our databases are well over a > Tb now, with our largest being 11Tb. Dumping that and restoring it, even with > fast storage, is an amount of downtime we just can't arrange. The way to do it without downtime is to do mydumper it (massively parallel mysqldump) and restore from that onto a clean machine, get it replicating from the old server, and then when replication has caught up, promote the new server and decommission the old one. > My response so far has been to kick the problem down the road and hope that > one day, the clever people at MariaDb will somehow invent a method to > automatically solve this without needing a dump/restore and affecting > downtime. > > Am I being naively optimistic in thinking this is solvable and one day might > be? Or has it already been solved and I just didn't get the memo? The problem is partly solved since 11.2: https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-tablespaces/innodb-system-tablespaces#decreasing-the-size with caveats that there are limitations: https://jira.mariadb.org/browse/MDEV-36722 The guys over at FromDual put together an experimental method that sort of partly works most of the time: https://www.fromdual.com/blog/shrinking-innodb-system-tablespace-file-ibdata1-poc/ with the caveats that: 1) It will not reclaim anywhere near all the space that could be reclaimed 2) It is dangerous - and if it goes wrong, corrupted database means it's time to restore from backup. So if what 11.2+ achieves with autoshrink configuration adjustment isn't sufficient in your case, dump/restore is probably the only option you are going to have in the foreseeable future. -- Gordan Bobic Database Specialist, Shattered Silicon Ltd. https://shatteredsilicon.net Follow us: LinkedIn: https://www.linkedin.com/company/shatteredsilicon X: https://x.com/ssiliconbg _______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
