Thursday, April 18, 2013

Database NUMA support - AIX Oracle, OEL Oracle, and SQL Server

By default, Oracle 11g database NUMA support is disabled, whether running on x86 Linux or IBM Power AIX,  See MetaLink Note 759565.1. Very few people know how or why to enable it with parameter enable_NUMA_support.(11gR2) or _enable NUMA_optimization (11gr1).  The Oracle OEL RDM disables NUMA support at the Linux OS level.

"How I Simplified Oracle Database 11g Installation on Oracle Linux 6"
On Oracle Linux, I discovered that there is a remarkably easy way to address these installation prerequisites: First install an RPM package called oracle-rdbms-server-11gR2-preinstall. This RPM performs a number of preconfiguration steps, including the following:
Setting numa=off in the kernel for x86_64 machines.

Interesting.  Compare the Oracle 11gR2 NUMA support situation to the SQL Server status quo.  On SQL Server, database NUMA support is enabled by default.  Few people know how or why to disable it with trace flag 8015 (which should be accompanied by trace flag 8048 to avoid escalating spinlock contention among simultaneous threads allocating query memory).. 

Kevin Closson gives a pretty good rundown of Oracle NUMA support, as well as NUMA history and a summary of the current state.

And this is great material for understanding the context of Oracle on IBM Power AIX:
IBM Power Systems Technical University - Oracle DB and AIX Best Practices for Performance & tuning (2.5 mb pdf)

Page 49:
"On AIX, the NUMA support code has been ported, default is off in Oracle 11g."

Page 59 shows that with round-robin connection distribution among NUMA nodes against an in-memory database, they experienced a 12% gain in performance (with NUMA support enabled) over baseline.  With affinitised connections per NUMA node, they experienced a 44% gain in performance over baseline.
But wait! Page 63 indicates they saw performance degradation with In-Memory Parallel Execution and NUMA support enabled - up to 5x degradation!   I'd expect similar inconsistency with a disk IO-heavy ETL and a heavy report batch workload with in memory parallel execution..

Hmmm... Oracle's been at NUMA since 1998.  SQL Server included most (if not all) of the current NUMA support in SQL Server 2005.  Oracle's got database level NUMA support off by default, SQL Server has it on by default... am I telegraphing where I am going with this yet?

**UPDATE 10 August 2013**
Finally got around to posting some graphs.  Take a look :)


1 comment: