VMware Cloud Community
billmathews2013
Contributor
Contributor

Database performance problem

We're running vcenter 5.1.0b with an Oracle 11g database.

We've had various performance problems such as slow search results, slow vcenter service startup, etc.  So I started to look at the database.  It turns out it has  854 tables!  Lots of VPX_HIST_STATX_XX tables.  And some of the various tables I checked like vpx_event_arg and they have over 10 million entries.

Looking at Administration | Database Retention Policy, I have 120 days of tasks and 61 days of events stored.  I suppose I could decrease that, but still something is obviously wrong.

Are there some performance things I could ask our DBAs to do manually?  For example some tables I could truncate or drop?

Reply
0 Kudos
4 Replies
RParker
Immortal
Immortal

billmathews2013 wrote:

We're running vcenter 5.1.0b with an Oracle 11g database.

We've had various performance problems such as slow search results, slow vcenter service startup, etc.  So I started to look at the database.  It turns out it has  854 tables!  Lots of VPX_HIST_STATX_XX tables.  And some of the various tables I checked like vpx_event_arg and they have over 10 million entries.

Looking at Administration | Database Retention Policy, I have 120 days of tasks and 61 days of events stored.  I suppose I could decrease that, but still something is obviously wrong.

Are there some performance things I could ask our DBAs to do manually?  For example some tables I could truncate or drop?

1) the number of tables is irrelevant.  You can have a million tables, if the DB server is idle, it doesn't matter.  A windows registry has like 10,000 tables.. so that's not a good measure.  A table is just a place to store data, it only matters how quickly the server can respond and send requests.

2) DISK is the ONLY stat you should be concerned with when it comes to DB performance.  My guess is you have SATA drives, and your vCenter includes the DB (which is a no no) in the same VM instance.

I could be wrong, but that is my theory anyway.

Why don't you post your environment, and we can go from there.  vCenter , server, ESX host, and disk statistics.

Reply
0 Kudos
billmathews2013
Contributor
Contributor

Thanks for the reply.

vCenter is a VM, with 8GB of RAM and 4 vCPUs assigned.  It's vmdk lives on our fibrechannel SAN. Performance stats on that VM show no problems.

Our hosts are Dell PowerEdge R720s, two 8 core Intel(R) Xeon(R) CPU E5-2650 0 @ 2.00GHz, 128GB RAM, disk is fibre channel SAN

Our database server is a Sun/Oracle 890, 64GB RAM, disk via fiber channel. Runs Oracle 11g

Our SAN is an EMC (I don't know the model).  But looking at performance stats we're only doing about 2k total IOs/sec, and average latency is around 5 milliseconds.

Reply
0 Kudos
RParker
Immortal
Immortal

billmathews2013 wrote:

vCenter is a VM, with 8GB of RAM and 4 vCPUs assigned.  It's vmdk lives on our fibrechannel SAN. Performance stats on that VM show no problems.

Our hosts are Dell PowerEdge R720s, two 8 core Intel(R) Xeon(R) CPU E5-2650 0 @ 2.00GHz, 128GB RAM, disk is fibre channel SAN

Our database server is a Sun/Oracle 890, 64GB RAM, disk via fiber channel. Runs Oracle 11g

OK, so we can rule out disk performance, that only leaves Oracle performance.  WE can be here all day trying to diagnose what could be wrong.  Best thing for you to do is look up oracle performance tweak.

How important is the performance?  Is it really bad or just lower than what you think it should be?

There are numerous things that can be done, Oracle has it's own memory management, separate from the OS.  Is Oracle running in Windows or Linux?  You will have to do some reading, it's easy to find.  Once you get a few hits you will be able to narrow the problem down.  Windows is slower Oracle performance vs Linux but it's easier to tune.  Linux is much better, but if you don't know Linux it can be a real pain to figure out what parameters then restart Oracle.  They can both be done via the web for Oracle, but some parameters may require modification of certain files...

I don't remember the exact parameters or proper memory, but Orace is a custom DB for tailored for memory.  If you have 8GB of RAM, you have to change Oracle to use 8GB of memory it doesn't autoset (yeah you pay a million dollars for Oracle and it can't even tune itself??!)  Oracle is nuts, they WANT you to pay a Oracle DB to setup Oracle, that's how it works.  Short of that, you have to start googling.

Unless someone else responds to this post and maybe can give you a head start.. it WILL require a restart of Oracle (more than 1) and LOTS of tweaking I can assure you.

Reply
0 Kudos
TomHowarth
Leadership
Leadership

I am moving this post to the Oracle virtuatualization section,   you may also find some tweeks there.

Tom Howarth VCP / VCAP / vExpert
VMware Communities User Moderator
Blog: http://www.planetvm.net
Contributing author on VMware vSphere and Virtual Infrastructure Security: Securing ESX and the Virtual Environment
Contributing author on VCP VMware Certified Professional on VSphere 4 Study Guide: Exam VCP-410
Reply
0 Kudos