What is the best configuration for OE 10.2B Database?

Posted by bluelinebrotha on 29-Aug-2014 13:35

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

OpenEdge RDBMS - Forum

All Replies

Posted by Thomas Mercer-Hursh on 29-Aug-2014 13:44

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?

Posted by bluelinebrotha on 29-Aug-2014 13:51

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.

Posted by Paul Koufalis on 29-Aug-2014 13:54

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.

Posted by bluelinebrotha on 29-Aug-2014 13:59

  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

Posted by bluelinebrotha on 29-Aug-2014 14:02

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  

Posted by Paul Koufalis on 29-Aug-2014 14:13

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?

Posted by bluelinebrotha on 29-Aug-2014 14:21

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

Posted by Paul Koufalis on 29-Aug-2014 14:39

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.

Posted by Thomas Mercer-Hursh on 29-Aug-2014 14:42

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?

Posted by TheMadDBA on 29-Aug-2014 14:52

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?

Posted by gus on 29-Aug-2014 14:57

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

Posted by Paul Koufalis on 29-Aug-2014 15:00

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.

Posted by ChUIMonster on 29-Aug-2014 15:06

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

Posted by ChUIMonster on 29-Aug-2014 15:10

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

Posted by ChUIMonster on 29-Aug-2014 15:13

> 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.

Posted by jmls on 29-Aug-2014 15:13

didn't you read the part where he mentioned that the server is the
domain controller, exchange server, file server, sharepoint server,
IIS Server and terminal server ?

;)

On 29 August 2014 21:11, ChUIMonster
wrote:
> Re: What is the best configuration for OE 10.2B Database?
> Reply by ChUIMonster
> On 8/29/14, 3:02 PM, bluelinebrotha wrote:
>
> RE: What is the best configuration for OE 10.2B Database?
> 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
>
> Stop receiving emails on this subject.
>
> Flag this post as spam/abuse.



--
Julian Lyndon-Smith
IT Director,
dot.r
http://www.dotr.com

Posted by bluelinebrotha on 29-Aug-2014 16:40

The situation is that this is a customer that I used to support when working for the POS company. Back in the days of being a grunt worker :) and just being told what to do. I have come to learn that this barebones way of installing a progress database could actually be impacting performance. I am not ruling out bad code. The code was always bad in my opinion.

I am freelancing work for these customers that are not getting the appropriate support from that POS software provider. So all I can really do is support the OpenEdge stuff and make sure that runs like a top. Beyond that the POS provider will need to support the bad code (or the customer will need to move on).

Posted by Thomas Mercer-Hursh on 29-Aug-2014 16:53

Would it, however, be within your remit to identify bad code so that the vendor could fix it?  While it is worth checking all the stuff that is being discussed, it is still quite possible that there is a piece of bad code in the commit which is the primary contributor to the performance.  If true, you can make it better through tuning, but not fix it.  But you could point out to the vendor what was wrong and possibly even tell them how to fix it.  This is why I asked the question about past vs current performance since that will give us a clue.

Posted by bluelinebrotha on 29-Aug-2014 17:10

I am all about solutions.

when employed by the POS provider, we frequently put out outdated and half-baked versions of the software that would introduce new features but break several others. I've identified this version of the software to be 2 years old, and newer code exists but my customer told me the POS provider will not give them the latest code.

As much as I am into solutions, there is a fair amount of bad blood between myself and the POS provider and they would laugh at me if they knew I was helping their customer base as a freelance. I'll do whatever it takes though.

Posted by Thomas Mercer-Hursh on 30-Aug-2014 09:25

The answer to the question about the pattern of onset is potentially key to deciding whether this is something you can address separately from the vendor or whether they are going to need to be involved.

If the problem is the code and if the vendor won't give them a new version, then I would think that fixing it independently was the only choice.  It wouldn't be the first time.

This thread is closed