0
209
ViewsMySQL undo log keep growing
MySQL undo log is keep growing to 180G and there is no rollback records and I just cant figure out why. As far that I know, it will do auto truncate when it reach innodb_max_undo_log_size which have set to 1GB. Any good solution for this? This is the query:
SELECT Name, ALLOCATED_SIZE/1024/1024/1024 AS SIZE (GB) FROM INFORMATION_SCHEMA.INNODB_TABLESPACES ORDER BY file_size DESC LIMIT 1;
|Name | SIZE (GB) |
|innodb_undo_001 | 183.953220367432 |
What is the thing that I should check? Will restart MySQL service will reduce the undo log size? Please help.
1 answers
Answer question0
Check for a transaction that is running for a very long time.
mysql> show engine innodb status\G
Now scroll to the last entries in the section
------------
TRANSACTIONS
------------
There you will see the longest running transactions. Here's an example:
---TRANSACTION 184428602997, ACTIVE 236 sec
8057 lock struct(s), heap size 980520, 2000277 row lock(s)
MySQL thread id 124353057, OS thread handle 0x7ee6ef041700, query id 6717837828 10.20.30.40 a_mysql_username cleaning up
Here you see that the transaction runs for 236 seconds. When you do
mysql> show processlist;
you probably won't see it in there with the same time. In the processlist the time column gives you the seconds since the last status change in the transaction. When the transaction executes a new query, the timer is reset to 0.
Anyway, what you also see in the example above is the mysql thread id for this transaction. Use this to kill the thread.
mysql> kill 124353057;
And your problem should be solved. This will take quite some time (actually for 180GB it will take ages), as the transaction gets rolled back. The same would happen however if you would restart the server. Don't restart, your server will just be down for quite some time. Just kill the thread and wait.