Perfmon: SQL Server Memory Manager\Maximum Workspace Memory (KB)

I'm back for a little more SQL Server perfmon fun. 
Today lets look at the "Memory Manager\Maximum Workspace Memory (KB)" counter.
The SQL Server terms "workspace memory" and "query memory" are largely interchangeable - one or the other might be preferable when the context is perfmon, dmvs, or another area that tends to favor one term over another.
This is my favorite reference for high-level understanding of this memory use - its a 90+ minute video. :)
Query Tuning Mastery: Zen and the Art of Workspace Memory - Adam Machanic
http://bit.ly/1dK81Pw

Usually, the workspace memory maximum, or the limit for total outstanding memory grants, is fairly stable when SQL Server "total memory" is at the "target memory" level.  But not always - sometimes this limit can be quite volatile even when overall shared memory allotment to SQL Server is very stable.  I'm hoping to track down the resource that is shrinking the workspace memory maximum in the environments I work with - probably have to do it by using a DMV query every 5 minutes, maybe summing the memory amounts for the memory object types.

Here are two graphs - the first shows the database, stolen and free pages on a given system.
SQL server version: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2769.0 (X64)
Windows version: Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Just like the graph in the previous blog post, total memory is stable at target memory level: max server memory has been achieved and is being maintained.  But, the workspace memory maximum is volatile, and seems to have a correlation to the number of database pages in "total memory".

Why do I care?  On this system there are pending query memory grants.  When do they happen?  Well, they tend to congregate during times that the workspace memory limit is lower.  From 6 am to 11 am the pending query memory grants are a significant performance concern.  So the lower limit on query memory grants is contributing to a query concurrency issue on this system (although the lower query memory limit is far from the only culprit in this crime story).

Perfmon: SQL Server Database pages + Stolen pages + Free pages = Total pages

I stare at a lot of perfmon numbers, and I stare for a really, really long time.

I like to understand the relationship of utilization among related resources, and how variable limits are set in response to system conditions.  I don't often easily find resources which explain such things.

The max server memory attribute should be set to govern the size of SQL Server shared memory regions - the value should allow enough server RAM for Windows, SQL Server processes themselves, a small bit of filesystem cache, and other memory needs of the server*.  If that value is set to a value that can be achieved by SQL Server, it'll appear in perfmon as the value for "SQLServer:Buffer Manager\Target pages" (among other places).  As the SQL Server page allocator strives toward the target memory allotment, its progress will be noted in "SQLServer:Buffer Manager\Total pages".

There's a lot of stuff about SQL Server memory management and utilization that I haven't figured out yet... but this one's written in ink:

Database pages + Stolen pages + Free pages = Total pages

And, if a server has achieved max server memory, total pages will be equal to target pages.  If it maintains its target pages value in total pages, then you can create a nice stacked area graph like the one above to show how database pages, stolen pages, and free pages fit together within max server memory.  If total pages grows and shrinks - well maybe your max server memory setting is a bit too high :)  You can still make a stacked graph - the top edge will just be ragged and it will indicate the total pages in the struggle of trying to achieve the target pages on your server.

Now, understanding what makes up stolen pages is a bit more complicated.  So its for another day.

****

*In versions previous to SQL Server 2012,  among the "other memory needs of the server" are any allocations made by the multi-page allocator.  Be careful to consider that when setting max server memory for SQL Server 2008 R2 or before.  For SQL Server 2012 and beyond, the previous single page allocator (governed by max server memory) and multi-page allocator (scoffs at max server memory) have given way to the multi-page allocator (governed by max server memory).  Thus the memory uses previously using the multi-page allocator is now governed by max server memory and overall memory management has become a little more predictable.

Exploring Trace Flag 4199 with SQL Server 2012 Querytraceon Part 1

Trace flag 4199 is a rollup trace flag, which will enable a large number of query optimizer fixes that are otherwise individually enabled by separate trace flags.
More information on the query optimizer behavior related to trace flag 4199 here, including individual kb numbers and links for the rolled-up stuff.
http://support.microsoft.com/kb/974006

Querytraceon has now been publicly documented, and trace flag 4199 is one of the flags listed as supported with querytraceon.
http://support.microsoft.com/kb/2801413

Game on!

The SQL Server version: Microsoft SQL Server 2012 - 11.0.2332.0 (X64)
In query executions with and without trace flag 4199 below, I used 'SET STATISTICS IO ON' in order to get physical/logical reads, etc. 
I repeated the tests until I got a run with and without OPTION (QUERYTRACEON 4199) that had all data in cache and performed no physical reads.
That way the comparison was a little more straightforward - neither execution had any IO waits if neither performed any physical reads.

The test query executions returned 68 rows - this known resultset size allowed easy retrieval of information from sys.dm_exec_query_stats.

So... in SQL Server 2012 two executions of a complex query, differing only by inclusion of trace flag 4199 to for query optimizer behavior.
Without trace flag 4199, there were almost 10 times as many logical reads.  Without trace flag 4199, there was over 10 times as much accumulated worker time.
For the queries I spend most of my time with, trace flag 4199 merits consideration - if not as a global trace flag, certainly at the query level for those queries whose performance needs improvement.

I'm sure that just the io statistics and the query stats won't satisfy many as a full exploration of trace flag 4199 behavior with respect to this query.  I'll also compare the "actual plan" xml to see if I can locate where the significant difference in logical IOs occurs.  But not today.  Otherwise... how would I get you to come back to my blog, dear read?
:) 

(68 row(s) affected)
Table 'DIM_1'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_2'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_3'. Scan count 0, logical reads 136, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_4'. Scan count 1, logical reads 204, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FACT_DETAIL'. Scan count 2761, logical reads 8405, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FACT_1'. Scan count 409, logical reads 1635, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_5'. Scan count 4, logical reads 138, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_6'. Scan count 0, logical reads 1008, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FACT_2'. Scan count 0, logical reads 1671, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)


(68 row(s) affected)
Table 'DIM_1'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_2'. Scan count 0, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_3'. Scan count 0, logical reads 136, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_4'. Scan count 1, logical reads 204, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FACT_DETAIL'. Scan count 28038, logical reads 84834, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FACT_1'. Scan count 2150, logical reads 7309, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_5'. Scan count 0, logical reads 990, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DIM_6'. Scan count 0, logical reads 2378, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 28246, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FACT_2'. Scan count 1, logical reads 5852, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SELECT
   last_execution_time AS exec_time,
   last_logical_reads AS logical_rds,
   last_worker_time AS worker_time,
   last_elapsed_time AS elapsed_time
FROM
   sys.dm_exec_query_stats
WHERE
   last_rows = 68


              exec_time  logical_rds  worker_time  elapsed_time
2013-09-24 22:33:26.000        13211       138990        603339
2013-09-24 22:35:29.030       130019      1913968       4098876

I Tell Some Folks to Buy SQL Server Enterprise Edition When They Kinda Don't Wanna

Sometimes I am asked to justify the expense of Microsoft SQL Server Enterprise Edition license for organizations and systems I work with.  Here's some of the stuff I say.  I was motivated to collect these thoughts in part by the following blog post from @flashdba.

The Real Cost of Enterprise Database Software
http://flashdba.com/2013/09/10/the-real-cost-of-enterprise-database-software/

Certainly database licensing is one of the most significant cost components of a business critical database, whether the licensed product is Oracle, SQL Server, or another commercial database engine.  And Microsoft and Oracle both have various tiers of licenses that should be weighed and applied to different systems.

First, an explanation of why this list won't fit your situation - your mileage WILL vary so just take this as food for thought.  I work with a lot of organizations, each with numerous large databases across a variety of SQL RDBMS and noSQL platforms.  The context for a discussion of SQL Server Enterprise Edition vs Standard Edition may seem a little unusual - these are organizations that have already selected SQL Server over Oracle for the project.  I do my best to stay out of the conversations that lead to the Oracle/SQL Server decision, unless someone asks me questions I can answer about performance/scalability of the platforms or the applicability/value of a given feature exclusive to one platform or the other. 

My own roots go back to particular noSQL engine on UNIX servers, with nearly every type of storage you can imagine worked into the systems.  But these days I pay a lot more attention to SQL Server and Oralce RDBMS systems at the same organizations, performing workloads that are very closely tied to the noSQL systems I know and love.  The database systems I'm concerned with occupy a narrow place in the taxonomy of databases.  I share what I know, learn and argue.  But there is a wide scope of database system design, workload, scale, and performance that is outside my daily concern and interaction.  Your system is almost certainly more different from my systems than it is similar to them.  But this may give you a new perspective on some of these features, or at least a collection of resources to read and share about them. :)_

***

The following two sources provide a good overview of SQL Server features per license edition.  The contribution of selected Microsoft SQL Server Enterprise Edition features is explained below.
http://www.microsoft.com/en-us/sqlserver/editions.aspx
http://msdn.microsoft.com/en-us/library/cc645993.aspx

Server CPU and Memory scaling: While SQL Server Enterprise Edition supports the OS maximum amount of RAM and number of CPUs, SQL Server standard Edition is limited to 64 GB of RAM and the lesser of 4 sockets or 16 cores for CPU.  These systems almost always enjoy more than 64GB of RAM on the database server, whether for query performance, to lower the overall rate of physical IO on shared storage resources, or both.

Enterprise Edition includes page compression for tables and indexes, and on this system I recommend using page compression throughout the database.  On OLTP systems the CPU cost of compression may outweigh the benefits it provides.  However, the primary workload profiles on this system are very different from OLTP workloads.  The benefits of decreased logical reads for a given query plan, decreased physical reads for a given query, decreased total data network traffic, and decreased database data footprint on disk are significant on this type of system.  As the database continues to grow in size, the decreased data footprint in comparison to an uncompressed database can be a significant benefit in the total amount of storage needed for long term backup retention. (Backup compression is expected to reduce the footprint even of page compressed databases on this system.)
http://technet.microsoft.com/en-us/library/cc280449.aspx

Online index operations, including online index reorganization and rebuild are available in Enterprise Edition.  Managing index fragmentation is important the performance of this system, as well as to maintain good neighbor status on shared storage resources.  I have not seen a successful implementation of a long term index maintenance strategy on a system like this utilizing exclusively offline index operations.
http://msdn.microsoft.com/en-us/library/ms177442.aspx

Partitioning is available in Enterprise Edition.  In this system the main benefit yielded by partitioning selected tables is a reduction in total capacity needed for long term backup retention.  Index maintenance can also be made less intensive, and in the future statistics maintenance will also be made less intensive through appropriate partitioning strategies.
http://msdn.microsoft.com/en-us/library/ms190787.aspx

Enterprise DBCC checkdb can perform parallel consistency check operations.  As the database grows, without parallelism consistency time check will likely grow at least linearly in duration.
http://technet.microsoft.com/en-us/library/ms176064%28v=sql.110%29.aspx

Enterprise read-ahead is more capable than Standard.  Enterprise read-ahead may issue single read requests up to 1 mb.  Enterprise read-ahead can request up to 1024 pages ahead of query processing, while standard can only request 128 pages ahead of query processing.
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx
http://blogs.msdn.com/b/psssql/archive/2010/03/24/how-it-works-bob-dorr-s-sql-server-i-o-presentation.aspx

More information on read-ahead can be found at the following link, which also mentions the Enterprise advanced scanning feature.  Advanced Scanning allows multiple concurrent tasks to share a full table scan, reducing the total combined IO for the sharing queries/tasks.  Advanced scanning is also sometimes referred to as "merry-go-round scanning."
http://technet.microsoft.com/en-us/library/ms191475%28v=sql.105%29.aspx

AlwaysOn Availability Groups are available with SQL Server Enterprise Edition.  This allows continuous asynchronous database replication for the system.  Some organizations have implemented workload isolation with this feature, and it is an important technology to consider in the availability and disaster recovery design.

Enterprise Edition also includes the online restore feature, including online page restore.
http://technet.microsoft.com/en-us/library/ms188671.aspx

Most of these ideas can be summed up in the following: maintaining acceptable performance of a such a system without Enterprise Edition features would be difficult; maintaining assurance of recover-ability, regulatory compliance, and mitigating litigation risk with integrity check, backup and recovery operations without Enterprise features would be nearly impossible.

AIX 64k medium size memory pages; performance considerations

In general what I have observed is, when memory_affinity is enabled, 64k pages tend to make unexpected paging space write activity more prominent.  That may be due to some combination of issues such as those listed below :).

Sometimes folks will notice high CPU utilization by lrud (the page stealing least recently used daemon)  or by psmd (page size management daemon) that correlates to poor performance.  I contend that its not usually the CPU utilization and CPU shortage that is degrading performance... rather performance degradation is more likely due to paging space traffic and/or memory free frame waits.  These are closely correlated to activity of lrud and psmd.  Of course, in some cases it really is that tasks in the runqueue for the same core that is executing lrud/psmd are experiencing too much dispatch wait time as a result of that CPU being busy.  But I don't think that's normally the case.

I've done lots of work on a database engine with very high concurrent activity where we chose to routinely disable 64k pages.  In doing so, we avoided the potential paging space utilization and free frame waits associated with AIX defects such as those described in the APARs below.

Disabling 64k pages comes at a cost: TLB misses may increase and the added cost of handling all memory as 4k pages may degrade performance.  But its something to be considered in a jam.  If necessary, you can monitor the change in TLB misses in a critical workload.
http://sql-sasquatch.blogspot.com/2013/04/aix-hpmstat-utility-on-power-7.html

Here are some ways that AIX 6 defects that can cause performance impact when 64k pages are enabled.

IZ42626: PSMD OPERATIONS ARE VERY SLOW APPLIES TO AIX 6100-02
http://www-01.ibm.com/support/docview.wss?uid=isg1IZ42626

IZ92561: 64K KERNEL HEAP CAUSES PAGING WHEN RAM IS OTHERWISE AVAILABLE APPLIES TO AIX 6100-03
http://www-01.ibm.com/support/docview.wss?uid=isg1IZ92561

IZ72031: 64K PAGING TAKING PLACE WHEN AVAILABLE SYSTEM RAM EXISTS APPLIES TO AIX 6100-03
http://www-01.ibm.com/support/docview.wss?uid=isg1IZ92561

IV13560: HIGH PSMD THREADS CPU CONSUMPTION APPLIES TO AIX 6100-06
http://www-01.ibm.com/support/docview.wss?uid=isg1IV13560

IBM Power 7 Problem Case: Unexpected paging resolved by disabling memory_affinity

A system running Oracle on IBMPower AIX was seeing lots of paging space traffic, and I was pulled in.  Even when paging space writes occurred, vmstat seemed to show enough server RAM to avoid paging out.  It was worse than folks originally thought in some ways: there were paging space buffer (psbuf) shortages causing additional waits in some paging space operations.  And the paging space disk was experiencing sqfull conditions according to iostat.

This is a big system.  16 memory pools because of the default cpu_scale_memp value of 8: every 8 logical CPUs gets a memory pool.  So there are 128 logical CPUs on the system: 32 Power7 cores.

With memory_affinity enabled, even though the NB_PAGES memory pool sizes were fairly well balanced, the NUMFRB free 4k frames per pool were not very well balanced.
The kdb NB_PAGES and NUMFRB values below are in hex.

(0)> memp *
                 VMP MEMP  NB_PAGES  FRAMESETS        NUMFRB
F1000F0009740000  00  000   000C9D00  000 001 002 003 00030A12
F1000F0009740500  00  001   000CA100  004 005 006 007 0000A828
F1000F0009740A00  00  002   000C9E00  008 009 00A 00B 0000B346
F1000F0009740F00  00  003   000C9300  00C 00D 00E 00F 0001EEB8
F1000F0009741400  00  004   000C9D90  010 011 012 013 000138AB
F1000F0009741900  00  005   000CAA00  014 015 016 017 00014807
F1000F0009741E00  00  006   000CA000  018 019 01A 01B 0000346E
F1000F0009742300  00  007   000C9000  01C 01D 01E 01F 0002C69A
F1000F0009742800  01  008   000C9800  020 021 022 023 0001FA32
F1000F0009742D00  01  009   000CA000  024 025 026 027 0000118B
F1000F0009743200  01  00A   000CA000  028 029 02A 02B 00011B04
F1000F0009743700  01  00B   000C9380  02C 02D 02E 02F 000131E8
F1000F0009743C00  01  00C   000C9000  030 031 032 033 00015683
F1000F0009744100  01  00D   000C9000  034 035 036 037 00003573
F1000F0009744600  01  00E   000C9000  038 039 03A 03B 000097D1
F1000F0009744B00  01  00F   000C9000  03C 03D 03E 03F 0002C819

Advised them to disable vmo parameter memory_affinity (requires bosboot and reboot).
This was what the memory pools looked like afterward.

(0)> memp *
                 VMP MEMP  NB_PAGES  FRAMESETS        NUMFRB
F1000F0009740000  00  000   000C9E00  000 001 002 003 000B3848
F1000F0009740500  00  001   000C8700  004 005 006 007 000B2952
F1000F0009740A00  00  002   000C9E00  008 009 00A 00B 000B379D
F1000F0009740F00  00  003   000C8500  00C 00D 00E 00F 000B2895
F1000F0009741400  00  004   000C8C70  010 011 012 013 000B2F53
F1000F0009741900  00  005   000CA000  014 015 016 017 000B3568
F1000F0009741E00  00  006   000CA000  018 019 01A 01B 000B3702
F1000F0009742300  00  007   000CA000  01C 01D 01E 01F 000B34CF
F1000F0009742800  00  008   000C8000  020 021 022 023 000B2E72
F1000F0009742D00  00  009   000C8000  024 025 026 027 000B305F
F1000F0009743200  00  00A   000C9000  028 029 02A 02B 000B32BA
F1000F0009743700  00  00B   000C9000  02C 02D 02E 02F 000B322C
F1000F0009743C00  00  00C   000C9000  030 031 032 033 000B3215
F1000F0009744100  00  00D   000C9000  034 035 036 037 000B3211
F1000F0009744600  00  00E   000C9000  038 039 03A 03B 000B313E
F1000F0009744B00  00  00F   000C9000  03C 03D 03E 03F 000B33C3

The memory pool NB_PAGES size have became a little more balanced with memory_affinity disabled.  But the NUMFRB numbers have become a LOT more balanced.  Excellent.  That was exactly what I wanted.  After two weeks, I was able to confirm that there had been NO paging space traffic at all based on the "paging space page ins" and "paging space page outs" reported by vmstat -s.

The absence of any paging space traffic allowed them to increase their SGA size.  That further benefited performance - significantly, actually,  In Oracle 11GR2, the small table threshold is 10% of the database cache.  Some of the SGA memory uses are fixed size - increasing SGA size can disproportionately favor the database cache.  In this case, the database cache was increased enough to bring a whole new set of high concurrency tables underneath the small table threshold.  That discouraged Oracle from performing direct path read full table scans of these tables redundantly into PGA, and instead brought more of them into SGA database cache.

I didn't even try to get more data for the memory pools in this case.  I felt bad enough about asking their administrator to run "echo 'memp *' | kdb" for me as root before and after disabling memory_affinity :).

But I felt pretty good about seeing their performance turn around.

Disabling memory_affinity is certainly not a general recommendation to address unwanted paging space use.  By disabling memory_affinity, you sacrifice a significant amount of optimization in memory latency that can be provided by keeping the memory as close as possible to the threads that use it.  But, truth be told, that optimization becomes less valuable on a large database server, with CPU cores and physical RAM that span sockets.

Why expose memory pool stats in vmstat?

Now that I have put out the request to expose memory pool stats in vmstat* I'm digging up some system stats to support the request.

I also want to lay out the argument that trying to understand memory management behavior in AIX 6.1 and AIX 7.1 without access to memory pool (and maybe even frameset) size and free size is a futile game.

I'm primarily concerned with database servers, and I've seen unexpected paging on lots and lots of IBM Power systems for a long time.  I've seen lots and lots of APARs come out for 64k page use leading to paging, the introduction of numperm_global, and on and on.  I know that there are many administrators for IBM Power database servers running AIX that have more or less given up - from their perspective some amount of paging space use just goes along with AIX and there's no use trying to fight it.  That's too bad.

I'm always trying to eliminate paging, regardless of the OS I am concerned with at the time.

So, when I saw a new set of vmo tunables introduced in AIX 7.1, I wanted to learn as much about them as possible.

Consider the vmo tunable enhanced_affinity_private.

# vmo -h enhanced_affinity_private
Help for tunable enhanced_affinity_private:
Purpose:
Specifies percentage of process private memory allocations that are affinitized by default.
Values:
        Default: 40
        Range: 0 - 100
        Type: Dynamic
        Unit: numeric
Tuning:
This tunable limits the default amount of affinitized memory allocated for process private memory.  Affinitizing private memory may improve process performance.  However, too much affinitized memory in a vmpool can cause paging and impact overall system performance.

OK... I guess 40% is a reasonable default.  How can it be evaluated?  The only way I know of evaluating would involve memory pool monitoring.

Hmmm... in there I also see enhanced_affinity_attach_limit.  What's that?

# vmo -h enhanced_affinity_attach_limit
Help for tunable enhanced_affinity_attach_limit:
Purpose:
Specifies percentage of memory allocation requests with affinity attachments that are affinitized.
Values:
        Default: 100
        Range: 0 - 100
        Type: Dynamic
        Unit: numeric
Tuning:
This tunable limits the amount of affinitized memory allocated for memory allocations that have affinity attachments.  Affinitizing such memory allocations may improve performance.  However, too much affinitzed memory in a vmpool can cause paging and impact overall system performance.

Wait a minute!  It carries the same warning that too much affinitized memory can cause paging... but its default is 100%?  That seems like something I'll want to monitor.

But before I start digging into how to monitor the effects of enhanced_affinity_attach_limit and
enhanced_affinity_private, I run across this:

IBM Power Systems Performance Guide - Implementing and Optimizing

http://www.redbooks.ibm.com/redbooks/pdfs/sg248080.pdf

(3.9 mb pdf)

Page 281

Shared memory is not impacted by the enhanced_affinity_private parameter. This kind of memory allocation is controlled by memplace_shm_named and memplace_shm_anonymous.

I've never paid attention to memplace_shm_anonymous before.  Historically it has determined first-touch or round-robin strategy for affinitized anonymous shared memory allocations.  If I had and wanted to evaluate round-robin vs first-touch... I'd probably do it by monitoring memory pool use.  Then... what if a new value sneaks in? There it is in the AIX 6.1 documentation:

Default placement of anonymous shared memory can be set to first-touch (value of 1) or round-robin across the system (value of 2) or automatic (value of 0), where the system decides the best placement for the memory.

http://pic.dhe.ibm.com/infocenter/aix/v6r1/topic/com.ibm.aix.cmds/doc/aixcmds6/vmo.htm

Truth be told, the new setting was present in the code release before it made its way into the documentation :).

http://www-01.ibm.com/support/docview.wss?uid=isg1IZ74067 

Note that AIX 7.1 on IBM Power7 memplace_shm_anonymous defaults to value 0 - system determined memory allocation.  Well... shoot... the new value is the default?  How does the system determine what strategy it'll use for allocation?  How can I evaluate whether the new "system determined" behavior is responsible for increased paging space traffic that I notice after an upgrade to AIX 7.1?

# vmo -h memplace_shm_anonymous
Help for tunable memplace_shm_anonymous:
Purpose:
Specifies the default memory placement policy for anonymous shared memory.
Values:
        Default: 0
        Range: 0 - 2
        Type: Dynamic
        Unit:
Tuning:
Anonymous shared memory refers to working storage memory, created via shmget() or mmap(), that can be accessed only by the creating process or its descendants. This memory is not associated with a name (or key). Default placement of anonymous shared memory can be set to first-touch (value of 1) or round-robin across the system (value of 2) or auto-affinitized (value of 0), where the system decides the best placement for the memory.

I've been doing this for a while - I like to keep disruptive tuning recommendations (such as changing memory_affinity or vmm_mpsize_support values) to an absolute minimum.  I also don't want to encourage a cycle of problem detection-diagnostics-fix selection-fix apply-repeat with no known end.                               

If memory pool statistics, the fundamental stats driving lrud to steal pages, are exposed, more administrators will be able to track them.  Shoot, maybe they'll even make their way into nmon :)  At any rate, it'll shed more light on the large number of cases of unexpected page outs, and it will make possible evaluating the enhanced_affinity and memplace families of vmo tunables, as well as the behavior of ASO and DSO when used.  I don't know of another method of evaluating those tunables, ASO, or DSO.

*I'll keep saying "vmstat", but I don't really care if its in vmstat or not.  I just want memory pool size, and memory pool free list size, exposed through a tool other than kdb which doesn't require root privileges to execute.