Tales From A Lazy Fat DBA

Loves all databases! – Its all about performance, troubleshooting & much more …. ¯\_(ツ)_/¯

  • Prashant Dixit is the 'FatDBA' ...
  • Follow me on Twitter

Archive for December, 2014

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

Posted in Advanced | Tagged: , | Leave a Comment »

Want to monitor your Exa Health — Answer is ‘Exachk’ …

Posted by FatDBA on December 30, 2014

Recently while working on one of the test exadata machine came across one of the very useful tool to monitoring exadata system health. Being one of the most popular topics that customers talk about is the monitoring of your exadata health.

The best tool for this is the Exachk (see MOS Doc ID 1070954.1)
This document contains the current Exachk release.

The recommendation or advices for Exachk is to:

1) Run the exachk (at a minimum) quarterly, and after any changes are made to the configuration
2) ALWAYS run the current exachk.  This script is periodically updated/improved upon so it is very important to be current
3) Keep track of any failures to ensure that you can identify any new items that appear in the report
4) A score of 80 or above is a good score for production. It is very rare to have a score that is 99+.

There are also a great whitepaper released in September 2013. This white paper can be referred and downloaded from here.

Click to access exadata-health-resource-usage-2021227.pdf

Thanks
Prashant Dixit

Posted in Advanced | Tagged: , | 2 Comments »

 
%d bloggers like this: