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.