Web lists-archives.com

Re: threads in cleaning up mode




Hi,
It sounds like a bad case of transactions handling by the application. If you have transactions/statements open for very long time, the list which contains the unflushed changes, grows. These unflushed changes will slow down the purge and in extreme cases, will also affect the DMLs.
So, IMO, the easiest and best way is to make sure the application cleans up after itself and closes the transactions as soon as it can.
I hope this helps!
Regards,Nitin 

    On Saturday, April 2, 2016 8:07 PM, geetanjali mehra <mailtogeetanjali@xxxxxxxxx> 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
increasing.
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.

Anty help?


Best Regards,
Geetanjali Mehra
Senior Database Administrator