1

I have a database server with many poorly written queries that causes the sql server to spike then drop constantly ( a massive start from scratch is happening). I need to know if the cpu allocation on the vm to expand as needed is best practice for a case like this. I am wondering if the esxi platform cant expand as fast as the spikes happen.

I am curious what is best practice for vm cpu allocation on sql server (with horribly written queries)

Improve this question
3
  • 4
    Your best practice in this situation is to not have horrible queries that murder your CPU for no reason. Fix the root cause of the problem wherever possible, instead of just managing the symptoms.
    – HopelessN00b
    Jun 9, 2014 at 14:46
  • Oh no I completely agree and a rewrite from the ground up is happening but the main question is can vmware expand the resources fast enough to handle a spike from a horrible query.
    – user1552172
    Jun 9, 2014 at 14:48
  • Ah, well you can't have balloon CPU like you can have balloon RAM. Your VM has the CPU it has, and that's that.
    – HopelessN00b
    Jun 9, 2014 at 14:55

3 Answers 3

Reset to default
3

Observe, adjust and try to right-size your virtual machines.

That's really all you can do.

VMware does not have a notion of autoscale in terms of adding additional CPU resources dynamically... however, the VM will use what you give it. There's no "expansion of resources" here.

e.g. If you allocate 2 virtual CPUs of 2.8GHz, the VM's normal CPU demand will be anywhere between 50MHz and 5600MHz. Watch the system in real-time, see how it reacts and go from there.

Improve this answer
2

CPU management is quite real time. Actually CPU time is divided into 20ms quantums (iirc). So whenever there is enough idle CPU things are going to be relatively fine.

In the case there was a contention for CPU between the DB server and other VMs someone is going to win and someone is going to lose.

You can play with several factors: minimum, maximum and shares. If you don't want the DB to disturb other machins you can give it low CPU shares, or give it a maximum CPU time. If you do want the DB to have high priority you can give it a minimum reserved CPU time, or high shares.

Another advice is to not give VM too many vCPU, as it may be counter productive. In the worst cases even having idle physical CPUs the system may not be able to schedule the VMs. You may notice this because of high "co-stop" values in performance charts.

Also, if you have DRS license you can give the DB VM some minimum CPU reservation so the system tries to pace that machine on an ESXi that has enough spare CPU.

Improve this answer
-1

Consider implementing something like vCenter Operations Manager to better understand these peaks and troughs, it's and included part of select bundles. That said if you were to over-allocate CPU up-front there's little real downside.

Improve this answer
3
  • Untrue. Overallocating CPU up front can kill your CPU ready % and introduce latency. I've improved performance on even just moderately loaded boxes by removing vCPUs from VMs...
    – Brian Knoblauch
    Jun 9, 2014 at 20:15
  • Certainly that was a problem with version 4 and earlier - and has become something of a myth because of that - but it was almost completely addressed in 5.5 - it was only ever an issue in over-subscribed hosts anyway.
    – Chopper3
    Jun 10, 2014 at 6:01
  • I have experience with 5.5 on hosts that I don't consider over-subscribed (total vCPU allocated is more than total physical CPU, but utilization % is very low) and it still holds true.
    – Brian Knoblauch
    Jun 10, 2014 at 12:46

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .