O'Reilly logo

MySQL 8 Cookbook by Karthik Appigatla

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

How to do it...

Verify the size of the UNDO logs:

shell> sudo ls -lhtr /var/lib/mysql/undo_00*-rw-r-----. 1 mysql mysql 19M Oct  7 17:43 /var/lib/mysql/undo_002-rw-r-----. 1 mysql mysql 16M Oct  7 17:43 /var/lib/mysql/undo_001

Suppose you want to reduce the files that are more than 15 MB. Remember, only one undo tablespace can be truncated. Selection of an undo tablespace for truncation is performed in a circular fashion to avoid truncating the same undo tablespace every time. After all the rollback segments in the undo tablespace are freed, the truncate operation runs and the undo tablespace is truncated to its initial size. The initial size of an undo tablespace file is 10 MB:

  1. Make sure that the innodb_undo_log_truncate is enabled:
mysql> ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required