Very large ibdata1 growth (335Gb) with 10.6.13, solved in 10.6.14 ?
Hi All,
We run around 60 Mariadb 10.6 servers on EL in vms and varying workloads.
Some time after they updated from 10.6.12 to 10.6.13, four of the busiest started showing higher disk usage than expected. Upon investigation, /var/lib/mysql/ibdata1 was exceptionally large, up to 335Gb, and was still growing at several Gb/day.
Note: All servers have only run with "innodb_file_per_table" set and only have innodb engines for user DBs.
I cobbled together some data collection just before I went on holiday and checked the graphs today. (Pic attached - if not stripped by the list software)
[A picture containing screenshot, line, parallel Description automatically generated]
Four machines growing, rest static.
Two affected machines stopped growing a week ago - and on checking, Mariadb was upgraded from 10.6.13 to 10.6.14 on them and they were rebooted then. The two still growing are still running 10.6.13
My questions!
1. Am I right that this was a known bug in Mariadb and has been resolved with the .14 release?
2. The .14 release notes mention "InnoDB does not free UNDO after the fix of MDEV-30671 (MDEV-31234)" - is the failure to free UNDO logs a likely cause for ibdata1 to grow, given that it stores the undo log? (The two worst affected machines process many million queries a day)
3. To recover this space, I will need to do a full mysqldump, wipe and reimport of the data, correct?
I guess I'm mostly asking for reassurance that this issue is now resolved and that once I schedule in the downtime to recover the space, that it's not going to resurface...
Thanks,
Simon
On 19.06.2023 15:44, Simon Avery via discuss wrote:
My questions!
1. Am I right that this was a known bug in Mariadb and has been resolved with the .14 release?
Yes, we improved the purge thread to try harder to free UNDO records in MariaDB 10.6.14.
2. The .14 release notes mention “InnoDB does not free UNDO after the fix of MDEV-30671 (MDEV-31234)” – is the failure to free UNDO logs a likely cause for ibdata1 to grow, given that it stores the undo log?
Yes.
3. To recover this space, I will need to do a full mysqldump, wipe and reimport of the data, correct?
Unfortunately yes. You can avoid that in the future by creating separate UNDO table spaces. Put this in your my.cnf:
innodb_undo_tablespaces = 3
see https://mariadb.com/kb/en/innodb-system-variables/#innodb_undo_tablespaces for a discussion of the value 3.
This will create 3x 10MB table spaces for the UNDO logs. If those will grow again, you can shrink them:
SET GLOBAL innodb_undo_log_truncate = ON
but make sure to set it back to OFF afterwards and do it only in times of low database activity because it can have some impact on performance.
HTH
Hello Axel,
Thank you for your reply on this for answering my questions and for the very useful suggestion of setting innodb_undo_tablespaces in future.
Much appreciated,
Simon
participants (2)
-
Axel Schwenke -
Simon Avery