Re: threads in cleaning up mode

[geetanjali mehra]

Can it be  a consequence of slow disk also?

Best Regards,
Geetanjali Mehra
Senior Database Administrator


On Tue, Apr 5, 2016 at 10:50 AM, geetanjali mehra <
mailtogeetanj...@gmail.com> wrote:

> 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 
> 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   | 100 |
>>> | 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
>>
>>
>

Re: threads in cleaning up mode

[geetanjali mehra]

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 
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   | 100 |
>> | 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
>
>

Re: threads in cleaning up mode

[shawn l.green]

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   | 100 |
| 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

test - ignore

[Pawel Eljasz]

ignore

failed to decrypt log block

[lejeczek]

hi everybody..

would you know how to recover from such a problem:

InnoDB: ok header, but checksum field contains 792537472, 
should be 1776874443
2016-04-04 12:41:15 140333716928640 [ERROR] InnoDB: Redo log 
crypto: failed to decrypt log block. Reason could be that 
requested key version is not found, required encryption key 
management plugin is not found or configured encryption 
algorithm and/or method does not match.
2016-04-04 12:41:15 140333716928640 [ERROR] Plugin 'InnoDB' 
init function returned error.
2016-04-04 12:41:15 140333716928640 [ERROR] Plugin 'InnoDB' 
registration as a STORAGE ENGINE failed.


gee, I really have not clue what happened there.
for sharing thought & suggestions,
many thanks,
L.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: threads in cleaning up mode

[Nitin Mehta]

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 
 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      | 100 |
| 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