Question How much free information is too much?

[Rob Fitzpatrick]

In newer versions of OpenEdge this should not be a problem as the size of the OM is supposed to be self tuned.

I wasn't aware of this enhancement. Which release(s) are you referring to?

 

[TheMadDBA]

Rich Banville had posted some information about omsize being set automatically on startup in future versions. I will try and track that down and make sure it is accurate or if this is only planned for 11.6+ (or got shelved).

 

[TheMadDBA]

Looking back through my notes it seems like this was planned but not implemented (or at least not documented if it was). I removed those lines until I can verify this actually happened and which version.

Thanks for the catch Rob.

 

[Rob Fitzpatrick]

Suggestion for the Links page: the ProKB application, a good support tool that works offline and provides functionality not found in the online public and partner KB sites.
Found here: http://knowledgebase.progress.com
Direct download link: http://download.progress.com/open/products/prokb/ProKBsetup.exe

 

[Rob Fitzpatrick]

For the discussion of -lruskips, you may want to mention the minimum versions: 10.2B06 and 11.1.

 

[TheMadDBA]

I have a lot of work to go through and add the minimum versions for a lot of features. I also need to decide some kind of style that keeps the pages from being cluttered for that kind of information. Trying to learn CSS3/HTML5 on the fly for this

 

[Cringer]

Some places actually include (AIW) after the description. I think that should be the standard.

Yeah I think OEM should include the parameter name for all settings. It's desperately confusing. I've submitted it as an enhancement request in the past.

 

[Cringer]

Spotted a typo in the report:
"Excessive buffers flsuhed can cause serious performance issues, usually resulting in reports of the system hanging or freezing."

 

[Cringer]

Would it be possible to add the option to output a script (in chosen OS flavour) to do the index rebuilds? No problem if you don't want to add that in - I'll add it to my edition of the code, was just a thought

 

[TheMadDBA]

Certainly... I will add that to the list. Options for idxcompact and idxbuild should be easy enough to generate. Just need to figure out which parameters I need to add to get it added... probably some level of logging in the scripts would be nice.

For your side I would start out with idxcompact instead of idxbuild... use 80 for the percentage (proutil dbname -C idxcompact pub.table.index 80) to avoid any nasty problems when you start updating data again.

 

[Cringer]

Thanks - I'll give it a go when I can get some downtime scheduled in. Should tide us over until full D&L over Christmas.

 

[TheMadDBA]

The plus side of idxcompact is you can run it online

I would run them in smaller groups and outside of the main working hours though (depending on the size of the indexes).

 

[Cringer]

Yeah I'll use weekends. We have less batch jobs over weekends too which is a bonus.

 

[RealHeavyDude]

Just had a look at it - great work! For a part-time dba like me this is really useful.

One thing that I observed playing with B2 - one should make sure that the B2 is big enough. As far as I understand, one method to determine whether B2 is too small is that lru2skips occur. Therefore you might want to consider something like B2 > 0 and lru2skips ( _BuffStatus._BfStatus-LRU2-Skips) > 0 as an issue.

Thanks a lot,
RealHeavyDude.

 

[Rob Fitzpatrick]

One thing that I observed playing with B2 - one should make sure that the B2 is big enough. As far as I understand, one method to determine whether B2 is too small is that lru2skips occur. Therefore you might want to consider something like B2 > 0 and lru2skips ( _BuffStatus._BfStatus-LRU2-Skips) > 0 as an issue.

The value of -lru2skips is a broker startup parameter, not a performance metric. It can be useful to set it to a non-zero value (usually doing so online) once you have determined that there are no free buffers in -B2 and you now have LRU2 latch activity. Setting a value for -lru2skips will minimize that latch activity.

The -lru2skips parameter has the same function in -B2 that -lruskips has in -B. Ordinarily, every time a block is accessed it is moved to the head (MRU end) of the LRU chain (or LRU2 chain, in B2). When doing many reads on a small table this can cause thrashing as the same few blocks continually get moved to the head of the chain, moved a few steps down, back the the head, etc.

As an example, setting a value of 10 for -lruskips tells the database to put an access counter on each block in the buffer pool. Each time the block is accessed, the counter is incremented. Only when the counter reaches the value of -lruskips (10 in this case) is it moved to the head of the chain and the counter zeroed. So that would reduce LRU latch contention by 90%. Setting -lruskips 100 would reduce it by 99%.

As you said, it is very important when setting -B2 to make sure it is large enough. You should do a sizing exercise on a test DB before setting -B2. Progress doesn't make this as straightforward as it should be unfortunately, as it is difficult to determine the size of a table in blocks. One approach you can take to size assigned tables is to create a test DB (via probkup of prod DB) and then start that test DB with a -B value large enough to hold all the tables. (You will know, in retrospect, that it wasn't large enough if promon R&D 1 7 shows zero unused buffers). Then do FOR EACH table TABLE-SCAN: END. on each table that you want to assign to -B2. The total used buffers in R&D 1 7 is the size of those tables in blocks. The size in blocks of the indexes you want to assign can be taken directly from dbanalys/idxanalys. Sum those and add some extra, let's say 20%, to allow for growth of those objects (depending on whether historical CRUD stats show creates and updates). That's the value to use for -B2.

As for monitoring if you've used all of -B2, there are a couple of things to look at. One is whether you have ongoing LRU2 latch activity (in _Latch or in promon R&D debghb 6 11). The other is whether the ABP OS reads (in promon R&D 2 3) are greater than -B2. Also, check that the bottom of R&D 2 3 shows "LRU2 replacement policy disabled". (That's from memory; may not be the exact wording but you want to see "disabled", not "enabled".)

 

[TheMadDBA]

Glad to hear at least a few people are getting some value out of this. Hard to find time for updates now with my new job but I will try and keep putting a few things in here and there over time.

I purposely left out any sizing magic for -B or -B2.. partly because of the issues that Rob described and partly because I am a strong believer in getting your queries and storage correct before making any judgments about the "proper" size of the buffer pools.

 

[Rob Fitzpatrick]

I put in an enhancement request for new VST/promon fields to distinguish between free buffers in -B versus -B2; right now all we have is the sum of free buffers in both.

This request had its status changed from "Under Review" to "Coming Soon", so that's a good sign. It would make ABP monitoring more straightforward.

 

[Cringer]

Brian Bowman explained at the EMEA PUG that the statuses in the community make life really hard for him, but 'Coming Soon' means it will be in a release soon. So in this case I guess it's earmarked for 11.7.
I've asked Jean Richert to get the vendor of the Community software to add more statuses. I received a long list of swear words in response but I believe he's looking at it!

 

[Rob Fitzpatrick]

The first thing they need to do, which I've already requested, is change the default status. "Under Review" suggests someone has looked at it or is looking at it. I'd like to see a default of "Submitted", and have it change to "Under Review" when a person has actually read it.

 

[Cringer]

Yeah Brian commented that this was an issue for him too as he has to re-read a number of requests before he realises he's up to where he's already reviewed.