Online backups and buffer hit rate
-
Hi all,
I'm a bit surprised by the effect that probkup online seems to be having on the buffer pool hit rate in OE11.5 and 11.7, as seen in the dashboard. Two customers run a full online backup and I see the hit rate drop to 20% and 30% respectively around the time of the backup. A third customer runs an incremental online backup, and their hit rate drops to 0.5% around the time of the backup. This pattern repeats over several consecutive days. On all sites, backups occur during the night when there is no other activity.
All other things being equal, I would have expected the incremental backup site to experience less of a drop, because fewer blocks are being backed up. I know that each data block has its backup counter read during an incremental backup, to see if it should be included in the backup. Does that mean then that every data block comes into the buffer pool even for an incremental backup? Although the throughput in all 3 systems is broadly similar, the customer doing the incremental backup has a substantially larger database than the other two, and I guess that's why their hit rate is dropping so low.
FWIW, we're using -bp 10 and -com.
TIA
Nick -
@nickw regardless of which blocks are actually backed up, the probkup utility must load them all into the buffer pool to check the backup counter.
Quick correction for anyone else landing here: -Bp 10, not -bp 10. This is the private buffer pool count.
-
As Paul says every block with data in it must be read - even for an incremental backup. Which is why an incremental backup takes about as long to run as a full backup.
-Bp 10 helps to prevent the entire -B buffer pool getting flushed as a result of a backup running and the follow-on impact that that will have to the hit ratio. During the backup itself though you will still almost certainly see a poor hit ratio.
