Startup parameters for progress database -- Optimization

-B 50,000 is ridiculously low.

The article says that bug referenced exists in 10.2a. I see no reason to doubt that.

Whether it is "the" bug causing your problem or problems is unknown because your problem or problems are not well enough defined to say that we know the root cause or causes and we therefore cannot judge that.

The whole idea of a singular bug in such a diffuse area as memory leaks is problematic. It goes hand in hand with the idea that fixing one singular bug will magically resolve all sorts of problems whose connection to the bug in question is tenuous at best.

I am reminded of a team of half a dozen people who wasted several weeks pursuing "solutions" to the wrong problem because the lead was obsessed with pursuing the *last* error message in the .lg file rather than the first message to indicate a problem. Similarly this message reported an out of memory type of problem. Ultimately that turned out to be a red herring -- the system was running out of memory because of a long series of events that started well before the out of memory condition. But quite a lot of time and energy was wasted increasing memory, changing OS limits and chasing exotic hypothetical bugs when actually, just reading the first message and understanding it led almost directly to a fairly minor coding mistake that was easily fixed and which not only completely remedied the problem but also improved performance of that chunk of code by an order of magnitude.

To "save face" a couple of months were then invested in a major rewrite of that chunk of code which provided zero benefits other than a fun little exercise in learning about certain new 4gl features.
Hi Tom,
For the info you provided we are applying the service pack along with some changes to -B and -Mxs...

As you say 50000 is low, what would you suggest for a database of 124GB or in general rule of thumb.

Thanks a lot...
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
-B is specified in units of database blocks, so at a minimum we need to know your DB block size. Find your last database start in the log file (a "(333)" message) and copy the next 70-80 lines (up to "Database connections have been enabled."). Paste that in here within CODE tags. That will help to assess what you might want to change in your startup parameters. Also, the output of "proutil dbname -C describe" would help.
 
-B is specified in units of database blocks, so at a minimum we need to know your DB block size. Find your last database start in the log file (a "(333)" message) and copy the next 70-80 lines (up to "Database connections have been enabled."). Paste that in here within CODE tags. That will help to assess what you might want to change in your startup parameters. Also, the output of "proutil dbname -C describe" would help.
"
[2014/09/15@15:01:59.794+0800] P-12692 T--2034470784 I BROKER 0: (333) Multi-user session begin.
[2014/09/15@15:02:01.402+0800] P-12692 T--2034470784 I BROKER 0: (5644) Started for sgdtest1-srv using TCP IPV4 address 0.0.0.0, pid 12692.
[2014/09/15@15:02:02.405+0800] P-12692 T--2034470784 I BROKER 0: (4234) Progress OpenEdge Release 10.2A build 1185 on Linux gl-mfgdev.fciconnect.com 2.6.18-308.4.1.el5 #1 SMP Wed Mar 28 01:54:56 EDT 2012.
[2014/09/15@15:02:02.405+0800] P-12692 T--2034470784 I BROKER 0: (4281) Server started by root on /dev/pts/35.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (6574) Started using pid: 12692.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (4235) Physical Database Name (-db): /db/testdb/sgd/sgdnew90.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (4236) Database Type (-dt): PROGRESS.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (4237) Force Access (-F): Not Enabled.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (4238) Direct I/O (-directio): Not Enabled.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (-----) LRU mechanism enabled.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (4239) Number of Database Buffers (-B): 100000.
[2014/09/15@15:02:02.407+0800] P-12692 T--2034470784 I BROKER 0: (9422) Maximum private buffers per user (-Bpmax): 64.
[2014/09/15@15:02:02.407+0800] P-12692 T--2034470784 I BROKER 0: (4240) Excess Shared Memory Size (-Mxs): 1024.
[2014/09/15@15:02:02.412+0800] P-12692 T--2034470784 I BROKER 0: (10014) The shared memory segment is not locked in memory.
[2014/09/15@15:02:02.412+0800] P-12692 T--2034470784 I BROKER 0: (4241) Current Size of Lock Table (-L): 25024.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (13953) Maximum Area Number (-maxArea): 32000.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4242) Hash Table Entries (-hash): 25621.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4243) Current Spin Lock Tries (-spin): 20000.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (6526) Number of Semaphore Sets (-semsets): 3.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (13924) Maximum Shared Memory Segment Size (-shmsegsize) 1024 Mb.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4244) Crash Recovery (-i): Enabled.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (6573) Database Blocksize (-blocksize): 8192.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4245) Delay of Before-Image Flush (-Mf): 3.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4247) Before-Image File I/O (-r -R): Reliable.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4249) Before-Image Truncate Interval (-G): 0.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4250) Before-Image Cluster Size: 2097152.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4251) Before-Image Block Size: 8192.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4252) Number of Before-Image Buffers (-bibufs): 30.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (-----) Record free chain search depth factor 5 (-recspacesearchdepth)
[2014/09/15@15:02:02.421+0800] P-12692 T--2034470784 I BROKER 0: (9238) BI File Threshold size (-bithold): 1.5 GBytes.
[2014/09/15@15:02:02.421+0800] P-12692 T--2034470784 I BROKER 0: (6551) BI File Threshold Stall (-bistall): Enabled.
[2014/09/15@15:02:02.421+0800] P-12692 T--2034470784 I BROKER 0: (4254) After-Image Stall (-aistall): Not Enabled.
[2014/09/15@15:02:02.421+0800] P-12692 T--2034470784 I BROKER 0: (4255) After-Image Block Size: 8192.
[2014/09/15@15:02:02.421+0800] P-12692 T--2034470784 I BROKER 0: (4256) Number of After-Image Buffers (-aibufs): 30.
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (8527) Storage object cache size (-omsize): 1024
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (4257) Maximum Number of Clients Per Server (-Ma): 10.
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (4258) Maximum Number of Servers (-Mn): 14.
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (4259) Minimum Clients Per Server (-Mi): 10.
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (4260) Maximum Number of Users (-n): 201.
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (4261) Host Name (-H): gl-mfgdev.fciconnect.com.
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (4261) Host Name (-H): gl-mfgdev.fciconnect.com.
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (4262) Service Name (-S): sgdtest1-srv.
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (14268) TCP/IP Version (-ipver) : IPV4
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (4263) Network Type (-N): TCP.
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (4264) Character Set (-cpinternal): ISO8859-1.
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (4282) Parameter File: Not Enabled.
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (5647) Maximum Servers Per Broker (-Mpb): 6.
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (5648) Minimum Port for Auto Servers (-minport): 1025.
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (5649) Maximum Port for Auto Servers (-maxport): 2000.
[2014/09/15@15:02:02.433+0800] P-12692 T--2034470784 I BROKER 0: (8865) This broker supports both 4GL and SQL server groups.
[2014/09/15@15:02:02.433+0800] P-12692 T--2034470784 I BROKER 0: (9426) Large database file access has been enabled.
[2014/09/15@15:02:02.434+0800] P-12692 T--2034470784 I BROKER 0: (9336) Created shared memory with segment_id: 60194838
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12813) Allowed index cursors (-c): 804.
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12814) Group delay (-groupdelay): 10.
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12815) Lock table hash table size (-lkhash): 3407
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12816) Maxport (-maxport): 2000
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12817) Minport (-minport): 1025
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12818) Message Buffer Size (-Mm): 1024
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12820) Maximum Servers per Broker (-Mpb): 6
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12821) Use muxlatches (-mux): 1
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12823) Semaphore Sets (-semsets): 3
[2014/09/15@15:02:02.447+0800] P-12692 T--2034470784 I BROKER 0: (13870) Database Service Manager - IPC Queue Size (-pica) : 64.0 KBytes.
[2014/09/15@15:02:02.448+0800] P-12692 T--2034470784 I BROKER 0: (13896) TXE Commit lock skip limit (-TXESkipLimit): 10000.
[2014/09/15@15:02:02.449+0800] P-12692 T--2034470784 I BROKER 0: (10471) Database connections have been enabled.
[2014/09/15@15:02:02.458+0800] P-12704 T-2046558336 I BROKER 1: (5644) Started for sgdtest1odbc-srv using TCP IPV4 address 0.0.0.0, pid 12704.
[2014/09/15@15:02:02.458+0800] P-12704 T-2046558336 I BROKER 1: (5645) This is an additional broker for this protocol.
[2014/09/15@15:02:02.458+0800] P-12704 T-2046558336 I BROKER 1: (8864) This broker supports SQL server groups only.
[2014/09/15@15:02:02.483+0800] P-12716 T--996331392 I BROKER 2: (5644) Started for sgdtest14gl-srv using TCP IPV4 address 0.0.0.0, pid 12716.
[2014/09/15@15:02:02.483+0800] P-12716 T--996331392 I BROKER 2: (5645) This is an additional broker for this protocol.
[2014/09/15@15:02:02.483+0800] P-12716 T--996331392 I BROKER 2: (8863) This broker supports 4GL server groups only.
"
 

TheMadDBA

Active Member
Tom means starting a new thread on this forum, just like you did for this one.

That -L is very low for a 124GB MFG/PRO database, I would push that up to at least 100,000.

Good luck with the patch and the watchdog. Let us know if it solved the problem or not.
 
Tom means starting a new thread on this forum, just like you did for this one.

That -L is very low for a 124GB MFG/PRO database, I would push that up to at least 100,000.

Good luck with the patch and the watchdog. Let us know if it solved the problem or not.
Hi TheMadDBA,

Thanks a lot. I will increase -L to 100000. Also will update once patch has been applied...

BTW, I believe the issue will still be there due to inefficient application coding by developers. As a DBA I am clearing my area to isolate the issue,,,

Thanks...
 

TheMadDBA

Active Member
You probably want to monitor the high water mark on the locks through promon or VSTs and see how high it goes. If it moves from the initial value or you see lock table overflows in the logs you will have to push back on the developers.
 
[2014/09/15@15:01:59.794+0800] P-12692 T--2034470784 I BROKER 0: (333) Multi-user session begin.
[2014/09/15@15:02:01.402+0800] P-12692 T--2034470784 I BROKER 0: (5644) Started for sgdtest1-srv using TCP IPV4 address 0.0.0.0, pid 12692.
[2014/09/15@15:02:02.405+0800] P-12692 T--2034470784 I BROKER 0: (4234) Progress OpenEdge Release 10.2A build 1185 on Linux gl-mfgdev.fciconnect.com 2.6.18-308.4.1.el5 #1 SMP Wed Mar 28 01:54:56 EDT 2012.
[2014/09/15@15:02:02.405+0800] P-12692 T--2034470784 I BROKER 0: (4281) Server started by root on /dev/pts/35.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (6574) Started using pid: 12692.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (4235) Physical Database Name (-db): /db/testdb/sgd/sgdnew90.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (4236) Database Type (-dt): PROGRESS.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (4237) Force Access (-F): Not Enabled.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (4238) Direct I/O (-directio): Not Enabled.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (-----) LRU mechanism enabled.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (4239) Number of Database Buffers (-B): 100000.
[2014/09/15@15:02:02.407+0800] P-12692 T--2034470784 I BROKER 0: (9422) Maximum private buffers per user (-Bpmax): 64.
[2014/09/15@15:02:02.407+0800] P-12692 T--2034470784 I BROKER 0: (4240) Excess Shared Memory Size (-Mxs): 1024.
[2014/09/15@15:02:02.412+0800] P-12692 T--2034470784 I BROKER 0: (10014) The shared memory segment is not locked in memory.
[2014/09/15@15:02:02.412+0800] P-12692 T--2034470784 I BROKER 0: (4241) Current Size of Lock Table (-L): 25024.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (13953) Maximum Area Number (-maxArea): 32000.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4242) Hash Table Entries (-hash): 25621.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4243) Current Spin Lock Tries (-spin): 20000.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (6526) Number of Semaphore Sets (-semsets): 3.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (13924) Maximum Shared Memory Segment Size (-shmsegsize) 1024 Mb.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4244) Crash Recovery (-i): Enabled.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (6573) Database Blocksize (-blocksize): 8192.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4245) Delay of Before-Image Flush (-Mf): 3.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4247) Before-Image File I/O (-r -R): Reliable.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4249) Before-Image Truncate Interval (-G): 0.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4250) Before-Image Cluster Size: 2097152.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4251) Before-Image Block Size: 8192.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4252) Number of Before-Image Buffers (-bibufs): 30.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (-----) Record free chain search depth factor 5 (-recspacesearchdepth)
[2014/09/15@15:02:02.421+0800] P-12692 T--2034470784 I BROKER 0: (9238) BI File Threshold size (-bithold): 1.5 GBytes.
[2014/09/15@15:02:02.421+0800] P-12692 T--2034470784 I BROKER 0: (6551) BI File Threshold Stall (-bistall): Enabled.
[2014/09/15@15:02:02.421+0800] P-12692 T--2034470784 I BROKER 0: (4254) After-Image Stall (-aistall): Not Enabled.
[2014/09/15@15:02:02.421+0800] P-12692 T--2034470784 I BROKER 0: (4255) After-Image Block Size: 8192.
[2014/09/15@15:02:02.421+0800] P-12692 T--2034470784 I BROKER 0: (4256) Number of After-Image Buffers (-aibufs): 30.
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (8527) Storage object cache size (-omsize): 1024
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (4257) Maximum Number of Clients Per Server (-Ma): 10.
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (4258) Maximum Number of Servers (-Mn): 14.
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (4259) Minimum Clients Per Server (-Mi): 10.
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (4260) Maximum Number of Users (-n): 201.
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (4261) Host Name (-H): gl-mfgdev.fciconnect.com
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (4262) Service Name (-S): sgdtest1-srv.
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (14268) TCP/IP Version (-ipver) : IPV4
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (4263) Network Type (-N): TCP.
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (4264) Character Set (-cpinternal): ISO8859-1.
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (4282) Parameter File: Not Enabled.
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (5647) Maximum Servers Per Broker (-Mpb): 6.
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (5648) Minimum Port for Auto Servers (-minport): 1025.
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (5649) Maximum Port for Auto Servers (-maxport): 2000.
[2014/09/15@15:02:02.433+0800] P-12692 T--2034470784 I BROKER 0: (8865) This broker supports both 4GL and SQL server groups.
[2014/09/15@15:02:02.433+0800] P-12692 T--2034470784 I BROKER 0: (9426) Large database file access has been enabled.
[2014/09/15@15:02:02.434+0800] P-12692 T--2034470784 I BROKER 0: (9336) Created shared memory with segment_id: 60194838
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12813) Allowed index cursors (-c): 804.
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12814) Group delay (-groupdelay): 10.
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12815) Lock table hash table size (-lkhash): 3407
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12816) Maxport (-maxport): 2000
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12817) Minport (-minport): 1025
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12818) Message Buffer Size (-Mm): 1024
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12820) Maximum Servers per Broker (-Mpb): 6
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12821) Use muxlatches (-mux): 1
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12823) Semaphore Sets (-semsets): 3
[2014/09/15@15:02:02.447+0800] P-12692 T--2034470784 I BROKER 0: (13870) Database Service Manager - IPC Queue Size (-pica) : 64.0 KBytes.
[2014/09/15@15:02:02.448+0800] P-12692 T--2034470784 I BROKER 0: (13896) TXE Commit lock skip limit (-TXESkipLimit): 10000.
[2014/09/15@15:02:02.449+0800] P-12692 T--2034470784 I BROKER 0: (10471) Database connections have been enabled.
[2014/09/15@15:02:02.458+0800] P-12704 T-2046558336 I BROKER 1: (5644) Started for sgdtest1odbc-srv using TCP IPV4 address 0.0.0.0, pid 12704.
[2014/09/15@15:02:02.458+0800] P-12704 T-2046558336 I BROKER 1: (5645) This is an additional broker for this protocol.
[2014/09/15@15:02:02.458+0800] P-12704 T-2046558336 I BROKER 1: (8864) This broker supports SQL server groups only.
[2014/09/15@15:02:02.483+0800] P-12716 T--996331392 I BROKER 2: (5644) Started for sgdtest14gl-srv using TCP IPV4 address 0.0.0.0, pid 12716.
[2014/09/15@15:02:02.483+0800] P-12716 T--996331392 I BROKER 2: (5645) This is an additional broker for this protocol.
[2014/09/15@15:02:02.483+0800] P-12716 T--996331392 I BROKER 2: (8863) This broker supports 4GL server groups only.
 
[2014/09/15@15:01:59.794+0800] P-12692 T--2034470784 I BROKER 0: (333) Multi-user session begin.
[2014/09/15@15:02:01.402+0800] P-12692 T--2034470784 I BROKER 0: (5644) Started for sgdtest1-srv using TCP IPV4 address 0.0.0.0, pid 12692.
[2014/09/15@15:02:02.405+0800] P-12692 T--2034470784 I BROKER 0: (4234) Progress OpenEdge Release 10.2A build 1185 on Linux gl-mfgdev.fciconnect.com 2.6.18-308.4.1.el5 #1 SMP Wed Mar 28 01:54:56 EDT 2012.
[2014/09/15@15:02:02.405+0800] P-12692 T--2034470784 I BROKER 0: (4281) Server started by root on /dev/pts/35.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (6574) Started using pid: 12692.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (4235) Physical Database Name (-db): /db/testdb/sgd/sgdnew90.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (4236) Database Type (-dt): PROGRESS.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (4237) Force Access (-F): Not Enabled.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (4238) Direct I/O (-directio): Not Enabled.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (-----) LRU mechanism enabled.
[2014/09/15@15:02:02.406+0800] P-12692 T--2034470784 I BROKER 0: (4239) Number of Database Buffers (-B): 100000.
[2014/09/15@15:02:02.407+0800] P-12692 T--2034470784 I BROKER 0: (9422) Maximum private buffers per user (-Bpmax): 64.
[2014/09/15@15:02:02.407+0800] P-12692 T--2034470784 I BROKER 0: (4240) Excess Shared Memory Size (-Mxs): 1024.
[2014/09/15@15:02:02.412+0800] P-12692 T--2034470784 I BROKER 0: (10014) The shared memory segment is not locked in memory.
[2014/09/15@15:02:02.412+0800] P-12692 T--2034470784 I BROKER 0: (4241) Current Size of Lock Table (-L): 25024.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (13953) Maximum Area Number (-maxArea): 32000.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4242) Hash Table Entries (-hash): 25621.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4243) Current Spin Lock Tries (-spin): 20000.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (6526) Number of Semaphore Sets (-semsets): 3.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (13924) Maximum Shared Memory Segment Size (-shmsegsize) 1024 Mb.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4244) Crash Recovery (-i): Enabled.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (6573) Database Blocksize (-blocksize): 8192.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4245) Delay of Before-Image Flush (-Mf): 3.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4247) Before-Image File I/O (-r -R): Reliable.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4249) Before-Image Truncate Interval (-G): 0.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4250) Before-Image Cluster Size: 2097152.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4251) Before-Image Block Size: 8192.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (4252) Number of Before-Image Buffers (-bibufs): 30.
[2014/09/15@15:02:02.420+0800] P-12692 T--2034470784 I BROKER 0: (-----) Record free chain search depth factor 5 (-recspacesearchdepth)
[2014/09/15@15:02:02.421+0800] P-12692 T--2034470784 I BROKER 0: (9238) BI File Threshold size (-bithold): 1.5 GBytes.
[2014/09/15@15:02:02.421+0800] P-12692 T--2034470784 I BROKER 0: (6551) BI File Threshold Stall (-bistall): Enabled.
[2014/09/15@15:02:02.421+0800] P-12692 T--2034470784 I BROKER 0: (4254) After-Image Stall (-aistall): Not Enabled.
[2014/09/15@15:02:02.421+0800] P-12692 T--2034470784 I BROKER 0: (4255) After-Image Block Size: 8192.
[2014/09/15@15:02:02.421+0800] P-12692 T--2034470784 I BROKER 0: (4256) Number of After-Image Buffers (-aibufs): 30.
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (8527) Storage object cache size (-omsize): 1024
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (4257) Maximum Number of Clients Per Server (-Ma): 10.
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (4258) Maximum Number of Servers (-Mn): 14.
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (4259) Minimum Clients Per Server (-Mi): 10.
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (4260) Maximum Number of Users (-n): 201.
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (4261) Host Name (-H): gl-mfgdev.fciconnect.com
[2014/09/15@15:02:02.425+0800] P-12692 T--2034470784 I BROKER 0: (4262) Service Name (-S): sgdtest1-srv.
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (14268) TCP/IP Version (-ipver) : IPV4
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (4263) Network Type (-N): TCP.
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (4264) Character Set (-cpinternal): ISO8859-1.
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (4282) Parameter File: Not Enabled.
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (5647) Maximum Servers Per Broker (-Mpb): 6.
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (5648) Minimum Port for Auto Servers (-minport): 1025.
[2014/09/15@15:02:02.432+0800] P-12692 T--2034470784 I BROKER 0: (5649) Maximum Port for Auto Servers (-maxport): 2000.
[2014/09/15@15:02:02.433+0800] P-12692 T--2034470784 I BROKER 0: (8865) This broker supports both 4GL and SQL server groups.
[2014/09/15@15:02:02.433+0800] P-12692 T--2034470784 I BROKER 0: (9426) Large database file access has been enabled.
[2014/09/15@15:02:02.434+0800] P-12692 T--2034470784 I BROKER 0: (9336) Created shared memory with segment_id: 60194838
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12813) Allowed index cursors (-c): 804.
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12814) Group delay (-groupdelay): 10.
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12815) Lock table hash table size (-lkhash): 3407
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12816) Maxport (-maxport): 2000
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12817) Minport (-minport): 1025
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12818) Message Buffer Size (-Mm): 1024
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12820) Maximum Servers per Broker (-Mpb): 6
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12821) Use muxlatches (-mux): 1
[2014/09/15@15:02:02.435+0800] P-12692 T--2034470784 I BROKER 0: (12823) Semaphore Sets (-semsets): 3
[2014/09/15@15:02:02.447+0800] P-12692 T--2034470784 I BROKER 0: (13870) Database Service Manager - IPC Queue Size (-pica) : 64.0 KBytes.
[2014/09/15@15:02:02.448+0800] P-12692 T--2034470784 I BROKER 0: (13896) TXE Commit lock skip limit (-TXESkipLimit): 10000.
[2014/09/15@15:02:02.449+0800] P-12692 T--2034470784 I BROKER 0: (10471) Database connections have been enabled.
[2014/09/15@15:02:02.458+0800] P-12704 T-2046558336 I BROKER 1: (5644) Started for sgdtest1odbc-srv using TCP IPV4 address 0.0.0.0, pid 12704.
[2014/09/15@15:02:02.458+0800] P-12704 T-2046558336 I BROKER 1: (5645) This is an additional broker for this protocol.
[2014/09/15@15:02:02.458+0800] P-12704 T-2046558336 I BROKER 1: (8864) This broker supports SQL server groups only.
[2014/09/15@15:02:02.483+0800] P-12716 T--996331392 I BROKER 2: (5644) Started for sgdtest14gl-srv using TCP IPV4 address 0.0.0.0, pid 12716.
[2014/09/15@15:02:02.483+0800] P-12716 T--996331392 I BROKER 2: (5645) This is an additional broker for this protocol.
[2014/09/15@15:02:02.483+0800] P-12716 T--996331392 I BROKER 2: (8863) This broker supports 4GL server groups only.
Hi Rob,

My database startup info from .lg file is posted.

Waiting for suggestion from your side...

Thanks,
Surya
 

TomBascom

Curmudgeon
In your other thread you mentioned that -B is 50,000. Here it is 100,000... inconsistencies like that make it hard to know what to believe.

In any event -- both are very small values on modern servers. You have 8k blocks to you have allocated 800MB to buffers. That's not very much. How much memory does your server have? You other thread mentioned 24GB but everything seems to be a moving target. There isn't really a good "rule of thumb" for setting -B (although you might hear some bad ones like "10% of db size"). The goal is to buffer as much IO as possible -- to do that you want to use as much memory as feasible. Keeping in mind that you have many databases (19?) and that their relative demands and performance needs are different. And you need to leave /some/ memory for other purposes.

But -B 100,000 is smaller than what most people would set their primary production db to.

-n is set to just 201 -- yet you mentioned "hundreds" of "runaways". Something doesn't add up. Having said that -- many people confuse -n with license count (sales people encourage that so that they get more revenue). They are actually not at all related. There are some bugs in various versions where logins will silently "hang" if you have -n connections. There is also an old bug related to false connection counts when online backups are run. Both issues can be worked around by setting -n to a value that is larger than you really need it to be. So if you have a 200 user license and you often have nearly 200 users you might be well advised to set -n to 300.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
The online backup/user count bug was in 10.2B01, fixed in SP02. The online backup/_License bug was from 10.2B SP01 to SP04 inclusive, fixed in SP05. So when you install 10.2B, make sure you install the latest (and last) service pack: SP08. At present Progress is saying that there will be no more service packs for 10.2B.

As Tom said, Version 10 is almost 5 years old. Version 11 is now almost 3 years old and is now quite stable. If I were you I would seriously consider moving to 11.x (the current version is 11.4) if you are able. And even if you can't move to it in the short term you should have a plan for getting there sooner rather than later.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
I assumed your earlier tuning questions were about a production environment. Based on the service name and server name from the log above it looks like it was taken from a development database. Can you confirm if this is the database you want to tune, and if it is the one that displays the "hang" behaviour you described earlier?
 
In your other thread you mentioned that -B is 50,000. Here it is 100,000... inconsistencies like that make it hard to know what to believe.

In any event -- both are very small values on modern servers. You have 8k blocks to you have allocated 800MB to buffers. That's not very much. How much memory does your server have? You other thread mentioned 24GB but everything seems to be a moving target. There isn't really a good "rule of thumb" for setting -B (although you might hear some bad ones like "10% of db size"). The goal is to buffer as much IO as possible -- to do that you want to use as much memory as feasible. Keeping in mind that you have many databases (19?) and that their relative demands and performance needs are different. And you need to leave /some/ memory for other purposes.

But -B 100,000 is smaller than what most people would set their primary production db to.

-n is set to just 201 -- yet you mentioned "hundreds" of "runaways". Something doesn't add up. Having said that -- many people confuse -n with license count (sales people encourage that so that they get more revenue). They are actually not at all related. There are some bugs in various versions where logins will silently "hang" if you have -n connections. There is also an old bug related to false connection counts when online backups are run. Both issues can be worked around by setting -n to a value that is larger than you really need it to be. So if you have a 200 user license and you often have nearly 200 users you might be well advised to set -n to 300.
Hi Tom,

This is from test where we just started to tune. -B is 35000 and cache hit is 90% in production. We want 95% and more so we are increasing to 50000 first and see the difference.
Runaway users are those which we see at OS level but unable to connect to database. Runaway is experienced only when we have issue.
Today we are going to apply 10.2A03 in Test.
Thanks...
 
I assumed your earlier tuning questions were about a production environment. Based on the service name and server name from the log above it looks like it was taken from a development database. Can you confirm if this is the database you want to tune, and if it is the one that displays the "hang" behaviour you described earlier?
Hi Rob,

You are right! We are setting parameters in development to check if additional memory will be required. Stress rest is in progress...
I will update last production parameters later today.
Thanks
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
It's not a bad idea to test parameter changes first in a non-production environment. However it is quite unlikely that the application workload in that environment mirrors the workload in production. So do not assume that the performance change, or lack thereof, that you measure in test as the result of a parameter change will be duplicated in prod.

90% cache hits is very, very low. Large changes in -B are required to yield meaningful changes (reductions) in disk I/O. As Tom often says, there is an inverse-square relation between buffer pool size and cache hit ratio. In other words if you want to reduce your cache misses (disk I/Os) by a factor of 2 you should increase the size of your buffer pool by a factor of 4. In this case that would mean increasing it from 35,000 to 140,000 (about 1.1 GB, assuming prod also has 8 KB blocks).

Even 95% is quite low. It means one in every 20 logical reads, on average, results in a disk I/O. You should be able to do much better than that. Also, be mindful that although a very low hit percentage or hit ratio is definitely bad, a high ratio isn't necessarily definitely good. Frequent table scans of relatively small tables will result in very high ratio of logical to physical reads but that doesn't mean your application is performing well or your users are happy.
 

TomBascom

Curmudgeon
That's the 3rd reported -B value so far...

Not that hit ratio is the be all and end all of tuning - it isn't. But since you mention it:

A hit ratio of 90% is horrifically bad. You almost have to work at it to be that bad. That basically means that 1 in 5 record ops has to go to disk.

95% isn't horrific - it just sucks.

98% is "you aren't trying very hard but at least it isn't completely embarrassing".

99% and you're starting to get somewhere.

Also - the relationship between -B and hit ratio follows an inverse square rule. To move your hit ratio half way from where it is towards 100% you need to increase -B 4x. IOW if -B 35000 gets you a 90% hit ratio for a certain workload you need to increase it to 140000 to improve to 95%. 500000 should get you close to 98%.
 

TomBascom

Curmudgeon
... for that same workload. Which, as Rob points out, in this case is not PROD so the same numbers will not pan out in the same way.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
I assume you're running 64-bit Enterprise RDBMS and your available RAM and OS shared memory parameters will allow the buffer pool increases proposed.
 
Top