IBM DB2 performance problems on ESX 3.5

[norbsch]

Hi!

Our IBM DB2 szerver is running in virtual environment and I have performance problems with it.

The parameters of the virtual environment:

- Virtual host: HP ProLiant BL685c G6 (4 AMD Opteron 6 core CPU, 128GB RAM)

- VMware version: ESX 3.5 U4

- Virtual machine: 4vCPU, 4GB RAM

- OS: Windows Server 2003 SP2 Standard 32bit

- Database: IBM DB2 v9.1 Enterprise Server Edition Fix Pack3 (9.1.300.257)

Our experience was that some complex database query takes 3 times longer to run than on physical server.

We checked all the BIOS and VMware hardware recommendations and the environment seems to correspond to them.

What we tried:

- VMware tools is up-to-date

- All Windows Update were installed

- Installing DB2 9.1 ESE FP8

- Increasing virtual memory and CPU number

An interesting result:

If we set the vCPU number of the machine, the query run time is changing by the following.

vCPU query run time processor usage

4 about 14 minutes 30%

2 about 10 minutes 50%

1 about 8 minutes 100%

The query takes 5 minutes on physical server.

The DB2 application is multi-threaded.

We found a lot of sites where we read that IBM DB2 is supported on VMware ESX 3.5 environment. Furthermore the new generation of AMD Opteron CPUs (with NUMA and AMD-RVI features) are optimized for database applications.

For example:

IBM: http://www.ibm.com/developerworks/wikis/display/im/DB2VirtualizationSupport

VMware: http://www.vmware.com/pdf/db2_scalability_wp_vi3.pdf

AMD: http://developer.amd.com/documentation/articles/pages/832006138.aspx

Has anyone any ideas or experiences in these case?

Thx,

Norbert

[Yosias]

hi there

we had a similar issue with another of our server running sql server and coldfusion. Did you create this server from scratch or did you do a p2v? We have encountered some issues when migrating physical servers such as sql server and domain controllers to virtual environment. Now we bypass all of that and create those servers from scratch and restore things from backups to get what we want. So my main question is: Did you create this server from scratch or did you do a p2v?

Hope this helps.

[marcelo_soares]

Great explanation. The numbers also show that less CPU, less time - this means that the VM maybe is residing on an ESX Server shared with other VMs (you have there 24 cores, so I think maybe >20 VMs are running on it), so the CPU scheduler have some work to do. Just for understanding: if you have 4vCPUs, when the Guest needs 1 of them (or all) ESX needs to schedule all 4 at the same time, what is a bit complicated with more VMs. I think you can place vCPUs number in 2, and try to move the VM to a less used ESX (obviously, if is the case and if you have one)

Marcelo Soares

VMWare Certified Professional 310/410

Technical Support Engineer

Globant Argentina

Consider awarding points for "helpful" and/or "correct" answers.

[norbsch]

Yosias,

Thanks, I read some articles about that and that's why I made a newly deployed test server with the same database. I got the posted results from that environment.

An other thing... the test server was created from a template. I think it can't be a problem, cause the template has a multiprocessor HAL.

Norbert

[norbsch]

Marcelo,

I have three host with the same parameter. I moved all the other virtual machines to second and third hosts and only the DB2 machine were running on first. Unfortunately the results are the same.

Any other idea?

Thanks,

Norbert

[marcelo_soares]

No more clues... try checking at the resources tab if everything (cpu/ram) is set to "unlimited" (make sure the options are checked).

Marcelo Soares

VMWare Certified Professional 310/410

Technical Support Engineer

Globant Argentina

Consider awarding points for "helpful" and/or "correct" answers.

[Jackobli]

Any other idea?

I didn't find the part about your disk subsystem…

What kind of RAID Level on what kind of RAID controller? BBWC?

[norbsch]

ESX servers are connected to SAN through 4GB fiber connection. SAN disk are in RAID 5 configuration. We tried to run the virtual machine on the ESX server local disk too which is in RAID 0. Same result...

[vmuser219]

No ideas but same kind of experiences...

I have same kind of a problem with our ESX 3.5 Update 4 environment and SQL2005 vm. Database query that takes about 7 minutes in physical host (mid level desktop) takes about 25-30 minutes in vm.

I have tried to use different storages: ESX local + 2 different SAN´s with RAID 0/1/5, different vcpu´s 1/2/4 and different vm memory sizes. Also tried run this vm on ESX where is no other vm´s running. Vm is not P2V´d. Anything i have tried does not make any significantly performance boost.

[Yosias]

well you know how it is, anything can be the problem anywhere in the chain. any part of the chain is a suspect until proven innocent, server itself, nic, switch etc etc.

1. First I would test your stuff on a purely physical environment. install IBM DB2 on a physical server and see what happens, how is performance

2. In ESXi 3.5 what else is installed on that host?

3. Try installing it not using a template

4. Is there another guest that is running just fine using that template?

5. Maybe there is a setting in that template IBM DB2 does not like.

Etc

I feel your pain!

[Jackobli]

Most action was already done.

There are several kind of known behaviour for ESX(i):

• more vCPUS will not scale, if there is a loaded host (I think Norbert has tested again with no other load on the host)

• Memory is a key factor. Norbert wrote, he increased it. Perhaps the numbers would be interesting (32Bit OS! PAE?)

• There could be limitations based on ressource pool settings? Have they checked.

• I don't know if there could be more tuning options in advanced settings of ESXi

• I would also try ESXi 4.0.1, this machine should be supported.

I am unsure if the query is running locally on the db2-guest itself? If not (running from a client somewhere in the network), the network card performance of the host/guest could also be a player in that game.