Thanks to everyone for response. Any other views from any one is always welcome.
Best Regards, Geetanjali Mehra Senior Database Administrator On Mon, Apr 4, 2016 at 7:51 PM, shawn l.green <shawn.l.gr...@oracle.com> wrote: > > > 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 >> 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 >> >> > 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.) > > > -- > Shawn Green > 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/ for > details. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > >