Tuesday, April 9, 2013

Oracle Database on AIX; memory_affinity

What is memory affinity?  Memory affinity is a performance strategy to optimize memory latency based on locality of threads and the physical memory they use.

Does it help?  For some workloads, its awesome.

Optimizing IBM DB2 pureScale transaction throughput in virtualized IBM Power Systems
http://www.ibm.com/developerworks/aix/library/au-aix-optimize-ibm-db2/au-aix-optimize-ibm-db2-pdf.pdf
This document shows the benefit of optimized memory latency for in-memory database transaction throughout.  Also detailed is the attention needed to allocate LPAR resources for optimized memory latency.

How hard is it to get the benefits?
It can be tough.  As I mentioned above, attention must be given to resource allocation for LPARs.
In fact, consider the somewhat counter-intuitive recommendations on page 40 of the following document.
When's the last time you heard someone - other than me :) - warn against too many CPUs or too much RAM for the workload?  But its right there...

Oracle Architecture and Tuning on AIX v2.30
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP100883

=====
~~~~~
Don’t over-allocate CPUs
- If a given workload (LPAR) requires <= 16 processors (single CEC), don’t allocate more than 16 processors (2 or more CECs)
- If all the LPARs in a given shared pool require (in aggregate) <= 32 processors (2 CECs), don’t allocate more than 32 processors (3 or more CECs) to the shared pool
- For Shared Processor LPARs, don’t overallocate vCPUs relative to Entitled Capacity
Don’t over-allocate memory
- May cause processors/memory to be allocated on additional CECs because there wasn’t sufficient free memory available on the optimal CEC
~~~~~
=====

OK.  So it's not easy to get the benefits of memory affinity.  Does it always help?  Nope.
Here's the general considerations for a developer.
AIX 7.1 information > Performance management and tuning > Performance management > Memory performance > AIX memory affinity support > Performance impact of local MCM memory allocation
http://pic.dhe.ibm.com/infocenter/aix/v7r1/index.jsp?topic=%2Fcom.ibm.aix.prftungd%2Fdoc%2Fprftungd%2Fperf_impact_mcm_mem_alloc.htm

=====
~~~~~
Applications with threads that individually allocate and reference unique data areas may see improved performance. Applications that share memory among all the threads are more likely to get a degradation from memory affinity.
~~~~~
=====

That is echoed by the following IBM Data Warehouse documentation.
'Infrastructure Solutions: Design, Manage, and Optimize a 60 TB SAP NetWeaver Business Intelligence Data Warehouse'
http://www.redbooks.ibm.com/redbooks/pdfs/sg247385.pdf

=====
~~~~~
Memory affinity only benefits an application if the application’s threads can stay on the same processor that the memory was allocated from. Since most of the memory accessed in a database environment is shared, there is no point in using memory affinity. Also, there is even less need for memory affinity in shared processor environment.'
~~~~~
Often, when there is memory on the freelist but the system is still paging out, a likely cause is that one or more memory pools have fewer memory frames than the other pools. This causes the freelist of these smaller pools to become depleted more quickly. If LRU cannot find enough pages to steal in those pools, then paging could occur.
An easy solution to fix this problem is to let the system create evenly balanced memory pools. The reason why they are not balanced is because when memory affinity is enabled, at least one pool is created from each MCM or DCM that has contributed a processor to that partition. Since the order of boots by different partitions can affect which processor they get, the number of memory pools may differ over subsequent reboots. Also, since the amount of memory left to allocate to the LPAR from that MCM or DCM may be small, one or more memory pools may end up with a lot less memory than the other pools.
~~~~~
=====

And finally, there is a small reference in this IBM/Oracle document, which I keep forgetting about.
Oracle Performance Monitoring on IBM Power Systems
http://www.ibm.com/developerworks/wikis/download/attachments/104533522/Oracle+Performance+Monitoring+on+IBM+Power+Systems.pdf

From page 44:
=====
~~~~~
Memory Affinity
- Not generally a benefit unless processes are bound to a particular processor
- It can exacerbate any page replacement algorithm issues (e.g. system paging or excessive lrud scanning activity) if memory pool sizes are unbalanced
- If there are paging or lrud related issues, try basic vmo parameter or Oracle SGA/PGA tuning first
- If issues remain, use 'kdb' to check if memory pool sizes are unbalanced:
- If the pool sizes are not balanced, consider disabling Memory Affinity:
  # vmo –r –o memory_affinity=0 (requires a reboot)
~~~~~
=====

An imbalance in pool sizes makes it more likely that paging will result from a pool running low on free memory (each pool has its own lrud daemon for page stealing and minfree/maxfree apply at the pool level).

So if your pools are imbalanced, and there is a low likelihood of balancing the pools based on LPAR profile, consider disabling memory_affinity.

If your pools are imbalanced, and there is paging in the face of free memory - especially of Oracle SGA contents - please resolve the paging in a manner that doesn't require shrinking the PGA to an unreasonably small size (which will increase pressure on the Oracle disks).

Memory management should be predictable, and a primary intent or Oracle system design should be to keep database cache in physical memory.

Oracle Architecture and Tuning on AIX v2.30
http://www-03.ibm.com/support/techdocs/atsmastr.nsf/WebIndex/WP100883
Page 28
2.2.3. AIX file system cache size
=====
~~~~~
With Oracle database workloads, we want to make sure that the computational pages used for Oracle executable code, the Oracle SGA and Oracle PGA, etc. always stay resident in memory.
~~~~~
=====

No comments:

Post a Comment