IDS Forum

Re: RE: Checkpoints slowing down the insert on....

Posted By: david@smooth1.co.uk
Date: Wednesday, 2 April 2014, at 4:32 p.m.

In Response To: Re: RE: Checkpoints slowing down the insert on.... (NITIN MATHUR)

1 chunk will one be cleaned by 1 page cleaner thread at checkpoint time.

Spread different 'active' indexes/tables onto more dbspaces.

If that t_quote_history index is getting many inserts than fragment (partition)
the index across multiple dbspaces to also help.

> On 02 April 2014 at 20:21 NITIN MATHUR <nitin_maths@rediffmail.com> wrote:
>
>
> I posted the output after rolling back the change, please see the lines from
> the online log (I also turned OFF AUTO_CKPTS)
>
> 4/01/14 16:48:29 Maximum server connections 31
> 04/01/14 16:48:29 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns
> blocked 0, Plog used 12727, Llog used 25651
>
> 04/01/14 16:53:30 Checkpoint Completed: duration was 0 seconds.
> 04/01/14 16:53:30 Tue Apr 1 - loguniq 100491, logpos 0xdaf0018, timestamp:
> 0xbd378740 Interval: 191005
>
> 04/01/14 16:53:30 Maximum server connections 31
> 04/01/14 16:53:30 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns
> blocked 0, Plog used 13964, Llog used 28492
>
> 04/01/14 16:58:30 Checkpoint Completed: duration was 0 seconds.
> 04/01/14 16:58:30 Tue Apr 1 - loguniq 100491, logpos 0x14f15018, timestamp:
> 0xbd3ff0a7 Interval: 191006
>
> 04/01/14 16:58:30 Maximum server connections 31
> 04/01/14 16:58:30 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns
> blocked 0, Plog used 10918, Llog used 29775
>
> 04/01/14 17:00:06 Logical Log 100491 Complete, timestamp: 0xbd43dd3a.
> 04/01/14 17:00:06 Logical Log 100491 - Backup Started
> 04/01/14 17:00:21 Logical Log 100491 - Backup Completed
> 04/01/14 17:00:58 Value of RTO_SERVER_RESTART has been changed to 1800.
> 04/01/14 17:01:01 Checkpoint Completed: duration was 2 seconds.
> 04/01/14 17:01:01 Tue Apr 1 - loguniq 100492, logpos 0x5cf2138, timestamp:
> 0xbd49dd36 Interval: 191007
>
> 04/01/14 17:01:01 Maximum server connections 31
> 04/01/14 17:01:01 Checkpoint Statistics - Avg. Txn Block Time 0.001, # Txns
> blocked 1, Plog used 63430, Llog used 38520
>
> 04/01/14 17:01:07 Value of AUTO_CKPTS has been changed to Off.
> 04/01/14 17:10:09 Checkpoint Completed: duration was 3 seconds.
> 04/01/14 17:10:09 Tue Apr 1 - loguniq 100492, logpos 0xd8c4198, timestamp:
> 0xbd549679 Interval: 191008
>
> 04/01/14 17:10:09 Maximum server connections 31
> 04/01/14 17:10:09 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns
> blocked 1, Plog used 25876, Llog used 33234
>
> 04/01/14 17:10:16 Checkpoint Completed: duration was 2 seconds.
>
> I rolled it back this morning
>
> 04/02/14 08:09:56 Wed Apr 2 - loguniq 100500, logpos 0x7784018, timestamp:
> 0xbe15b4c9 Interval: 191077
>
> 04/02/14 08:09:56 Maximum server connections 34
> 04/02/14 08:09:56 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns
> blocked 0, Plog used 16420, Llog used 19850
>
> 04/02/14 08:11:07 Checkpoint Completed: duration was 1 seconds.
> 04/02/14 08:11:07 Wed Apr 2 - loguniq 100500, logpos 0xc5e8018, timestamp:
> 0xbe1b9491 Interval: 191078
>
> 04/02/14 08:11:07 Maximum server connections 34
> 04/02/14 08:11:07 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns
> blocked 0, Plog used 19474, Llog used 20332
>
> 04/02/14 08:11:42 Value of AUTO_CKPTS has been changed to On.
> 04/02/14 08:11:50 Value of RTO_SERVER_RESTART has been changed to 0.
> 04/02/14 08:12:05 Value of CKPTINTVL has been changed to 600.
> 04/02/14 08:13:49 Logical Log 100500 Complete, timestamp: 0xbe29b8ef.
> 04/02/14 08:13:49 Logical Log 100500 - Backup Started
> 04/02/14 08:14:01 Logical Log 100500 - Backup Completed
> 04/02/14 08:17:36 Logical Log 100501 Complete, timestamp: 0xbe467e42.
> 04/02/14 08:17:36 Logical Log 100501 - Backup Started
> 04/02/14 08:17:50 Logical Log 100501 - Backup Completed
> 04/02/14 08:21:32 Checkpoint Completed: duration was 1 seconds.
> 04/02/14 08:21:32 Wed Apr 2 - loguniq 100502, logpos 0x148dc018, timestamp:
> 0xbe5e5076 Interval: 191079
>
> The size of logical log is 195 MB and we have 121 logs. Every day, we use
> approx 140 logs, during the peak business hours, we use one log every 2-3
> minutes.
>
> I also ran onstat -F -r 1 and noticed that there is one chunk which appears
> every time when the instance is busy with checkpoint. This chunk contains
> indexes from the most busy tables. Strangely, one index which is on a single
> column (which is a unique value for each row for some specific message type)
> does maximum disk writes (not buffer writes).
>
> create index "informix".tqh_idx9 on "informix".t_quote_history
>
> (qh_id) using btree in t_quote_indx01;
>
> At the moment, I am just trying to delay the checkpoint by making the interval
> equal to almost 2 hours so that trading doesnt get impacted.
>
> regards,
>
> Nitin
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>

Messages In This Thread

• Checkpoints slowing down the insert on Primary
  NITIN MATHUR -- Tuesday, 18 March 2014, at 12:01 p.m.
  • RE: Checkpoints slowing down the insert on Primary
    Alexandre Marini -- Tuesday, 18 March 2014, at 1:50 p.m.
    • Re: RE: Checkpoints slowing down the insert on Pri
      NITIN MATHUR -- Tuesday, 18 March 2014, at 2:25 p.m.
      • RE: Checkpoints slowing down the insert on....
        Alexandre Marini -- Tuesday, 18 March 2014, at 2:45 p.m.
        • Re: RE: Checkpoints slowing down the insert on....
          NITIN MATHUR -- Wednesday, 2 April 2014, at 10:24 a.m.
          • RE: Checkpoints slowing down the insert on....
            Alexandre Marini -- Wednesday, 2 April 2014, at 2:09 p.m.
            • Re: RE: Checkpoints slowing down the insert on....
              NITIN MATHUR -- Wednesday, 2 April 2014, at 3:21 p.m.
              • Re: RE: Checkpoints slowing down the insert on....
                david@smooth1.co.uk -- Wednesday, 2 April 2014, at 4:32 p.m.
                • Re: RE: Checkpoints slowing down the insert on....
                  NITIN MATHUR -- Thursday, 3 April 2014, at 9:57 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.