No self-respecting post on How to Maximise CPU Performance for the Oracle Database on Linux would be complete without also mentioning Hyper-Threading(HT). This page describes HT perfectly as a feature that “uses processor resources more efficiently, enabling multiple threads to run on each core.” And is an implementation of a feature called Simultaneous Multithreading (SMT). In more details the figure shows 2 threads being scheduled on a processor, firstly without HT and then with HT illustrating fewer processor execution units sitting idle when HT is enabled.
From this description you can see that HT enables 2 threads to be executing on the same processor core at the same time and thereby improves performance by improved efficiency. It does not increase the number of physical cores available and whether HT is enabled or disabled does not impact the Oracle licence cost which is calculated based on the number of cores.
As always the best place to start to see if you can benefit from a CPU features is http://ark.intel.com. With an
example E5-2680 we can see that HT is available.
Ensuring that HT is enabled at the BIOS once the Linux operating system started you can see double the number of processors in your processor listing. In this example system with 2 x E5-2680 there are 16 physical cores however the system now sees 32 logical processors.
[root@sandep1 ~]# cat /proc/cpuinfo | grep processor
processor : 0
processor : 1
processor : 2
processor : 3
…
processor : 28
processor : 29
processor : 30
processor : 31
You can discover some more about the configuration by using the Processor Topology Tool . Running this tool on the example system tells us that there are 2 packages, 16 processors and 32 logical processors. Also it tells us which Operating System CPU corresponds to which logical processor so core 0 and thread 0 are OS processor 0 however Core 0 and thread 1 are OS processor 16. You can see that what Linux does is number the first 16 OS processors as thread 0 on the CPUs and then thread 1 for processors 16 to 31. Scheduling threads across the processor count ensures that the maximum number of physical resources are used first before then using HT or as using the initial physical processor as thread 0 and an additional logical processor as thread 1.
[root@sandep1 oracle]# ./cpu_topology64.out
…
Software visible enumeration in the system:
Number of logical processors visible to the OS: 32
Number of processor cores visible to this process: 16
Number of physical packages visible to this process: 2
…
Hierarchical counts by levels of processor topology:
# of cores in package 0 visible to this process: 8 .
# of logical processors in Core 0 visible to this process: 2 .
…
OScpu
0 16|1 17|2 18|3 19|4 20|
5 21|6 22|7 23|
…
Core
c0_t0 c0_t1|c1_t0 c1_t1|c2_t0 c2_t1|c3_t0 c3_t1|c4_t0 c4_t1|
c5_t0 c5_t1|c6_t0 c6_t1|c7_t0 c7_t1|
Oracle is also aware of the distinction between available CPUs and physical cores as shown within an AWR report.
| Host Name |
Platform |
CPUs |
Cores |
Sockets |
Memory (GB) |
| sandep1.example.com |
Linux x86 64-bit |
32 |
16 |
2 |
|
As always to prove the benefits of any feature beyond doubt I always run tests. To do so with HT is especially straightforward – what we need to do is to put a system under test conditions, turn HT off and run the test and then turn it on and run the test. The difference between the 2 will be the impact of HT. For the test I will use HammerDB, as the documentation shows the tests are fully repeatable so we can be sure that the impact between the 2 tests are attributed to HT. Reading the introduction to transactional testing guide will also help understand the results here. I took the E5-2680 system installed it with Oracle 12c and repeated the same test unattended with the autopilot feature with HT on and off. The performance profile looking from left to right showed an increased system load by increasing the number of virtual users up to a point full system utilisation. The actual performance values are not shown and the bar graph shows the point of peak performance. The red bar shows HT off and the blue line HT on.
With HT enabled we can scale beyond the level reached by the HT off configuration and in this example with this workload on this system the performance is 1.35X or 35% higher by enabling HT. Additionally for these increased numbers of virtual users transaction response times will also be improved as we can achieve a higher level of throughput. (You can measure these response times withHammerDB).
The important factor to note is the improvement in scalability from enabling HT equivalent at full utilisation in this case to the throughput e would expect from more than additional 5 physical cores, the test gives compelling evidence that overall HT can improve your Oracle performance.
I have heard of recommendations to turn off HT which put in context of this testing does seem unusual however it is possible to see how this could happen. With this example we already know it is highly scalable and predictable and the system is well balanced configured, in other words if we add more CPU resource we can take advantage of it and therefore can take advantage of HT. However in some scenarios there can be issues with either the configuration or the application where it cannot take advantage of more CPU resource. For example there will be a single point of contention such as Oracle latch contention and adding even more sessions requesting the same latch can cause the system to slow even further and throughput to lower. Similarly insufficient or misconfigured I/O, memory or network can also suffer by adding additional CPU resource to a workload. We have discussed transactional workloads in this post however Parallel Query is a type of workload where this type of scenario comes to mind by increasing the number of parallel query threads beyond the system capability.
In these scenarios removing physical CPUs from a server for testing is not a straightforward task however turning off HT at the BIOS is. In these circumstances it is possible to see how turning off HT could be seen to solve the problem however in reality system configuration or application scalability is likely to be the cause. Putting Oracle (and SQL Server) aside with some other databases, database scalability may also be an issue, for example Redis is a single-threaded database and therefore a single instance will not benefit from HT although enabling HT could mean that you could run more instances on a single system.
In summary, Hyper-Threading is a performance and scalability feature that can be of significant benefit in an Oracle environment. This example of a scalable application shows a performance improvement of 35% by using the same number of cores more efficiently. The actual benefit you see will depend on application scalability and overall system configuration to take advantage of CPU resource when available. In an Oracle environment if you have HT available I recommend enabling this feature and if you receive a recommendation to turn it off to spend some time investigating configuration or application issues to see why you not benefiting by adding CPU resource to the system.
