Testing C-State Settings and Performance with the Oracle Database on Linux

In my previous post How to Maximise CPU Performance for the Oracle Database on Linux I looked at some of the settings related to CPU performance and in this post I will look at some tests that prove out the concepts discussed in the earlier post. To do that I am going to use the test introduced here Engineered for Innovation at OOW11 - are you using Turbo Boost for your Oracle Database?

This Oracle PL/SQL routine from Julian Dyke can be run in any Oracle instance on any system and reports a result that should always be 873729.72 as well as an elapsed time.

1. SET SERVEROUTPUT ON
2. SET TIMING ON
3. DECLARE
4.   n NUMBER := 0;
5. BEGIN
6.   FOR f IN 1..10000000
7.   LOOP
8.     n := MOD (n,999999) + SQRT (f);
9.   END LOOP;
10.   DBMS_OUTPUT.PUT_LINE ('Res = '||TO_CHAR (n,'999999.99'));
11. END;
12. /

 

The aim is to observe how fast the Oracle single thread performance is by achieving a lower elapsed time as possible. For example with the 2011 post  the Intel Xeon Processor 5680 completed the PL/SQL test ran in 8.89 secs with turbo boost off (at the BIOS) and 8.21 with it enabled (again at the BIOS level). Therefore we can also use the same test to observe the impact of different C-state settings on Turbo Boost for a single threaded workload. After this we can use a HammerDB workload to observer similar data on a multi-threaded workload. But firstly we will look at the PL/SQL test along with some of the turbostat data on the E5-2680 system.

 

As a baseline I turned turbo boost off by setting the EIST BIOS setting to off.  Running the PL/SQL test showed the frequency at a maximum of 2.7GHz without turbo boost frequency.

 

pkg core CPU   %c0  GHz  TSC  %c1  %c3  %c6  %c7

…

  1    0 24 100.00 2.69 2.69 0.00 0.00 0.00 0.00  

…

 

And the completion time for the baseline test was 10.45 seconds.

Res = 873729.72

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.45

 

I then re-enabled EIST for all subsequent tests.

 

In the previous post I briefly mentioned that in an Oracle workload you would not wish to set the In-kernel governor  to powersave mode and the PL/SQL test shows why. In this instance the frequency for the core that the test is run on does not rise above 1.2Gz

 

pkg core CPU %c0    GHz  TSC  %c1  %c3  %c6  %c7

…

  1    2 10 100.00 1.20 2.69 0.00 0.00 0.00 0.00  

…

 

And the elapsed time is over 23 seconds close to 3X more than the performance seen in the 2011 post.

 

Res = 873729.72

PL/SQL procedure successfully completed.

Elapsed: 00:00:23.50

 

For subsequent tests I therefore set the In-kernel governor and energy performance bias settings to performance.

Firstly leaving the system settings at the default letting intel_idle manage the C-state settings we see that when idle the system makes full use of deeper C-states.

 

pkg core CPU  %c0  GHz  TSC    %c1 %c3    %c6   %c7

…

   0   0 0   0.11 2.42 2.69   5.39 0.31  0.00  94.20  

   0   1 1   0.10 2.72 2.69   1.64 0.84  0.00  97.41  

   0   2 2   0.02 2.93 2.69   0.03 0.00  0.00  99.95  

   0   3 3   0.00 2.41 2.69   4.24 0.00  0.00  95.76

…

 

And the PL/SQL routine consistently completes consistently in 8.01 seconds. (subsequent elapsed times are just shown once)

 

Res = 873729.72

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.01

 

Res = 873729.72

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.01

 

I then set the kernel parameter intel_idle.max_cstate=3, rebooted, started Oracle and re-ran the test which completed marginally faster.

 

Res = 873729.72

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.97

 

Looking at turbostat showed turbo boost was being used and this time the deepest C-State was C6.

 

pkg core CPU %c0   GHz  TSC   %c1 %c3  %c6    %c7

…

   0   0 0   0.12 3.44 2.69  0.50 0.01 99.37  0.00  

   0   1 1  99.94 3.45 2.69  0.06 0.00  0.00  0.00  

   0   2 2   0.08 3.38 2.69  0.15 0.01 99.76  0.00

…

 

Repeating the test with  intel_idle.max_cstate=2 produces a very similar elapsed time and the deepest C-State as expected is C3.  We can see that whilst one core is in turbo boost other cores are at deeper C-States.

 

Res = 873729.72

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.98

 

pkg core CPU %c0   GHz  TSC   %c1   %c3  %c6    %c7

…

   0   0 0   0.21 3.35 2.69  4.67  95.12 0.00   0.00  

   0   1 1  93.33 3.49 2.69  0.13   6.54 0.00   0.00  

   0   2 2   0.22 3.33 2.69  1.42  98.36 0.00   0.00  

…

 

I then moved  to  intel_idle.max_cstate=1 and now this produces an elapsed time that is almost 13% lower than previous.

 

Res = 873729.72

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.00

 

Turbostat shows that the max turbo frequency is now 3.09GHz again almost 13% lower than previous and the deepest C-State is C1.

 

pkg core CPU %c0   GHz  TSC    %c1  %c3  %c6  %c7 

…

   0   0 0   0.61 3.07 2.69  99.39 0.00 0.00 0.00  

   0   1 1   1.12 3.09 2.69  98.88 0.00 0.00 0.00

   0   2 2  92.89 3.09 2.69   7.11 0.00 0.00 0.00  

…

 

Setting intel_idle.max_cstate=0 processor.max_cstate=0 produces almost the same result as the previous test.

 

Res = 873729.72

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.00

 

pkg core CPU %c0   GHz  TSC    %c1  %c3  %c6  %c7  

…

   0   0 0   0.50 3.09 2.69  99.50 0.00 0.00 0.00  

   0   1 1 100.00 3.09 2.69   0.00 0.00 0.00 0.00

   0   2 2   0.63 3.09 2.69  99.37 0.00 0.00 0.00 

…

 

Returning back to kernel parameter intel_idle.max_cstate=3 I then ran a schema creation with HammerDB to simulate a full Oracle workload and monitored the system using turbostat. Whilst building the schema with all cores active we achieve a turbo boost frequency of 3.09GHz on all cores.

 

pkg core CPU %c0   GHz  TSC %c1  %c3  %c6  %c7 

…

   0   0 0  99.27 3.09 2.69 0.73 0.00 0.00 0.00  

   0   1 1  99.25 3.09 2.69 0.75 0.00 0.00 0.00  

   0   3 3  98.93 3.09 2.69 1.07 0.00 0.00 0.00  

   0   4 4  98.95 3.09 2.69 1.05 0.00 0.00 0.00  

   0   5 5  99.23 3.09 2.69 0.76 0.00 0.01 0.00  

   0   6 6  99.04 3.09 2.69 0.94 0.01 0.00 0.00  

   0   7 7  99.28 3.09 2.69 0.72 0.00 0.00 0.00  

   1   0 8  98.95 3.09 2.69 0.94 0.02 0.09 0.00  

 

And once the build is complete and the single threaded DBA workload of creating indexes and gathering statistics takes place this is boosted to the full max turbo frequency of the CPU.

 

pkg core CPU %c0   GHz  TSC  %c1  %c3  %c6   %c7

… 

   0   0 0  99.87 3.48 2.69  0.13 0.00 0.00  0.00  

   0   1 1   0.75 3.46 2.69 99.25 0.00 0.00  0.00  

   0   2 2   0.67 3.36 2.69  4.05 0.42 94.86 0.00  

 

In summary what we have observed correlates bears out the findings from the post How to Maximise CPU Performance for the Oracle Database on Linux.  If we disable or limit C-State settings we are very likely to impact the systems ability to make use of Turbo Boost Technology up to its full potential.  We also saw that Oracle makes use of both single and multi-threaded workloads and with the correct settings turbo boost provides the ideal adaptation to provide performance exactly where you need it. At the very least if you change high level BIOS or kernel parameter settings you should find out what the settings change and always test single and multi-threaded workloads such as the example shown here to ensure that you get the best CPU performance in your Oracle environment.