And the answer is…

It depends.

Well, of course it depends on something.  And I know, you’re all saying — that’s a cop out, consultant answer.  A prelude to proposing an expensive “discovery” engagement.  Just tell me the answer, darn it! :-)

To me, the answer is less interesting than in how you tell me what the answer might depend upon.  Here are some of my ideas:

  1. Does the workload remain constant? (or, more importantly, does the arrival rate of work remain the same?)
  2. Where is the workload coming from?
  3. How is the workload “packaged” or “translated” into database work requests?

I can reasonable see answers between 30% and 60%, and can suppose of scenarios where it goes in any direction, even up to 100%.  Here are 2 scenarios:

  1. The workload is from a public web site in which database work requests are “OLTP” like and demand is roughly constant
  2. The workload is from a set of worker programs which are polling the database to see if they have any new work to do

In case #1, I think we’d expect the CPU utilization to decline, as the database can turn around requests more quickly and take some time between requests to “take a break”.

In case #2, the CPU utilization might remain the same, as the work programs can now poll even faster!

The thought exercise for me was how would I phrase my set of questions if confronted with such a demand from my manager, Operations colleague or customer?  I’m not sure I’ve got a set of good questions yet, but I like to think about how I would ask and explain them…

3 Responses to “And the answer is…”

  1. Andy Rivenes Says:

    Hi Dominic, I brought this up to a couple of friends at coffee this morning. Once we got past the “it depends” part of it we came up with the following observations. If you plug your original question into a M/M/n queueing model (wonder where we could get one of those?) the answer is 30%. This assumes, as you state, that the arrival rate doesn’t change. It also assumes that there isn’t competition for some other resource that 1) imposes an artificial gate on CPU utilization; and 2) that won’t become a bottleneck if requests are serviced faster by the new CPUs. I think the key thing to remember is that one needs to be careful about upgrading any resource such that it doesn’t cause unintended competition for another resource and therefore adversely affect performance.

    So our questions centered around the workload and the current “system” utilization. So you have questions about workload and others have asked about various utilizations of other parts of the “system”.
    So, can we characterize the workload and the system utilization? I think that really sums up the “it depends”.

  2. ddelmoli Says:

    “Can we characterize the workload and system utilization?”

    Yes, we can. In our case, the workload is driven by processes which “poll” queues for work to do. We found out that the processes just started to poll FASTER after the CPU upgrade — keeping cpu utilization relatively constant….

    I think I’m trying to come up with a set of questions which expose the fact that the workload changed as a result of lower service time. Obviously something which loops against the service time would change it’s arrival rate.

  3. Naresh Says:

    “The workload is from a set of worker programs which are polling the database to see if they have any new work to do ”

    Slightly off topic -
    We had a situation where a polling program polled continuosly – what’s more, there were 31 threads doing this, and the CPU was at 60%. statspack showed 99.7% LIO for the polling qyery and plenty latch contention!

    We told the application developers that there should be a 1 second pause for each thread of there was no data found. AFter fixing this, the LIO due to it was still 40% of total, but CPU went down to 10%.

    It took us 4 months to convince the app guys to make a one-line code change – but that’s another story!

Leave a Reply

Posting code can be a pain. To make sure your code doesn't get eaten, you may want to pre-format it first by using HTML Encoder