Undo size increasing Percona MySql 5.6

Hi Guys,

Please suggest me how i can truncate or drop undo tablespace because its size increasing and below are the parameter setting.

innodb_version | 5.6.34-79.1

(root@)>show variables like ‘innodb_undo%’;
±------------------------±------+
| Variable_name | Value |
±------------------------±------+
| innodb_undo_directory | . |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 12 |
±------------------------±------+

-rw-rw---- 1 mysql mysql 38G May 9 09:36 undo004
-rw-rw---- 1 mysql mysql 35G May 9 09:36 undo012
-rw-rw---- 1 mysql mysql 38G May 9 09:36 undo002
-rw-rw---- 1 mysql mysql 38G May 9 09:36 undo003
-rw-rw---- 1 mysql mysql 35G May 9 09:36 undo009
-rw-rw---- 1 mysql mysql 35G May 9 09:36 undo011
-rw-rw---- 1 mysql mysql 35G May 9 09:36 undo010
-rw-rw---- 1 mysql mysql 38G May 9 09:36 undo006
-rw-rw---- 1 mysql mysql 38G May 9 09:36 undo005
-rw-rw---- 1 mysql mysql 35G May 9 09:36 undo008
-rw-rw---- 1 mysql mysql 38G May 9 09:36 undo007
-rw-rw---- 1 mysql mysql 42G May 9 09:36 undo001

Thanks

1 Like

Hi Daljit1,

Sadly, on 5.6 it’s not possible to truncate the undo tablespaces MySQL :: MySQL 5.6 Reference Manual :: 14.6.3.3 Undo Tablespaces , but it’s possible on 5.7: MySQL :: MySQL 5.7 Reference Manual :: 14.6.3.4 Undo Tablespaces

Do note that MYSQL 5.6 already reached EOL and is strongly suggested to upgrade to 5.7

I see that all the undo tablespaces were updated “around the same time”. Chances are there was a very large transaction ongoing, and the inability of MySQL purge threads to remove undo entries until the transaction finished, made the files grew so much.

Next time undo logs are needed, the existing files unused internal space will be used instead of growing the files again (unless you do a larger transacion that requires even more undo log entries).

It’s suggested that for large transactions, you split them un chunks, and also execute them during off peak hours because of MVCC MySQL :: MySQL 5.6 Reference Manual :: 14.3 InnoDB Multi-Versioning

Regards

1 Like

Hi C Tutte,

Thanks a lots for described in details and quick response.

I will upgrade with 5.7 version soon and please send me document upgradation from 5.6 to 5.7 step by step.

I really like the Percona community.

regards,
Daljit Singh

1 Like

Hello @Daljit1,
Upgrading to 5.7 from 5.6 is very simple. Stop 5.6. Install 5.7 packages (replace 5.6). Start 5.7. Very simple. Be sure to take a complete backup of the 5.6 data directory after you stop mysql. Just copy the directory somewhere safe.

1 Like

thanks for the information.

1 Like