Re: can the behaviour of LocalCheckPoint be changed ?
Hi,
Balasubramanian Azhagappan wrote:
> Hello Mr.Fisk,
> Thank you for your response. I have one more
> doubt regarding this.
>
> Harrison Fisk wrote:
> > The LocalCheckpoint mechanism is how it makes
> it
> > possible to reuse REDO log space that is
> stored on
> > disk. In order to safely remove the log
> records
> > it needs to make sure that all corresponding
> data
> > is written to disk.
>
> I observed the traffic during the
> Local Check point. As i was populating the tables
> with 1 Million Entries, the traffic during each
> LocalCheckPoint is increasing linearly. So, from
> this i assumed that all the records in the memory
> are written to the disk and the REDO log space is
> adjusted. does corresponding rows as you said,
> refers to only new records or to all ?
The local checkpoint will write all of the data out. You can think of it basically as a rolling online backup that is being taken. The REDO log will be written out in between Local CheckPoints which is only the changes being made, so it generally is much smaller. The LCP allows the REDO log space to be reused.
> > Keep in mind that you can set how often it
> will do
> > the LocalCheckPoint. If it is doing it for
> one
> > row, then you can bump up the setting
> > TimeBetweenLocalCheckpoints to cause it to
> wait
> > for more data changes before doing it. The
> > default is 4MB, so it shouldn't do it for
> just a
> > single row (unless you changed it).
>
> I am not sure whether
> LocalCheckPoint runs when the data changes reaches
> 4MB. If so, then i wonder why the LocalCheckPoint
> ran even when i did not carry out any tests in the
> nights, that too it ran for every 1 hour exactly.
> Is it that the checkPoint interval is not only
> based on data changes but also on the time
> interval ? I have not changed the default value of
> TimeBetweenLocalCheckPoint.
As far as I know it is only based on Data Changes. Are you sure that the disk i/o you are seeing is based on the LCP? You can take a look in the cluster log and it will contain information about when an LCP starts and when it finishes. There might be a timer as well, but I imagine in production the data is always changing, so it doesn't seem like it is of consequence.
If you are still worried about the amount of disk i/o, you can actually control how fast it is written out. The settings are called NoOfDiskPagesToDiskAfterRestartTUP and NoOfDiskPagesToDiskAfterRestartACC. The default is 3.2MB/s and 1.6MB/s and you can reduce them if you are worried about disk i/o during the LCP.
Hope that helps clear up any confusion.
Harrison Fisk, Trainer and Consultant
MySQL AB, www.mysql.com