Question 32b Progress Above the 4G line

Rob Fitzpatrick · Oct 22, 2014

kdefilip said:
Not sure which latches are most important. By far OM are the most numerous, followed by LKT. Right now, MTX seem to be the only ones that are napping, 4 - 8 per any 10 second period.

also note we have zero BFP yet we have very very many BUF (listed 4 times??)

LRU: very many. LRU is listed twice, one is zero and the other for a 10 sec period is13,500

There are two buffer pools, primary and secondary (aka alternate). As you may know by now their sizes are determined by the -B and -B2 parameters respectively. Each buffer pool has its own LRU chain protected by an LRU latch. Think of them as LRU (primary) and LRU2 (alternate).

Two approaches you can take to relieve contention:

Allocate some small, very frequently-accessed objects to the Alternate Buffer Pool and size B2 appropriately so the objects fit entirely within it. In that case, contention is removed from the primary buffer pool LRU chain/latch, and no LRU chain need be maintained, provided that B2 is large enough that no block evictions are necessary.
Use the -lruskips parameter to reduce the overhead of maintaining the primary buffer pool LRU chain, and thereby reduce latch contention.

The definitive resource on alternate buffer pool (Tom Bascom, DBAppraise):
"The B2 Buzz"
http://dbappraise.com/ppt/B2Buzz.pptx

More great info on latches, in general (Rich Banville, OE RDBMS architect):
"A New Spin on Some Old Latches"
http://download.psdn.com/media/exch_audio/2008/OPS/OPS-28_Banville.ppt

Info on -lruskips (Rich Banville):
"Still More Database Performance Improvements"
http://pugchallenge.org/2012PPT/NEPUG_Performance.pptx

kdefilip · Oct 22, 2014

Rob Fitzpatrick said:
There are two buffer pools, primary and secondary (aka alternate). As you may know by now their sizes are determined by the -B and -B2 parameters respectively. Each buffer pool has its own LRU chain protected by an LRU latch. Think of them as LRU (primary) and LRU2 (alternate).

Two approaches you can take to relieve contention:

Allocate some small, very frequently-accessed objects to the Alternate Buffer Pool and size B2 appropriately so the objects fit entirely within it. In that case, contention is removed from the primary buffer pool LRU chain/latch, and no LRU chain need be maintained, provided that B2 is large enough that no block evictions are necessary.

Use the -lruskips parameter to reduce the overhead of maintaining the primary buffer pool LRU chain, and thereby reduce latch contention.

The definitive resource on alternate buffer pool (Tom Bascom, DBAppraise):
"The B2 Buzz"
http://dbappraise.com/ppt/B2Buzz.pptx

More great info on latches, in general (Rich Banville, OE RDBMS architect):
"A New Spin on Some Old Latches"
http://download.psdn.com/media/exch_audio/2008/OPS/OPS-28_Banville.ppt

Info on -lruskips (Rich Banville):
"Still More Database Performance Improvements"
http://pugchallenge.org/2012PPT/NEPUG_Performance.pptx

Hi
Thanks. Some of those I have seen, but I will revisit and read in more depth.

One thing I just have trouble understanding about the lruskipis. In theory, any "New" block placed in the buffer should immediately move to the top of the MRU list and as such, should not be on the list of blocks that can be removed/overwritten. All this happening while the system maintains a list of least recently used (LRU), those of which are blocks that when needed, are targeted for removal/overwriting. However, when you set -lruskips to say 100, you are telling the system to subjugate its algorithm for calculating LRU and skip each block 100 times, thus removing it from the calculation of MRU/LRU. So in my case, I have a buffer of 200000 blocks, all of which contain data, and all of which are on the LRU chain which I can only interpret as meaning zero blocks are on the MRU, totally subjugating the orderly flow of data into and out of the buffer. Furthermore, when I check the hidden menu you suggested, out of the 200000 blocks, none of them have reached the value of -lruskips. Which then, I can only interpret that to mean blocks are not staying resident in the buffer long enough to be skipped 100 times. Or am I totally out of understanding of this issue?

TheMadDBA · Oct 22, 2014

Basically lruskips just keeps the DB from moving the block to the top of the MRU list every time the block is accessed. Instead a less expensive counter is maintained and when that counter is reached it will move the block to the top of the MRU list. The idea is that if you are hammering the same blocks (common data/index entries) you can get an improvement since the MRU/LRU list can only maintained by one connection at a time.

I am not sure you can trust the Lru and Skips columns in promon without lruskips being turned on.

I have had mixed results with lruskips in my environment but every application is different. Some guys that I trust (like Rich) swear by it and it does help if I set up a test to just hammer a database... So I would consider it worth a shot for you. Especially with suspect code like you are probably running. We usually just try and fix the code/design issues, but we have our own source code to modify as needed.

Rob Fitzpatrick · Oct 22, 2014

I don't think we've discussed your database structure yet. The structure is reflected in the file dbname.st in the database directory. Look up the PROSTRCT LIST command (DB Admin manual) to see how to ensure the structure file is current.

Data area entries in the structure file are basically of this form:
d "<area name>":<area number>,<records per block>[;<blocks per cluster>] <extent path> [<extent type> [<extent size>]]

If blocks per cluster is 8, 64, or 512, the storage area is called a Type II area. If it is 1 or not specified, it is a Type I area (circa Progress v9.x-era and before). All application objects (tables, indexes, and LOB columns) should reside in Type II storage areas. The schema area is always Type I, and should never contain application objects. Migrating data from Type I to Type II typically involves a dump and load. Though small or empty objects can be move from one area to another via command line utility (proutil tablemove or proutil indexmove).

Having data in Type I areas impacts you in a number of ways, including application performance.

TomBascom · Oct 22, 2014

...This DB in my install is choking right now due to stressed shared memory structures...

That's a pretty definite statement. What is your evidence?

kdefilip · Oct 22, 2014

TomBascom said:
That's a pretty definite statement. What is your evidence?

2.3 million latch timeouts in a 10 day period

750m buffer cache who's blocks never reach the -spinskip value, at a time that -spinlocktimeout was set to 800,000. Correct me if I'm wrong, but that would indicate that the buffer is flushing faster than blocks can be moved to the MRU with given spinskip. OR the code is so bad that we are just churning our buffer cache.

A 4k blocksize

At times, checkpoints at a rate of 6-8 during a 30 second period.

Occasional buffer flushes in BI during checkpoints

A BI file that is on a Raid5 system drive with very high split I/O, AI on same drive

An 8k BI blocksize on a disk that is allocated at 2k.

Misaligned disks across the board

A pagefile on same disks as database files

All database files on same disk.

Shared locks that hold long after a transaction is complete

More errors in OS system logs and DB logs than I can shake a stick at

500 application crash logs in a 15 day period

More end-user complaints than I have ever seen with any database of any size

I could go on, but I don't want to bore you. When I say "our database is choking" please don't misconstrued my statement to imply a slight to Progress. This system is exhibiting issues from the client keyboard all the way to the back end.

kdefilip · Oct 22, 2014

Rob Fitzpatrick said:
I don't think we've discussed your database structure yet. The structure is reflected in the file dbname.st in the database directory. Look up the PROSTRCT LIST command (DB Admin manual) to see how to ensure the structure file is current.

Data area entries in the structure file are basically of this form:
d "<area name>":<area number>,<records per block>[;<blocks per cluster>] <extent path> [<extent type> [<extent size>]]

If blocks per cluster is 8, 64, or 512, the storage area is called a Type II area. If it is 1 or not specified, it is a Type I area (circa Progress v9.x-era and before). All application objects (tables, indexes, and LOB columns) should reside in Type II storage areas. The schema area is always Type I, and should never contain application objects. Migrating data from Type I to Type II typically involves a dump and load. Though small or empty objects can be move from one area to another via command line utility (proutil tablemove or proutil indexmove).

Having data in Type I areas impacts you in a number of ways, including application performance.

Hi.
I sent you a private message with file details, and you are correct, it would appear we have Type I and II mixed on same drive.
kd

Rob Fitzpatrick · Oct 22, 2014

I don't see anything confidential in your structure but I'll respect your wishes and not repost its contents here. I'll briefly summarize for the benefit of the others in the conversation (and ultimately, for your benefit).

All of your application data storage areas are Type II, which is good. The schema area is required to be Type I, so as long as you have no application objects stored in it, you're fine there.

All RPB settings are 64, which is fine for a DB with 4 KB blocks ("fine" = "not the cause of severe performance problems"). Cluster sizes are a mix of 64 and 512 for table areas and 8 for index areas. (At this point I have to assume that the area names truly reflect the data stored in them.)

You have lots of small (2 GB or less) extents per storage area, many of which are quite small. My guess: the tiny ones (2 MB or less) are the result of the area growing into the variable extent (the last one in the area). Then someone noticed that and added more extents, changing the former variable one to fixed and capping its size. If you were going to dump and load I would suggest 8 KB blocks and a simpler structure (fewer extents) but given the reported severity of your symptoms I doubt your structure is your worst bottleneck.

You have 7 variable-size AI areas.

Your BI file is 8 GB of fixed extents with a size-limited variable extent of another 10 GB. Not knowing your application I can't assess an appropriate BI size for you.

Your DB directory (where the schema area resides) and AI extents are on the C: drive. BI and data extents are all in the same directory on the D: drive. That is different from the configuration you described above. You should really have AI extents on separate physical media from the DB. At present if you lose the C: drive, your DB is toast and so are the AI files you would want to use for recovery. That should be as much a priority to address as the performance woes.

Can you add more physical disks to this box? Getting paging file I/O away from your DB would be a good thing. Getting all of your extents away from a RAID 5 volume would be a very good thing.

If you can run a brief test on your system to get a rough ballpark sense of disk write performance, try this script:

Code:

@echo off
rem simple BI grow test
echo.
echo %time%: Creating a sports database...
prodb sports sports
echo.
echo %time%: Setting the BI cluster size to 32 MB...
call proutil sports -C truncate bi -bi 32768
echo.
echo %time%: Growing the BI by 4 additional clusters (256 MB total)...
call proutil sports -C bigrow 4
echo.
echo %time%: Removing the database...
echo y | prodel sports
del sports.st
echo.
echo %time%: Done.

Run it from a proenv command prompt in an empty directory on the C: drive. Repeat on the D: drive. Post the timings for each run.

Rob Fitzpatrick · Oct 22, 2014

I didn't see the output of showcfg (or config, from the start menu). Do you have a compiler installed on the DB server? The product is called "4GL Development System". If you have that installed you can run ad hoc ABL queries that will help you troubleshoot and configure this system. Assuming you can do that, one of the first things I'd suggest is configuring the DB to be able to collect CRUD statistics for your tables and indexes. If you have small static tables and indexes that are "hot" then moving them to the alternate buffer pool could help your performance.

But to do that we need to know the highest table number and index number in your application schema, thus the need to run ad hoc queries. You can get around that by writing the queries, compiling them on a machine that does have the compiler installed, and copying the r-code back the the DB server to run it, but it's a bit of a pain.

Rob Fitzpatrick · Oct 23, 2014

There's a mix of stuff here; DB, OS, storage, client applications. I'll make an attempt at organizing.

OS:

kdefilip said:
More errors in OS system logs than I can shake a stick at

Interesting, but we can't help without details. Unless you're just venting. Which is fine by me; I do it too.

DB:

kdefilip said:
2.3 million latch timeouts in a 10 day period

What to do about this depends on which latch or latches show contention.

kdefilip said:
A 4k blocksize

Perhaps not ideal. But by itself I don't think this is causing severe problems.

kdefilip said:
750m buffer cache who's blocks never reach the -spinskip value, at a time that -spinlocktimeout was set to 800,000. Correct me if I'm wrong, but that would indicate that the buffer is flushing faster than blocks can be moved to the MRU with given spinskip. OR the code is so bad that we are just churning our buffer cache.

I think you're confusing parameter names and meanings. We have mentioned -spin (you had 800,000, then adjusted to 100,000). That is the number of times a process will "spin on" (i.e. repeatedly attempt to acquire) a latch before giving up and "napping" for a set amount of time, and then repeating the cycle. It is not a timer. We have also mentioned -lruskips, which you are not currently using. There are no -spinskip or -spinlocktimeout parameters. The -spin parameter is not directly related to buffer pool LRU chain maintenance.

kdefilip said:
At times, checkpoints at a rate of 6-8 during a 30 second period.

During these periods, what is the value of "sync time" (promon R&D | 3 | 4)? This is the time in seconds when your forward processing is frozen (no transaction activity). This could be related to users' reports of application freezes.

kdefilip said:
Occasional buffer flushes in BI during checkpoints

Those would be buffer pool buffers being flushed at checkpoint, because the blocks are "dirty". Ideally you want zero buffers flushed at checkpoint. Increasing BI cluster size may help, provided your disks aren't saturated with I/O load.

kdefilip said:
All database files on same disk.

Not according to your structure file.

kdefilip said:
An 8k BI blocksize on a disk that is allocated at 2k.

I don't understand this. Do you mean the OS file system allocation unit is 2 KB instead of the NTFS default of 4 KB?
Having a file system block size (aka page size) that is larger than the DB's blocks is a big performance penalty. But you have the opposite situation. So you do multiple file system block I/Os per database disk I/O. That means a theoretical possibility of "torn pages" (physically completing only part of an atomic logical I/O to disk), but not a severe performance penalty as far as I know. But of course I could be mistaken.

kdefilip said:
A BI file that is on a Raid5 system drive with very high split I/O, AI on same drive

Not according to your structure file.

kdefilip said:
More errors in DB logs than I can shake a stick at

Perhaps relevant, but we can't help you without knowing what the errors are. You should know also that some errors in DB logs are actually written by or on behalf of clients, and they actually reflect client-side problems, not DB problems. But you need the error numbers (and in some cases, the error message text) to make that determination.

Client application(s):

kdefilip said:
More end-user complaints than I have ever seen with any database of any size

Hopefully if the other items in this list (and others in this thread) are dealt with, this one will take care of itself.

kdefilip said:
Shared locks that hold long after a transaction is complete

That's a function of record and transaction scope in the application code. Without at least one of (a) a knowledgeable developer with tools and source, or (b) a willing and capable vendor, this one isn't going away any time soon.

kdefilip said:
500 application crash logs in a 15 day period

What to do about this depends on what the errors are.

Storage:

kdefilip said:
Misaligned disks across the board

Not sure quite what you mean by this.

kdefilip said:
A pagefile on same disks as database files

Yeah, not ideal. While you're at it, check if you have anti-virus or other anti-malware software scanning the DB directories. If so, add exceptions as needed.

kdefilip said:
I could go on, but I don't want to bore you. When I say "our database is choking" please don't misconstrued my statement to imply a slight to Progress. This system is exhibiting issues from the client keyboard all the way to the back end.

Not bored. But the devil is in the details. At this rate, despite our best efforts and yours, it will take a while to make meaningful headway.

Time is money, and you'll burn through lots of both trying to figure this out (mostly) on your own. And there is only so much that can be done with remote hands-off help. You may want to consider acquiring the services of a Progress consultant to come on site and help you. It would likely turn around your situation faster and better than you could do on your own, and it would be a fantastic lesson for you to boot.

Rob Fitzpatrick · Oct 23, 2014

More relevant conference goodness, from PUG Challenge Americas 2013:
Rich Banville
Database I/O Mechanisms
slides: http://pugchallenge.org/downloads2013/321_Database_IO.pptx
audio: http://pugchallenge.org/downloads2013/audio/321_Database_IO.mp3

Next year, be sure to get yourself to Westford, Mass. for PUG Challenge 2015. It's the best Progress technical conference there is. I'll buy you a beer and toast your success at resolving this issue.

kdefilip · Oct 23, 2014

Rob Fitzpatrick said:
There's a mix of stuff here; DB, OS, storage, client applications. I'll make an attempt at organizing.

OS:

Interesting, but we can't help without details. Unless you're just venting. Which is fine by me; I do it too.

DB:

What to do about this depends on which latch or latches show contention.

Perhaps not ideal. But by itself I don't think this is causing severe problems.

I think you're confusing parameter names and meanings. We have mentioned -spin (you had 800,000, then adjusted to 100,000). That is the number of times a process will "spin on" (i.e. repeatedly attempt to acquire) a latch before giving up and "napping" for a set amount of time, and then repeating the cycle. It is not a timer. We have also mentioned -lruskips, which you are not currently using. There are no -spinskip or -spinlocktimeout parameters. The -spin parameter is not directly related to buffer pool LRU chain maintenance.

During these periods, what is the value of "sync time" (promon R&D | 3 | 4)? This is the time in seconds when your forward processing is frozen (no transaction activity). This could be related to users' reports of application freezes.

Those would be buffer pool buffers being flushed at checkpoint, because the blocks are "dirty". Ideally you want zero buffers flushed at checkpoint. Increasing BI cluster size may help, provided your disks aren't saturated with I/O load.

Not according to your structure file.

I don't understand this. Do you mean the OS file system allocation unit is 2 KB instead of the NTFS default of 4 KB?
Having a file system block size (aka page size) that is larger than the DB's blocks is a big performance penalty. But you have the opposite situation. So you do multiple file system block I/Os per database disk I/O. That means a theoretical possibility of "torn pages" (physically completing only part of an atomic logical I/O to disk), but not a severe performance penalty as far as I know. But of course I could be mistaken.

Not according to your structure file.

Perhaps relevant, but we can't help you without knowing what the errors are. You should know also that some errors in DB logs are actually written by or on behalf of clients, and they actually reflect client-side problems, not DB problems. But you need the error numbers (and in some cases, the error message text) to make that determination.

Client application(s):

Hopefully if the other items in this list (and others in this thread) are dealt with, this one will take care of itself.

That's a function of record and transaction scope in the application code. Without at least one of (a) a knowledgeable developer with tools and source, or (b) a willing and capable vendor, this one isn't going away any time soon.

What to do about this depends on what the errors are.

Storage:

Not sure quite what you mean by this.

Yeah, not ideal. While you're at it, check if you have anti-virus or other anti-malware software scanning the DB directories. If so, add exceptions as needed.

Not bored. But the devil is in the details. At this rate, despite our best efforts and yours, it will take a while to make meaningful headway.

Time is money, and you'll burn through lots of both trying to figure this out (mostly) on your own. And there is only so much that can be done with remote hands-off help. You may want to consider acquiring the services of a Progress consultant to come on site and help you. It would likely turn around your situation faster and better than you could do on your own, and it would be a fantastic lesson for you to boot.

TheMadDBA said:
For the _proapsv crashes.... check the log files and find the error numbers. That will help us and you searching the KB to determine the root cause.

There are settings for the clients (on the user desktop shortcut) and settings for the appservers. For the three I mentioned they apply to both types of connections. Getting the database startup options will also be helpful to make sure there aren't any other crazy settings. You can look at <Progress Install Directory>/properties/ubroker.properties to find the appserver startup options, this will also give you the locations of the log files.

If you only have the runtime you will not be able to run those kind of statements. You would be looking through the start menu for Client or Character Client. While you are there you can choose the Config option to get the installed products.

If you don't have a query/compile client installed I would suggest starting with -omsize 2048 and see how that goes. Keep increasing from there based on how many OM waits you see after the change.
Then ask your boss to buy a single user development license so you can poke around in the database a little more, without it you will only be able to run compile code and your options will be limited.

TheMadDBA said:
For the _proapsv crashes.... check the log files and find the error numbers. That will help us and you searching the KB to determine the root cause.

Primarily, what we are seeing mostly on the client side is very many -l (little L) which I believe are grabs for more temp space/file at the client. This in and of itself is not the real issue, I don't believe. So what we see is this. ClientOne with begin to gram more and more temp in increments of 10 (starting at what appears to be a devault of 200). It will raise up, sometimes into the 300's and sometimes up to the high 500's. In any event, at some point, you will see clientOne stop grabbing temp space, a short pause in time, One or more OTHER clients will crash, another short pause, then ClientOne begins to grab more temp space.... -l 450 to 460, 460 to 470, etc. Now obviously temp space on one client can not cause another client across town to crash. However, I believe the root of the problem is whatever ClientOne is doing on the backend, long running query or some other issue, that is then exhausing some resource causing one and sometimes multiple clients to crash. Note: This is ALL guess at this point.

kdefilip · Oct 23, 2014

Rob Fitzpatrick said:
Your BI file is 8 GB of fixed extents with a size-limited variable extent of another 10 GB. Not knowing your application I can't assess an appropriate BI size for you.

How can you tell by looking at the structure file which is the BI file. I had an indication early on that BI was on C: with the AI file. If it's not there now, I can only assume I mis-read something or it has been moved.

kdefilip · Oct 23, 2014

Rob Fitzpatrick said:
Can you add more physical disks to this box? Getting paging file I/O away from your DB would be a good thing. Getting all of your extents away from a RAID 5 volume would be a very good thing.

Yes, we are in planning stages of adding more disks to this system, have already talked to vendor about splitting their files in an appropriate fashion.

kdefilip · Oct 23, 2014

Rob Fitzpatrick said:
If you can run a brief test on your system to get a rough ballpark sense of disk write performance, try this script:

A little reluctant to do this on a production system due to my green-ness

kdefilip · Oct 23, 2014

Rob Fitzpatrick said:
I didn't see the output of showcfg (or config, from the start menu).

I think I posted this before, but here it is in case I didn't.

kdefilip · Oct 23, 2014

TheMadDBA said:
I am not sure you can trust the Lru and Skips columns in promon without lruskips being turned on.

Our lruskips are set at 100

kdefilip · Oct 23, 2014

Rob Fitzpatrick said:
I think you're confusing parameter names and meanings. We have mentioned -spin (you had 800,000, then adjusted to 100,000). That is the number of times a process will "spin on" (i.e. repeatedly attempt to acquire) a latch before giving up and "napping" for a set amount of time, and then repeating the cycle. It is not a timer. We have also mentioned -lruskips, which you are not currently using. There are no -spinskip or -spinlocktimeout parameters. The -spin parameter is not directly related to buffer pool LRU chain maintenance.

Yes, I am mixing metaphors. I loosely refer to -spin as a spin lock timeout. My bad. Here is what I understand.
A missed Latch attempt will hammer away at a resource to the value of -spin. If it fails to acquire the resource after that time, "latch timeout" is incremented by 1. The latch attempt then sleeps (or I guess in Progress they call that "nap") for a period of time determined by an internal algorithm which increases upon each subsequent failed attempt at acquiring that Latch. At some point I assume it gives up, but that may be 6 or more "naps" later.
The unbelievable thing to me is that in multiple -spin attempts (800,000 attempts per), a Latch is not being acquired, based upon our high "latch timeout". I also believe that latches, unlike Locks, should be, in most cases, acquired and released VERY quickly - nanoseconds. To steal a line from the kids show "blues clues", That's a clue! that something is wrong.

and again, we are using -lruskips, currently set at 100. It appears, in the buffer cache at least, that no block during peak times EVER reaches the 100 mark which again I belive "That's a clue!" Where I am fuzzy is what other structures also contribute to the "LatchTimout" count.

Also, we are not implementing B2 and hence, the second LRU counter which is always 0.

kdefilip · Oct 23, 2014

Rob Fitzpatrick said:
During these periods, what is the value of "sync time" (promon R&D | 3 | 4)? This is the time in seconds when your forward processing is frozen (no transaction activity). This could be related to users' reports of application freezes.

I don't know, but I will check it out today if it is something I have not already collected. Thanks.

Rob Fitzpatrick · Oct 23, 2014

kdefilip said:
A little reluctant to do this on a production system due to my green-ness

Fair enough. Do you have another system (e.g. test, UAT, DR) with equivalent hardware and OS configuration?

Question 32b Progress Above the 4G line

ProgressTalk.com Sponsor

Member

Active Member

ProgressTalk.com Sponsor

Curmudgeon

Member

Member

ProgressTalk.com Sponsor

ProgressTalk.com Sponsor

ProgressTalk.com Sponsor

ProgressTalk.com Sponsor

Member

Member

Member

Member

Member

Attachments

Member

Member

Member

ProgressTalk.com Sponsor

Similar threads