Memory Management
If you’ve just installed a machine with 16Gb of RAM and want to install an Oracle instance, how much memory should you allocate to the SGA, the PGA target, and the operating system.
There’s an old ‘rule of thumb’ that I see from time to time that says something like: 50% to the SGA, 30% to the PGA, and 20% to the O/S.
That seems to me to be a naive (though possibly fairly safe) suggestion for many OLTP systems. Why do you need so much memory for the operating system - are you failing to make best use of your memory if you set aside so much.
Take the time to read, and listen to, this presentation from Christo Kutrovsky from Pythian and you’ll find out what that 20% is needed for - and how you can avoid “wasting” it if you configure your system properly.
Take note, especially, of the sections on “Page Tables” and “Huge Pages”. (Many of the examples reference Linux, but the principles do apply to other flavours of Unix).
I went through some sizing “exercises” (of a large production Oracle 9.2 OLTP database) under AIX 5L this year and if I felt inclined to formulate ROTs, that would be mine for AIX 5L:
Use Concurrent I/O for the datafiles (>= DirectIO), set lru_file_repage to 0 (to protect computational pages), put SGA in 16MB-pages (to both reduce the page tables’s size and pin SGA into physical memory), and leave PGA in non-pinned memory to allow unused but allocated PGA memory to get paged out. Keep an eye on the memory pinned by the OS - it often turns out to be way more than 20%. Measure and monitor.
But I don’t like rules of thumb
Flado
Comment by Flado — December 20, 2007 @ 4:01 pm UTC Dec 20,2007
OS memory is also used for the network devices. If you have many concurrent connections, the TCP queues may account for a significant portion of OS memory usage.
Comment by prodlife — December 20, 2007 @ 6:04 pm UTC Dec 20,2007