Re: threads in cleaning up mode
- Date: Mon, 04 Apr 2016 10:21:38 -0400
- From: "shawn l.green" <shawn.l.green@xxxxxxxxxx>
- Subject: Re: threads in cleaning up mode
On 4/2/2016 10:36 AM, geetanjali mehra wrote:
Hi to all,
For many days, I am struggling with the problem of increasing history list
length on mysql 5.6.29. Application that this server is running IOT based.
Also, this server has so many threads running in sleeping mode. SHOW ENGINE
INNODB STATUS shows all these threads in *cleaning up* mode.
I tried all the options to reduce history list length. But it is constantly
Below are the current settings of purge related threads:
innodb_max_purge_lag | 1000000 |
| innodb_max_purge_lag_delay | 0 |
| innodb_purge_batch_size | 1 |
| innodb_purge_threads | 8 |
Also, please let me know that whether cleaning up mode of threads and
history list length are correlated.
Senior Database Administrator
No, the "cleaning up" status and your ever-increasing history are not
related. The "cleaning up" status is a very low-impact bug in our code.
We simply forgot to reset a flag at the end of that section of the code.
It is not indicative of any ongoing operations.
What has happened is one of those idle threads (the sleeping ones) has
opened a transaction but never committed it. That is why your history
list is never shrinking. As was already said in another response, this
is an indication of poor client-side transaction management. If you
start a transaction, you must always terminate it or situations like
yours will happen. (This is also one of the dangers if someone changes
autocommit=0 and forgets to change it back.)
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN
Become certified in MySQL! Visit https://www.mysql.com/certification/
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql