What is the best configuration for OE 10.2B Database? - Forum - OpenEdge RDBMS - Progress Community

What is the best configuration for OE 10.2B Database?

 Forum

What is the best configuration for OE 10.2B Database?

This question is not answered

Hello,

I have a client that is running an OpenEdge RDBMS backend with a database. The software is a POS system that links to that database and uses OpenEdge as an engine. They run Windows Server 2008 with 32GB Ram and a 1TB drive. Speed and space do not seem to be the issue for this customer.

He claims that when finalizing a transaction iun the POS system, there is a 30 second or so lag that occurs about 90% of the time on each of his workstations (Note that a full network diagnostic, router replacement, and rewiring was recently done and that did not fix the issue).

I immediately think that this might be a problem with how the OE database was initially setup in OE Explorer. Perhaps there are not enough users/servers or the ratio's are all wrong. This server is a powerhouse so the lag might be coming from setup.

I can post information on the servers configuration, but wanted to see what the idea setup is and what I could do to reduce lag and maintain database efficiency?

Thanks

All Replies
  • A good starting point would be to post the contents of the log from the last server boot.  That will tell us about what your parameters are set to.  Beyond that, you want to gather some information on disk activity and such to let us know where the actual bottleneck is.  How familiar are you with OS and OE diagnostic tools?

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • I am a light user who has moved on from using that kind of database functionally. I can get around the OE Explorer tool, look at logs, run a PROMON and do backups, but besides that, I would need to know exactly how to dial in settings so there are no choke points. My fear is that this customer purchased a overpowered server that is now choking the software due to the configuration and setup of the OE Explorer Tool.

  • Enable LOG-MANAGER logging on one client, specifically 4GLTrans and 4GLTrace.  That will give you a good amount of information about timing on the client side.  Look at the Degugging Guide in the documentation for details on how to do this - it's quite simple.  It is very possible that when you are "finalizing a transaction", there is a very badly indexed FOR EACH occuring.

    On the server side you say "a 1 TB drive".  Are you saying there is ONE hard disk of 1TB?  If yes, you won't get much I/O throughput there unless it's an SSD.

    On the DB side, look specifically at checkpoints frequency and buffers-flushed-at-ckpt.  If it's every 1-2-3 seconds then that's probably a problem.  You can also see how much logical write vs physical write you are doing.

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  •   Fri Aug 29 03:18:37 2014

    31,930    [2014/08/29@03:18:37.399-0400] P-3556       T-3416  I BROKER  0: (333)   Multi-user session begin.  

    31,931    [2014/08/29@03:18:37.417-0400] P-3556       T-3416  I BROKER  0: (5326)  Begin Physical Redo Phase at 0 .  

    31,932    [2014/08/29@03:18:39.774-0400] P-3556       T-3416  I BROKER  0: (7161)  Physical Redo Phase Completed at blk 81 off 1726 upd 733.  

    31,933    [2014/08/29@03:18:39.777-0400] P-3556       T-3416  I BROKER  0: (13547) At end of Physical redo, transaction table size is 128.  

    31,934    [2014/08/29@03:18:40.003-0400] P-3556       T-3416  I BROKER  0: (452)   Login by SYSTEM on batch.  

    31,935    [2014/08/29@03:18:40.033-0400] P-3556       T-3416  I BROKER  0: (5644)  Started for 4000 using TCP IPV4 address 0.0.0.0, pid 3556.  

    31,936    [2014/08/29@03:18:40.034-0400] P-3556       T-3416  I BROKER  0: (8836)  Connecting to Admin Server on port 7842.  

    31,937    [2014/08/29@03:18:40.045-0400] P-3556       T-3416  I BROKER  0: (14262) Successfully connected to AdminServer on port 7842 using TCP/IP IPV4 address 192.168.1.10.  

    31,938    [2014/08/29@03:18:40.068-0400] P-3556       T-3416  I BROKER  0: (8846)  Registered with Admin Server.  

    31,939    [2014/08/29@03:18:40.076-0400] P-3556       T-3416  I BROKER  0: (4234)  Progress OpenEdge Release 10.2B build 1699 SP07 on WINNT .  

    31,940    [2014/08/29@03:18:40.077-0400] P-3556       T-3416  I BROKER  0: (4281)  Server started by SYSTEM on batch.  

    31,941    [2014/08/29@03:18:40.078-0400] P-3556       T-3416  I BROKER  0: (-----) Started using pid: 3556.

    31,942    [2014/08/29@03:18:40.078-0400] P-3556       T-3416  I BROKER  0: (4235)  Physical Database Name (-db): c:\v10\dbase\compass.  

    31,943    [2014/08/29@03:18:40.079-0400] P-3556       T-3416  I BROKER  0: (4236)  Database Type (-dt): PROGRESS.  

    31,944    [2014/08/29@03:18:40.080-0400] P-3556       T-3416  I BROKER  0: (4237)  Force Access (-F): Not Enabled.  

    31,945    [2014/08/29@03:18:40.081-0400] P-3556       T-3416  I BROKER  0: (4238)  Direct I/O (-directio): Not Enabled.  

    31,946    [2014/08/29@03:18:40.082-0400] P-3556       T-3416  I BROKER  0: (-----) LRU mechanism enabled.

    31,947    [2014/08/29@03:18:40.083-0400] P-3556       T-3416  I BROKER  0: (-----) Number of LRU force skips (-lruskips): 0

    31,948    [2014/08/29@03:18:40.084-0400] P-3556       T-3416  I BROKER  0: (-----) Number of LRU2 force skips (-lru2skips): 0

    31,949    [2014/08/29@03:18:40.085-0400] P-3556       T-3416  I BROKER  0: (4239)  Number of Database Buffers (-B): 250000.  

    31,950    [2014/08/29@03:18:40.085-0400] P-3556       T-3416  I BROKER  0: (-----) Number of Alternate Database Buffers (-B2): 0.

    31,951    [2014/08/29@03:18:40.086-0400] P-3556       T-3416  I BROKER  0: (9422)  Maximum private buffers per user (-Bpmax): 64.  

    31,952    [2014/08/29@03:18:40.087-0400] P-3556       T-3416  I BROKER  0: (4240)  Excess Shared Memory Size (-Mxs): 21.  

    31,953    [2014/08/29@03:18:40.088-0400] P-3556       T-3416  I BROKER  0: (10014) The shared memory segment is not locked in memory.  

    31,954    [2014/08/29@03:18:40.089-0400] P-3556       T-3416  I BROKER  0: (4241)  Current Size of Lock Table (-L): 16384.  

    31,955    [2014/08/29@03:18:40.090-0400] P-3556       T-3416  I BROKER  0: (13953) Maximum Area Number (-maxArea): 32000.  

    31,956    [2014/08/29@03:18:40.091-0400] P-3556       T-3416  I BROKER  0: (4242)  Hash Table Entries (-hash): 70289.  

    31,957    [2014/08/29@03:18:40.092-0400] P-3556       T-3416  I BROKER  0: (4243)  Current Spin Lock Tries (-spin): 1.  

    31,958    [2014/08/29@03:18:40.093-0400] P-3556       T-3416  I BROKER  0: (6526)  Number of Semaphore Sets (-semsets): 3.  

    31,959    [2014/08/29@03:18:40.093-0400] P-3556       T-3416  I BROKER  0: (13924) Maximum Shared Memory Segment Size (-shmsegsize) 2048 Mb.  

    31,960    [2014/08/29@03:18:40.094-0400] P-3556       T-3416  I BROKER  0: (4244)  Crash Recovery (-i): Enabled.  

    31,961    [2014/08/29@03:18:40.095-0400] P-3556       T-3416  I BROKER  0: (6573)  Database Blocksize (-blocksize): 4096.  

    31,962    [2014/08/29@03:18:40.095-0400] P-3556       T-3416  I BROKER  0: (4245)  Delay of Before-Image Flush (-Mf): 3.  

    31,963    [2014/08/29@03:18:40.096-0400] P-3556       T-3416  I BROKER  0: (4247)  Before-Image File I/O (-r -R): Reliable.  

    31,964    [2014/08/29@03:18:40.096-0400] P-3556       T-3416  I BROKER  0: (4249)  Before-Image Truncate Interval (-G): 0.  

    31,965    [2014/08/29@03:18:40.097-0400] P-3556       T-3416  I BROKER  0: (4250)  Before-Image Cluster Size: 524288.  

    31,966    [2014/08/29@03:18:40.098-0400] P-3556       T-3416  I BROKER  0: (4251)  Before-Image Block Size: 8192.  

    31,967    [2014/08/29@03:18:40.099-0400] P-3556       T-3416  I BROKER  0: (4252)  Number of Before-Image Buffers (-bibufs): 20.  

    31,968    [2014/08/29@03:18:40.099-0400] P-3556       T-3416  I BROKER  0: (-----) Record free chain search depth factor 5 (-recspacesearchdepth)

    31,969    [2014/08/29@03:18:40.100-0400] P-3556       T-3416  I BROKER  0: (9238)  BI File Threshold size (-bithold): 0.0   Bytes.  

    31,970    [2014/08/29@03:18:40.101-0400] P-3556       T-3416  I BROKER  0: (6552)  BI File Threshold Stall (-bistall): Disabled.  

    31,971    [2014/08/29@03:18:40.102-0400] P-3556       T-3416  I BROKER  0: (4254)  After-Image Stall (-aistall): Not Enabled.  

    31,972    [2014/08/29@03:18:40.102-0400] P-3556       T-3416  I BROKER  0: (4255)  After-Image Block Size: 8192.  

    31,973    [2014/08/29@03:18:40.103-0400] P-3556       T-3416  I BROKER  0: (4256)  Number of After-Image Buffers (-aibufs): 20.  

    31,974    [2014/08/29@03:18:40.104-0400] P-3556       T-3416  I BROKER  0: (8527)  Storage object cache size (-omsize): 1024  

    31,975    [2014/08/29@03:18:40.104-0400] P-3556       T-3416  I BROKER  0: (4257)  Maximum Number of Clients Per Server (-Ma): 2.  

    31,976    [2014/08/29@03:18:40.106-0400] P-3556       T-3416  I BROKER  0: (4258)  Maximum Number of Servers (-Mn): 7.  

    31,977    [2014/08/29@03:18:40.107-0400] P-3556       T-3416  I BROKER  0: (4259)  Minimum Clients Per Server (-Mi): 1.  

    31,978    [2014/08/29@03:18:40.108-0400] P-3556       T-3416  I BROKER  0: (-----) Server network message wait time (-Nmsgwait): 2

    31,979    [2014/08/29@03:18:40.109-0400] P-3556       T-3416  I BROKER  0: (-----) Delay first prefetch message (-prefetchDelay): Disabled

    31,980    [2014/08/29@03:18:40.110-0400] P-3556       T-3416  I BROKER  0: (-----) Prefetch message fill percentage (-prefetchFactor): 0

    31,981    [2014/08/29@03:18:40.110-0400] P-3556       T-3416  I BROKER  0: (-----) Minimum records in prefetch ms (-prefetchNumRecs): 16

    31,982    [2014/08/29@03:18:40.111-0400] P-3556       T-3416  I BROKER  0: (-----) Suspension queue poll priority (-prefetchPriority): 0

    31,983    [2014/08/29@03:18:40.112-0400] P-3556       T-3416  I BROKER  0: (4260)  Maximum Number of Users (-n): 13.  

    31,984    [2014/08/29@03:18:40.113-0400] P-3556       T-3416  I BROKER  0: (4261)  Host Name (-H): W2K8-SERVER.  

    31,985    [2014/08/29@03:18:40.114-0400] P-3556       T-3416  I BROKER  0: (4262)  Service Name (-S): 4000.  

    31,986    [2014/08/29@03:18:40.115-0400] P-3556       T-3416  I BROKER  0: (14268) TCP/IP Version (-ipver) : IPV4  

    31,987    [2014/08/29@03:18:40.116-0400] P-3556       T-3416  I BROKER  0: (4263)  Network Type (-N): TCP.  

    31,988    [2014/08/29@03:18:40.116-0400] P-3556       T-3416  I BROKER  0: (4264)  Character Set (-cpinternal): ISO8859-1.  

    31,989    [2014/08/29@03:18:40.117-0400] P-3556       T-3416  I BROKER  0: (4282)  Parameter File: Not Enabled.  

    31,990    [2014/08/29@03:18:40.118-0400] P-3556       T-3416  I BROKER  0: (5647)  Maximum Servers Per Broker (-Mpb): 6.  

    31,991    [2014/08/29@03:18:40.119-0400] P-3556       T-3416  I BROKER  0: (5648)  Minimum Port for Auto Servers (-minport): 3000.  

    31,992    [2014/08/29@03:18:40.121-0400] P-3556       T-3416  I BROKER  0: (5649)  Maximum Port for Auto Servers (-maxport): 5000.  

    31,993    [2014/08/29@03:18:40.121-0400] P-3556       T-3416  I BROKER  0: (8865)  This broker supports both 4GL and SQL server groups.  

    31,994    [2014/08/29@03:18:40.122-0400] P-3556       T-3416  I BROKER  0: (9336)  Created shared memory with segment_id: 34930688  

    31,995    [2014/08/29@03:18:40.123-0400] P-3556       T-3416  I BROKER  0: (12813) Allowed index cursors (-c): 52.  

    31,996    [2014/08/29@03:18:40.124-0400] P-3556       T-3416  I BROKER  0: (12814) Group delay (-groupdelay): 10.  

    31,997    [2014/08/29@03:18:40.125-0400] P-3556       T-3416  I BROKER  0: (12815) Lock table hash table size (-lkhash): 2437  

    31,998    [2014/08/29@03:18:40.127-0400] P-3556       T-3416  I BROKER  0: (12816) Maxport (-maxport): 5000  

    31,999    [2014/08/29@03:18:40.128-0400] P-3556       T-3416  I BROKER  0: (12817) Minport (-minport): 3000  

    32,000    [2014/08/29@03:18:40.129-0400] P-3556       T-3416  I BROKER  0: (12818) Message Buffer Size (-Mm): 1024  

    32,001    [2014/08/29@03:18:40.130-0400] P-3556       T-3416  I BROKER  0: (12820) Maximum Servers per Broker (-Mpb): 6  

    32,002    [2014/08/29@03:18:40.131-0400] P-3556       T-3416  I BROKER  0: (12821) Use muxlatches (-mux): 1  

    32,003    [2014/08/29@03:18:40.132-0400] P-3556       T-3416  I BROKER  0: (12823) Semaphore Sets (-semsets): 3  

    32,004    [2014/08/29@03:18:40.132-0400] P-3556       T-3416  I BROKER  0: (13870) Database Service Manager - IPC Queue Size (-pica) : 64.0  KBytes.  

    32,005    [2014/08/29@03:18:40.133-0400] P-3556       T-3416  I BROKER  0: (13896) TXE Commit lock skip limit (-TXESkipLimit): 10000.  

    32,006    [2014/08/29@03:18:40.134-0400] P-3556       T-3416  I BROKER  0: (15219) Encryption enabled: 0  

    32,007    [2014/08/29@03:18:40.135-0400] P-3556       T-3416  I BROKER  0: (15218) Encryption cache size (-ecsize): 1000  

    32,008    [2014/08/29@03:18:40.227-0400] P-3556       T-3416  I BROKER  0: (10471) Database connections have been enabled.  

    32,009    [2014/08/29@03:18:42.866-0400] P-3052       T-2932  I WDOG    7: (-----) Login by SYSTEM.

    32,010    [2014/08/29@03:18:42.890-0400] P-3052       T-2932  I WDOG    7: (2518)  Started.  

    32,011    [2014/08/29@03:18:42.910-0400] P-1072       T-3168  I FMAGENT 8: (452)   Login by SYSTEM on batch.  

    32,012    [2014/08/29@03:18:42.914-0400] P-1072       T-3168  I FMAGENT 8: (7129)  Usr 8 set name to DB_Agent.  

    32,013    [2014/08/29@03:18:42.923-0400] P-1072       T-3168  I FMAGENT 8: (14262) Successfully connected to AdminServer on port 8842 using TCP/IP IPv4 address 192.168.1.10.  

    32,014    [2014/08/29@03:18:42.942-0400] P-1072       T-3168  I FMAGENT 8: (8846)  Registered with Admin Server.  

    32,015    [2014/08/29@05:00:02.992-0400] P-3988       T-1548  I SRV     1: (452)   Login by SYSTEM on CON:.  

    32,016    [2014/08/29@05:00:03.007-0400] P-3988       T-1548  I SRV     1: (5646)  Started on port 3000 using TCP IPV4 address 0.0.0.0, pid 3988.  

    32,017    [2014/08/29@05:00:03.979-0400] P-3988       T-1548  I SRV     1: (742)   Login usernum 18, userid Administrato client type ABL , on W2K8-SERVER using TCP/IP IPV4 address 127.0.0.1.  

    32,018    [2014/08/29@05:00:03.997-0400] P-3988       T-1548  I SRV     1: (708)   Userid is now user01.  

    32,019    [2014/08/29@05:00:04.003-0400] P-3988       T-1548  I SRV     1: (14658) Previous message sent on behalf of user 18, server pid 3988, broker pid 3556. (5512)  

    32,020    [2014/08/29@05:00:04.008-0400] P-3988       T-1548  I SRV     1: (5180)  Event Logger error 0: Could not post a 'COMPASS_PROGRESS' event to the Event Viewer.  

    32,021    [2014/08/29@05:00:04.008-0400] P-3988       T-1548  I SRV     1: (14658) Previous message sent on behalf of user 18, server pid 3988, broker pid 3556. (5512)  

    32,022    [2014/08/29@05:00:04.287-0400] P-3988       T-1548  I SRV     1: (12699) Database compass Options:  

    32,023    [2014/08/29@05:00:04.288-0400] P-3988       T-1548  I SRV     1: (14658) Previous message sent on behalf of user 18, server pid 3988, broker pid 3556. (5512)  

    32,024    [2014/08/29@05:00:05.329-0400] P-3988       T-1548  I SRV     1: (739)   Logout usernum 18, userid user01, on W2K8-SERVER.  

    32,025    [2014/08/29@05:43:34.133-0400] P-3988       T-1548  I SRV     1: (742)   Login usernum 18, userid Broadview3 client type ABL , on W2K8-SERVER using TCP/IP IPV4 address 127.0.0.1.  

    32,026    [2014/08/29@05:43:34.134-0400] P-3988       T-1548  I SRV     1: (708)   Userid is now user03.  

    32,027    [2014/08/29@05:43:34.135-0400] P-3988       T-1548  I SRV     1: (14658) Previous message sent on behalf of user 18, server pid 3988, broker pid 3556. (5512)  

    32,028    [2014/08/29@05:43:34.152-0400] P-3988       T-1548  I SRV     1: (12699) Database compass Options:  

    32,029    [2014/08/29@05:43:34.153-0400] P-3988       T-1548  I SRV     1: (14658) Previous message sent on behalf of user 18, server pid 3988, broker pid 3556. (5512)  

    32,030    [2014/08/29@06:39:34.736-0400] P-4224       T-4228  I SRV     2: (452)   Login by SYSTEM on CON:.  

    32,031    [2014/08/29@06:39:34.746-0400] P-4224       T-4228  I SRV     2: (5646)  Started on port 3001 using TCP IPV4 address 0.0.0.0, pid 4224.  

    32,032    [2014/08/29@06:39:35.722-0400] P-4224       T-4228  I SRV     2: (742)   Login usernum 17, userid broadview4 client type ABL , on W2K8-SERVER using TCP/IP IPV4 address 127.0.0.1.  

    32,033    [2014/08/29@06:39:35.723-0400] P-4224       T-4228  I SRV     2: (708)   Userid is now user04.  

    32,034    [2014/08/29@06:39:35.723-0400] P-4224       T-4228  I SRV     2: (14658) Previous message sent on behalf of user 17, server pid 4224, broker pid 3556. (5512)  

    32,035    [2014/08/29@06:39:35.733-0400] P-4224       T-4228  I SRV     2: (12699) Database compass Options:  

    32,036    [2014/08/29@06:39:35.733-0400] P-4224       T-4228  I SRV     2: (14658) Previous message sent on behalf of user 17, server pid 4224, broker pid 3556. (5512)  

    32,037    [2014/08/29@06:41:41.144-0400] P-568        T-4360  I SRV     3: (452)   Login by SYSTEM on CON:.  

    32,038    [2014/08/29@06:41:41.154-0400] P-568        T-4360  I SRV     3: (5646)  Started on port 3002 using TCP IPV4 address 0.0.0.0, pid 568.  

    32,039    [2014/08/29@06:41:42.130-0400] P-568        T-4360  I SRV     3: (742)   Login usernum 16, userid Broadview2 client type ABL , on W2K8-SERVER using TCP/IP IPV4 address 127.0.0.1.  

    32,040    [2014/08/29@06:41:42.131-0400] P-568        T-4360  I SRV     3: (708)   Userid is now user02.  

    32,041    [2014/08/29@06:41:42.131-0400] P-568        T-4360  I SRV     3: (14658) Previous message sent on behalf of user 16, server pid 568, broker pid 3556. (5512)  

    32,042    [2014/08/29@06:41:42.143-0400] P-568        T-4360  I SRV     3: (12699) Database compass Options:  

    32,043    [2014/08/29@06:41:42.144-0400] P-568        T-4360  I SRV     3: (14658) Previous message sent on behalf of user 16, server pid 568, broker pid 3556. (5512)  

    32,044    [2014/08/29@06:44:26.585-0400] P-2520       T-2660  I SRV     4: (452)   Login by SYSTEM on CON:.  

    32,045    [2014/08/29@06:44:26.595-0400] P-2520       T-2660  I SRV     4: (5646)  Started on port 3003 using TCP IPV4 address 0.0.0.0, pid 2520.  

    32,046    [2014/08/29@06:44:27.571-0400] P-2520       T-2660  I SRV     4: (742)   Login usernum 15, userid Broadview1 client type ABL , on W2K8-SERVER using TCP/IP IPV4 address 127.0.0.1.  

    32,047    [2014/08/29@06:44:27.572-0400] P-2520       T-2660  I SRV     4: (708)   Userid is now user01.  

    32,048    [2014/08/29@06:44:27.572-0400] P-2520       T-2660  I SRV     4: (14658) Previous message sent on behalf of user 15, server pid 2520, broker pid 3556. (5512)  

    32,049    [2014/08/29@06:44:27.582-0400] P-2520       T-2660  I SRV     4: (12699) Database compass Options:  

    32,050    [2014/08/29@06:44:27.582-0400] P-2520       T-2660  I SRV     4: (14658) Previous message sent on behalf of user 15, server pid 2520, broker pid 3556. (5512)  

    32,051    [2014/08/29@06:54:06.648-0400] P-4900       T-3076  I SRV     5: (452)   Login by SYSTEM on CON:.  

    32,052    [2014/08/29@06:54:06.665-0400] P-4900       T-3076  I SRV     5: (5646)  Started on port 3004 using TCP IPV4 address 0.0.0.0, pid 4900.  

    32,053    [2014/08/29@06:54:07.627-0400] P-4900       T-3076  I SRV     5: (742)   Login usernum 14, userid StateRd21 client type ABL , on W2K8-SERVER using TCP/IP IPV4 address 127.0.0.1.  

    32,054    [2014/08/29@06:54:07.628-0400] P-4900       T-3076  I SRV     5: (708)   Userid is now user21.  

    32,055    [2014/08/29@06:54:07.629-0400] P-4900       T-3076  I SRV     5: (14658) Previous message sent on behalf of user 14, server pid 4900, broker pid 3556. (5512)  

    32,056    [2014/08/29@06:54:07.654-0400] P-4900       T-3076  I SRV     5: (12699) Database compass Options:  

    32,057    [2014/08/29@06:54:07.655-0400] P-4900       T-3076  I SRV     5: (14658) Previous message sent on behalf of user 14, server pid 4900, broker pid 3556. (5512)  

    32,058    [2014/08/29@06:54:08.252-0400] P-2624       T-4648  I SRV     6: (5180)  Event Logger error 0: Could not post a 'COMPASS_PROGRESS' event to the Event Viewer.  

    32,059    [2014/08/29@06:54:08.252-0400] P-2624       T-4648  I SRV     6: (452)   Login by SYSTEM on CON:.  

    32,060    [2014/08/29@06:54:08.276-0400] P-2624       T-4648  I SRV     6: (5646)  Started on port 3005 using TCP IPV4 address 0.0.0.0, pid 2624.  

    32,061    [2014/08/29@06:54:09.227-0400] P-2624       T-4648  I SRV     6: (742)   Login usernum 13, userid staterd222 client type ABL , on W2K8-SERVER using TCP/IP IPV4 address 127.0.0.1.  

    32,062    [2014/08/29@06:54:09.228-0400] P-2624       T-4648  I SRV     6: (708)   Userid is now user22.  

    32,063    [2014/08/29@06:54:09.229-0400] P-2624       T-4648  I SRV     6: (14658) Previous message sent on behalf of user 13, server pid 2624, broker pid 3556. (5512)  

    32,064    [2014/08/29@06:54:09.251-0400] P-2624       T-4648  I SRV     6: (12699) Database compass Options:  

    32,065    [2014/08/29@06:54:09.252-0400] P-2624       T-4648  I SRV     6: (14658) Previous message sent on behalf of user 13, server pid 2624, broker pid 3556. (5512)  

    32,066    [2014/08/29@06:58:36.580-0400] P-3988       T-1548  I SRV     1: (742)   Login usernum 12, userid StateRd23 client type ABL , on W2K8-SERVER using TCP/IP IPV4 address 127.0.0.1.  

    32,067    [2014/08/29@06:58:36.581-0400] P-3988       T-1548  I SRV     1: (708)   Userid is now user23.  

    32,068    [2014/08/29@06:58:36.582-0400] P-3988       T-1548  I SRV     1: (14658) Previous message sent on behalf of user 12, server pid 3988, broker pid 3556. (5512)  

    32,069    [2014/08/29@06:58:36.598-0400] P-3988       T-1548  I SRV     1: (12699) Database compass Options:  

    32,070    [2014/08/29@06:58:36.599-0400] P-3988       T-1548  I SRV     1: (14658) Previous message sent on behalf of user 12, server pid 3988, broker pid 3556. (5512)  

    32,071    [2014/08/29@07:14:23.636-0400] P-4224       T-4228  I SRV     2: (742)   Login usernum 11, userid Bernie client type ABL , on W2K8-SERVER using TCP/IP IPV4 address 127.0.0.1.  

    32,072    [2014/08/29@07:14:23.645-0400] P-4224       T-4228  I SRV     2: (708)   Userid is now user32.  

    32,073    [2014/08/29@07:14:23.645-0400] P-4224       T-4228  I SRV     2: (14658) Previous message sent on behalf of user 11, server pid 4224, broker pid 3556. (5512)  

    32,074    [2014/08/29@07:14:23.656-0400] P-4224       T-4228  I SRV     2: (12699) Database compass Options:  

    32,075    [2014/08/29@07:14:23.657-0400] P-4224       T-4228  I SRV     2: (14658) Previous message sent on behalf of user 11, server pid 4224, broker pid 3556. (5512)  

    32,076    [2014/08/29@07:17:26.076-0400] P-4224       T-4228  I SRV     2: (739)   Logout usernum 11, userid user32, on W2K8-SERVER.  

    32,077    [2014/08/29@07:22:46.464-0400] P-4224       T-4228  I SRV     2: (742)   Login usernum 11, userid Bernie client type ABL , on W2K8-SERVER using TCP/IP IPV4 address 127.0.0.1.  

    32,078    [2014/08/29@07:22:46.465-0400] P-4224       T-4228  I SRV     2: (708)   Userid is now user32.  

    32,079    [2014/08/29@07:22:46.466-0400] P-4224       T-4228  I SRV     2: (14658) Previous message sent on behalf of user 11, server pid 4224, broker pid 3556. (5512)  

    32,080    [2014/08/29@07:22:46.481-0400] P-4224       T-4228  I SRV     2: (12699) Database compass Options:  

    32,081    [2014/08/29@07:22:46.482-0400] P-4224       T-4228  I SRV     2: (14658) Previous message sent on behalf of user 11, server pid 4224, broker pid 3556. (5512)  

    32,082    [2014/08/29@07:51:31.118-0400] P-4224       T-4228  I SRV     2: (739)   Logout usernum 11, userid user32, on W2K8-SERVER.  

    32,083    [2014/08/29@08:09:37.028-0400] P-4224       T-4228  I SRV     2: (742)   Login usernum 11, userid Bernie client type ABL , on W2K8-SERVER using TCP/IP IPV4 address 127.0.0.1.  

    32,084    [2014/08/29@08:09:37.029-0400] P-4224       T-4228  I SRV     2: (708)   Userid is now user32.  

    32,085    [2014/08/29@08:09:37.029-0400] P-4224       T-4228  I SRV     2: (14658) Previous message sent on behalf of user 11, server pid 4224, broker pid 3556. (5512)  

    32,086    [2014/08/29@08:09:37.046-0400] P-4224       T-4228  I SRV     2: (12699) Database compass Options:  

    32,087    [2014/08/29@08:09:37.046-0400] P-4224       T-4228  I SRV     2: (14658) Previous message sent on behalf of user 11, server pid 4224, broker pid 3556. (5512)  

    32,088    [2014/08/29@08:20:38.031-0400] P-4224       T-4228  I SRV     2: (794)   Usernum 11 terminated abnormally.  

    32,089    [2014/08/29@08:20:38.032-0400] P-4224       T-4228  I SRV     2: (739)   Logout usernum 11, userid user32, on W2K8-SERVER.  

    32,090    [2014/08/29@09:35:30.171-0400] P-4224       T-4228  I SRV     2: (742)   Login usernum 11, userid Bernie client type ABL , on W2K8-SERVER using TCP/IP IPV4 address 127.0.0.1.  

    32,091    [2014/08/29@09:35:30.171-0400] P-4224       T-4228  I SRV     2: (708)   Userid is now user32.  

    32,092    [2014/08/29@09:35:30.172-0400] P-4224       T-4228  I SRV     2: (14658) Previous message sent on behalf of user 11, server pid 4224, broker pid 3556. (5512)  

    32,093    [2014/08/29@09:35:30.188-0400] P-4224       T-4228  I SRV     2: (12699) Database compass Options:  

    32,094    [2014/08/29@09:35:30.189-0400] P-4224       T-4228  I SRV     2: (14658) Previous message sent on behalf of user 11, server pid 4224, broker pid 3556. (5512)  

    32,095    [2014/08/29@10:44:47.476-0400] P-4224       T-4228  I SRV     2: (794)   Usernum 11 terminated abnormally.  

    32,096    [2014/08/29@10:44:47.476-0400] P-4224       T-4228  I SRV     2: (739)   Logout usernum 11, userid user32, on W2K8-SERVER.  

    Compass is the dbname and name of the software as well. They have an 8 user limit to their license. Runing 10.2B Service Pack 7

  • I think I got confused on HD size. 1TB in a Raid 10, so multiple drives I believe. I am remote and not physically on location so I am relying on the owner of the store to get this info for me. What I can see in OE Explorer I have posted below:

    Blocks in database buffers (primary):    250000    

    Alternate buffer pool:    0    

    Hash table entries:    0    

    Lock table entries:    16384    

    Max users:    12    

    Max servers:    6    

    Maximum private database buffer per user:    0  

    Watch dog: Enabled

    Maximum clients per server:    2    

    Minimum clients per server:    1  

  • Those parameters, on the surface, look fine. From the log you posted it looks like the users are opening Terminal Server sessions directly on the same physical box as the DB and connecting to the DB through -H localhost -S 4000.

    How many CPUs does the server have?  Is it a physical or virtual box?

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • That is correct. Client machines log in with RDP to a terminal client session.

    1 processor, a Xeon 2.20ghz (This is a Dell PowerEdge T320 Server)

    Physical box

  • Well with 8 clients and a handful of server processes I doubt that you're CPU bound but it's possible that the clients and server are competing for CPU time.

    My money is still on bad code.

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • Apropos bad code, is the current bad behavior something that has always been there, something that has grown gradually this way over time, or something that suddenly got bad at some point?

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • Likely suspects are bad code or flushing at checkpoints.

    Is this a Workgroup install and not Enterprise?

    I see -spin is set to 1 and no apws or biws.

    Also no AI :(

    What does promon R&D 3 4 (checkpoints) show?

  • just a guess, but it smells like maybe there is a full table scan occurring.

  • MadDBA: It's an 8 user system so very likely a WG. And if they're posting POS tx then it's not like they're running MRP!!

    Unless, of course, it's bad code.

    Paul Koufalis
    White Star Software

    pk@wss.com
    @oeDBA (https://twitter.com/oeDBA)

    ProTop: The #1 Free OpenEdge DB Monitoring Tool
    http://protop.wss.com
  • On 8/29/14, 2:59 PM, bluelinebrotha wrote:
    Reply by bluelinebrotha

      Fri Aug 29 03:18:37 2014

    31,930    [2014/08/29@03:18:37.399-0400] P-3556       T-3416  I BROKER  0: (333)   Multi-user session begin.  

    31,949    [2014/08/29@03:18:40.085-0400] P-3556       T-3416  I BROKER  0: (4239)  Number of Database Buffers (-B): 250000.  

    Kind of small, just 1GB of cache (I see 4k db blocks later on...).  But probably not related to slow commits.

    31,957    [2014/08/29@03:18:40.092-0400] P-3556       T-3416  I BROKER  0: (4243)  Current Spin Lock Tries (-spin): 1.  

    A "Workgroup" database?

    If true that is probably the fundamental problem.  Slow commits are the trade-off that you make for cheap licenses.

    If false and the license is actually "Enterprise" then start a BIW and an APW and set -spin 5000.

    31,965    [2014/08/29@03:18:40.097-0400] P-3556       T-3416  I BROKER  0: (4250)  Before-Image Cluster Size: 524288.  

    The default bi cluster size -- if this is, indeed, a workgroup database try making it smaller.  Maybe 128k.  That will result in more frequent, but shorter, pauses when committing transactions.  If it is Enterprise make it larger.  16384 is "a good start" (if and only if it is an enterprise license).

    31,978    [2014/08/29@03:18:40.108-0400] P-3556       T-3416  I BROKER  0: (-----) Server network message wait time (-Nmsgwait): 2

    31,979    [2014/08/29@03:18:40.109-0400] P-3556       T-3416  I BROKER  0: (-----) Delay first prefetch message (-prefetchDelay): Disabled

    31,980    [2014/08/29@03:18:40.110-0400] P-3556       T-3416  I BROKER  0: (-----) Prefetch message fill percentage (-prefetchFactor): 0

    31,981    [2014/08/29@03:18:40.110-0400] P-3556       T-3416  I BROKER  0: (-----) Minimum records in prefetch ms (-prefetchNumRecs): 16

    31,982    [2014/08/29@03:18:40.111-0400

    --
    Tom Bascom
    tom@wss.com

  • On 8/29/14, 3:02 PM, bluelinebrotha wrote:
    Reply by bluelinebrotha

    I think I got confused on HD size. 1TB in a Raid 10, so multiple drives I believe. I am remote and not physically on location so I am relying on the owner of the store to get this info for me.


    RAID10 strikes me as extremely unlikely.   Someone who bought workgroup db licenses is *much* more likely to have a RAID5 or RAID6 NAS for disk.  And if the disk is being shared write throughput is generally the first thing to suffer.

    Is this server virtual?  If it is virtual and "thin provisioned" or "over committed" that's a problem too.

    -- 
    Tom Bascom
    603 396 4886
    tom@greenfieldtech.com

    --
    Tom Bascom
    tom@wss.com

  • > 31,978    [2014/08/29@03:18:40.108-0400] P-3556       T-3416  I BROKER  0: (-----) Server network message wait time (-Nmsgwait): 2

    >

    > 31,979    [2014/08/29@03:18:40.109-0400] P-3556       T-3416  I BROKER  0: (-----) Delay first prefetch message (-prefetchDelay): Disabled

    >

    > 31,980    [2014/08/29@03:18:40.110-0400] P-3556       T-3416  I BROKER  0: (-----) Prefetch message fill percentage (-prefetchFactor): 0

    >

    > 31,981    [2014/08/29@03:18:40.110-0400] P-3556       T-3416  I BROKER  0: (-----) Minimum records in prefetch ms (-prefetchNumRecs): 16

    >

    > 31,982    [2014/08/29@03:18:40.111-0400] P-3556       T-3416  I BROKER  0: (-----) Suspension queue poll priority (-prefetchPriority): 0

    These are new with 10.2B06 and they relate to fetching data -- so probably not related to committing transactions unless your user is misleading you.  If there is a chance that the real problem is that "transaction" includes fetching a bunch of data then set:

    -Mm 8192 -prefetchDelay -prefetchFactor 100 -prefetchNumRecs 200

    (Use "otherargs" in the properties file.)

    Also enable "jumbo frames" in all of the networking infrastructure.

    Also enable after-imaging.  Just because it is a workgroup license doesn't mean that you should not be using after imaging.

    --
    Tom Bascom
    tom@wss.com