2

We have an Oracle database of roughly 100 GB that runs on top of a Windows Server 2k3R2 VM in ESX in a 1 socket, 1 core configuration. Whenever it has to handle a major query it seems to get "pegged" for the duration of that query (50% - 75% CPU utilization) and in some cases limits functionality of applications that use it. The most typical case is when we run massive reports, the query alone will significantly slow down application performance while the query is being run, as if it is queuing the tasks up. My question is: will "upgrading" the VM to a 1 socket, 2 core configuration significantly improve this "pegging" issue?

tacos_tacos_tacos
  • 3,220
  • 16
  • 58
  • 97
  • 3
    This is easy enough to test, right? Shutdown, modify VM parameters, boot, run reports, benchmark... – ewwhite Jul 04 '12 at 07:06

4 Answers4

6

That depends. If the CPU utilization is from user, then the answer is most likely "yes". If the CPU utilization is from iowait, then you're wasting your money until you upgrade your storage system. If it's mostly from system, then you probably have a driver or hardware device that is taking up too many interrupt resources (network card, maybe?)

Hyppy
  • 15,458
  • 1
  • 37
  • 59
  • Well this is a VM so there are no real costs associated with making this a dual-core... but I see your point. How would you recommend I go about determining the source of CPU utilization as you broke down? – tacos_tacos_tacos Jun 13 '12 at 20:38
  • If you're in *nix, use `top`. – Hyppy Jun 13 '12 at 20:41
  • Unfortunately it is running on Windows Server 2k3R2 – tacos_tacos_tacos Jun 13 '12 at 20:41
  • In Windows, you're going to need to dig into PerfMon. That's an entirely separate topic though, so take a look here to start: http://www-304.ibm.com/support/docview.wss?uid=swg27002840 – Hyppy Jun 13 '12 at 20:45
  • +1 for being the only one pointing out that basically this is most likely an IO problem to start with. ESX is not known for super high IO and it would make sense that to assume the IO subsystem is not designed for database requirements. – TomTom Jul 04 '12 at 08:17
0

I am pretty sure any upgrade in CPU (count) will have performance benefits, as long as you add these to the virtual machine also. Make sure you don't over commit though. That is, say you have 4 virtual machines on a 32 GB server and you assign each virtual machine 25 GB of RAM.

However in my opinion you are limiting yourself with oracle running in a virtual environment.

I would seriously consider running oracle on a dedicated server without the virtualisation layer. Oracle is quite a beast and needs all the resources it can get. Also use a hardware raid10 with at least 8 disks, the more disks the better. That also applies to the ESXi server.

aseq
  • 4,550
  • 1
  • 22
  • 46
  • Memory is not an issue - we enforce quotas that cap memory to physical limit, and memory is never "pegged" while the CPU is being pegged - no swapping seems to be occurring at the os OR hypervisor layers – tacos_tacos_tacos Jun 13 '12 at 20:39
  • -1. Databases are notorious for being IO bound, not processor bound. – TomTom Jul 04 '12 at 08:16
-1

This greatly depends on how much parallelizable is Oracle's work, i.e. on the specific query that is being run. Generally speaking, a DBMS is a highly parallel application, so it should benefit from having more CPUs; but some workloads might not benefit from it at all.

Massimo
  • 68,714
  • 56
  • 196
  • 319
-1

Having more than one core for the database engine will help in performance. Oracle has several core processes which all have to compete on CPU time if there is too few of them. Additionally when you introduce capacity consuming user sessions (queries for example) competition gets worse. So, yes, it will help. Oracle is built to have and use a multi-CPU platform. Additionally if you run the application services in the same server, there is no question about it.

STi
  • 11
  • 1
  • -1. Databases are notorious for being IO bound, not processor bound. – TomTom Jul 04 '12 at 08:16
  • @TomTom This is correct, in general. However, in this case we are told that the DBS size is 100G, there is only one vCPU and significant raise in CPU consumption during the query. These facts indicate that CPU power may be the bottleneck. However, since disk I/O plays a major role in databases, it would also be important to analyze the behavior of the database deeper for example with [Statspack](http://www.akadia.com/services/ora_statspack_survival_guide.html). That would also tell if the CPU really is what is needed. – STi Jul 04 '12 at 09:56
  • Ah, no. We are told CPU utilization is between 50% and 75%. Do not cherry pick ONE little item, read the whole question. – TomTom Jul 04 '12 at 11:58