Stephen W (Customer) asked a question.
Does enacting PROQUIET impact the contents of the primary -B buffer?
The below article suggests PROQUIET will flush BI and master block buffers to disk, but I do not understand whether this relates to records in the primary buffer or not.
https://community.progress.com/s/article/P99512
My question arises from a client who currently chooses to shut down their database (12.2.18 Enterprise) nightly for DR snapshotting purposes.
The client wishes to improve buffer hit rates and hence query performance by removing the nightly DB shutdown, but keeping the DR solution in place that images the database storage, hence move to PROQUIETing the database prior to snapshot instead, hoping this retains the contents of the cache.
The application vendor however suggests that this won't help cache hit ratios since PROQUIET also 'flushes the buffer' but gives no more detail.
Ultimately a move to 24/7 DB uptime and probkup online is recommended but this is not an option at this time.
Thankyou for any information.
We can check the behavior:
proserve sports -Mf 600
Then run:
promon -> Status: Buffer Cache -> Modified buffers: 5
promon -> Activity: Summary -> Z Zero the activity counters
proquiet sports -C enable
promon -> Activity: Summary -> U Update activity counters.
DB Writes: 1
BI Writes: 8
promon -> Activity: Other -> Flush master block: 1
As the article says: "3. Flushes the bi buffer pool and master block."
Flushing is not the same thing as emptying. Flushing synchronizes shared memory to disk so you can make a consistent copy of the files that can successfully go through crash recovery. The data remains in the buffer pool.
Here is a quick demo from a sports2020 database with one table (customer) in area 10.
Proserve, then read some (5) record blocks into the buffer pool:
Modify those blocks:
Then enable a quiet point, and then disable it.
The five record blocks in question have dbkeys 3328, 3584, 3840, 4096, and 4352. We can see their presence in the buffer pool via promon R&D, 6, 1 (Advanced R&D, Cache Entries):
Similarly, you can look at the before-and-after counts of used and empty buffers, and index and record blocks, in promon R&D, 1, 7 (Status, Buffer Cache).
The blocks remain in the buffer pool after a proquiet.
> The below article suggests PROQUIET will flush BI and master block buffers to disk, but I do not understand whether this relates to records in the primary buffer or not.
Flushing of BI buffers (sized by -bibufs) to the BI file is separate from the database buffer pool (sized by -B/-B2).
> Ultimately a move to 24/7 DB uptime and probkup online is recommended but this is not an option at this time.
I am curious to know which release is being used and why probkup online is not considered to be a viable option. Also, since DR is mentioned, are after imaging or OpenEdge Replication in use?
Thankyou both for your replies. This is just the evidence I need to take to the vendor.
> Flushing of BI buffers (sized by -bibufs) to the BI file is separate from the database buffer pool (sized by -B/-B2).
Perfect, thankyou.
> I am curious to know which release is being used and why probkup online is not considered to be a viable option. Also, since DR is mentioned, are after imaging or OpenEdge Replication in use?
OE Enterprise 12.2.18 Windows.
probkup online +AI is being recommended to the the client but they are reluctant to alter their DR methods at this time (VM/SAN snapshot). Neither probkup nor AI is currently in use (!). PROQUIET is therefore an intermediate step to improve cache hits until the change to DR arrangements can be made to introduce probkup online+AI.
Adding to Rob's explanation:
a: the reason the proquiet feature exists is specifically so that databases stored on disk arrays can be backed up by breaking a disk mirror, turning off proquiet to allow database updates to resume, backing up the isolated disks, and then restoring the mirroring. This reduces downtime quite considerably. Since then, some other methods for using proquiet have come about, such as snapshots.
b: the reason the transaction logs are flushed to disk is so that they are 100% up to date. Same for master block. The remainder of the buffer pool does not have to be touched.
c: Just as with a probkup, when a proquiet backup is restored, the resulting database looks like a crashed database. Crash recovery reads the BI log and updates database blocks as needed and then rolls back any incomplete transactions.
HTH
It sounds like they are doing daily snapshots. What is the customer's RPO?
What is their desired RPO, if it is different from current?
What is the database size?
All OpenEdge production databases with write activity should be using probkup and after imaging, at a minimum. Protecting the data is a higher priority than buffer hit ratio.
Reluctance aside, so far I haven't seen a reason why probkup online and after imaging cannot be used.
Thankyou for the feedback; you are preaching to the converted.
Probkup+AI can be used and is recommended to them ad nauseam , it is purely reluctance from their infrastructure department to move away from familiar snapshot-based DR. Cost of current RPO (24hrs) has been emphasised at length to management for their 200GB DB.
We are therefore frustratingly limited to comparing proshut+snapshot vs proquiet+snapshot, whilst condoning neither approach, and simultaneously emphasising to the client how both approaches are inferior whilst they insist on shapshots for their DR strategy. It is perhaps a niche scenario for this reason and we concur with all your points above, and any other business would take the recommendation of probkup online + AI.
I sense your frustration, and I can sympathize.
It sounds like this customer will only change their ways after an issue where they unnecessarily lose hours, perhaps days, of data.
Last point: if snapshots are their security blanket, there should be nothing preventing them using probkup online in addition to their existing regime. It should be quite quick for a database of this modest size. And it will get even better if they can upgrade to 12.8.
a valid point thanks: at the risk of sliding further off-topic, we could indeed enable probkup online + AI anyway, if we ensure it completes before the nightly shutdown/after the DB comes back online and it doesn't roll on in duration to impact production hours. The client's IT dept can carry on with their DR validation processes using snapshots, and we will be able to reduce their RPO despite their efforts to the contrary. No ability to upgrade to 12.8: the software vendor currently supports only 12.2 LTS.
Back on-topic, I conclude that given the choice solely between proshut+snapshot vs proquiet+snapshot, then proquiet would be preferable due to blocks remaining in the buffer pool.