Re: purge thread

[Akshay Suryavanshi]

Hi Geetanjali,

Well word of caution with this setting, it can block the whole server if
the purge thread is delayed too much. Also look into other things like IO
saturation or issues with disk as to why the purge thread is not able to
keep up with the backlog. If IO is not the issue then there is a very old
read view, which might be blocking the purge thread, so long running
transactions not recommended.

We have seen situations where a bad disk had caused huge History_length.

Cheers!!!
Akshay


On Tue, Sep 2, 2014 at 12:27 PM, geetanjali mehra <
mailtogeetanj...@gmail.com> wrote:

> Could you please put some more light on it?
>
> Best Regards,
> Geetanjali Mehra
> Senior Oracle and MySQL DBA Corporate Trainer and Database Security
> Specialist
>
>
>
> On Thu, Aug 28, 2014 at 2:09 AM, shawn l.green 
> wrote:
>
> > Hello Geetanjali,
> >
> >
> > On 8/26/2014 1:16 AM, geetanjali mehra wrote:
> >
> >> I want to understand how to tune  innodb_max_purge_lag
> >> <http://dev.mysql.com/doc/refman/5.5/en/innodb-
> >> parameters.html#sysvar_innodb_max_purge_lag>
> >>
> >> when history list length is high.
> >>
> >>
> >> Could anyone explain me.
> >>
> >>
> >> Best Regards,
> >> Geetanjali Mehra
> >> Senior Oracle and MySQL DBA Corporate Trainer and Database Security
> >> Specialist
> >>
> >>
> > This setting not related to the history value. It is there to help
> control
> > the gap between these two values of the InnoDB status report
> >
> > Trx id counter 0 290328385
> > Purge done for trx's n:o < 0 290315608 undo n:o < 0 17
> >
> > What is the current transaction compared what is the oldest transaction
> > that still has aged copies of data left in the data area of the
> tablespace.
> > The difference between those two values is the "purge backlog"
> >
> > Yours,
> > --
> > Shawn Green
> > MySQL Senior Principal Technical Support Engineer
> > Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> > Office: Blountville, TN
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql
> >
> >
>

Re: purge thread

[geetanjali mehra]

Could you please put some more light on it?

Best Regards,
Geetanjali Mehra
Senior Oracle and MySQL DBA Corporate Trainer and Database Security
Specialist



On Thu, Aug 28, 2014 at 2:09 AM, shawn l.green 
wrote:

> Hello Geetanjali,
>
>
> On 8/26/2014 1:16 AM, geetanjali mehra wrote:
>
>> I want to understand how to tune  innodb_max_purge_lag
>> > parameters.html#sysvar_innodb_max_purge_lag>
>>
>> when history list length is high.
>>
>>
>> Could anyone explain me.
>>
>>
>> Best Regards,
>> Geetanjali Mehra
>> Senior Oracle and MySQL DBA Corporate Trainer and Database Security
>> Specialist
>>
>>
> This setting not related to the history value. It is there to help control
> the gap between these two values of the InnoDB status report
>
> Trx id counter 0 290328385
> Purge done for trx's n:o < 0 290315608 undo n:o < 0 17
>
> What is the current transaction compared what is the oldest transaction
> that still has aged copies of data left in the data area of the tablespace.
> The difference between those two values is the "purge backlog"
>
> Yours,
> --
> Shawn Green
> MySQL Senior Principal Technical Support Engineer
> Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> Office: Blountville, TN
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: purge thread

[shawn l.green]

Hello Geetanjali,

On 8/26/2014 1:16 AM, geetanjali mehra wrote:

I want to understand how to tune  innodb_max_purge_lag

when history list length is high.


Could anyone explain me.


Best Regards,
Geetanjali Mehra
Senior Oracle and MySQL DBA Corporate Trainer and Database Security
Specialist



This setting not related to the history value. It is there to help 
control the gap between these two values of the InnoDB status report


Trx id counter 0 290328385
Purge done for trx's n:o < 0 290315608 undo n:o < 0 17

What is the current transaction compared what is the oldest transaction 
that still has aged copies of data left in the data area of the 
tablespace. The difference between those two values is the "purge backlog"


Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

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

purge thread

[geetanjali mehra]

I want to understand how to tune  innodb_max_purge_lag

when history list length is high.


Could anyone explain me.


Best Regards,
Geetanjali Mehra
Senior Oracle and MySQL DBA Corporate Trainer and Database Security
Specialist