Slow response time

[autoent]

We are running on 2003 Server R2 SP2 w/ 4GB of memory. The server keeps running out of memory and crashes. Here are my database settings. Is there any settings I can tweak for optimal performance?

-S 23330
-inp 10240
-TB 8
-nb 255
-mmax 20000
-D 250
-l 2000
-Bt 10000
-s 128
-debugalert
-lng ENGLISH
-rereadnolock

 

[LarryD]

Which version of Progress?
Workgroup or Enterprise?
What are the settings for the database startup?
How many users? Normal and peak?
What else is running on the Windows server?
... amongst other possible questions...

 

[TomBascom]

In addition to what Larry said:

Those are client settings not server settings.

A server crashing is a very different problem from slow performance. Both are serious but they a long ways from being equivalent.

 

[autoent]

Which version of Progress? Openedge 10.1B
Workgroup or Enterprise? Workgroup
What are the settings for the database startup? Where can I see this? I am not a progress programmer, just try to figure out this on my own. The vendor is not helpful
How many users? Normal and peak? Normal=15 Peak=20
What else is running on the Windows server? Terminal Services & DNS

 

[VageeshA]

What are the settings for the database startup? Where can I see this? I am not a progress programmer, just try to figure out this on my own. The vendor is not helpful

If you are starting the database using "Progress Explorer Tool" you will find the details in
Progress Installation Directory\properties\conmgr.properties file

If you are using some script to start the database, it will have complete path for the .pf file.
.pf file contains the parameters for database startup.


Regards,
Vageesh

 

[LarryD]

There may or may not be a .pf file for the database startup, so you cannot rely on that being the case. There may be a .BAT file for the startup on Windows if starting with a script and not the explorer tool.

As to you trying to figure it out, unless you can find the startup parameters it will be extremely difficult to 'fix' any issues that may be there. You would typically be looking for the command "proserve", "proserve.bat", "_mprosrv.exe", or "_mprosrv" it is within a batch startup script.

 

[TomBascom]

The easiest way to determine what startup parameters and configuration options are actually in use is to examine the .lg file. The first few lines after startup list everything. The most recent startup is the most interesting -- if your .lg file has never been truncated we probably don't care about startup options from 3 years ago... so go to the bottom and work your way back to find the most recent startup. Or shutdown, rename the .lg file and restart. Or use the "prolog" command to truncate it and then restart.

Post the relevant portion of the .lg file using [ C O D E ] tags so that it is readable.

Just guessing but you probably have a -B setting that is way too low.

As for your "out of memory crashes" you will need to provide more details. Actual error messages are always helpful.

 

[autoent]

Here are the start up parameters for 1 of 5 databases. Should the parameters be the same for all Databases?

(333) Multi-user session begin.
(5326) Begin Physical Redo Phase at 0 .
(7161) Physical Redo Phase Completed at blk 125 off 5441 upd 2804.
(13547) At end of Physical redo, transaction table size is 256.
(5644) Started for 23330 using TCP, pid 1788.
(8836) Connecting to Admin Server on port 7839.
(8846) Registered with Admin Server.
(4234) Progress OpenEdge Release 10.1B build 1216 on WINNT .
(4281) Server started by SYSTEM on CON:.
(6574) Started using pid: 1788.
(4235) Physical Database Name (-db): R:\Royal4-6.6\db\r4sys.
(4236) Database Type (-dt): PROGRESS.
(4237) Force Access (-F): Not Enabled.
(4238) Direct I/O (-directio): Not Enabled.
(4239) Number of Database Buffers (-B): 3000.
(9422) Maximum private buffers per user (-Bpmax): 64.
(4240) Excess Shared Memory Size (-Mxs): 16398.
(10014) The shared memory segment is not locked in memory.
(4241) Current Size of Lock Table (-L): 8192.
(4242) Hash Table Entries (-hash): 887.
(4243) Current Spin Lock Tries (-spin): 0.
(6526) Number of Semaphore Sets (-semsets): 3.
(13924) Maximum Shared Memory Segment Size (-shmsegsize) 128 Mb.
(4244) Crash Recovery (-i): Enabled.
(6573) Database Blocksize (-blocksize): 4096.
(4245) Delay of Before-Image Flush (-Mf): 3.
(4247) Before-Image File I/O (-r -R): Reliable.
(4249) Before-Image Truncate Interval (-G): 0.
(4250) Before-Image Cluster Size: 524288.
(4251) Before-Image Block Size: 8192.
(4252) Number of Before-Image Buffers (-bibufs): 20.
(9238) BI File Threshold size (-bithold): 0.0 Bytes.
(6552) BI File Threshold Stall (-bistall): Disabled.
(4254) After-Image Stall (-aistall): Not Enabled.
(4255) After-Image Block Size: 8192.
(4256) Number of After-Image Buffers (-aibufs): 20.
(8527) Storage object cache size (-omsize): 1024
(4257) Maximum Number of Clients Per Server (-Ma): 5.
(4258) Maximum Number of Servers (-Mn): 21.
(4259) Minimum Clients Per Server (-Mi): 4.
(4260) Maximum Number of Users (-n): 51.
(4261) Host Name (-H): pdk-ts1.
(4262) Service Name (-S): 23330.
(4263) Network Type (-N): TCP.
(4264) Character Set (-cpinternal): ISO8859-1.
(4282) Parameter File: Not Enabled.
(5647) Maximum Servers Per Broker (-Mpb): 20.
(5648) Minimum Port for Auto Servers (-minport): 3000.
(5649) Maximum Port for Auto Servers (-maxport): 5000.
(8865) This broker supports both 4GL and SQL server groups.
(9336) Created shared memory with segment_id: 19005440
(12813) Allowed index cursors (-c): 204.
(12814) Group delay (-groupdelay): 10.
(12815) Lock table hash table size (-lkhash): 1237
(12816) Maxport (-maxport): 5000
(12817) Minport (-minport): 3000
(12818) Message Buffer Size (-Mm): 1024
(12820) Maximum Servers per Broker (-Mpb): 20
(12823) Semaphore Sets (-semsets): 3
(13870) Database Service Manager - IPC Queue Size (-pica) : 6.0 KBytes.
(13896) TXE Commit lock skip limit (-TXESkipLimit): 10000.
(10471) Database connections have been enabled.
(5644) Started for 23430 using TCP, pid 4092.
(5645) This is an additional broker for this protocol.
(8864) This broker supports SQL server groups only.
P-3408 T-3412 I WDOG 21: (2518) Started.

 

[TomBascom]

Here are the start up parameters for 1 of 5 databases. Should the parameters be the same for all Databases?

That depends on the parameter, the application and the usage of the databases. And the problem being addressed.

(333) Multi-user session begin.
(5326) Begin Physical Redo Phase at 0 .
(7161) Physical Redo Phase Completed at blk 125 off 5441 upd 2804.
(13547) At end of Physical redo, transaction table size is 256.
(5644) Started for 23330 using TCP, pid 1788.
(8836) Connecting to Admin Server on port 7839.
(8846) Registered with Admin Server.
(4234) Progress OpenEdge Release 10.1B build 1216 on WINNT .
(4281) Server started by SYSTEM on CON:.
(6574) Started using pid: 1788.
(4235) Physical Database Name (-db): R:\Royal4-6.6\db\r4sys.
(4236) Database Type (-dt): PROGRESS.
(4237) Force Access (-F): Not Enabled.
(4238) Direct I/O (-directio): Not Enabled.
(4239) Number of Database Buffers (-B): 3000.

3000 is a very small number of buffers. This is Windows so you have a 4k db block. 3,000 4k buffers = 12MB of RAM being used as a data cache. That's pathetic. Change it to 30,000.

(9422) Maximum private buffers per user (-Bpmax): 64.
(4240) Excess Shared Memory Size (-Mxs): 16398.
(10014) The shared memory segment is not locked in memory.
(4241) Current Size of Lock Table (-L): 8192.
(4242) Hash Table Entries (-hash): 887.
(4243) Current Spin Lock Tries (-spin): 0.

The Workgroup DB is inherently slow and this is why. Worse yet if it happens to be running on a multi-core system and using the default semaphore based locking mechanism (which is what -spin 0 says) it is terrible.

Personally, I never work with WG.

Just for kicks try adding -spin 1. (I believe that a change was made to WG somewhere around the 10.1B time frame to allow that.)

(6526) Number of Semaphore Sets (-semsets): 3.
(13924) Maximum Shared Memory Segment Size (-shmsegsize) 128 Mb.
(4244) Crash Recovery (-i): Enabled.
(6573) Database Blocksize (-blocksize): 4096.
(4245) Delay of Before-Image Flush (-Mf): 3.
(4247) Before-Image File I/O (-r -R): Reliable.
(4249) Before-Image Truncate Interval (-G): 0.
(4250) Before-Image Cluster Size: 524288.
(4251) Before-Image Block Size: 8192.
(4252) Number of Before-Image Buffers (-bibufs): 20.
(9238) BI File Threshold size (-bithold): 0.0 Bytes.
(6552) BI File Threshold Stall (-bistall): Disabled.
(4254) After-Image Stall (-aistall): Not Enabled.
(4255) After-Image Block Size: 8192.
(4256) Number of After-Image Buffers (-aibufs): 20.
(8527) Storage object cache size (-omsize): 1024
(4257) Maximum Number of Clients Per Server (-Ma): 5.
(4258) Maximum Number of Servers (-Mn): 21.
(4259) Minimum Clients Per Server (-Mi): 4.

This is probably wrong. -Mi should be 1 to enable "round robin" connection pooling. That generally provides smoother performance.

(4260) Maximum Number of Users (-n): 51.
(4261) Host Name (-H): pdk-ts1.
(4262) Service Name (-S): 23330.
(4263) Network Type (-N): TCP.
(4264) Character Set (-cpinternal): ISO8859-1.
(4282) Parameter File: Not Enabled.
(5647) Maximum Servers Per Broker (-Mpb): 20.
(5648) Minimum Port for Auto Servers (-minport): 3000.
(5649) Maximum Port for Auto Servers (-maxport): 5000.
(8865) This broker supports both 4GL and SQL server groups.
(9336) Created shared memory with segment_id: 19005440
(12813) Allowed index cursors (-c): 204.
(12814) Group delay (-groupdelay): 10.
(12815) Lock table hash table size (-lkhash): 1237
(12816) Maxport (-maxport): 5000
(12817) Minport (-minport): 3000
(12818) Message Buffer Size (-Mm): 1024

You might want to consider changing this to 4096 or 8192. But if you do, you need to do it everywhere -- both server side and client side. (The benefit is that it allows more data to be passed in a message when NO-LOCK reads are performed.)

(12820) Maximum Servers per Broker (-Mpb): 20
(12823) Semaphore Sets (-semsets): 3
(13870) Database Service Manager - IPC Queue Size (-pica) : 6.0 KBytes.
(13896) TXE Commit lock skip limit (-TXESkipLimit): 10000.
(10471) Database connections have been enabled.
(5644) Started for 23430 using TCP, pid 4092.
(5645) This is an additional broker for this protocol.
(8864) This broker supports SQL server groups only.
P-3408 T-3412 I WDOG 21: (2518) Started.[/QUOTE]

 

[autoent]

Here are the start up parameters for the other "Main" database. Thanks!

(333) Multi-user session begin.
(5326) Begin Physical Redo Phase at 896 .
(7161) Physical Redo Phase Completed at blk 1021 off 7125 upd 83.
(13547) At end of Physical redo, transaction table size is 256.
(5644) Started for 23331 using TCP, pid 1856.
(8836) Connecting to Admin Server on port 7839.
(8846) Registered with Admin Server.
(4234) Progress OpenEdge Release 10.1B build 1216 on WINNT .
(4281) Server started by SYSTEM on CON:.
(6574) Started using pid: 1856.
(4235) Physical Database Name (-db): R:\Royal4-6.6\db\erp.
(4236) Database Type (-dt): PROGRESS.
(4237) Force Access (-F): Not Enabled.
(4238) Direct I/O (-directio): Enabled.
(4239) Number of Database Buffers (-B): 40000.
(9422) Maximum private buffers per user (-Bpmax): 64.
(4240) Excess Shared Memory Size (-Mxs): 16398.
(10014) The shared memory segment is not locked in memory.
(4241) Current Size of Lock Table (-L): 60000.
(4242) Hash Table Entries (-hash): 13063.
(4243) Current Spin Lock Tries (-spin): 0.
(6526) Number of Semaphore Sets (-semsets): 3.
(13924) Maximum Shared Memory Segment Size (-shmsegsize) 256 Mb.
(4244) Crash Recovery (-i): Enabled.
(6573) Database Blocksize (-blocksize): 4096.
(4245) Delay of Before-Image Flush (-Mf): 3.
(4247) Before-Image File I/O (-r -R): Reliable.
(4249) Before-Image Truncate Interval (-G): 0.
(4250) Before-Image Cluster Size: 524288.
(4251) Before-Image Block Size: 8192.
(4252) Number of Before-Image Buffers (-bibufs): 20.
(9238) BI File Threshold size (-bithold): 0.0 Bytes.
(6551) BI File Threshold Stall (-bistall): Enabled.
(4254) After-Image Stall (-aistall): Not Enabled.
(4255) After-Image Block Size: 8192.

(4256) Number of After-Image Buffers (-aibufs): 20.
(8527) Storage object cache size (-omsize): 1024
(4257) Maximum Number of Clients Per Server (-Ma): 3.
(4258) Maximum Number of Servers (-Mn): 26.
(4259) Minimum Clients Per Server (-Mi): 2.
(4260) Maximum Number of Users (-n): 51.
(4261) Host Name (-H): pdk-ts1.
(4262) Service Name (-S): 23331.
(4263) Network Type (-N): TCP.
(4264) Character Set (-cpinternal): ISO8859-1.
(4282) Parameter File: Not Enabled.
(5647) Maximum Servers Per Broker (-Mpb): 25.
(5648) Minimum Port for Auto Servers (-minport): 3000.
(5649) Maximum Port for Auto Servers (-maxport): 5000.
(8863) This broker supports 4GL server groups only.
(9336) Created shared memory with segment_id: 19005440
(12813) Allowed index cursors (-c): 204.
(12814) Group delay (-groupdelay): 10.
(12815) Lock table hash table size (-lkhash): 9337
(12816) Maxport (-maxport): 5000
(12817) Minport (-minport): 3000
(12818) Message Buffer Size (-Mm): 1024
(12820) Maximum Servers per Broker (-Mpb): 25
(12823) Semaphore Sets (-semsets): 3
(13870) Database Service Manager - IPC Queue Size (-pica) : 6.0 KBytes.
(13896) TXE Commit lock skip limit (-TXESkipLimit): 10000.
(10471) Database connections have been enabled.
(5644) Started for 23341 using TCP, pid 3376.
(5645) This is an additional broker for this protocol.
(8864) This broker supports SQL server groups only.
P-3940 T-3944 I WDOG 26: (2518) Started.