Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of Cassandra, MySQL and PostgreSQL …

  • Likes

    • 129,480
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Large Pages & use_large_pages parameter in Oracle — BMC Patrol Alerts global parameter ”MEMUsedMemPerc” triggered on ”MEMORY.MEMORY”

Posted by FatDBA on December 31, 2014

All of sudden one morning we started constant alerts from one of our production system used for SAP and other  BI/BO applications which reads about Memory Used Percentage threshold breached. Being one of the very rarely used and least bust production system out of all, this was never expected from the server. I have previously written about the benefits and usage of large pages in Linux.

Summary: prashantdb053 global parameter ”MEMUsedMemPerc” triggered on ”MEMORY.MEMORY”.  95 <= 95.03 <= 100
Notes: prashantdb053 global parameter ”MEMUsedMemPerc” triggered on ”MEMORY.MEMORY”.  95 <= 95.03 <= 100 Object=MEMORY Object Class=MEMORY Parameter=MEMUsedMemPerc
BMC Impact Manager Initiated Incident

The database I have running doesn’t use large pages, as shown in the alert.log:
****************** Large Pages Information *****************
Total Shared Global Region in Large Pages = 0 KB (0%)
Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB) (alloc incr 16 MB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB

RECOMMENDATION:
Total Shared Global Region size is 2514 MB. For optimal performance,
prior to the next instance restart increase the number
of unused Large Pages by atleast 1257 2048 KB Large Pages (2514 MB)
system wide to get 100% of the Shared
Global Region allocated with Large pages
***********************************************************

Load statistics on the server at the time when we got the error message.

top – 07:26:24 up 30 days, 34 min,  3 users,  load average: 0.07, 0.05, 0.01
Tasks: 183 total,   1 running, 182 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.1%us,  0.1%sy,  0.0%ni, 99.8%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   3913512k total,  3886112k used,    27400k free,   149432k buffers
Swap:  4947960k total,   284392k used,  4663568k free,  3036064k cached

Memory Statistics on the server.

[oracle@prashantdb053 /backup/dixit/scripts]# free -m
total       used       free     shared    buffers     cached
Mem:          3821       3795         26          0        146       2965
-/+ buffers/cache:        684       3137
Swap:         4831        277       4554

[oracle@prashantdb053 /backup/dixit/scripts]# cat /proc/meminfo
MemTotal:      3913512 kB
MemFree:         26504 kB
Buffers:        149548 kB
Cached:        3036664 kB
SwapCached:       4620 kB
Active:        2250272 kB
Inactive:      1377312 kB
HighTotal:           0 kB
HighFree:            0 kB
LowTotal:      3913512 kB
LowFree:         26504 kB
SwapTotal:     4947960 kB
SwapFree:      4663568 kB
Dirty:             132 kB
Writeback:           0 kB
AnonPages:      437440 kB
Mapped:        1668000 kB
Slab:            86592 kB
PageTables:     143008 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
CommitLimit:   6904716 kB
Committed_AS:  4125228 kB
VmallocTotal: 34359738367 kB
VmallocUsed:    265480 kB
VmallocChunk: 34359472519 kB
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB

Below stats shows that Large Page are not utlized or used.
[oracle@prashantdb053 /backup/dixit/scripts]# grep Huge /proc/meminfo
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB

[oracle@prashantdb053 /backup/dixit/scripts]# vmstat -s
3913512  total memory
3884496  used memory
2248032  active memory
1376892  inactive memory
29016  free memory
149560  buffer memory
3036676  swap cache
4947960  total swap
284392  used swap
4663568  free swap
4399893 non-nice user cpu ticks
123135 nice user cpu ticks
1173193 system cpu ticks
497863320 idle cpu ticks
9827196 IO-wait cpu ticks
156731 IRQ cpu ticks
337956 softirq cpu ticks
0 stolen cpu ticks
14560315 pages paged in
286499680 pages paged out
1086328 pages swapped in
1030486 pages swapped out
2808993403 interrupts
2839798366 CPU context switches
1417413111 boot time
333424 forks

As you can see the parameter is static and requires an instance restart, so this is what I did next. Here is an interesting side effect of setting the parameter to “auto”: it doesn’t have an effect if you didn’t prepare the system for use of large pages in /etc/security/limits.conf.

07:33:48 SQL> select value,isdefault from V$PARAMETER_VALID_VALUES where name = ‘use_large_pages’;

VALUE                ISDEFAULT
——————– —————
TRUE                 TRUE
AUTO                 FALSE
ONLY                 FALSE
FALSE                FALSE

07:44:05 SQL> show parameter use_large_pages

NAME                                 TYPE        VALUE
———————————— ———– ——————————
use_large_pages                      string      TRUE

So let’s change that, but dynamically and not manually.
07:57:32 SQL> alter system set use_large_pages=auto scope=spfile;
System altered.

HugePagesTotal is still 0, which means system is still not using Large Pages, To allow oracle to lock memory you need to grant it the privilege. I had to edit /etc/security/limits.conf and set the memlock parameters.

[oracle@prashantdb053 /backup/dixit/scripts]# grep Huge /proc/meminfo
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB

Being a static parameter we need to bounce the database to make changes persistent.
07:58:00 SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

07:58:18 SQL> startup
ORACLE instance started.

07:58:54 SQL> show parameter use_large_pages

NAME                                 TYPE        VALUE
———————————— ———– ——————————
use_large_pages                      string      AUTO

Entries from alert log which shows that it has been changed to ‘AUTO’ values and started using large pages.

****************** Large Pages Information *****************
Parameter use_large_pages = AUTO
Total Shared Global Region in Large Pages = 2048 KB (0%)
Large Pages used by this instance: 1 (2048 KB)
Large Pages unused system wide = 3 (6144 KB) (alloc incr 16 MB)
Large Pages configured system wide = 4 (8192 KB)
Large Page size = 2048 KB
Time taken to allocate Large Pages = 0.033721 sec

RECOMMENDATION:
Total Shared Global Region size is 2514 MB. For optimal performance,
prior to the next instance restart increase the number
of unused Large Pages by atleast 1253 2048 KB Large Pages (2506 MB)
system wide to get 100% of the Shared
Global Region allocated with Large pages
***********************************************************

Finally after the change of parameter and hard coding limits system finally started using large pages and alerts coming from the Patrol Agents.

[oracle@prashantdb053 /opt/oracle/diag/rdbms/prashantdb/prashantdb/trace]# grep Huge /proc/meminfo
HugePages_Total:     4
HugePages_Free:      3
HugePages_Rsvd:      0
Hugepagesize:     2048 kB

Thanks
Prashant Dixit

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

 
%d bloggers like this: