Business Insights: Data Analytics for Business Insights

HugePages for Oracle database in Oracle Cloud

Written by Gleb Otochkin | Jul 24, 2018 4:00:00 AM
I recently published a blog post about large pages support in AWS RDS for Oracle and someone asked me if and how it works on Oracle cloud. I didn't actually know the answer and decided to look it up. Documentation for Oracle Cloud didn't say much about HugePages or large pages support for Oracle Cloud Database Service. I only found directions for Oracle Database Exadata Cloud Service in the documentation. The most reliable way, of course, was to go and check and that's what I did. Please keep in mind that the information in this post may become obsolete since Oracle Cloud is constantly undergoing changes. Oracle has two types of Oracle Cloud platforms where one is called "Classic" and another is called "Oracle Cloud Infrastructure" or "OCI". I started my tests with the latter. In the OCI interface I created a DB system (VM based) with “VM.Standard1.1” shape which comes with 1 OCPU and 7 GB of memory. As soon as the VM was successfully deployed I started to check the system. To my surprise I found that the HugePages were not configured. On the OS level we saw 0 defined large pages in the memory:
[opc@gl ~]$ cat /proc/meminfo | grep Hu
 AnonHugePages: 0 kB
 HugePages_Total: 0
 HugePages_Free: 0
 HugePages_Rsvd: 0
 HugePages_Surp: 0
 Hugepagesize: 2048 kB
 [opc@gl ~]$ 
 
And the database instance parameter "use_large_pages" was explicitly setup to false:
SQL> show parameter large_pages
 
 NAME  TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 use_large_pages  string false
 SQL> 
 
 
The large pages were not set up out of the box with the provisioned service as I was expecting. On the Oracle Cloud, we possessed full control on OS level for our VM. What if we tried to enable the HugePages for the database? The database was not using AMM and was configured for ASMM instead which was fully aligned with using large pages for SGA.
SQL> show parameter memory_target
 
 NAME  TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 memory_target  big integer 0
 SQL> show parameter memory_max_target
 
 NAME  TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 memory_max_target  big integer 0
 SQL> show parameter sga_target
 
 NAME  TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 sga_target  big integer 1792M
 SQL> show parameter sga_max
 
 NAME  TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 sga_max_size  big integer 1792M
 SQL> 
 
 
Also I noticed that the users limits for Oracle were already configured to support up to almost 5Tb of locked memory.
[oracle@gl ~]$ ulimit -a
 core file size (blocks, -c) unlimited
 data seg size (kbytes, -d) unlimited
 scheduling priority (-e) 0
 file size (blocks, -f) unlimited
 pending signals (-i) 26559
 max locked memory (kbytes, -l) 5158329
 max memory size (kbytes, -m) unlimited
 open files (-n) 131072
 pipe size (512 bytes, -p) 8
 POSIX message queues (bytes, -q) 819200
 real-time priority (-r) 0
 stack size (kbytes, -s) 10240
 cpu time (seconds, -t) unlimited
 max user processes (-u) 131072
 virtual memory (kbytes, -v) unlimited
 file locks (-x) unlimited
 [oracle@gl ~]$ 
 
It seemed like the system was almost ready and we should just enable HugePages on OS level, switch the use_large_pages parameter to "TRUE" or "ONLY" and bounce the instance. It was exactly what I did. I changed the database instance parameter:
SQL> alter system set use_large_pages=true sid='*' scope=spfile;
 
 System altered.
 
 SQL> 
 
Stopped the instance using srvctl:
[oracle@gl ~]$ srvctl stop database -db orcl_iad1zp 
 
Modified the hugepages.
[root@gl ~]# vi /etc/sysctl.conf
 [root@gl ~]# cat /etc/sysctl.conf | grep nr_hugepages
 vm.nr_hugepages = 900
 [root@gl ~]# sysctl -p 
 …
 [root@gl ~]# cat /proc/meminfo | grep Hu
 AnonHugePages: 0 kB
 HugePages_Total: 900
 HugePages_Free: 900
 HugePages_Rsvd: 0
 HugePages_Surp: 0
 Hugepagesize: 2048 kB
 [root@gl ~]# 
 
And started the database back:
[oracle@gl ~]$ srvctl start database -db orcl_iad1zp 
 
After that I could see that the memory in large pages had been used and the instance alert log clearly stated that 897 large pages out of 900 were allocated by the database.
[oracle@gl ~]$ cat /proc/meminfo | grep Hu
 AnonHugePages: 0 kB
 HugePages_Total: 900
 HugePages_Free: 6
 HugePages_Rsvd: 3
 HugePages_Surp: 0
 Hugepagesize: 2048 kB
 [oracle@gl ~]$ 
 
 
 
 **********************************************************************
 2018-07-06T19:47:45.508519+00:00
 Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
 
 2018-07-06T19:47:45.508680+00:00
  Per process system memlock (soft) limit = UNLIMITED
 2018-07-06T19:47:45.508773+00:00
  Expected per process system memlock (soft) limit to lock
  SHARED GLOBAL AREA (SGA) into memory: 1794M
 2018-07-06T19:47:45.508907+00:00
  Available system pagesizes:
  4K, 2048K
 2018-07-06T19:47:45.509119+00:00
  Supported system pagesize(s):
 2018-07-06T19:47:45.509173+00:00
  PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s)
 2018-07-06T19:47:45.509220+00:00
  4K Configured 3 3 NONE
 2018-07-06T19:47:45.509400+00:00
  2048K 900 897 897 NONE
 2018-07-06T19:47:45.509481+00:00
 **********************************************************************
 
Everything worked out fine and I got the database with SGA running in HugePages memory. I didn't see any errors or problems while doing some small tests on the database. Everything worked correctly. I tried with a couple of different shapes for the VM and got the similar results. It appeared that on the OCI platform the HugePages were not used for database systems based on VM by default. I didn't try all types of VM but the configuration and behaviour looked the same for the other shapes only SGA and PGA parameters were growing along with new size of the instances. For example, on the "VM.Standard1.2" we were getting more memory on the box, bigger SGA but the large pages were not used anyway. The next step was to check the Oracle "Classic" cloud database service. It had different definition for the standard configurations and I started from "OC4" with 2 OCPU and 15 GB memory. The instance was created and I could see that it was using HugePages out of box. It was exactly what I expected from the Oracle Cloud. The database instance parameter "use_large_pages" was setup to "TRUE"
SQL> show parameter use_large_pages
 
 NAME  TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 use_large_pages  string TRUE
 SQL> show parameter sga_target
 
 NAME  TYPE VALUE
 ------------------------------------ ----------- ------------------------------
 sga_target  big integer 5296M
 SQL>
 
 
And the instance alert log showed clearly that 2649 large pages out of configured 2916 were used allocated.
 Starting ORACLE instance (normal) (OS id: 13119)
 2018-07-20T16:52:59.994496+00:00
 **********************************************************************
 2018-07-20T16:52:59.994556+00:00
 Dump of system resources acquired for SHARED GLOBAL AREA (SGA)
 
 2018-07-20T16:52:59.994659+00:00
  Per process system memlock (soft) limit = 128G
 2018-07-20T16:52:59.994710+00:00
  Expected per process system memlock (soft) limit to lock
  SHARED GLOBAL AREA (SGA) into memory: 5298M
 2018-07-20T16:52:59.994809+00:00
  Available system pagesizes:
  4K, 2048K
 2018-07-20T16:52:59.994901+00:00
  Supported system pagesize(s):
 2018-07-20T16:52:59.994948+00:00
  PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s)
 2018-07-20T16:52:59.994998+00:00
  4K Configured 7 7 NONE
 2018-07-20T16:52:59.995102+00:00
  2048K 2916 2649 2649 NONE
 2018-07-20T16:52:59.995150+00:00
 **********************************************************************
 
Here is a short summary. Oracle database service on OCI gives you VM and database instance without large pages configuration when the database service on "Classic" cloud platform provides you a VM and an instance with large pages setup. I was not able to find any recommendation or notes in the documentation for Oracle Cloud regarding large page usage for database service on "OCI" cloud. I was surprised that it was not configured out of the box there. From my point of view, it definitely makes sense to use large pages if a system supports it. So, if you still don't use it, you may try to test it on OCI and maybe double verify with Oracle support before going to production.