Slow database

[Snow90]

Hello,

The company which I work at is currently having problems with our progress database. We recently moved all of our physical servers to a VMware environment, including our progress database. I am not a progress expert, nor a VMware expert, I’m actually just a Help Desk person. But we are having this problem day in and day out, and users are starting to get on our case about it. Does anyone else here run their database on VMware? Or has anybody ever heard of slow performance when moving the database to a virtual environment? Our company actually has a Progress contractor that works here twice a week, and he said nothing is wrong on the datbase side, that it has to be our network (Vmware).

Any insight would be greatly appreciated!

 

[TomBascom]

It is common to run Progress databases under VMware and they can run quite well.

Some additional information would be helpful:

• What version of Progress?
• How is Progress configured? (The first 50 lines of the dbname.lg file after the most recent db start shows most of this -- the structure file (dbname.st) shows a lot of the rest.)
• What version/flavor of VMware?
• Can you describe the performance problems more specifically? Why do you think that it is a database problem rather than an application problem?
• What hardware resources do you have available and how are they allocated between the various VMs?

My initial WAGs:

• The VMs are implemented on shared RAID5 storage. This is an all too common recipe for a performance disaster.
• The VMs are over committed -- you have too many VMs on the physical server for the demand that they represent.
• The occasional contractor may have overlooked something that changed in the migration or that is behaving differently since the migration -- do you have historical data for db and application configuration and performance metrics?

 

[Snow90]

• What version of Progress? 10.1A
• How is Progress configured? (The first 50 lines of the dbname.lg file after the most recent db start shows most of this -- the structure file (dbname.st) shows a lot of the rest.) From our dbname.lg file:

[2011/10/11@06:59:33.289-0500] P-356 T-2104 I BROKER 0: (1553) The last backup was not completed successfully.
[2011/10/11@06:59:33.320-0500] P-356 T-2104 I BROKER 0: (333) Multi-user session begin.
[2011/10/11@06:59:34.570-0500] P-356 T-2104 I BROKER 0: (5644) Started for 3000 using TCP, pid 356.
[2011/10/11@06:59:34.586-0500] P-356 T-2104 I BROKER 0: (8836) Connecting to Admin Server on port 7838.
[2011/10/11@06:59:34.601-0500] P-356 T-2104 I BROKER 0: (8846) Registered with Admin Server.
[2011/10/11@06:59:34.601-0500] P-356 T-2104 I BROKER 0: (4234) Progress OpenEdge Release 10.1A build 1326 on WINNT .
[2011/10/11@06:59:34.601-0500] P-356 T-2104 I BROKER 0: (4281) Server started by SYSTEM on CON:.
[2011/10/11@06:59:34.601-0500] P-356 T-2104 I BROKER 0: (6574) Started using pid: 356.
[2011/10/11@06:59:34.601-0500] P-356 T-2104 I BROKER 0: (4235) Physical Database Name (-db): f:\openedge\db\cis\copesan.
[2011/10/11@06:59:34.601-0500] P-356 T-2104 I BROKER 0: (4236) Database Type (-dt): PROGRESS.
[2011/10/11@06:59:34.601-0500] P-356 T-2104 I BROKER 0: (4237) Force Access (-F): Not Enabled.
[2011/10/11@06:59:34.601-0500] P-356 T-2104 I BROKER 0: (4238) Direct I/O (-directio): Not Enabled.
[2011/10/11@06:59:34.601-0500] P-356 T-2104 I BROKER 0: (4239) Number of Database Buffers (-B): 100000.
[2011/10/11@06:59:34.601-0500] P-356 T-2104 I BROKER 0: (9422) Maximum private buffers per user (-Bpmax): 64.
[2011/10/11@06:59:34.601-0500] P-356 T-2104 I BROKER 0: (4240) Excess Shared Memory Size (-Mxs): 16407.
[2011/10/11@06:59:34.601-0500] P-356 T-2104 I BROKER 0: (10014) The shared memory segment is not locked in memory.
[2011/10/11@06:59:34.601-0500] P-356 T-2104 I BROKER 0: (4241) Current Size of Lock Table (-L): 300000.
[2011/10/11@06:59:34.601-0500] P-356 T-2104 I BROKER 0: (4242) Hash Table Entries (-hash): 25621.
[2011/10/11@06:59:34.601-0500] P-356 T-2104 I BROKER 0: (4243) Current Spin Lock Tries (-spin): 24000.
[2011/10/11@06:59:34.601-0500] P-356 T-2104 I BROKER 0: (6526) Number of Semaphore Sets (-semsets): 3.
[2011/10/11@06:59:34.601-0500] P-356 T-2104 I BROKER 0: (4244) Crash Recovery (-i): Enabled.
[2011/10/11@06:59:34.617-0500] P-356 T-2104 I BROKER 0: (6573) Database Blocksize (-blocksize): 4096.
[2011/10/11@06:59:34.617-0500] P-356 T-2104 I BROKER 0: (4245) Delay of Before-Image Flush (-Mf): 3.
[2011/10/11@06:59:34.617-0500] P-356 T-2104 I BROKER 0: (4247) Before-Image File I/O (-r -R): Reliable.
[2011/10/11@06:59:34.617-0500] P-356 T-2104 I BROKER 0: (4249) Before-Image Truncate Interval (-G): 0.
[2011/10/11@06:59:34.617-0500] P-356 T-2104 I BROKER 0: (4250) Before-Image Cluster Size: 524288.
[2011/10/11@06:59:34.617-0500] P-356 T-2104 I BROKER 0: (4251) Before-Image Block Size: 8192.
[2011/10/11@06:59:34.617-0500] P-356 T-2104 I BROKER 0: (4252) Number of Before-Image Buffers (-bibufs): 20.
[2011/10/11@06:59:34.617-0500] P-356 T-2104 I BROKER 0: (9238) BI File Threshold size (-bithold): 0.0 Bytes.
[2011/10/11@06:59:34.617-0500] P-356 T-2104 I BROKER 0: (6552) BI File Threshold Stall (-bistall): Disabled.
[2011/10/11@06:59:34.617-0500] P-356 T-2104 I BROKER 0: (4254) After-Image Stall (-aistall): Not Enabled.
[2011/10/11@06:59:34.617-0500] P-356 T-2104 I BROKER 0: (4256) Number of After-Image Buffers (-aibufs): 20.
[2011/10/11@06:59:34.633-0500] P-356 T-2104 I BROKER 0: (8527) Storage object cache size (-omsize): 1024
[2011/10/11@06:59:34.633-0500] P-356 T-2104 I BROKER 0: (4257) Maximum Number of Clients Per Server (-Ma): 120.
[2011/10/11@06:59:34.648-0500] P-356 T-2104 I BROKER 0: (4258) Maximum Number of Servers (-Mn): 5.
[2011/10/11@06:59:34.648-0500] P-356 T-2104 I BROKER 0: (4259) Minimum Clients Per Server (-Mi): 1.
[2011/10/11@06:59:34.648-0500] P-356 T-2104 I BROKER 0: (4260) Maximum Number of Users (-n): 81.
[2011/10/11@06:59:34.648-0500] P-356 T-2104 I BROKER 0: (4261) Host Name (-H): cpncis01.
[2011/10/11@06:59:34.648-0500] P-356 T-2104 I BROKER 0: (4262) Service Name (-S): 3000.
[2011/10/11@06:59:34.648-0500] P-356 T-2104 I BROKER 0: (4263) Network Type (-N): TCP.
[2011/10/11@06:59:34.648-0500] P-356 T-2104 I BROKER 0: (4264) Character Set (-cpinternal): ISO8859-1.
[2011/10/11@06:59:34.648-0500] P-356 T-2104 I BROKER 0: (4282) Parameter File: Not Enabled.
[2011/10/11@06:59:34.648-0500] P-356 T-2104 I BROKER 0: (5647) Maximum Servers Per Broker (-Mpb): 1.
[2011/10/11@06:59:34.648-0500] P-356 T-2104 I BROKER 0: (5648) Minimum Port for Auto Servers (-minport): 3000.
[2011/10/11@06:59:34.648-0500] P-356 T-2104 I BROKER 0: (5649) Maximum Port for Auto Servers (-maxport): 5000.
[2011/10/11@06:59:34.648-0500] P-356 T-2104 I BROKER 0: (8865) This broker supports both 4GL and SQL server groups.
[2011/10/11@06:59:34.648-0500] P-356 T-2104 I BROKER 0: (9426) Large database file access has been enabled.
[2011/10/11@06:59:34.648-0500] P-356 T-2104 I BROKER 0: (9336) Created shared memory with segment_id: 18939904
[2011/10/11@06:59:34.648-0500] P-356 T-2104 I BROKER 0: (9336) Created shared memory with segment_id: 153157632
[2011/10/11@06:59:34.648-0500] P-356 T-2104 I BROKER 0: (9336) Created shared memory with segment_id: 287375360
[2011/10/11@06:59:34.648-0500] P-356 T-2104 I BROKER 0: (9336) Created shared memory with segment_id: 421593088
[2011/10/11@06:59:34.664-0500] P-356 T-2104 I BROKER 0: (12813) Allowed index cursors (-c): 324.
[2011/10/11@06:59:34.664-0500] P-356 T-2104 I BROKER 0: (12814) Group delay (-groupdelay): 10.
[2011/10/11@06:59:34.664-0500] P-356 T-2104 I BROKER 0: (12815) Lock table hash table size (-lkhash): -15329
[2011/10/11@06:59:34.664-0500] P-356 T-2104 I BROKER 0: (12816) Maxport (-maxport): 5000
[2011/10/11@06:59:34.664-0500] P-356 T-2104 I BROKER 0: (12817) Minport (-minport): 3000
[2011/10/11@06:59:34.664-0500] P-356 T-2104 I BROKER 0: (12818) Message Buffer Size (-Mm): 1024
[2011/10/11@06:59:34.664-0500] P-356 T-2104 I BROKER 0: (12820) Maximum Servers per Broker (-Mpb): 1
[2011/10/11@06:59:34.664-0500] P-356 T-2104 I BROKER 0: (12821) Use muxlatches (-mux): 1
[2011/10/11@06:59:34.664-0500] P-356 T-2104 I BROKER 0: (12823) Semaphore Sets (-semsets): 3
[2011/10/11@06:59:34.805-0500] P-356 T-2104 I BROKER 0: (10471) Database connections have been enabled.
[2011/10/11@06:59:35.117-0500] P-1412 T-2984 I SRV 1: (5646) Started on port 3001 using TCP, pid 1412.

And this is what is in the dbname.st shows:

#
b .
#
d "Schema Area":6,32;1 .

• What version/flavor of VMware? VMware Vsphere 5.0.0
• Can you describe the performance problems more specifically? Why do you think that it is a database problem rather than an application problem? End users are saying that our application accessing the database is running very slow, even to start up the application it takes a while. It really seems like the more people that access it the slower it will get. And this is where the hard part comes in. It's hard to tell really if it's the database or VMware. We have had some issues with performance even before we switched over to VMware, but they were never as bad as they are right now. And the only big change that we did with VMware was upgrade from version 4.1 to 5.
• What hardware resources do you have available and how are they allocated between the various VMs? For our Progress database we have 3 GB of RAM (4 GB, but OS only sees 3) running Quad Core (E5540 @ 2.53 GHz). On that specific host there is a total of 32 GB of RAM, 8 CPUs (16 Logical Hyperthreaded) x 2.533 GHz, and a total of 5 VMs running off of it. The CPU utilization usually peaks at about 15% and hovers at about 10%. Memory utilization peaks at 60% and usually hovers around 40%.

Some other details that might be helpful: The SAN is a Netapps use Raid 6. The cache hit averages about 90%. The CPU utilization usually peaks at about 15% and hovers at about 10%. Memory utilization peaks at 60% and usually hovers around 40%. When the slowdown is happening the disk reads is at 100%. Usually there is about 5 to 6 vm per host. Also in this environment we have a couple of SQL database server which don’t seem to have a performance issue.
Hardware for VM 1 - 4 GB RAM, 4 CPUs
Hardware for VM 2 - 2 GB RAM, 2 CPUs
Hardware for VM 3 - 4 GB RAM, 2 CPUs
Hardware for VM 4 - 1 GB RAM, 1 CPU
Hardware for VM 5 (Database Server) - 3 GB RAM, 4 CPUs



If you need any more information, let me know!

 

[TomBascom]

• What version of Progress? 10.1A

Is there any special reason to be at 10.1A (which is about 5 or 6 years old) rather than at 10.2B?

[*]How is Progress configured? (The first 50 lines of the dbname.lg file after the most recent db start shows most of this -- the structure file (dbname.st) shows a lot of the rest.)

From our dbname.lg file:
[2011/10/11@06:59:33.289-0500] P-356 T-2104 I BROKER 0: (1553) The last backup was not completed successfully.

Yikes! Is that just a one-time aberration that has since been repaired? Or is your system really not backed up?

...
[2011/10/11@06:59:34.601-0500] P-356 T-2104 I BROKER 0: (4239) Number of Database Buffers (-B): 100000.

100,000 4k blocks = 400MB of buffer cache. That isn't much. This is a 32 bit Progress executable so, at most, -B can be 2GB. On Windows you probably end up a bit lower than that. You report that you have 3GB of RAM in this VM. I would try to set -B to 500,000 (the db may complain that it cannot allocate that much shared memory -- if so back off by 50,000 and try again until you find the limit. It might be 350,000 -- I often end up around there.)

[2011/10/11@06:59:34.617-0500] P-356 T-2104 I BROKER 0: (4250) Before-Image Cluster Size: 524288.

That is the default and it is almost certainly too small for an 50+ user system (you have -n at 80 so I am assuming that you have approx 50+ users). You are probably experiencing buffer flushes at checkpoints and your checkpoints are probably very close together during peak activity. (You can check this in PROMON. R&D, 3 "Other Displays", 3 "Checkpoints".)

Anyhow... you should increase the bi cluster size:

proutil dbname -C truncate bi -bi 8192

I don't see any APWs being started in this .lg file extract -- that could be because you didn't include quite enough lines or it might be that they are not being started.

If you are not starting APWs and a BIW at startup you should do that too.

Also, while I am nagging, if you are not running after-imaging you need to fix that too. (Your .st file does not show any ai extents so I'm guessing "no".) Backup failures plus no after-imaging is a train wreck waiting to happen.

For some more info about after-imaging: After Imaging

[2011/10/11@06:59:34.633-0500] P-356 T-2104 I BROKER 0: (4257) Maximum Number of Clients Per Server (-Ma): 120.
[2011/10/11@06:59:34.648-0500] P-356 T-2104 I BROKER 0: (4258) Maximum Number of Servers (-Mn): 5.

This is double plus ungood.

If your users are connecting client/server, and it seems like they are else why would you be starting servers? (plus, this is Windows, of course they are client/server...) Then you want to spread those connections out over a large number of servers. Not bunch them together into a chokepoint.

Personally, I do not like to see more than a few sessions per server. So if I had 50 users I would set -Mn 25 and -Ma 3. This way no more than 3 sessions would ever be sharing a server and usually only 1 or 2 would be.

I might even do -Mn 50 -Ma 2.

[2011/10/11@06:59:34.648-0500] P-356 T-2104 I BROKER 0: (8865) This broker supports both 4GL and SQL server groups.

Are you allowing SQL-92 connections? (ODBC/JDBC) If you are then you should setup dedicated 4GL and SQL login brokers rather than sharing a broker.

[2011/10/11@06:59:34.648-0500] P-356 T-2104 I BROKER 0: (9426) Large database file access has been enabled.

Good!

[2011/10/11@06:59:34.664-0500] P-356 T-2104 I BROKER 0: (12818) Message Buffer Size (-Mm): 1024

You should consider increasing this. 8192 is a good start. The downside is that you have to increase it everywhere -- all databases and all clients must use the same value. So you may need to chase down a lot of configuration files to get it changed.

On the other hand if you have a nice centralized system for managing that stuff it could be easy.

And this is what is in the dbname.st shows:

#
b .
#
d "Schema Area":6,32;1 .

Everything is in the schema area and there is only one extent (and no after-imaging). That is a version 6 db structure.

On the bright side it has just the one variable extent and you are using large files so it is easy to manage.

But it is horrible from almost any other perspective. You are on OE10. You should implement type 2 storage areas. This will greatly improve your IO efficiency and any set oriented queries (like inquiries, lookups, reports and so forth) will benefit tremendously.

Take a look at this for an overview on how to approach that: Storage Optimization Strategies

[*]What version/flavor of VMware? VMware Vsphere 5.0.0

That seems reasonable.

[*]Can you describe the performance problems more specifically? Why do you think that it is a database problem rather than an application problem?

End users are saying that our application accessing the database is running very slow, even to start up the application it takes a while. It really seems like the more people that access it the slower it will get. And this is where the hard part comes in. It's hard to tell really if it's the database or VMware. We have had some issues with performance even before we switched over to VMware, but they were never as bad as they are right now. And the only big change that we did with VMware was upgrade from version 4.1 to 5.

VMware, by itself, shouldn't be a problem. But there is some overhead involved and if the system was already having problems adding VMware certainly won't make those problems go away.

[*]What hardware resources do you have available and how are they allocated between the various VMs?

For our Progress database we have 3 GB of RAM (4 GB, but OS only sees 3) running Quad Core (E5540 @ 2.53 GHz). On that specific host there is a total of 32 GB of RAM, 8 CPUs (16 Logical Hyperthreaded) x 2.533 GHz, and a total of 5 VMs running off of it. The CPU utilization usually peaks at about 15% and hovers at about 10%. Memory utilization peaks at 60% and usually hovers around 40%.

When you talk about CPU & Memory utilization are you viewing that from within the Progress VM or at the Hypervisor?

Some other details that might be helpful: The SAN is a Netapps use Raid 6.

Big red flag.

No matter what the sales guy says Netapp "filers" are not designed for or good at supporting databases. They aren't good for any database. They may label it a "SAN" but it isn't. It is designed and optimized for file sharing. Those things are totally inappropriate for databases.

RAID6 is, essentially, RAID5 made even worse. They also try to hide behind "RAID DP". Remember, any time a storage vendor makes up a RAID acronym they are selling snake oil.

If you were trying to pick horrifically bad technology to support a database you couldn't do worse.

They are also way over-priced.

How big is this database? If it is less than 500GB go out and buy an SSD. Heck, buy 2 and mirror them. It will save you a ton of money over the RAID6 boat anchor and the performance will knock your socks off.

The cache hit averages about 90%

90% is also known as "sucky".

When the slowdown is happening the disk reads is at 100%

See my comments above about your IO subsystem.

Also in this environment we have a couple of SQL database server which don’t seem to have a performance issue.

Those databases probably have enough memory to buffer their data and avoid IO. They are also probably way better organized.

Like many sites you have probably been focused on functionality -- making customizations and building stuff for users. As you have been successful and grown the load on the system has probably increased and it is now demanding attention.

Basically your system hasn't had any significant DBA attention paid to it. It sounds like you do not actually have a DBA (which is very typical).

You can work through the stuff that I've outlined and anything else that others might offer or you can bring in some help. If you need to bring in help my contact info is below

 

[dynamicE]

Hello,


Can you tell me if you managed to solve the performance problem using VM Ware ?
We have the same problem with several of our clients and we can't get it solved.

We have done some benchmarking with a 'simple' Progress query that fetches 150.000 records.

First we run the query on our virtual server connecting the database through shared memory ---> 1 seconds
Second we run the query on our virtual server connecting the database through tcp/ip ---> 3 seconds
Third we run the query from a client connecting the database through tcp/ip ---> 41 seconds

Finally we did the same test again but we connected our client to the old physical server ---> 20 seconds

Conclusion : it looks like running the db on the virtual server causes the query to be 50% slower !

ICT says that there is no networking problem but possibly someting with the TCP/IP stack. I am a software engineer so I don't have a lot of knowledge concerning hardware and virtualization but our clients are calling for help and we don't find the solution.

Any help would be appreciated !

 

[Rob Fitzpatrick]

See the above conversation between Tom and the original poster. We need information about the configuration of your Progress installation, database, clients, server platform (hardware/software/storage) and network to be able to offer meaningful advice.

 

[dynamicE]

Here is some additional information. If you need more information, just let me know !

On the virtual server Progress 10.1C SP4 (32-bit version) is installed. The OS installed on this server is Windows 2008 64-bit.

These are the first 70 lines in our dbname.lg file lines after the last time the db was started :

2011/12/14@20:49:57.347+0100] P-5256 T-5260 I BROKER 0: (333) Multi-user session begin.
[2011/12/14@20:49:57.430+0100] P-5256 T-5260 I BROKER 0: (5326) Begin Physical Redo Phase at 1280 .
[2011/12/14@20:49:57.501+0100] P-5256 T-5260 I BROKER 0: (7161) Physical Redo Phase Completed at blk 1351 off 4663 upd 386.
[2011/12/14@20:49:57.501+0100] P-5256 T-5260 I BROKER 0: (13547) At end of Physical redo, transaction table size is 256.
[2011/12/14@20:49:57.791+0100] P-5256 T-5260 I BROKER 0: (5644) Started for 50001 using TCP IPV4 address 0.0.0.0, pid 5256.
[2011/12/14@20:49:57.792+0100] P-5256 T-5260 I BROKER 0: (8836) Connecting to Admin Server on port 7840.
[2011/12/14@20:49:57.796+0100] P-5256 T-5260 I BROKER 0: (14262) Successfully connected to AdminServer on port 7840 using TCP/IP IPV4 address 192.168.228.6.
[2011/12/14@20:49:57.799+0100] P-5256 T-5260 I BROKER 0: (8846) Registered with Admin Server.
[2011/12/14@20:49:57.799+0100] P-5256 T-5260 I BROKER 0: (4234) Progress OpenEdge Release 10.1C build 1526 SP04 on WINNT .
[2011/12/14@20:49:57.800+0100] P-5256 T-5260 I BROKER 0: (4281) Server started by SYSTEM on batch.
[2011/12/14@20:49:57.800+0100] P-5256 T-5260 I BROKER 0: (6574) Started using pid: 5256.
[2011/12/14@20:49:57.801+0100] P-5256 T-5260 I BROKER 0: (4235) Physical Database Name (-db): C:\Db100\OV\test_ov.
[2011/12/14@20:49:57.802+0100] P-5256 T-5260 I BROKER 0: (4236) Database Type (-dt): PROGRESS.
[2011/12/14@20:49:57.802+0100] P-5256 T-5260 I BROKER 0: (4237) Force Access (-F): Not Enabled.
[2011/12/14@20:49:57.803+0100] P-5256 T-5260 I BROKER 0: (4238) Direct I/O (-directio): Not Enabled.
[2011/12/14@20:49:57.803+0100] P-5256 T-5260 I BROKER 0: (-----) LRU mechanism enabled.
[2011/12/14@20:49:57.803+0100] P-5256 T-5260 I BROKER 0: (4239) Number of Database Buffers (-B): 3000.
[2011/12/14@20:49:57.804+0100] P-5256 T-5260 I BROKER 0: (9422) Maximum private buffers per user (-Bpmax): 64.
[2011/12/14@20:49:57.804+0100] P-5256 T-5260 I BROKER 0: (4240) Excess Shared Memory Size (-Mxs): 34.
[2011/12/14@20:49:57.805+0100] P-5256 T-5260 I BROKER 0: (10014) The shared memory segment is not locked in memory.
[2011/12/14@20:49:57.805+0100] P-5256 T-5260 I BROKER 0: (4241) Current Size of Lock Table (-L): 8192.
[2011/12/14@20:49:57.806+0100] P-5256 T-5260 I BROKER 0: (13953) Maximum Area Number (-maxArea): 32000.
[2011/12/14@20:49:57.806+0100] P-5256 T-5260 I BROKER 0: (4242) Hash Table Entries (-hash): 887.
[2011/12/14@20:49:57.807+0100] P-5256 T-5260 I BROKER 0: (4243) Current Spin Lock Tries (-spin): 24000.
[2011/12/14@20:49:57.807+0100] P-5256 T-5260 I BROKER 0: (6526) Number of Semaphore Sets (-semsets): 3.
[2011/12/14@20:49:57.808+0100] P-5256 T-5260 I BROKER 0: (13924) Maximum Shared Memory Segment Size (-shmsegsize) 128 Mb.
[2011/12/14@20:49:57.808+0100] P-5256 T-5260 I BROKER 0: (4244) Crash Recovery (-i): Enabled.
[2011/12/14@20:49:57.808+0100] P-5256 T-5260 I BROKER 0: (6573) Database Blocksize (-blocksize): 4096.
[2011/12/14@20:49:57.809+0100] P-5256 T-5260 I BROKER 0: (4245) Delay of Before-Image Flush (-Mf): 3.
[2011/12/14@20:49:57.809+0100] P-5256 T-5260 I BROKER 0: (4247) Before-Image File I/O (-r -R): Reliable.
[2011/12/14@20:49:57.810+0100] P-5256 T-5260 I BROKER 0: (4249) Before-Image Truncate Interval (-G): 0.
[2011/12/14@20:49:57.810+0100] P-5256 T-5260 I BROKER 0: (4250) Before-Image Cluster Size: 524288.
[2011/12/14@20:49:57.811+0100] P-5256 T-5260 I BROKER 0: (4251) Before-Image Block Size: 8192.
[2011/12/14@20:49:57.811+0100] P-5256 T-5260 I BROKER 0: (4252) Number of Before-Image Buffers (-bibufs): 20.
[2011/12/14@20:49:57.812+0100] P-5256 T-5260 I BROKER 0: (-----) Record free chain search depth factor 5 (-recspacesearchdepth)
[2011/12/14@20:49:57.812+0100] P-5256 T-5260 I BROKER 0: (9238) BI File Threshold size (-bithold): 0.0 Bytes.
[2011/12/14@20:49:57.813+0100] P-5256 T-5260 I BROKER 0: (6552) BI File Threshold Stall (-bistall): Disabled.
[2011/12/14@20:49:57.813+0100] P-5256 T-5260 I BROKER 0: (4254) After-Image Stall (-aistall): Not Enabled.
[2011/12/14@20:49:57.813+0100] P-5256 T-5260 I BROKER 0: (4255) After-Image Block Size: 8192.
[2011/12/14@20:49:57.814+0100] P-5256 T-5260 I BROKER 0: (4256) Number of After-Image Buffers (-aibufs): 20.
[2011/12/14@20:49:57.815+0100] P-5256 T-5260 I BROKER 0: (8527) Storage object cache size (-omsize): 1024
[2011/12/14@20:49:57.815+0100] P-5256 T-5260 I BROKER 0: (4257) Maximum Number of Clients Per Server (-Ma): 10.
[2011/12/14@20:49:57.816+0100] P-5256 T-5260 I BROKER 0: (4258) Maximum Number of Servers (-Mn): 12.
[2011/12/14@20:49:57.816+0100] P-5256 T-5260 I BROKER 0: (4259) Minimum Clients Per Server (-Mi): 4.
[2011/12/14@20:49:57.817+0100] P-5256 T-5260 I BROKER 0: (4260) Maximum Number of Users (-n): 51.
[2011/12/14@20:49:57.817+0100] P-5256 T-5260 I BROKER 0: (4261) Host Name (-H): HOSTDB.
[2011/12/14@20:49:57.818+0100] P-5256 T-5260 I BROKER 0: (4262) Service Name (-S): 50001.
[2011/12/14@20:49:57.818+0100] P-5256 T-5260 I BROKER 0: (14268) TCP/IP Version (-ipver) : IPV4
[2011/12/14@20:49:57.819+0100] P-5256 T-5260 I BROKER 0: (4263) Network Type (-N): TCP.
[2011/12/14@20:49:57.819+0100] P-5256 T-5260 I BROKER 0: (4264) Character Set (-cpinternal): ISO8859-1.
[2011/12/14@20:49:57.819+0100] P-5256 T-5260 I BROKER 0: (4282) Parameter File: Not Enabled.
[2011/12/14@20:49:57.820+0100] P-5256 T-5260 I BROKER 0: (5647) Maximum Servers Per Broker (-Mpb): 5.
[2011/12/14@20:49:57.820+0100] P-5256 T-5260 I BROKER 0: (5648) Minimum Port for Auto Servers (-minport): 3000.
[2011/12/14@20:49:57.821+0100] P-5256 T-5260 I BROKER 0: (5649) Maximum Port for Auto Servers (-maxport): 5000.
[2011/12/14@20:49:57.821+0100] P-5256 T-5260 I BROKER 0: (8863) This broker supports 4GL server groups only.
[2011/12/14@20:49:57.822+0100] P-5256 T-5260 I BROKER 0: (9336) Created shared memory with segment_id: 40042496
[2011/12/14@20:49:57.822+0100] P-5256 T-5260 I BROKER 0: (12813) Allowed index cursors (-c): 204.
[2011/12/14@20:49:57.823+0100] P-5256 T-5260 I BROKER 0: (12814) Group delay (-groupdelay): 10.
[2011/12/14@20:49:57.823+0100] P-5256 T-5260 I BROKER 0: (12815) Lock table hash table size (-lkhash): 1237
[2011/12/14@20:49:57.823+0100] P-5256 T-5260 I BROKER 0: (12816) Maxport (-maxport): 5000
[2011/12/14@20:49:57.824+0100] P-5256 T-5260 I BROKER 0: (12817) Minport (-minport): 3000
[2011/12/14@20:49:57.824+0100] P-5256 T-5260 I BROKER 0: (12818) Message Buffer Size (-Mm): 1024
[2011/12/14@20:49:57.825+0100] P-5256 T-5260 I BROKER 0: (12820) Maximum Servers per Broker (-Mpb): 5
[2011/12/14@20:49:57.825+0100] P-5256 T-5260 I BROKER 0: (12821) Use muxlatches (-mux): 1
[2011/12/14@20:49:57.826+0100] P-5256 T-5260 I BROKER 0: (12823) Semaphore Sets (-semsets): 3
[2011/12/14@20:49:57.826+0100] P-5256 T-5260 I BROKER 0: (13870) Database Service Manager - IPC Queue Size (-pica) : 64.0 KBytes.
[2011/12/14@20:49:57.827+0100] P-5256 T-5260 I BROKER 0: (13896) TXE Commit lock skip limit (-TXESkipLimit): 10000.
[2011/12/14@20:49:57.828+0100] P-5256 T-5260 I BROKER 0: (10471) Database connections have been enabled.


This is what's inside the dbname.st file :

#
b C:\Db100\OV\test_ov.b1
#
d "Schema Area":6,64;1 C:\Db100\OV\test_ov.d1 f 2096640
d "Schema Area":6,64;1 C:\Db100\OV\test_ov.d2 f 1024000
d "Schema Area":6,64;1 C:\Db100\OV\test_ov.d3 f 1024000
d "Schema Area":6,64;1 C:\Db100\OV\test_ov.d4

The version of VMware we are using is ESXi-5.0.0 (Vsphere 5.0.0).

If you read my earlier post, you will see that there is no problem in the db itself. If I query the db on the virtual server, I have my result set within 2 seconds.

Here is some additional info about the hardware :

The host server is a Proliant ML350 with 8 CPU’s @ 2,399 Ghz (2 sockets with 4 cores, hyperthreading possible which gives 16 logical cores)
Memory: 24GB RAM

Resources are divided between 2 virtual servers and 1 virtual pc, only the database/application server is in production
The original intend was 8 logical cores for Database server, 4 for a Remote User server and 2 for the workstation.
Memory: Database server has 8 GB RAM, Remote user server has 8GB and the workstation has 4GB.

The current configuration has changed significantly to try and find the problem. The Remote user server and workstation are being disregarded.
Current Situation (temporary):
Database server has 4 cores with Hyperthreading disabled and 16GB RAM but there was no change in performance.
CPU cores runs on average at 5% and on average about 4GB of RAM is in use.

We tested the performance from 1 single client. There are no other users using the virtual server at this time.

Thanx in advance !



​

 

[TomBascom]

It seems apparent that your networking configuration within the VM is causing problems.

Although I do see that you have pretty much all of the problems with your db config that I identified for the original poster as well. So you have plenty of opportunities to make things better.

 

[dynamicE]

I will check the database configuration and take your advice into account.
The problem with the network configuration is something for our ICT departement. One thing : they don't know where to look anymore, so do you have any hints.

Thanx for all the help !

 

[TomBascom]

Come up with some tests that do not include a Progress component.

Files transfers, pings and so forth. Test them the same way -- run them on bare metal and then with the VM. Compare. Are the results the same sort of difference as what you see with Progress?