slow transactions lead to record locks

[ceolivas]

Hi there,

We are running an ERP software that is on a 10.1A progress database, we have a program that records manufacturing items. The same program can run transactions within seconds and then suddenly starts to get the same code but takes 4 or 8 minutes to complete.

I now that I have to post some more information, but as I am not a progress experienced DBA I need your guide.

Any Ideas?

Thanks in advance

 

[TomBascom]

Sounds like a checkpoint. What is your bi cluster size? Are you running a BIW, an AIW and one or more APWs? What is -spin set to?

Use ProTop to investigate.

You might also want to hire a consultant to take a look and bring you up to speed

 

[comatt1]

Sounds like a checkpoint. What is your bi cluster size? Are you running a BIW, an AIW and one or more APWs? What is -spin set to?

Use ProTop to investigate.

You might also want to hire a consultant to take a look and bring you up to speed

ProTop is definately the best product available for free. However, you may need some assistance from the GURU himself to get it to work if you are new to Progress.

I recommend reading some of the documentation about Progress and After/Before imaging.

Unless you can answer the questions above from Tom, you really won't have much using ProTop, or any other utility on the market.

Even ProTop won't give you the answers, just the ability to analyze data so you can troubleshoot based on the information/statistics that are provided.

Progress isn't really a "buy the book and learn kind of language." Unless you get training even ProTop won't help once the consultant leaves - however, if you get them, pick their brain while you can (because you won't have many opportunities to do that).

- good place to start with Progress is just browse the threads on here and pick up those words or phrases.

Be the informed customer for your consultant. If you have some basic knowledge, he/she won't have to spend their entire visit figuring out your setup (FYI, consultants are worth it - if you have Tom Bascom/Dan Foreman, and maybe one or two others).

The few GURUs are very few and far between, and aren't cheap, so get some basic training before you dole out the same amount of money for a two day visit as you would for two weeks of training.

Get training, then the consultant. And when you pick the consultant, pay the premium price, because the two names I mentioned above can answer EVERY question, and are good people to know in general (if you work with Progress DB for more than a year and don't know their names, well, you are living in a hole).

Matt

 

[ceolivas]

First of all,

thank you very much to Tom and Matt for taking their time to answer my question, I did work with progress as a developer for more than 6 years, but my skills on the DBA side are not very good, plus my work was for character based version 7.

I have read some articles trying to understand the new structure for the 10.1A version we are running to prepare myself to resolve this issue.

Noticed that the tool you recommended does not run on Windows (sorry for not posting the whole details) I will post the complete details and will get back to you .

Thanks again.

 

[TomBascom]

ProTop does run on windows. Use the character client. (There is a "GUI" port too but it is ugly, ugly, ugly...)

 

[TomBascom]

Thanks Matt :blush1:

 

[ceolivas]

Hi,

Following you will find the details for this issue

Database version 10.1A
Application developed for lower version but recompiled for 10.1A

Database parameter

bi cluster size 512
We are runnnig BIW for each database
We are not running proAIW No
We are running APWs 1 per database
-spin 2400

Server Data

Dell PowerEdge 2950

Processor:

Processor Brand Processor Version Current Speed State Core Count
Intel(R) Xeon(R) CPU E5410 @ 2.33GHz Model 23 Stepping 6 2333 MHz Present 4


Memory:

Attributes Memory Array 1

Location System Board or Motherboard
Use System Memory
Installed Capacity 4096 MB
Maximum Capacity 32768 MB
Slots Available 8
Slots Used 4
ECC Type Multibit ECC


Connector Name Type Size

DIMM1 DDR2 FB-DIMM-SYNCHRONOUS 1024 MB
DIMM2 DDR2 FB-DIMM-SYNCHRONOUS 1024 MB
DIMM3 DDR2 FB-DIMM-SYNCHRONOUS 1024 MB
DIMM4 DDR2 FB-DIMM-SYNCHRONOUS 1024 MB


Fuente de Poder


Attribute Value

Redundancy Status Full



Location Type Maximum Output Wattage Online Status Power Monitoring Capable

PS 1 Status AC 750 W Presence Detected No
PS 2 Status AC 750 W Presence Detected No



Operating System Microsoft Windows Server 2003, Standard Edition
Operating System Version Version 5.2 (Build 3790 : Service Pack 1) (x86)


Storage

PERC 6/i Integrated

Virtual Disk Details

Virtual Disk 0 RAID-0
Virtual Disk 1 RAID-1


HD

Name State Type Capacity Used RAID Hot Spare Vendor ID Product ID Revision Serial No. Manufacture SAS Address
Physical Disk 0:0:0 Online SAS 136.12GB 136.12GB No DELL ST3146855SS S527 3LN4EZ2J 08 17 2005 5000C5000AA223E9
Physical Disk 0:0:1 Online SAS 136.12GB 136.12GB No DELL ST3146855SS S527 3LN5545Q 08 17 2005 5000C5000AA27AD9
Physical Disk 0:0:2 Online SAS 136.12GB 136.12GB No DELL ST3146855SS S527 3LN557RX 08 17 2005 5000C5000AA27141


Virtual Disk

Name State Layout Size Device Name Type Read Policy Write Policy Stripe Element Size Disk Cache Policy
Virtual Disk 0 Ready RAID-0 136.12GB Windows Disk 0 SAS No Read Ahead Write Back 64 KB Disabled
Virtual Disk 1 Ready RAID-1 136.12GB Windows Disk 1 SAS No Read Ahead Write Back 64 KB Disabled

 

[TomBascom]

Hi,

Following you will find the details for this issue

Database version 10.1A
Application developed for lower version but recompiled for 10.1A

Is something stopping you from going to 10.1C? 10.1C has significant performance advantages over 10.1A.

Database parameter

bi cluster size 512

This is almost certainly too small. A good starting point for a small system is 8192.

We are runnnig BIW for each database
We are not running proAIW No

Bad decision. Every database that has valuable data in it should have after-imaging enabled. Not using after-imaging is like driving without a seat belt.

We are running APWs 1 per database

Per database? How many databases do you have? Are they all similarly configured?

-spin 2400

That's an interesting value. How did you decide on that?

What about -B, -n & -L?

Are you using client/server connections? If so the -Ma & -Mi are interesting too.

How about client settings? -T, -Bt, -q, -TB, -TM etc. They also have an impact.

What is the db block size? Are you using storage areas? If you are how are they configured? If not, why not?

Server Data
...

Is this saying that you have 4 x 2.66Ghz Xeon's in the box?

4GB isn't much for a Windows Server.

Is the server configured to be a server? That is, no screen saver, no file indexing, no crapware running, "optimize as a server" is set and all that stuff?

Your RAID setup looks decidedly fishy. Only 3 actual disks and yet you have RAID and, supposedly, 2 raid sets of virtual disks. I suspect that this setup was done at the factory and without much feel for a database server's needs.

No read ahead? Why not? Write back cache disabled is good for data integrity but bad for performance.

Anyhow it sounds as if you are running into a bottleneck on transaction commits. That is likely due to a combination of factors like the bi cluster size being too small (leading to hard checkpoints) and the disk subsystem being poorly configured for a database server (resulting in long checkpioints and slow write performance).

In the short term you can maybe mitigate that by increasing the bi cluster size (thus spreading out checkpoints) and possibly by increasing -B (although we don't know what the current setting is, a larger setting should result in less IO and thus less stress on the disk).

Longer term steps require more understanding of the system and the workload but things like dumping and re-loading to optimize your storage area configuration would probably make a big difference. As would fixing the "raid".

 

[ceolivas]

Hi,

Thanks for help us out with this, it has been really nice to receive your support, following some comments and answers:


We have not moved to 10.1C because the ERP vendor has not released the application under 10.1C only 10.1B and it seems that it's better as for your comments to wait until it is ready for 10.1C

We increased the bi size and you can see an improvement, but the problems stills appears so we will analyze your recommendation to increase the bi size to 8192

Is there any impact on server performance running the AIW?

There are 6 databases integrated within the ERP, we are only running BIW against the "movements" or "detail" database.


the -spin 2400 value was the vendor default recommendation


Some value as for -B, -n & -L

-L 260000
-Mn 5
-Ma 10
-n 60
-B 5000

-mmax 6000
-s 200
-TB 24
-TM 24
-d dmy
-Bt 2048
-Mm 4096
-cpstream ibm850
-cpterm iso8859-1
-q
-inp 32000
-h 50

We are reviewing the rest of your valuable comments will get the answers soon.

 

[tamhas]

I will let Tom give you the detail response since he is the expert in this area, but are you sure you didn't *reverse* the -L and -B numbers? Not always, but usually, a -L that large indicates bad program logic and the -B is tiny.

 

[TomBascom]

Hi,

Thanks for help us out with this, it has been really nice to receive your support, following some comments and answers:

We have not moved to 10.1C because the ERP vendor has not released the application under 10.1C only 10.1B and it seems that it's better as for your comments to wait until it is ready for 10.1C

With all due respect for the vendor and without knowing which one it is you'll have to excuse me for being somewhat cynical.

They (all of them) routinely throw these scare tactics about specific releases out there. If there is an actual problem with a specific release they should clearly specify what the problem is.

In reality if you can compile code you can upgrade. With, or without, their cooperation. And it is often in your best interests to do so.

(And even if you cannot compile code you can often upgrade at least until the version number changes enough to invalidate r-code...)

We increased the bi size and you can see an improvement, but the problems stills appears so we will analyze your recommendation to increase the bi size to 8192

That's a good start.

Is there any impact on server performance running the AIW?

Some. It's usually quite imperceptible. And it certainly has much less impact of on your career than not being able to recover a damaged database will.

Every database that you update should have after-imaging enabled.

There are 6 databases integrated within the ERP, we are only running BIW against the "movements" or "detail" database.

You need to run a BIW against every database that has write activity. And an AIW too.

the -spin 2400 value was the vendor default recommendation

With all due respect for your vendor I don't think that they're very performance savvy. They rarely are. Their focus is usually on application functionality. Which is a very different topic.

Some value as for -B, -n & -L

-L 260000

Wow! That's scary big. That implies some really, really big transactions. Which implies a poor understanding of transaction and record scoping and a likely confusion of the difference between a business transaction and a database transaction.

But it doesn't cause a performance problem all by itself.

-Mn 5
-Ma 10

That's backwards. What about -Mi? (It should be 1.)

-n 60
-B 5000

As Thomas mentioned that is awfully low. Painfully low even. Set it to 50,000 just for starters.

The next bunch are mostly client parameters:

-mmax 6000

Probably too low for a Windows GUI application. Try 16384 for starters.

-s 200
-TB 24
-TM 24
-d dmy
-Bt 2048
-Mm 4096
-cpstream ibm850
-cpterm iso8859-1
-q
-inp 32000

Those don't seem so bad.

-h 50

Are you really connecting to 50 databases?

We are reviewing the rest of your valuable comments will get the answers soon.

 

[J Ransom]

I know it probably sounds like a no-brainer to the gurus, but we had a similar issue a few years back with our 9.1 database. Do you have enough fixed-size BI and Data files in your database structure to handle the data your ERP is putting in? If you only have the one BI file that can grow, performance can and will get worse as the file grows. Similar with the data files: If you've run out of fixed-size files and have that last file growing, performance will get progressively worse.

Don't know if 10 is susceptible to this as well, but it's a thought I hadn't seen mentioned yet...

 

[ceolivas]

Thanks Tom,

I will review all yur comments with the vendor´s consultant, one interesting thing in J Ransom´s comment is that we suffer from the same slow performance before this started and got worse, checking the database yes we have just 1 bi file.

We can setup a test environment with the 10.1C progress release and test the ERP functionality.

Best Regards

 

[TomBascom]

Number of bi files is not important. Particularly now that "large files" are available. Whether or not they are (or it is) growing can be an issue -- particularly on older and slower systems (or systems with a poor IO subsystem).

The classic v9+ bi file configuration is 1 fixed + 1 variable extent. The biggest problem with that is guessing in advance how large to make the fixed bi extent.

Many customers are also starting to configure their databases with all variable extents these days. Type 2 storage areas take away much of the pain formerly associated with variable extents because they extend the db in much larger chunks (thus doing fewer extend operations) and generally handle IO better. Using variable extents plus large files means that you no longer have to spend lots of time monitoring and adding extents as your db grows. (You still have to pay attention to how full the filesystems are though...)

 

[comatt1]

Number of bi files is not important. Particularly now that "large files" are available. Whether or not they are (or it is) growing can be an issue -- particularly on older and slower systems (or systems with a poor IO subsystem).

The classic v9+ bi file configuration is 1 fixed + 1 variable extent. The biggest problem with that is guessing in advance how large to make the fixed bi extent.

Many customers are also starting to configure their databases with all variable extents these days. Type 2 storage areas take away much of the pain formerly associated with variable extents because they extend the db in much larger chunks (thus doing fewer extend operations) and generally handle IO better. Using variable extents plus large files means that you no longer have to spend lots of time monitoring and adding extents as your db grows. (You still have to pay attention to how full the filesystems are though...)

Won't argue with others choice, but I love using only fixed extents, and variable to handle overflow (which would always mean a problem, since I track area HWMs)

Part of my love of fixed extents is my OC nature.

I want to see

131072000 Nov 22 04:47 dstest_13.d1 - fix
131072000 Nov 22 04:47 dstest_13.d2 - fix
524288 Nov 22 04:47 dstest_13.d3 - var

not

131072000 Nov 22 04:47 dstest_13.d1 - fix
4939300 Nov 22 04:47 dstest_13.d2 - var

-------------

If you have the money/space available I say fixed extents is the method I would recommend.

 

[tamhas]

Tom's point, though, is that the perfectly good reasons for the N fixed and 1 variable strategy are going away in more recent versions. There were really good reasons for the approach, but they just aren't as valid now. Of course, I understand the desire for control, but there are ways to accomplish the same thing in different ways.

 

[ceolivas]

Hi,

Thanks for your responses, they were very helpful

We have been working with the vendor, they are doing an analysis and will give us some recommendations, I will review them and if necessary I will bother you for some insights about them.

Best Regards