Upgrade from 9.1e to 11.2 / Performance

[PMiddleton]

Windows 2008 / OpenEdge 11.2 32bit / GUI clients / Extents / Workgroup

I have recently upgraded the source code and database to OE 11.2. I have done a binary dump and load for my new database along with an index rebuild / truncated bi (before and after). Recompiled everything, created my new clients and deployed.

Reports are taking 25% more time to process.
Queries that took 5 seconds are now taking 10.

I've looked into some articles by Gus Bjorkland - Performance Tuning Made Simple

I don't have enterprise so enabling large files is not available nor is -spin. I surely didn't expect to go backwards on a new upgrade and I am certain, it's because I've missed something.

 

[Cringer]

Did you change your database to utilise type II storage areas (if you don't know then probably not, but if you pop your structure file up here then someone will tell you).
I'll also move this to the right forum

 

[PMiddleton]

Did you change your database to utilise type II storage areas (if you don't know then probably not, but if you pop your structure file up here then someone will tell you).
I'll also move this to the right forum

Here's my ST file:
#
b D:\db\wall\wall.b1
#
d "Schema Area":6,64 D:\db\wall\wall.d1
#
d "Plexus-Data":51,64 D:\db\wall\wall_51.d1
#
d "Plexus-Indexes":52,256 D:\db\wall\wall_52.d1
#
d "Mail-Table":61,64 D:\db\wall\wall_61.d1
#
d "Mail-Indexes":62,256 D:\db\wall\wall_62.d1
#
d "Envelope-Table":71,64 D:\db\wall\wall_71.d1
#
d "Envelope-Indexes":72,256 D:\db\wall\wall_72.d1
#
d "Mailheader-Table":81,64 D:\db\wall\wall_81.d1
#
d "Mailheader-Indexes":82,256 D:\db\wall\wall_82.d1

 

[Cringer]

That's a good start - you have Type II storage. Just to check, do you have any tables or indexes stored in the Schema Area?
I will leave further comment to those with greater understanding of these things!! Wouldn't want to give bad advice.

 

[LarryD]

What are your database startup parameters? How much memory on your Windows box?

 

[Rob Fitzpatrick]

OK, since you're interested in performance, here are some basic rules.

0. Don't use Workgroup. Period.
The time and effort you spend trying to make a silk purse out of a sow's ear costs real money. Spend that money on an Enterprise RDBMS license and open up a world of performance-tuning options that are currently unavailable to you. I can't emphasize that enough.

1. Use the 64-bit database.
With 32-bit you are very constrained on the total size of shared memory. Thus if your DB is of any meaningful size you are forcing down your buffer hit ratio and causing more disk I/O.

2. Use quality storage.
You haven't mentioned your storage back-end. I hope you have lots of fast disks in RAID 10 to deal with all the physical I/O you're doing. Especially given that your entire database is in one directory. If you have multiple disks you should distribute your I/O load by moving your BI, AI, and data extents onto different volumes.

3. Use Type II storage areas.
According to your structure file you dumped and loaded into Type I areas despite moving to 11.2. You should expect your performance to be bad until you correct that with another dump and load.

4. Tune your DB structure.
Your structure shows all table areas have 64 RPB. Is this appropriate for your data characteristics and block size, or do your 8 KB DB blocks each contain unnecessary empty space? If your records are small, particularly in actively-read blocks, then your caching performance is hurt further and your application performance suffers.

5. Tune your broker startup parameters.
Obviously, we can't even guess what you have at this point. But to correct one point you made, you can specify -spin 1 with a Workgroup DB. It's better to use spin locks than semaphores.

6. Tune your BI file.
If you stick with Workgroup you should reduce your cluster size from the default, whereas you would increase it from the default under Enterprise.

7. Not a performance tip: enable after imaging
If you care about your data, and if it would affect the business to lose it, then you have to enable after imaging. You should worry about this before you worry about performance. Being in a position where you only have DB backups and a crash could cost you up to 24 hours of business data (*IF* your backups are good, and available off-site) is not a place where you want to be.

 

[oli]

Beside the database tuning, I will focus on several things that might help.
The following suggestions have shown us concrete improvement (but we are using OpenEdge 10.2b):

- Possibly modify the session's .INI file (or registry settings) with the 2 following lines:
MultitaskingInterval=0
MTIDiscardMessages=yes
- Possibly set the Windows 2008's "Power Plan" to "High Performance" instead of the default "Balanced"
- If your app is referencing .Net objects, disable the DLL verification process by setting an extra property <generatePublisherEvidence enabled="false" /> in file \%DLC%\bin\prowin32.exe.config
- If your server is an HP Proliant, check (in the BIOS) the "Minimum Processor Idle Power State" (Processor C-State) that allows the processor to save energy by going into various low power idle states

If you are in a multi-users environment with network client/server connection, you can also:
- disable the "Receive Side Scaling" and "TCP Chimney Offload" options on both OS and Network Adapter(s) settings (see command netsh)
- evaluate a more adequate Message Buffer Size (-Mm) than the 1KB default.

If you are using the Windows 2008 Terminal Server from a Windows 7 or Vista client, there might be latencies in windows refresh. Things are going better if the color quality is reduced in the Remote Desktop Connection's "Display" settings (f.i. "High Color 16bits" instead of "Highest Quality 32 bits").

Hope it helps.

 

[tamhas]

Re-evaluate your startup parameters. Many defaults have changed over the years with consequent impacts on performance if you don't adjust them. Posting them here is likely to result in suggestions and probably should have been the first step in addition to the structure file.

 

[PMiddleton]

My client would not spend the extra $ for Enterprise - I would like that personally but I am not paying their bills.

I currently have 32bit because I was told you couldn't install 64bit rdbms on the same box as 32bit OE Developement Studio. The client wanted it setup this way so it's 32bit (they have maintenance so I have options possibly)

The last time I started the db I used -s 64 -bibufs 25 -spin 1 -biblocksize 4.

Previously I had the following startup parameters:

-Bt 400 # temp-table-buffers
-mmax 12288 # max-r-code-memory
-T c:\plexus\temp # temp-file-directory
-TB 24 # sort-block-size
-TM 16 # merge-buffer-count
-yy 1930 # year-offset
-s 1024 # stack
-nb 175 # nested blocks
-rand 2 # a more random number generator

Intel Xeon CPU E5-2650 0 @ 2ghz (4 processors)
4GB of installed RAM
64bit Operating System
VMWare Virtual disk SCSI Disk Device(s) - 2

 

[Rob Fitzpatrick]

4 GB of RAM, 4 CPUs, and VMware to boot.

So they're basically running a 32-bit Workgroup database with Type I storage on a three-year-old laptop. And running Eclipse on the same box. And wondering why performance is bad.

Money to spend on VMware licenses but can't dish out for a proper database license or hardware? (Or consulting.) Some people are penny-wise and pound-foolish.

The last time I started the db I used -s 64 -bibufs 25 -spin 1 -biblocksize 4.

• -s is a client startup parameter (stack size), not a broker parameter.
• -biblocksize is a parameter on proutil -C truncate bi, not a broker parameter.
• You haven't specified -B, which means you have the default of 3000. With your 8 KB database blocks you have 23.4 MB of buffers. With 4 GB of RAM and a 32-bit RDBMS you could be in the range of 800 - 1000 MB* with vastly improved caching performance, say 6x fewer disk reads as a back-of-the-envelope calculation. But as I said, every v10 or later Progress DB should use Type II storage. Without that your caching will not be nearly as efficient as it should be.

*assuming your DB is large enough for that to matter.

Just out of curiosity, how large is this DB and how many users?

 

[PMiddleton]

Thanks Rob -

One db that is just over 3.5gb and another that is just less than 1gb.

They have about 5 active users all day 2 shifts but 20 users total.
I thought someone asked what my client side pf was?

-Bt 400 # temp-table-buffers
-mmax 12288 # max-r-code-memory
-T c:\plexus\temp # temp-file-directory
-TB 24 # sort-block-size
-TM 16 # merge-buffer-count
-yy 1930 # year-offset
-s 1024 # stack
-nb 175 # nested blocks
-rand 2 # a more random number generator

I start the db with:

-s 64 -bibufs 25 -spin 1 -biblocksize 4

I see your comments about the -biblocksize - I thought it applied for DB Startup - my mistake.

So I found an article about Type II storage and how to define it:

<snip>
The method is no different from creating Type I Storage Areas (PROSTRCT CREATE | ADD). The difference is the definition in the structure file (.st) used. In addition to the Type I structure file, a third parameter after the "records per block" area definition, defines the number of blocks per cluster for that Storage Area.
# a Type II Storage Area definition:
#
# d "[your storage area name]":[area number],[records per block];[cluster size] [location] [f/v] [size (KB]
# Type II Storage Area 131, with 64 records per block and 8 blocks per cluster
d "Data AreaII":131,64;8 /data f 1024000
d "Data AreaII":131,64;8 /data

Type II Storage Areas are identified as being defined with either 8,64, or 512 database blocks per cluster.
</snip>

Right now the data looks like this in the Type I

b D:\db\wall\wall.b1
#
d "Schema Area":6,64 D:\db\wall\wall.d1
#
d "Plexus-Data":51,64 D:\db\wall\wall_51.d1 /*1,708,000 KB*/
#
d "Plexus-Indexes":52,256 D:\db\wall\wall_52.d1 /*187,000 KB*/
#
d "Mail-Table":61,64 D:\db\wall\wall_61.d1 /*673,000 KB*/
#
d "Mail-Indexes":62,256 D:\db\wall\wall_62.d1 /*26,000 KB*/
#
d "Envelope-Table":71,64 D:\db\wall\wall_71.d1 /*148,000 KB*/
#
d "Envelope-Indexes":72,256 D:\db\wall\wall_72.d1 /*59,000 KB*/
#
d "Mailheader-Table":81,64 D:\db\wall\wall_81.d1 /*552,000 KB*/
#
d "Mailheader-Indexes":82,256 D:\db\wall\wall_82.d1 /*140,000 KB*/

 

[PMiddleton]

More...hardware

I am not familiar with the hardware setup with VMWare. Server shows 4gb of ram but when I look at the actual hardware it's a Dell PowerEdge R720
Intel Xeon E5-2650 2.ghz, 64gb RAM, Dell PERC H710 Raid Controller, 6x 600GB Raid-10 10k, 6x146gb Raid-10 15k.

VMWare ESXI 5.0

 

[TomBascom]

Squandering money on ineffective and unnecessary hardware and virtualization software that the core application cannot leverage?

Sounds like an unconscionable misapplication of corporate capital.

Most business people really aren't that stupid. Or is it just that nobody has gone to the effort to explain it in terms that a business person can understand? Whoever it is that is making hardware & os recommendations is clearly better at this than whoever it is that is making db & application recommendations.