Question Checkpoint Duration Sync Time and Direct I/0 (-directio)

BigSlick

Member
My experience of directio (and it's small I must admit) is that all it does is mask other issues that should/could be resolved to much greater effect. As Tom suggests, it's shifting responsibility.
Am I reading it correctly that your database is around 5TB? A decent size then! I'd be interested to know what % of that is actually active data and how much of it is essentially just there for old time sake.
3 APWs is verging on the upper limit of what I'd consider implementing without serious thought. But then I've not worked with such a large DB in the past.
Tom's question re the storage system is really important. I might have missed it, but do you have info on how fast a bigrow is on the disk? The so-called Furgal Test: Furgal Test?

Yeah 5TB, We have another database too which is starting to catch up. But this particular database has one table with 5 billion records in it. Were looking at reducing this as its just not cricket. :)

The furgal test; i've done a variety of tests on most servers using this and Crystal Disk; the script has the following:

call proutil sports -C truncate bi -bi 32768
call proutil sports -C bigrow 4

G: Drive (mainly indexes, 4096 blocksize disk) - 17 seconds to grow
R: Drive (Mainly data 4096 blocksize disk) - 17 seconds to grow
B: Drive (BI 16384 blocksize disk) - 22 seconds to grow
 

BigSlick

Member
Hi

This story sounds very familiar as we're seeing the same behavior when running tests on 2012 vs 2016.

Checkpoint durations are very concerning and we're told to open a case with Microsoft.
Would you happen to have a Microsoft case number, so maybe we can refer to that one when we open a case ourselves?
I hope this could give us some more leverage for a fix on 2016.

Migrating to 2019 is not an option at this point due to OpenEdge 11.7 being tied in to .NET 4.6.


The intial problem we saw on 2016 (which we cannot replicate now -oddly) is that we would turn our systems on and run a process to check the checkpoints and the times would be around 0-2 seconds. We would then run our overnights and checkpoint times would peak at around 30 seconds, then once these had all finished and we ran the initial process to check the checkpoints again and these would consistently be 10-12 seconds.

Wehn we looked into this further using RAMMAP, it was the standby list causing the issues. Windows has changed the way it deals with the memory between 2012 and 2016. If we empty the memory the initial problem went away. We fed this back to our providers and they had a case with Mircosoft. They noticed a bug through their testing but wouldnt entertain a fix in 2016 and we received an alpha fix in 2019 which didnt seem to do much at all. That being said - we cannot replicate this now. Unfortunatley, i dont have a case number for it.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
The furgal test; i've done a variety of tests on most servers using this and Crystal Disk; the script has the following:

call proutil sports -C truncate bi -bi 32768
call proutil sports -C bigrow 4

G: Drive (mainly indexes, 4096 blocksize disk) - 17 seconds to grow
R: Drive (Mainly data 4096 blocksize disk) - 17 seconds to grow
B: Drive (BI 16384 blocksize disk) - 22 seconds to grow
When performing such a test, it's important to be aware of what OE is doing, which depends on both platform and version. Prior to 11.3, this test did synchronous (unbuffered) I/O to extend the BI file by, in the case of your command, 8 clusters (256 MB total).

In 11.3.0, bigrow was optimized to do buffered I/O on Solaris and AIX. This was good, in that operations like prostrct create and bigrow were quicker, but bad if you depended on bigrow to test your disk's unbuffered I/O performance, to gauge how it will write to BI and AI files (the "Furgal test"). However you can force bigrow to revert to its pre-11.3 behaviour and do unbuffered I/O with an undocumented parameter, e.g.:
call proutil sports -C bigrow 4 -zextendSyncIO

In 11.7.6 and 12.1, that optimization was extended to all other certified platforms, i.e. Linux and Windows. You mentioned you're using 11.7.3, so be aware of this change if you upgrade or apply SP06 or later. I suggest you add -zextendSyncIO now to your script so you don't forget. It won't cause an error; in releases where it isn't functional, proutil will just ignore it.

Reference: KB: Performance improvement when new biclusters are added

data/index partition: 256 MB in 17 seconds = 15.1 MB/s
BI partition: 256 MB in 22 seconds = 11.6 MB/s
AI partition: ??
Those times are in the range I would consider barely adequate for my smaller customers' DB servers, and those DBs are a few orders of magnitude smaller than yours. And your BI disk is slower than your data disks.

So if performance is a problem generally, I'd be looking not just at the OS but also at I/O system performance.
 

BigSlick

Member
When performing such a test, it's important to be aware of what OE is doing, which depends on both platform and version. Prior to 11.3, this test did synchronous (unbuffered) I/O to extend the BI file by, in the case of your command, 8 clusters (256 MB total).

In 11.3.0, bigrow was optimized to do buffered I/O on Solaris and AIX. This was good, in that operations like prostrct create and bigrow were quicker, but bad if you depended on bigrow to test your disk's unbuffered I/O performance, to gauge how it will write to BI and AI files (the "Furgal test"). However you can force bigrow to revert to its pre-11.3 behaviour and do unbuffered I/O with an undocumented parameter, e.g.:
call proutil sports -C bigrow 4 -zextendSyncIO

In 11.7.6 and 12.1, that optimization was extended to all other certified platforms, i.e. Linux and Windows. You mentioned you're using 11.7.3, so be aware of this change if you upgrade or apply SP06 or later. I suggest you add -zextendSyncIO now to your script so you don't forget. It won't cause an error; in releases where it isn't functional, proutil will just ignore it.

Reference: KB: Performance improvement when new biclusters are added

data/index partition: 256 MB in 17 seconds = 15.1 MB/s
BI partition: 256 MB in 22 seconds = 11.6 MB/s
AI partition: ??
Those times are in the range I would consider barely adequate for my smaller customers' DB servers, and those DBs are a few orders of magnitude smaller than yours. And your BI disk is slower than your data disks.

So if performance is a problem generally, I'd be looking not just at the OS but also at I/O system performance.

Thanks for the info Rob, i'll add that in.

How does the Furgal test compare with other tests, for instance doing a prostrct create and a 32GB file is created in 2m 32s. Which equates to 215 MB/s.

The disks do seem fast for certain operations, our backup times since moving are 20% faster for example. I can go to our provider and pose the questions again. But my lack of knowledge will probably lead them to sweep it under the carpet.

What i know is that we use NetApps with Filer fibre attached and Qos. But as always it never touches the Qos :rolleyes:

Cheers
 
Top