Linux and Large Pages
I've been accused before as wanting the 'quick fix' approach to performance tuning. I assure you that's not the case. But I do want to try to understand something about the effect of huge pages on performance. Sorry if this is too long, as well.
I have a standard test I can run on our search databases. It takes 208 common search terms our users have submitted in the past. It uses ctx_query to count the matches; then it opens a cursor of the results via a context query and loops through the first twenty results. Time is measured before the ctx_query is done, then it's measured at the end of the loop-through. Results are stored in a table. Then the next search term is retrieved, and so on. From the results table, I can extract the average, maximum and total time taken to count and retrieve all 208 search term results. It's not perfect, but it is at least repeatable.
So I have a new OEL5.4 64-bit box running *11gR2*, eight cores, *24GB* RAM. The Oracle instance is set to use 16GB for buffer cache, 1GB for Shared Pool, 128M for Large and Java Pools, 2GB for PGA, leaving around 4GB of physical RAM for the OS to use as it sees fit. (Note that these are static settings of the old-style init.ora parameters. I am NOT using any form of automatic memory management.) I set filesystemio_options=ASYNCH, and my test returned results of 0.26 seconds average; 29.39 seconds maximum; 55.05 seconds in total, immediately after instance startup. Those are excellent results compared with what we're used to on the production servers.
So then I wonder if I should enable huge pages (i.e., set vm.nr_hugepages=9216, where the page size is 2MB; also set memlock in the /etc/security/limits.conf file and check with ulimit -l that the new setting applies to the Oracle user). So that the huge pages are all used at startup, I set PRE_PAGE_SGA=TRUE. When I check after instance startup, 255 huge pages are free, 0 are reserved... so, the instance is using huge pages.
My test now returns results of 1.83 seconds average; 42.75 seconds maximum; 394.324 seconds in total.
(In case anyone suspects O/S paging, gnome-system-monitor shows 0Kb of Used Swap, so that's not an issue, I think.)
For no specific reason other than pure experimentation, I then increased the number of huge pages from 9216 to 10240, rebooted, measured that 1279 were free and 0 were reserved, and again performed my 208-searches test on my freshly-started-up instance: 1.97 avg, 43.48 max, 411.25 total. So, if anything, slightly worse than with the 9216 case... gnome-system-monitor still reported 0Kb of used swap, however.
So, finally, to my questions:
1. Are huge pages something that ought to be generally enabled when running Oracle on Linux?
2. Why in general terms would enabling them ever decrease a performance metric so drastically?
3. Is there anything about the way I configured my huge pages (numbers, etc) which explains why I am getting that drastic slow-down with them enabled?
4. I suppose if the answer to (1) is 'no', a supplementary would be: WHEN would you think to enable huge pages on Linux?
This is still merely a test server. There are no production issues here which need "fixing". I have the luxury of fooling with the thing for a couple of weeks, so I'm just exploring anything that might (or might not) help performance, theoretically. I am therefore just curious to know about huge pages, when they might be useful to Oracle-on-Linux and why they would seem like a really bad idea in this specific situation!
Thanks for any thoughts!
(PS: I have read Metalink 361323.1 and 361468.1, amongst others. It's because both of those state that there are performance enhancements to be had from huge pages that I'm mystified why my enabling them causes the problems I've described).