Hello all,
I'm a newbie here and have an ongoing issue with an Openedge 11.3 workgroup database. I'm not a DBA so I was hoping someone here might have some insight. We have a small database. Small as in only about 200MB with about 40 users (rarely that many logged on at once). At the end of each month I run about 2 dozen reports. The longest report takes about 2 hours which is ridiculous. If I log onto the server that same report will sometimes run in 5-10 minutes. This server is a VM server running Windows 2008 server with 4GB of RAM. Nothing else is used on this server.
Prior to upgrading to 11.3 we were on Openedge 10.0B and prior to that 9.1C. Those were on a local physical server that only had 1GB of ram, slow Xenon processors, and RAID-5. The longest report at that time took about 45 minutes. That server was located here in our office. Still on the network but didn't have any hops to deal with from the client to the server.
The contractor/developer for our product lives in another state and is not able to take a look. I don't know how we could go from using an old, slow 10 year old server that was located here to a brand new Windows server 2008 VM server and see performance take a complete nosedive. Our new VM server is hosted offsite on the network and requires 7 hops to complete when running a tracert. It's not just our monthly reports that have performance issues but even regular day-to-day data entry can be very laggy and slow. Does the workgroup server really perform that poorly on larger networks? Here are the startup parameters for the database.
[2015/11/08@21:05:00.465-0500] P-4216 T-2876 I BROKER 0: (333) Multi-user session begin.
[2015/11/08@21:05:00.468-0500] P-4216 T-2876 I BROKER 0: (15321) Before Image Log Initialization at block 69 offset 5970.
[2015/11/08@21:05:00.671-0500] P-4216 T-2876 I BROKER 0: (452) Login by progressdba on CON:.
[2015/11/08@21:05:00.781-0500] P-4216 T-2876 I BROKER 0: (5644) Started for dbs1 using TCP IPV4 address 0.0.0.0, pid 4216.
[2015/11/08@21:05:00.785-0500] P-4216 T-2876 I BROKER 0: (4234) Progress OpenEdge Release 11.3 build 1232 on WINNT .
[2015/11/08@21:05:00.786-0500] P-4216 T-2876 I BROKER 0: (4281) Server started by progressdba on CON:.
[2015/11/08@21:05:00.787-0500] P-4216 T-2876 I BROKER 0: (6574) Started using pid: 4216.
[2015/11/08@21:05:00.787-0500] P-4216 T-2876 I BROKER 0: (4235) Physical Database Name (-db): acts.
[2015/11/08@21:05:00.794-0500] P-4216 T-2876 I BROKER 0: (4236) Database Type (-dt): PROGRESS.
[2015/11/08@21:05:00.794-0500] P-4216 T-2876 I BROKER 0: (4237) Force Access (-F): Not Enabled.
[2015/11/08@21:05:00.795-0500] P-4216 T-2876 I BROKER 0: (4238) Direct I/O (-directio): Not Enabled.
[2015/11/08@21:05:00.796-0500] P-4216 T-2876 I BROKER 0: (-----) LRU mechanism enabled.
[2015/11/08@21:05:00.796-0500] P-4216 T-2876 I BROKER 0: (-----) Number of LRU force skips (-lruskips): 0
[2015/11/08@21:05:00.797-0500] P-4216 T-2876 I BROKER 0: (-----) Number of LRU2 force skips (-lru2skips): 0
[2015/11/08@21:05:00.797-0500] P-4216 T-2876 I BROKER 0: (4239) Number of Database Buffers (-B): 4000.
[2015/11/08@21:05:00.798-0500] P-4216 T-2876 I BROKER 0: (-----) Number of Alternate Database Buffers (-B2): 0.
[2015/11/08@21:05:00.799-0500] P-4216 T-2876 I BROKER 0: (9422) Maximum private buffers per user (-Bpmax): 64.
[2015/11/08@21:05:00.799-0500] P-4216 T-2876 I BROKER 0: (4240) Excess Shared Memory Size (-Mxs): 23.
[2015/11/08@21:05:00.800-0500] P-4216 T-2876 I BROKER 0: (10014) The shared memory segment is not locked in memory.
[2015/11/08@21:05:00.800-0500] P-4216 T-2876 I BROKER 0: (4241) Current Size of Lock Table (-L): 8192.
[2015/11/08@21:05:00.801-0500] P-4216 T-2876 I BROKER 0: (13953) Maximum Area Number (-maxArea): 32000.
[2015/11/08@21:05:00.802-0500] P-4216 T-2876 I BROKER 0: (4242) Hash Table Entries (-hash): 1237.
[2015/11/08@21:05:00.802-0500] P-4216 T-2876 I BROKER 0: (4243) Current Spin Lock Tries (-spin): 1.
[2015/11/08@21:05:00.803-0500] P-4216 T-2876 I BROKER 0: (6526) Number of Semaphore Sets (-semsets): 3.
[2015/11/08@21:05:00.803-0500] P-4216 T-2876 I BROKER 0: (13924) Maximum Shared Memory Segment Size (-shmsegsize) 128 Mb.
[2015/11/08@21:05:00.804-0500] P-4216 T-2876 I BROKER 0: (4244) Crash Recovery (-i): Enabled.
[2015/11/08@21:05:00.805-0500] P-4216 T-2876 I BROKER 0: (6573) Database Blocksize (-blocksize): 4096.
[2015/11/08@21:05:00.805-0500] P-4216 T-2876 I BROKER 0: (4245) Delay of Before-Image Flush (-Mf): 3.
[2015/11/08@21:05:00.808-0500] P-4216 T-2876 I BROKER 0: (4247) Before-Image File I/O (-r -R): Reliable.
[2015/11/08@21:05:00.809-0500] P-4216 T-2876 I BROKER 0: (4249) Before-Image Truncate Interval (-G): 0.
[2015/11/08@21:05:00.810-0500] P-4216 T-2876 I BROKER 0: (4250) Before-Image Cluster Size: 524288.
[2015/11/08@21:05:00.811-0500] P-4216 T-2876 I BROKER 0: (4251) Before-Image Block Size: 8192.
[2015/11/08@21:05:00.812-0500] P-4216 T-2876 I BROKER 0: (4252) Number of Before-Image Buffers (-bibufs): 20.
[2015/11/08@21:05:00.813-0500] P-4216 T-2876 I BROKER 0: (-----) Record free chain search depth factor 5 (-recspacesearchdepth)
[2015/11/08@21:05:00.814-0500] P-4216 T-2876 I BROKER 0: (9238) BI File Threshold size (-bithold): 0.0 Bytes.
[2015/11/08@21:05:00.814-0500] P-4216 T-2876 I BROKER 0: (6552) BI File Threshold Stall (-bistall): Disabled.
[2015/11/08@21:05:00.815-0500] P-4216 T-2876 I BROKER 0: (4254) After-Image Stall (-aistall): Not Enabled.
[2015/11/08@21:05:00.816-0500] P-4216 T-2876 I BROKER 0: (4255) After-Image Block Size: 8192.
[2015/11/08@21:05:00.817-0500] P-4216 T-2876 I BROKER 0: (4256) Number of After-Image Buffers (-aibufs): 20.
[2015/11/08@21:05:00.817-0500] P-4216 T-2876 I BROKER 0: (-----) Partition Manager cache size (-mtpmsize): 1024
[2015/11/08@21:05:00.819-0500] P-4216 T-2876 I BROKER 0: (8527) Storage object cache size (-omsize): 1024
[2015/11/08@21:05:00.820-0500] P-4216 T-2876 I BROKER 0: (4257) Maximum Number of Clients Per Server (-Ma): 5.
[2015/11/08@21:05:00.821-0500] P-4216 T-2876 I BROKER 0: (4258) Maximum Number of Servers (-Mn): 5.
[2015/11/08@21:05:00.823-0500] P-4216 T-2876 I BROKER 0: (4259) Minimum Clients Per Server (-Mi): 1.
[2015/11/08@21:05:00.824-0500] P-4216 T-2876 I BROKER 0: (16953) Use pollset mechanism for client/server (-pollset): Disabled
[2015/11/08@21:05:00.825-0500] P-4216 T-2876 I BROKER 0: (16954) Server network message wait time (-Nmsgwait): 2
[2015/11/08@21:05:00.826-0500] P-4216 T-2876 I BROKER 0: (16955) Delay first prefetch message (-prefetchDelay): Disabled
[2015/11/08@21:05:00.827-0500] P-4216 T-2876 I BROKER 0: (16956) Prefetch message fill percentage (-prefetchFactor): 0
[2015/11/08@21:05:00.827-0500] P-4216 T-2876 I BROKER 0: (16957) Minimum records in prefetch msg (-prefetchNumRecs): 16
[2015/11/08@21:05:00.828-0500] P-4216 T-2876 I BROKER 0: (16958) Suspension queue poll priority (-prefetchPriority): 0
[2015/11/08@21:05:00.828-0500] P-4216 T-2876 I BROKER 0: (4260) Maximum Number of Users (-n): 21.
[2015/11/08@21:05:00.829-0500] P-4216 T-2876 I BROKER 0: (4261) Host Name (-H): hqdaa4n00020-02.
[2015/11/08@21:05:00.829-0500] P-4216 T-2876 I BROKER 0: (4262) Service Name (-S): dbs1.
[2015/11/08@21:05:00.830-0500] P-4216 T-2876 I BROKER 0: (14268) TCP/IP Version (-ipver) : IPV4
[2015/11/08@21:05:00.831-0500] P-4216 T-2876 I BROKER 0: (4263) Network Type (-N): TCP.
[2015/11/08@21:05:00.831-0500] P-4216 T-2876 I BROKER 0: (4264) Character Set (-cpinternal): ISO8859-1.
[2015/11/08@21:05:00.832-0500] P-4216 T-2876 I BROKER 0: (4282) Parameter File: Not Enabled.
[2015/11/08@21:05:00.832-0500] P-4216 T-2876 I BROKER 0: (5648) Minimum Port for Auto Servers (-minport): 3000.
[2015/11/08@21:05:00.833-0500] P-4216 T-2876 I BROKER 0: (5649) Maximum Port for Auto Servers (-maxport): 5000.
[2015/11/08@21:05:00.834-0500] P-4216 T-2876 I BROKER 0: (8865) This broker supports both 4GL and SQL server groups.
[2015/11/08@21:05:00.835-0500] P-4216 T-2876 I BROKER 0: (12540) Size of JTA transaction table (-maxxids): 100
[2015/11/08@21:05:00.835-0500] P-4216 T-2876 I BROKER 0: (9336) Created shared memory with segment_id: 20905984
[2015/11/08@21:05:00.836-0500] P-4216 T-2876 I BROKER 0: (12813) Allowed index cursors (-c): 84.
[2015/11/08@21:05:00.836-0500] P-4216 T-2876 I BROKER 0: (12814) Group delay (-groupdelay): 10.
[2015/11/08@21:05:00.837-0500] P-4216 T-2876 I BROKER 0: (12815) Lock table hash table size (-lkhash): 1237
[2015/11/08@21:05:00.838-0500] P-4216 T-2876 I BROKER 0: (12816) Maxport (-maxport): 5000
[2015/11/08@21:05:00.838-0500] P-4216 T-2876 I BROKER 0: (12817) Minport (-minport): 3000
[2015/11/08@21:05:00.839-0500] P-4216 T-2876 I BROKER 0: (12818) Message Buffer Size (-Mm): 1024
[2015/11/08@21:05:00.839-0500] P-4216 T-2876 I BROKER 0: (12821) Use muxlatches (-mux): 1
[2015/11/08@21:05:00.840-0500] P-4216 T-2876 I BROKER 0: (12823) Semaphore Sets (-semsets): 3
[2015/11/08@21:05:00.841-0500] P-4216 T-2876 I BROKER 0: (13870) Database Service Manager - IPC Queue Size (-pica) : 64.0 KBytes.
[2015/11/08@21:05:00.841-0500] P-4216 T-2876 I BROKER 0: (13896) TXE Commit lock skip limit (-TXESkipLimit): 10000.
[2015/11/08@21:05:00.842-0500] P-4216 T-2876 I BROKER 0: (15219) Encryption enabled: 0
[2015/11/08@21:05:00.842-0500] P-4216 T-2876 I BROKER 0: (15218) Encryption cache size (-ecsize): 1000
[2015/11/08@21:05:00.843-0500] P-4216 T-2876 I BROKER 0: (15824) Multi-tenancy enabled: 0
[2015/11/08@21:05:00.844-0500] P-4216 T-2876 I BROKER 0: (10471) Database connections have been enabled.
Thanks!
J
I'm a newbie here and have an ongoing issue with an Openedge 11.3 workgroup database. I'm not a DBA so I was hoping someone here might have some insight. We have a small database. Small as in only about 200MB with about 40 users (rarely that many logged on at once). At the end of each month I run about 2 dozen reports. The longest report takes about 2 hours which is ridiculous. If I log onto the server that same report will sometimes run in 5-10 minutes. This server is a VM server running Windows 2008 server with 4GB of RAM. Nothing else is used on this server.
Prior to upgrading to 11.3 we were on Openedge 10.0B and prior to that 9.1C. Those were on a local physical server that only had 1GB of ram, slow Xenon processors, and RAID-5. The longest report at that time took about 45 minutes. That server was located here in our office. Still on the network but didn't have any hops to deal with from the client to the server.
The contractor/developer for our product lives in another state and is not able to take a look. I don't know how we could go from using an old, slow 10 year old server that was located here to a brand new Windows server 2008 VM server and see performance take a complete nosedive. Our new VM server is hosted offsite on the network and requires 7 hops to complete when running a tracert. It's not just our monthly reports that have performance issues but even regular day-to-day data entry can be very laggy and slow. Does the workgroup server really perform that poorly on larger networks? Here are the startup parameters for the database.
[2015/11/08@21:05:00.465-0500] P-4216 T-2876 I BROKER 0: (333) Multi-user session begin.
[2015/11/08@21:05:00.468-0500] P-4216 T-2876 I BROKER 0: (15321) Before Image Log Initialization at block 69 offset 5970.
[2015/11/08@21:05:00.671-0500] P-4216 T-2876 I BROKER 0: (452) Login by progressdba on CON:.
[2015/11/08@21:05:00.781-0500] P-4216 T-2876 I BROKER 0: (5644) Started for dbs1 using TCP IPV4 address 0.0.0.0, pid 4216.
[2015/11/08@21:05:00.785-0500] P-4216 T-2876 I BROKER 0: (4234) Progress OpenEdge Release 11.3 build 1232 on WINNT .
[2015/11/08@21:05:00.786-0500] P-4216 T-2876 I BROKER 0: (4281) Server started by progressdba on CON:.
[2015/11/08@21:05:00.787-0500] P-4216 T-2876 I BROKER 0: (6574) Started using pid: 4216.
[2015/11/08@21:05:00.787-0500] P-4216 T-2876 I BROKER 0: (4235) Physical Database Name (-db): acts.
[2015/11/08@21:05:00.794-0500] P-4216 T-2876 I BROKER 0: (4236) Database Type (-dt): PROGRESS.
[2015/11/08@21:05:00.794-0500] P-4216 T-2876 I BROKER 0: (4237) Force Access (-F): Not Enabled.
[2015/11/08@21:05:00.795-0500] P-4216 T-2876 I BROKER 0: (4238) Direct I/O (-directio): Not Enabled.
[2015/11/08@21:05:00.796-0500] P-4216 T-2876 I BROKER 0: (-----) LRU mechanism enabled.
[2015/11/08@21:05:00.796-0500] P-4216 T-2876 I BROKER 0: (-----) Number of LRU force skips (-lruskips): 0
[2015/11/08@21:05:00.797-0500] P-4216 T-2876 I BROKER 0: (-----) Number of LRU2 force skips (-lru2skips): 0
[2015/11/08@21:05:00.797-0500] P-4216 T-2876 I BROKER 0: (4239) Number of Database Buffers (-B): 4000.
[2015/11/08@21:05:00.798-0500] P-4216 T-2876 I BROKER 0: (-----) Number of Alternate Database Buffers (-B2): 0.
[2015/11/08@21:05:00.799-0500] P-4216 T-2876 I BROKER 0: (9422) Maximum private buffers per user (-Bpmax): 64.
[2015/11/08@21:05:00.799-0500] P-4216 T-2876 I BROKER 0: (4240) Excess Shared Memory Size (-Mxs): 23.
[2015/11/08@21:05:00.800-0500] P-4216 T-2876 I BROKER 0: (10014) The shared memory segment is not locked in memory.
[2015/11/08@21:05:00.800-0500] P-4216 T-2876 I BROKER 0: (4241) Current Size of Lock Table (-L): 8192.
[2015/11/08@21:05:00.801-0500] P-4216 T-2876 I BROKER 0: (13953) Maximum Area Number (-maxArea): 32000.
[2015/11/08@21:05:00.802-0500] P-4216 T-2876 I BROKER 0: (4242) Hash Table Entries (-hash): 1237.
[2015/11/08@21:05:00.802-0500] P-4216 T-2876 I BROKER 0: (4243) Current Spin Lock Tries (-spin): 1.
[2015/11/08@21:05:00.803-0500] P-4216 T-2876 I BROKER 0: (6526) Number of Semaphore Sets (-semsets): 3.
[2015/11/08@21:05:00.803-0500] P-4216 T-2876 I BROKER 0: (13924) Maximum Shared Memory Segment Size (-shmsegsize) 128 Mb.
[2015/11/08@21:05:00.804-0500] P-4216 T-2876 I BROKER 0: (4244) Crash Recovery (-i): Enabled.
[2015/11/08@21:05:00.805-0500] P-4216 T-2876 I BROKER 0: (6573) Database Blocksize (-blocksize): 4096.
[2015/11/08@21:05:00.805-0500] P-4216 T-2876 I BROKER 0: (4245) Delay of Before-Image Flush (-Mf): 3.
[2015/11/08@21:05:00.808-0500] P-4216 T-2876 I BROKER 0: (4247) Before-Image File I/O (-r -R): Reliable.
[2015/11/08@21:05:00.809-0500] P-4216 T-2876 I BROKER 0: (4249) Before-Image Truncate Interval (-G): 0.
[2015/11/08@21:05:00.810-0500] P-4216 T-2876 I BROKER 0: (4250) Before-Image Cluster Size: 524288.
[2015/11/08@21:05:00.811-0500] P-4216 T-2876 I BROKER 0: (4251) Before-Image Block Size: 8192.
[2015/11/08@21:05:00.812-0500] P-4216 T-2876 I BROKER 0: (4252) Number of Before-Image Buffers (-bibufs): 20.
[2015/11/08@21:05:00.813-0500] P-4216 T-2876 I BROKER 0: (-----) Record free chain search depth factor 5 (-recspacesearchdepth)
[2015/11/08@21:05:00.814-0500] P-4216 T-2876 I BROKER 0: (9238) BI File Threshold size (-bithold): 0.0 Bytes.
[2015/11/08@21:05:00.814-0500] P-4216 T-2876 I BROKER 0: (6552) BI File Threshold Stall (-bistall): Disabled.
[2015/11/08@21:05:00.815-0500] P-4216 T-2876 I BROKER 0: (4254) After-Image Stall (-aistall): Not Enabled.
[2015/11/08@21:05:00.816-0500] P-4216 T-2876 I BROKER 0: (4255) After-Image Block Size: 8192.
[2015/11/08@21:05:00.817-0500] P-4216 T-2876 I BROKER 0: (4256) Number of After-Image Buffers (-aibufs): 20.
[2015/11/08@21:05:00.817-0500] P-4216 T-2876 I BROKER 0: (-----) Partition Manager cache size (-mtpmsize): 1024
[2015/11/08@21:05:00.819-0500] P-4216 T-2876 I BROKER 0: (8527) Storage object cache size (-omsize): 1024
[2015/11/08@21:05:00.820-0500] P-4216 T-2876 I BROKER 0: (4257) Maximum Number of Clients Per Server (-Ma): 5.
[2015/11/08@21:05:00.821-0500] P-4216 T-2876 I BROKER 0: (4258) Maximum Number of Servers (-Mn): 5.
[2015/11/08@21:05:00.823-0500] P-4216 T-2876 I BROKER 0: (4259) Minimum Clients Per Server (-Mi): 1.
[2015/11/08@21:05:00.824-0500] P-4216 T-2876 I BROKER 0: (16953) Use pollset mechanism for client/server (-pollset): Disabled
[2015/11/08@21:05:00.825-0500] P-4216 T-2876 I BROKER 0: (16954) Server network message wait time (-Nmsgwait): 2
[2015/11/08@21:05:00.826-0500] P-4216 T-2876 I BROKER 0: (16955) Delay first prefetch message (-prefetchDelay): Disabled
[2015/11/08@21:05:00.827-0500] P-4216 T-2876 I BROKER 0: (16956) Prefetch message fill percentage (-prefetchFactor): 0
[2015/11/08@21:05:00.827-0500] P-4216 T-2876 I BROKER 0: (16957) Minimum records in prefetch msg (-prefetchNumRecs): 16
[2015/11/08@21:05:00.828-0500] P-4216 T-2876 I BROKER 0: (16958) Suspension queue poll priority (-prefetchPriority): 0
[2015/11/08@21:05:00.828-0500] P-4216 T-2876 I BROKER 0: (4260) Maximum Number of Users (-n): 21.
[2015/11/08@21:05:00.829-0500] P-4216 T-2876 I BROKER 0: (4261) Host Name (-H): hqdaa4n00020-02.
[2015/11/08@21:05:00.829-0500] P-4216 T-2876 I BROKER 0: (4262) Service Name (-S): dbs1.
[2015/11/08@21:05:00.830-0500] P-4216 T-2876 I BROKER 0: (14268) TCP/IP Version (-ipver) : IPV4
[2015/11/08@21:05:00.831-0500] P-4216 T-2876 I BROKER 0: (4263) Network Type (-N): TCP.
[2015/11/08@21:05:00.831-0500] P-4216 T-2876 I BROKER 0: (4264) Character Set (-cpinternal): ISO8859-1.
[2015/11/08@21:05:00.832-0500] P-4216 T-2876 I BROKER 0: (4282) Parameter File: Not Enabled.
[2015/11/08@21:05:00.832-0500] P-4216 T-2876 I BROKER 0: (5648) Minimum Port for Auto Servers (-minport): 3000.
[2015/11/08@21:05:00.833-0500] P-4216 T-2876 I BROKER 0: (5649) Maximum Port for Auto Servers (-maxport): 5000.
[2015/11/08@21:05:00.834-0500] P-4216 T-2876 I BROKER 0: (8865) This broker supports both 4GL and SQL server groups.
[2015/11/08@21:05:00.835-0500] P-4216 T-2876 I BROKER 0: (12540) Size of JTA transaction table (-maxxids): 100
[2015/11/08@21:05:00.835-0500] P-4216 T-2876 I BROKER 0: (9336) Created shared memory with segment_id: 20905984
[2015/11/08@21:05:00.836-0500] P-4216 T-2876 I BROKER 0: (12813) Allowed index cursors (-c): 84.
[2015/11/08@21:05:00.836-0500] P-4216 T-2876 I BROKER 0: (12814) Group delay (-groupdelay): 10.
[2015/11/08@21:05:00.837-0500] P-4216 T-2876 I BROKER 0: (12815) Lock table hash table size (-lkhash): 1237
[2015/11/08@21:05:00.838-0500] P-4216 T-2876 I BROKER 0: (12816) Maxport (-maxport): 5000
[2015/11/08@21:05:00.838-0500] P-4216 T-2876 I BROKER 0: (12817) Minport (-minport): 3000
[2015/11/08@21:05:00.839-0500] P-4216 T-2876 I BROKER 0: (12818) Message Buffer Size (-Mm): 1024
[2015/11/08@21:05:00.839-0500] P-4216 T-2876 I BROKER 0: (12821) Use muxlatches (-mux): 1
[2015/11/08@21:05:00.840-0500] P-4216 T-2876 I BROKER 0: (12823) Semaphore Sets (-semsets): 3
[2015/11/08@21:05:00.841-0500] P-4216 T-2876 I BROKER 0: (13870) Database Service Manager - IPC Queue Size (-pica) : 64.0 KBytes.
[2015/11/08@21:05:00.841-0500] P-4216 T-2876 I BROKER 0: (13896) TXE Commit lock skip limit (-TXESkipLimit): 10000.
[2015/11/08@21:05:00.842-0500] P-4216 T-2876 I BROKER 0: (15219) Encryption enabled: 0
[2015/11/08@21:05:00.842-0500] P-4216 T-2876 I BROKER 0: (15218) Encryption cache size (-ecsize): 1000
[2015/11/08@21:05:00.843-0500] P-4216 T-2876 I BROKER 0: (15824) Multi-tenancy enabled: 0
[2015/11/08@21:05:00.844-0500] P-4216 T-2876 I BROKER 0: (10471) Database connections have been enabled.
Thanks!
J