Checkpoints triggered by Log
Hi all,
we encounter checkpoints on a highly loaded server, which are triggered by
"PLog" or "LLog".
The thing I do not understand is:
We already have assigned 4.5GB of physical log (which is not filled more than
2% between checkpoints)
and 150 Logfiles / 50MB (which are getting filled one each ~5 minutes).
At a checkpoint, we have ~100000-180000 dirty buffers,
for Checkpoints triggered by PLog/Llog ~4000 Physical Total pages, ~8000
Logical total Pages,
for Checkpoints triggered by CKPTINTRVAL ~50000 Physical Total Pages, ~30000
Logical Total pages.
Checkpoint duration is between 5 and 18 seconds.
According to documentation, a Checkpoint with trigger PLog is executed when
either
75% of the physical log is full (which is not the case) or if
"the amount of physical log plus number of dirty partitions is more than 90%
of physical log size"
Also, for Llog trigger (which occurs sometimes), "Running out of logical log
resources".
My question would be: how can we determine the "real" reason for these
checkpoints triggered ?
We already increased physical log and log files, but now onstat -g ckp just
gives us
even higher values as recommendation.
We have this situation on one specific instance only. Checkpoint Interval is
300, which is rarely
ever reached at the moment.
We get Plog or Llog Checkpoints each 30-60 seconds, sometimes (depending on
load) also a CKPTINTVL.
Setup is PHYFILE 4500000, 150x50MB Logfiles, 64 Cleaners, no
RTO_SERVER_RESTART, AUTO_CKPTS=1,
BUFFERPOOL 2k buffers=4000000, lrus=128,max/min = 50/60%
Total data size is ~80 GB, spread over 100 single databases.
Only Chunk writes occur according to onstat -F, LRUs are dirty < 1 %.
11.70FC8XB8GE on Linux 64 bit.
Any hints ? Maybe the problem is buffering, by we cannot extend that one for
license reasons (growth edition).
We might have the possibility to splt the instance in two, in order to get
less modified pages per instance.
I just want to know if there are other reasons (undocumented) for triggering
physical or logical log based checkpoints,
because I cannot see the actual sizes are ever reached.
Marcus Haarmann