Blogs

Linux Hugepages and AUTO_ONLY in Oracle 19c

Most Oracle DBAs (Database Administrators) are sufficiently educated about the benefits of using large memory pages for Oracle database SGA (System Global Area) to reduce overhead and improve performance. If you want to read more about it you can start from that Oracle blog or read it from other multiple articles and blogs. Oracle is using parameter use_large_pages to direct the behaviour of an Oracle instance during startup.

In the previous versions before Oracle 19c we had three possible values – “TRUE”, “FALSE and “ONLY”. Since Oracle 11.2.0.3 the “TRUE” meant that the instance will allocate as many HugePages is free available in the system and get the rest from the normal small pages. The “FALSE” would tell it to not use the HugePages at all and the “ONLY” would be able to start an instance only if a sufficient number of free HugePages is available in the system to fit all SGA in it. The “TRUE” is the default for all databases. 

In the 19c version, we got one more value – “AUTO_ONLY” and now it is the default value for Exadata systems running Oracle Database 19c. The description in documentation is not totally clear and sounds very similar to the description of “ONLY” value. Here is an excerpt from the documentation:

“It specifies that, during startup, the instance will calculate and request the number of large pages it requires. If the operating system can fulfill this request, then the instance will start successfully. If the operating system cannot fulfill this request, then the instance will fail to start.”

Let me show you how it works. Here is my sandbox with a 19c database and no HugePages is configured on the box by default. 

[oracle@dbtest19 ~]$ cat /proc/meminfo | grep Huge
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:               0 kB

If I set up the use_large_pages parameter to “AUTO_ONLY” and restart the instance.

srcdb> sho parameter large

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
large_pool_size 		     big integer 0
use_large_pages 		     string	 TRUE
srcdb> alter system set use_large_pages='AUTO_ONLY' sid='*' scope=spfile;

System altered.

ORACLE instance shut down.
srcdb> startup
ORACLE instance started.

Total System Global Area       4865390936 bytes
Fixed Size			  9144664 bytes
Variable Size		       1828716544 bytes
Database Buffers	       3019898880 bytes
Redo Buffers			  7630848 bytes
Database mounted.
Database opened.
srcdb>

Then we can see that the HugePages were automatically configured on the system to fit the SGA.

[oracle@dbtest19 ~]$ cat /proc/meminfo | grep Huge
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:    2322
HugePages_Free:        3
HugePages_Rsvd:        3
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:         4755456 kB

The database instance is able to change kernel configuration and allocate space in memory with 2mb pages by itself, without our intervention. And the number is exactly what is required to fit the SGA.

In the alert log, we see that only 2mb pages were allocated for the instance.

 instance MAX SHARED GLOBAL AREA (SGA) into memory: 4644M
2021-06-04T16:34:58.386125+00:00
 Available system pagesizes:
  4K, 2048K
2021-06-04T16:34:58.386213+00:00
 Supported system pagesize(s):
2021-06-04T16:34:58.386254+00:00
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
2021-06-04T16:34:58.386345+00:00
     2048K             2322            2322            2322        NONE
2021-06-04T16:34:58.386389+00:00
 Reason for not supporting certain system pagesizes:
2021-06-04T16:34:58.386430+00:00
  4K - Large pagesizes only
2021-06-04T16:34:58.386472+00:00

What would happen if we shut down the database?

srcdb> shutdown IMMEDIATE
DATABASE closed.
DATABASE dismounted.
ORACLE instance shut down.
srcdb>

The database is down but we still have the HugePages allocated from the memory. And it seems like they will be there until reboot or somebody explicitly tries to change it.

[oracle@dbtest19 ~]$ sysctl vm.nr_hugepages
vm.nr_hugepages = 2322
[oracle@dbtest19 ~]$

If we start another database with the use_large_pages parameter, for example, set up to “TRUE” then it will allocate HugePages for its SGA from the already configured.

tstdb> startup
ORACLE instance started.
 
Total System Global Area       4294963960 bytes
Fixed SIZE			  9143032 bytes
Variable SIZE			838860800 bytes
DATABASE Buffers	       3439329280 bytes
Redo Buffers			  7630848 bytes
DATABASE mounted.
DATABASE opened.
tstdb>

And you can see it has allocated 2046 pages.

[oracle@dbtest19 ~]$ cat /proc/meminfo | grep Hu
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:    2322
HugePages_Free:      276
HugePages_Rsvd:        3
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:         4755456 kB

If we start our previous instance with use_large_pages=AUTO_ONLY it will count the rest of the available HugePages and add new ones to have 2322 pages to fit its SGA.

Here is a quick summary of the feature:

• The database will configure and allocate new HugePages if it is possible on the system.
• The number of the new HugePages depends on SGA max size and available free HugePagesalready configured on the system.
• The number of allocated HugePages is not going down when the instance is stopped.
• The new parameter works not only on Exadata but on any non-engineered systems too.
• The default value for the use_large_pages parameter on Exadata system on 19c databases is AUTO_ONLY when it is TRUE for any other systems.
• It is feasible to use it on a very fluid system when you don’t know in advance how many databases is going to be placed there. I would use it on the Dev, Test or sandbox environments. You don’t need to calculate HugePages in advance, modify the kernel settings and apply them. All will be done automatically. But you have to watch your memory.
• It is probably not so suitable to use the AUTO_ONLY on a system where you want full control on memory allocation. I easily can imagine situation when a simple error in sga_max_size can lead to overallocation of the HugePages and memory starvation on the system. 

More from this Author