Question Performance issues

[jackjack182]

Good day everyone, I'm planning to enable AI extents on our db but keep getting critical page errors on NMON. Here is our hardware config:

2 - cpus currently
2 - cpus configured
4204 - mhz cpu clock rate
Power PC - Power 6 - Processor
64 Bit - hardware
64 Bit - Kernel
1,06 - 88C34 - Logical Partition
5.3.10.0 TL10 - AIX Kernel Logon

Memory Physical Page Space
% used 94.2 % 0.0 %
% free 5.8% 100.0%
mb used 7264.0mb 5.7 mb
mb free 447.6 mb 16,378.3mb
total mb 7712.0mb 16,384 mb

proserve bill -Mn 1 -B 10000 -L 600000 -Ma 65 -n 65 -N tcp -S billdb -H aix_server

bill.st
#
b /bcs/bill.b1
#
d "Schema Area":6,64;1 /bcs/bill.d1 f 2097024
d "Schema Area":6,64;1 /bcs/bill.d2 f 2097024
d "Schema Area":6,64;1 /bcs/bill.d3 f 2097024
d "Schema Area":6,64;1 /bcs/bill.d4 f 2097024
d "Schema Area":6,64;1 /bcs/bill.d5 f 2097024
d "Schema Area":6,64;1 /bcs/bill.d6

 

[TomBascom]

Yes, with that configuration you probably have lots of performance issues.

 

[Rob Fitzpatrick]

You haven't actually articulated what your "performance issues" are but given that configuration I'd be more surprised if you didn't have performance issues than if you did.

Microscopic buffer pool, -n too small, 65 remote clients crowded onto one server, huge lock table, Type I storage, all tables and indexes in the schema area, -bibufs and -spin at default values. What is your BI configuration? Are you running helper processes?

You really need some help with that DB; maybe with the system too. Have you considered hiring a consultant? It would likely be cheaper, quicker, and yield a better end result than trying to become an expert overnight (which won't happen) and fix it yourself.

 

[TomBascom]

Now that I have another cup of coffee...

I use nmon quite a lot but I've never heard of a "critical page error". Can you be more specific? I'm guessing that someone has scripted something for you. But that's just a guess.

Beyond that, specific things that I see in your configuration:

0) The memory usage numbers seem important to you. I suspect that they do not mean what you think they mean. Modern UNIX (such as AIX) will pretty much always use almost all of your memory. That's normal. And it is nothing to be concerned about. If that is related to the "critical page error" then the error is probably specious.

1) You do not mention what version of Progress you have. I can see that is is version 10.something. But knowing the minor release and service pack is important. You also do not mention if it is 32 bit or 64 (it should be 64 bit -- there is no excuse for running 32 bit executables any longer) and wether this is a "workgroup" or "enterprise" license (I see -n 65 so I am worried that it is "workgroup" which is a very limiting license from a performance perspective).

1) It looks like you have 8GB of RAM in the partition. But your -B parameter is a mere 10,000 so the db is using approximately 40MB if the db has 4k blocks, 80MB if they are 8k. That's ridiculously low. It's a small database but a -B of 100,000 would be "a good start". (Look in the .lg file to find the db block size -- search for the last occurrence of "(333)" and scroll down a bit looking though the startup messages.)

2) -L 600,000 is huge. Especially for such a small database. That suggests that there is code which is not particularly well written. Crappy code can defeat any amount of tuning.

3) You have everything in the schema area. The schema area should only contain schema. All data, indexes and LOBs should be in discrete type 2 storage areas. You might want to look at this: http://pugchallenge.org/2012PPT/sos.pptx

4) All of your extents are approximately 2GB. That suggests that you do not have "large files" enabled. Which in turn suggests that you might have a "workgroup" license. If you care about performance you really need an "enterprise" license.

5) -N tcp is pointless. This parameter serves no purpose. TCP is the only network type and the default (there was a time several eons ago when other network types seemed like they might be relevant -- that day passed long, long ago).

6) -Mn 1 and -Ma 65 is pretty ugly. Do the clients run locally? Or are they remote connections?

7) What are the client startup parameters?

8) What is the bi cluster size? (Look in the .lg file as described above.)

 

[jackjack182]

Now that I have another cup of coffee...
Thank you very much for the reply.

I use nmon quite a lot but I've never heard of a "critical page error". Can you be more specific? I'm guessing that someone has scripted something for you. But that's just a guess.
I stand corrected, page fault danger goes up to 600 in nmon. Warning setting > 4 /s Danger > 40 /s

Beyond that, specific things that I see in your configuration:

0) The memory usage numbers seem important to you. I suspect that they do not mean what you think they mean. Modern UNIX (such as AIX) will pretty much always use almost all of your memory. That's normal. And it is nothing to be concerned about. If that is related to the "critical page error" then the error is probably specious.

1) You do not mention what version of Progress you have. I can see that is is version 10.something. But knowing the minor release and service pack is important. You also do not mention if it is 32 bit or 64 (it should be 64 bit -- there is no excuse for running 32 bit executables any longer) and wether this is a "workgroup" or "enterprise" license (I see -n 65 so I am worried that it is "workgroup" which is a very limiting license from a performance perspective).
Progress OE Workgroup 10.1C build 1282, 64 bit

1) It looks like you have 8GB of RAM in the partition. But your -B parameter is a mere 10,000 so the db is using approximately 40MB if the db has 4k blocks, 80MB if they are 8k. That's ridiculously low. It's a small database but a -B of 100,000 would be "a good start". (Look in the .lg file to find the db block size -- search for the last occurrence of "(333)" and scroll down a bit looking though the startup messages.)
Db block size is 8192.


2) -L 600,000 is huge. Especially for such a small database. That suggests that there is code which is not particularly well written. Crappy code can defeat any amount of tuning.
What would be the outcome if I try to lower this in half?


3) You have everything in the schema area. The schema area should only contain schema. All data, indexes and LOBs should be in discrete type 2 storage areas. You might want to look at this:

I should dump and load to the new .st?
Can you specify, thanks.


4) All of your extents are approximately 2GB. That suggests that you do not have "large files" enabled. Which in turn suggests that you might have a "workgroup" license. If you care about performance you really need an "enterprise" license. Yep this is Workgroup license, we chose this due to budget constraints.

5) -N tcp is pointless. This parameter serves no purpose. TCP is the only network type and the default (there was a time several eons ago when other network types seemed like they might be relevant -- that day passed long, long ago).
So I can omit this? Our present parameters was just inherited from our old CompaqDS20 machine, with Progress Version 9 (also workgroup).


6) -Mn 1 and -Ma 65 is pretty ugly. Do the clients run locally? Or are they remote connections?
Yes, all connections are presently LAN connected. But we have plans of remote connections next year or two years from now.



7) What are the client startup parameters?
Prowin32.exe -p SecScr.w -db billing -N tcp -H aix_server -S billingdb -ininame bcsv10.ini



8) What is the bi cluster size? (Look in the .lg file as described above.)

BI Cluster size = 524288

 

[TomBascom]

Page faults, by themselves, are nothing to worry about. Excessive page *outs* /might/ indicate a memory shortage -- but that can also be deceptive as a lot of legitimate IO also uses the VM subsystem.

Workgroup = performance is not important. Many performance oriented features are simply not available. Options are limited. The lost productivity from poor performance quickly outweighs the short-sighted savings of workgroup.

10.1C is ancient, obsolete and unsupported. You should upgrade. Can you compile the code? If so upgrading to something current, like 11.2, is trivial. If you only have r-code you can still upgrade to 10.2B07.

64 bit executable is good. That means that you are not limited with regards to -B. So the first thing I would do is increase -B by 10x to 100,000 (800MB). Then I'd likely bump it up to 500,000 (4GB). Or you could just go there in one step. Rule of thumb: to cut IO ops in half increase -B by 4x.

Reducing -L won't directly impact performance. The comment about -L is that such a large value strongly suggests that there are serious coding issues with the app. Which just means that tuning is much harder because bad code always defeats good tuning.

Yes, you can omit -N TCP. -Mn and -Ma (and the missing -Mi) are more typically set to values like -Mn 20 -Ma 5 -Mi 1. You should also set -minport and -maxport (specific values depend on what ports are available in your network but values line -minport 20000 -maxport 21000 are usually fine).

For workgroup you generally want the bi cluster size to be smaller. That will result in more frequent, but shorter duration, checkpoints. Try 128k.

Yes, you must dump & load in order to reconfigure to type 2 storage. The linked PPT has lots more details. At a minimum I would create 3 type 2 storage areas -- 1 for data, 1 for indexes and 1 for LOBs (if you have any). If you have any word indexes put those in their own storage area also. NOTHING EXCEPT SCHEMA should be in the schema area. In order for an area to be type 2 it must have a blocks per cluster of at least 8. If you have large tables (more than 1GB) they could go in an area with a cluster size of 512.

Don't forget to implement after-imaging. It's very important.

 

[TomBascom]

I forgot.. your client-side settings are in that INI file. Until you reveal those we can't comment on them.