Thursday, May 16, 2013

Oracle on AIX Best Practices: AIXTHREAD_SCOPE=S

When researching best practices, a recommendation may turn up for which details are absent. There may be vague descriptions, but important details such as versions of introduction, compatibility, and specifics of behavior (in order to model interaction with other components) may be missing.

One such "historyless" best practice for Oracle on IBM Power AIX is setting environment variable AIXTHREAD_SCOPE=S in the Oracle user profile, or /etc/environment.

There are descriptions of the CPU scheduling benefits of the 1:1 kernel to user thread model, and very vague descriptions of a lower per-process memory footprint for Oracle with system contention scope instead of process contention scope. It was the mention of lower memory footprint that drew me to this item; I was observing an AIX 6.1 Oracle 11gR2 system that was paging even though vmstat reported free memory (I'm fairly certain that on this particular LPAR, memory_affinity must be disabled to eliminate paging, but that's a topic for another day). I looked at /etc/environment, and at the Oracle user profile. Environment variable AIXTHREAD_SCOPE wasn't set. Anything I could do to reduce the computational page footprint without shrinking the SGA (and thus the database cache) would help this system. Had I found something that would lower the computational footprint just enough to eliminate the page outs (which honestly shouldn't have been happening anyway)? In a word... no.

What's the deal? The setting is mentioned as recently as March 2013 in the "IBM POWER7 AIX and Oracle Database performance considerations" from the IBM Oracle International Competency Center (although without any reference to the scheduling benefit or the memory footprint benefit).
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP102171

Regardless, I'm glad I didn't recommend incorporating this environment variable value on the system to change memory utilization. I don't think it would have. A thorough review of the documentation (which took hours between rounding up and reading), revealed the source and details below. This becomes an excellent example for the value of including version level interaction with a particular setting. This system was on AIX 6.1 - which already has a 1:1 thread model! The M:N thread model was introduced in AIX 4.3.1, and it seems the IBM Oracle recommendation AIXTHREAD_SCOPE=S likely originated from testing of the 1:1 model against M:N on AIX 4.3.2. It seems that AIX 5 versions and AIX 7.1 have M:N as default, while versions before AIX 5 and AIX 6.1 had 1:1 thread model as default. (In some cases, the M:N model may be favorable. At the bottom of this post is a documentation link for the threading model environment variables. AIXTHREAD_MNRATIO has a default value of 8:1)

So, if I had promised a change in memory utilization on the AIX 6.1 system in question... I would have been trying to sell them a benefit they had already realized through the default value. I can't afford to expend political capital lobbying for disruptive changes on production systems that have no net effect.

I will, however, ask them to incorporate setting this value in the Oracle user .profile at some convenient point in the near future. Once we've tuned the system well on AIX 6.1, I do not want system behavior to unexpectedly change with an upgrade to AIX 7.1, where the default thread model appears to again be M:N. Best practices should encourage predictable behavior, otherwise they are just practices :)

****
http://pic.dhe.ibm.com/infocenter/aix/v7r1/topic/com.ibm.aix.prftungd/doc/prftungd/thread_tuning.htm

"The 1:1 model indicates that each user thread will have exactly one kernel thread mapped to it. This is the default model on AIX® 4.1, AIX 4.2, AIX 4.3, and AIX 6.1."

"The M:N model was implemented in AIX 4.3.1 and is also now the default model."

"Depending on the type of application, the administrator can choose to use a different thread model. Tests on AIX 4.3.2 have shown that certain applications can perform much better with the 1:1 model. The default thread model was changed back to 1:1 from M:N in AIX 6.1, but for AIX 4.3.1 to AIX 7.1, by simply setting the environment variable AIXTHREAD_SCOPE=S for that process, we can set the thread model to 1:1 and then compare the performance to its previous performance when the thread model was M:N."

Individual thread variables, including AIXTHREAD_SCOPE and AIXTHREAD_MNRATIO are documented in a very near location.
http://pic.dhe.ibm.com/infocenter/aix/v7r1/topic/com.ibm.aix.prftungd/doc/prftungd/thread_env_vars.htm

No comments:

Post a Comment