Undo size increasing Percona MySql 5.6

MySQL & MariaDB Percona Server for MySQL 5.6
1

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
2

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
3

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
4

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
5

thanks for the information.

1 Like
6

thanks for the awesome information.

1 Like
7

@alexsunny123 You might want to check your computer for spyware. Your last post included some injected URLs

1 Like
8

Hi Team,

Greeting!

Any reason behind that the undo size getting increasing.(mysql 5.6)

-rw-rw---- 1 mysql mysql 77G Sep 14 14:31 undo011
-rw-rw---- 1 mysql mysql 85G Sep 14 14:40 undo007
-rw-rw---- 1 mysql mysql 85G Sep 14 14:47 undo006
-rw-rw---- 1 mysql mysql 85G Sep 14 15:00 undo002
-rw-rw---- 1 mysql mysql 85G Sep 14 15:03 undo003
-rw-rw---- 1 mysql mysql 92G Sep 14 15:07 undo001
-rw-rw---- 1 mysql mysql 85G Sep 14 15:07 undo004
-rw-rw---- 1 mysql mysql 77G Sep 14 15:07 undo009
-rw-rw---- 1 mysql mysql 77G Sep 14 15:13 undo012
-rw-rw---- 1 mysql mysql 77G Sep 14 15:15 undo008
-rw-rw---- 1 mysql mysql 85G Sep 14 15:16 undo005
-rw-rw---- 1 mysql mysql 77G Sep 14 15:17 undo010

regards,
Daljit Singh

1 Like