Databases and power management, not a perfect fit

Publish date: 2024-06-28
In our last article, I showed that the current power management does not seem to work well with the Windows Scheduler. We got tons of interesting suggestions and superb feedback. Also several excellent academic papers from two universities in Germany which confirm our findings and offer a lot of new insights. More about that later.The thing that is really haunting me once again is that our follow up article is long overdue. And it is urgent, because some people feel that the benchmark we used undermines all our findings. We disagree as we chose the Fritz benchmark not because it was realworld, but because it let us control the amount of CPU load and threads so easily. But the fact remains of course that the benchmark is hardly relevant for any server. Pleading guilty as charged.

 

So how about SQL Server 2008  Enterprise x64 on Windows 2008 x64? That should interest a lot more IT professionals.We used our "Nieuws.be" SQL Server test, you can read about our testing methods here. That is the great thing about the blog, you do not have to spend pages on benchmark configuration details :-). Hardware configuration details: a single Opteron 2435 2.6 GHz running in the server we described here. This test  is as real life as it gets: we test with 25, 50, 100 and so on users which fire off queries with an average rate of one per second. Our vApus stresstest makes sure that all those queries are not sent at the same time but within a certain time delta, just like real users. So this is much better than putting the CPU under 100% load and measuring maximum throughput. Remember in our first article, we stated that the real challenge of a server was to offer a certain number of users a decent responsetime, and this preferably at the lowest cost. And the lowest cost includes the lowest power consumption of course.  

While I keep some of the data for the article, I like to draw your attention to a few very particular findings when comparing the "balanced" and "performance" power plan of Windows 2008. Remember the balanced performance plan is the one that should be the best one: in theory it adapts the frequency and voltage of your CPU to the demanded performance with only a small performance hit. And when we looked at the throughput or queries per second figures, this was absolutely accurate. But throughtput is just throughput. Response time is the one we care about.

Let us take a look at the graph below. The response time and power usage of the server when set to performance (maximum clock all the time) is equal to one. The balanced power and response time are thus relative to the numbers we saw in performance.  Response time is represented by the columns and the first Y-axis (on the left), Power consumption is represented by the line and by the second Y-axis (on your right).

 The interesting  thing is that reducing the frequency and voltage never delivers more than 10% of power savings. One reason is that we are testing with only six-core CPU. The power savings would be obviously better when you look at a dual or even quad CPU system. Still, as the number of core per CPU increases, systems with less CPUs become more popular. If you have been paying attention to what AMD and Intel are planning in the next month(s), you'll notice that they are adapting to that trend. You'll see even more evidence next month.

What is really remarkable is that our SQL Server 2008 server took twice as much time to respond when the CPU is using DVFS (Dynamic Voltage Frequency Scaling) than when not. It clearly shows that in many cases, heavy queries were scheduled on cores which were running at a low frequency (0.8 - 1.4 GHz). 

 

I am not completely sure whether or not CPU load measurements are completely accurate when you use DVFS (Powernow!), but the CPU load numbers tell the same story.

   

The CPU load on the "balanced" server is clearly much higher. Only when the CPU load was approaching 90%, was the "balanced" server capable of delivering the same kind of performance as when running in "performance" mode. But then of course the power savings are insignificant. So while power management makes no difference for the number of users you can serve, the response time they experience might be quite different. Considering that most servers run at CPU loads much lower than 90%, that is an interesting thing to note.

ncG1vNJzZmivp6x7orrAp5utnZOde6S7zGiqoaenZIB3fJE%3D