Question Slow Database In New Server

grupoci

New Member
Hi, recently we change our databases to a new server but the startup and the queries are extremely slow, even tho the hardware is better than before.

We lack knowledge about databases, we manage our clients services and remote connection as well as other services but we do not know about databases, we cannot diagnose and fix the performance issues, so we thought you guys could help us.

The virtual machine specifications are:

Windows 2012 x64, Xeon E5-2620 v3 2,40Ghz, two processors, and 17Gb Ram. (8 cores for the virtual machine)

We currently use OpenEdge 11.1 x32, and VMware Sphere ESX 5.1.
As virtual network interface we use vmxnet3.

This is the startup log of the DB:

BROKER 0: (333) Login multi-user.
BROKER 0: (15321) Log Initialization Before Image in block 758, offset 964.
BROKER 0: (452) The SYSTEM user logs in batch.
BROKER 0: (5644) to 2509 using TCP Arrancado IPV4 address 0.0.0.0, pid 364.
BROKER 0: (8836) Connecting to the Admin Server on port 7842.
BROKER 0: (14262) Successfully connected to AdminServer on port 7842 using TCP / IP IPV4 address 192,168,150,254.
BROKER 0: (8846) Registered with the Admin Server.
BROKER 0: (4234) Release Progress OpenEdge 11.1 build 1143 in WINNT.
BROKER 0: (4281) initiated by SYSTEM on Server batch.
BROKER 0: (6574) Booted using pid: 364.
BROKER 0: (4235) Physical name of the database (-db): C: \ SYNERGY \ Active \ BDCemevisa \ ver2000.
BROKER 0: (4236) Database type (-dt): PROGRESS.
BROKER 0: (4237) Forced access (-F): Not Enabled.
BROKER 0: (4238) Direct I / S (-directio): Not Enabled.
BROKER 0: (-----) enabled LRU mechanism.
BROKER 0: (-----) Number of LRU force skips (-lruskips): 0
BROKER 0: (-----) Number of LRU2 force skips (-lru2skips): 0
BROKER 0: (4239) Number of Buffers Database (-B): 390000.
BROKER 0: (-----) Number of Alternate Database Buffers (-B2): 0.
BROKER 0: (9422) Max number of private buffers by to list it (-Bpmax): 256.
BROKER 0: (4240) has exceeded the size of shared memory (-Mxs): 100.
BROKER 0: (10013) The shared memory segment is locked into memory.
BROKER 0: (4241) Current size of the lock table (L): 819200.
BROKER 0: (13953) Number of maximum area (-maxArea): 32000.
BROKER 0: (4242) Table Entries Hash (-hash): 32.
BROKER 0: (4243) Current attempts Spin Locks (-spin): 80000.
BROKER 0: (6526) Number semaphore set (-semsets) 3.
BROKER 0: (13924) Maximum size of shared memory segment (-shmsegsize) 4096 Mb.
BROKER 0: (4244) Crash Recovery (-i): Enabled.
BROKER 0: (6573) Database Blocksize (-blocksize): 4,096.
BROKER 0: (4245) Delayed flow-Picture Previous (-mf): 2.
BROKER 0: (4247) E / S in file-Picture Previous (-r -R): Reliable.
BROKER 0: (4249) truncation Range Image-anteror (-G): 0.
BROKER 0: (4250) Cluster size Image-Previous: 524288.
BROKER 0: (4251) Block Size Image-Previous: 8192.
BROKER 0: (4252) Number of buffers IMAGE-Previous (-bibufs): 90.
BROKER 0: (-----) Record free chain search depth factor of 5 (-recspacesearchdepth)
BROKER 0: (9238) File Size Threshold of BI (-bithold): 999.5 MBytes.
[BROKER 0: (6551) File Stall Threshold BI (-bistall): Enabled.
BROKER 0: (4254) Since IMAGE-Posterior (-aistall): Enabled.
BROKER 0: (4255) Block Size Image-Posterior: 8192.
BROKER 0: (4256) Number of buffers IMAGE-Posterior (-aibufs): 40.
ROKER 0: (-----) Partition Manager cache size (-mtpmsize): 1024
BROKER 0: (8527) cache size storage object (-omsize): 8192
BROKER 0: (4257) Maximum number of clients per server (Ma): 2.
BROKER 0: (4258) Maximum number of servers (-Mn): 26.
BROKER 0: (4259) Minimum number of clients per server (My): 1.
BROKER 0: (-----) Server network message wait time (-Nmsgwait): 2
BROKER 0: (-----) Delay first prefetch message (-prefetchDelay): Disabled
BROKER 0: (-----) Prefetch message fill percentage (-prefetchFactor): 0
BROKER 0: (-----) Minimum records in prefetch msg (-prefetchNumRecs): 16
BROKER 0: (-----) Suspension poll queue priority (-prefetchPriority): 0
BROKER 0: (4260) Maximum number of users (n): 61.
BROKER 0: (4261) Host Name (H): bData.
BROKER 0: (4262) Name Service (-S): 2509.
BROKER 0: (14268) version of TCP / IP (-ipver): IPV4
BROKER 0: (4263) Network type (N): TCP.
BROKER 0: (4264) Character Set (-cpinternal): ISO8859-1.
BROKER 0: (4282) Parameter File: Not Enabled.
BROKER 0: (5647) Limit broker servers (-Mpb): 23.
BROKER 0: (5648) Minimum Port for Auto Servers (-minport): 3000.
BROKER 0: (5649) Maximum Port for Auto Servers (-maxport): 5000.
BROKER 0: (8863) This broker only supports groups 4GL servers.
BROKER 0: (9336) Created with the shared memory segment id: 43057152
BROKER 0: (12813) allowed Cursors index (c): 244.
BROKER 0: (12814) Group Delay (-groupdelay): 10.
BROKER 0: (12815) Block hash table, table size (-lkhash): 137,743
BROKER 0: (12816) Maxport (-maxport): 5000
BROKER 0: (12817) Minport (-minport): 3000
BROKER 0: (12818) Message Buffer Size (Mm): 32600
BROKER 0: (12820) Servers highs Broker (-Mpb): 23
BROKER 0: (12821) Use muxlatches (-mux): 1
BROKER 0: (12823) Traffic lights (-semsets): 3
BROKER 0: (13870) Database Service Manager - Queue Size IPC (-pica): 8.0 MBytes.
BROKER 0: (13896) Limit jump TXE lock Commit (-TXESkipLimit): 10000.
BROKER 0: (15219) encryption is enabled: 0
BROKER 0: (15218) cache size encryption (-ecsize): 1000
BROKER 0: (15824) Multi-tenancy enabled: 0
BROKER 0: (10471) Database Have Been enabled connections.
SRV 1: (452) The SYSTEM user logs in batch.
BROKER 1: (5644) 2500 Arrancado using TCP IPV4 address 0.0.0.0, pid 3272.
BROKER 1: (5645) This is an additional broker for this protocol.
BROKER 1: (8864) This broker only supports SQL server groups.
SRV 2: (452) The user logs on SYSTEM WITH :.
SRV 2: (5646) Booted in IPV4 TCP port 3001 using address 0.0.0.0, pid
SRV 2: (742) connecting the user number 85, userid client manager type ABL, in bData using TCP / IP IPV4 address 192,168,150,254.
SRV 2: (7129) 85 Usr set to administrator name.


Also, we have around 60 clients, which values should B1 and B2 be at?


I really appreciate your help guys.

- See more at: Slow database in new server. - Forum - OpenEdge General - Progress Community
 

grupoci

New Member
Looks like you're getting good responses over at the Community. :)
Haha yeah, just thought more insights would help. You know, speed the process and all that.
Are this two communities linked or anything? I thought they were different forums.
 

Cringer

ProgressTalk.com Moderator
Staff member
Yes different forums, but much the same people answering questions really.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Is this an in-house application or a third-party (vendor-supplied) application?

Who is responsible for database administration?
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Someone has clearly attempted some tuning of the database, given the number of parameters with non-default values. And while some of them are not what I would set out of the box, and some indicate possible application problems (-L 819200 tells me there is some bad code and this was the "temporary" workaround), it is difficult to do meaningful database tuning remotely without having a good understanding of the big picture. The big picture includes:
  • What is the database server's workload, if any, apart from the database itself?
  • What do the ABL and SQL clients do? Are they write-heavy, read-heavy, some mix?
  • Where are the clients and how do they connect to the database? TCP, self-service, a mix?
  • What do we know about the network path(s) that they use?
  • (echoing Thomas' question) Why are you using the 32-bit database license? Does anything about the application prevent you from swapping this for 64-bit? In this day and age, all databases should be 64-bit.
  • You're running a release of OpenEdge (11.1) that is several years old and is in retired status. Is there anything preventing you from upgrading to a more modern release, like 11.6, that would give you the possibility of better performance, security, and support?
  • Who originally tuned this database? Are they still available to answer questions? If so, why did they chose the settings they chose?
  • What is the actual problem? You said "the startup and the queries are extremely slow".
    • Do you mean application startup or database startup? If the latter, you have an OS or VMware problem, not an OpenEdge problem.
    • Regarding the queries, "extremely slow" compared with what? Do you still have a functioning implementation of this application on the old hardware for comparison? If so you should check how much logical and physical I/O is happening in each, so you can determine that this is actually an apples-to-apples comparison. Is it exactly the same code, application configuration, and database configuration in the old and the new installations?
    • Can you see any obvious bottlenecks in the virtual or physical host metrics during startup or during long-running queries?
    • Have you benchmarked the performance of the server (CPU, I/O) outside of OpenEdge to determine that its performance is reasonable given the specs? I've seen virtualized servers that were poorly configured that performed at a level that was a small fraction of what they should be able to do, in allocating memory, doing file reads and writes, doing network I/O, etc. All the application and database tuning in the world won't help if the underlying VM, physical host, and network aren't working as they should. Given your small buffer pool, which is necessarily small since you're using 32-bit, it is especially important to have very fast disks if you want reasonable query speed.
    • You can get some information about logical and physical database I/O using promon, the Progress database monitoring tool. But to diagnose query efficiency effectively you really need to dig deeper and get per-object and per-user logical I/O statistics from the database's virtual system tables (VSTs). That will require the appropriate code, database configuration, and knowledge, which may be inaccessible to you if you are new to OpenEdge or at least to the OpenEdge database. You may want to inquire about finding a reputable consultant who can help you.
  • Not a performance-related question, but I'll ask it as you're new to the database: does the production database have after imaging enabled?
  • Sort of a performance-related question: does your database need to run on Windows Server? Have you considered a platform like Linux or AIX?
 

MaLev

New Member
Hello,

New Server, do not look too much on the side of Progress OpenEdge, configure some hardware options defaults gives disastrous performance on the pretext of saving energy.

Windows 2012 x64, Xeon E5-2620 v3 2,40Ghz, two processors, and 17Gb Ram. (8 cores for the virtual machine)
- Search server manufacturer resources for low latency applications

We currently use OpenEdge 11.1 x32, and VMware Sphere ESX 5.1.
- vmware update with the latest patches, but 5.1 should be updated to 5.5 U4 or better ESXi 6.0
As virtual network interface we use vmxnet3.
- this is a good driver but you also apply the Low latency configuration on the Host and VM. Dedicate a port of the physical card OpenEdge and configure clients with the IP address. (Use at least 2 cards for OpenEdge server, one for the general network (DNS, AD ..., 1 or more for OpenEdge)

Important, affect a NUMA node has this VM, if a CPU uses memory of another CPU, the performance degradation is immediately noticeable.

Example, server fujitsu (all architectures, physical or virtual) et vmware
http://sp.ts.fujitsu.com/dmsp/Publications/public/wp-ivy-bridge-bios-settings-primergy-ww-en.pdf
https://www.vmware.com/files/pdf/techpaper/VMW-Tuning-Latency-Sensitive-Workloads.pdf

these previous operations concerning the Bios (Power + 25% Perf) and Ethernet (Interrupt Moderation, + 100-400% perf and LSO, RSS, EEE) mainly going to be enough, after you can lean on Progress points for going for the last%

Maximum size of shared memory segment (-shmsegsize) 4096 Mb.
- 512 Mb is compatible with various server and 4096 Mb is useless with OpenEdge 32 bit
Database Blocksize (-blocksize): 4,096
- Good, but 8192 (2 * NTFS) provides slightly better (Not essential)
Maximum number of clients per server (Ma): 2.
Maximum number of servers (-Mn): 26.
Maximum number of users (n): 61
- Mn 26 * 2 !!! > Mn 31 or +
Spin 8000
Testing with 40000, it is rare to have to exceed this value

Message Buffer Size (Mm): 32600
Progress tcp default or recommended values are !!!. An analysis of frames allows to know the best core values and it is 1460, Mn should always be a multiple of 1460 (standard Ethernet IPv4)
Mn is dependent on the application, the type of customer (Client Server or Appserver) and database (OLTP or data warehouse). Analysis Frames and Performance tests are necessary.
- Mr is absent, it should be Mm -12 * Multiple Mm.
example for OLTP no AppServer -Mm 5840, -Mr 5792, or data Waherouse -Mm 23360, Mr 23168

You must configure indexrangesize, tablerangesize to have reliable statistics on the use of your base with Promon or better with ProTop.
configure the maximum number of table and index the database includes, this does not affect performance and is very helpful.

Current size of the lock table (L): 819200
although elevated value, which would require a diagnosis to check what is actually used

enabled LRU mechanism.
Number of LRU force skips (-lruskips): 0
Number of LRU2 force skips (-lru2skips): 0
LRU enabled with value zero ?

I'm sorry for my english, google translation
cordially
 

MaLev

New Member
It must be configured on the Ethernet cards workstations, Interrupt Moderation, LSO in line with that of the server
+
 
Top