As far as the PageTables , those exist and serve as mapping memory (I think
that's the right term). Anyway, the reason yours is so large is the 4k
pages which are used by sessions and the Oracle internal procs.
So, by design, when you implement hugepages, your page table usage will go
way down.
Chris
On Wed, Mar 20, 2019, 2:49 PM Upendra nerilla <nupendra@xxxxxxxxxxx> wrote:
Thanks much everyone (Niall, Jonathan, Matt, Mladan, Rich, Chris and I'm
sure I missed someone) for providing a lot of valuable information. I have
summarized all the emails in this reply..
Niall, you hit it on the head.. I acquired this environment in its current
state. Most of the OS is in its default state and same in the database
as well.
I had been getting push back about changes with.."if it ain't broke..
don't fix it". But now finally there are issues and the opportunity to
cleanup.. 🙂
Here is how I am summarizing the plan of action in the same order of
implementation..
1. Configure huge pages
2. Reduce swappiness from 60 (current value) to 10 (I have setup
similar value on another server)
3. Disable file system buffering:
disk_asynch_io = true -> false
filesystemio_options=asynch -> all
4. It appears we are using PageTables, anything to be done here?
$ grep PageTables /proc/meminfo
PageTables: 18661352 kB
Are there any dependencies for these changes?
This is just one server.. I have several other servers to work on.. 🙂
Thanks again for all your insights and assistance.
-Upendra
------------------------------
*From:* niall.litchfield@xxxxxxxxx <niall.litchfield@xxxxxxxxx>
*Sent:* Wednesday, March 20, 2019 3:14 PM
*To:* nupendra@xxxxxxxxxxx
*Cc:* dimensional.dba@xxxxxxxxxxx; Mladen Gogala; oracle-l@xxxxxxxxxxxxx
*Subject:* Re: Server is swapping with free memory available
Hi Upendra
Please don't take this the wrong way but it looks to me as though there's
a history here. What is it that you are trying to do?
You aren't using huge pages which is a standard thing to do for at least a
decade.
You have set PGA_AGGREGATE_LIMIT to a non default value (and one I think
that implies a text pfile)
You are not using appropriate filesystem Io options.
If I were consulting with you I'd be asking for the reasons for all of the
above decisions. I'd expect to suggest you just unset non-default
parameters and use huge pages and direct Io to start with. If there are
still specific performance issues then let's drill into them.
Don't get me wrong you might have justification for the above choices, but
until we know why you are doing what you are doing advice is problematic.
On Wed, Mar 20, 2019, 18:46 Upendra nerilla <nupendra@xxxxxxxxxxx> wrote:
Hi Matt,
It's helpful to understand the data and the context in swapd column. I
also noticed that the swap file size reduced from 11G to 3GB in the last
couple of days.
We do have solarwinds configured to monitor memory, but i don't see any
spikes beyond 20-30% range. OEM shows similar numbers as well.
AWR collection frequency is 60 mins and 30 days retention.
DEV:
pga_aggrgate_target =129G
pga_aggregate_limit=64G
QA:
pga_aggrgate_target =129G
pga_aggregate_limit=64G
Thanks for sharing the query to gather the PGA usage history..
From looking at the pga usage over a period of time, I see very limited
use of PGA. both instances combined I see about 62GB of PGA use.
Note: I sorted the output on MAX PGA use.
$ tail pga_qa.log
MIN_DATE MIN_PGAMB AVG_PGAMB MAX_PGAMB
------------------- ---------- ---------- ----------
2019-03-02 20:59:00 14.88 654.74 23674.39
2019-02-25 23:59:00 1 3676.68 23963.65
2019-03-02 20:29:00 6.56 920.14 24193.17
2019-03-19 02:14:00 1.19 2157.78 24205.76
2019-03-11 20:59:00 1.56 673.72 24406.08
2019-02-20 22:44:00 5.93 958.65 24878.68
2019-03-04 22:29:00 1.87 894.47 24951.26
2019-03-16 21:44:00 5.17 780.89 26428.62
2019-03-10 01:29:00 2.82 7084.55 36019.66
$ tail pga_dev.log
MIN_DATE MIN_PGAMB AVG_PGAMB MAX_PGAMB
------------------- ---------- ---------- ----------
2019-03-19 20:57:00 3.05 1425.86 23086.64
2019-03-14 02:12:00 .81 1707.77 23127.75
2019-03-10 04:12:00 29.93 1632.92 23250.54
2019-03-04 21:27:00 4.79 1553.81 23581.92
2019-03-05 12:12:00 29.93 6632.3 23594.02
2019-02-20 22:42:00 5.68 2343.14 23619.98
2019-03-06 21:12:00 3.37 1762.66 23651.27
2019-02-27 21:12:00 1.06 1752.54 24369.76
2019-02-25 22:42:00 1.93 1910.79 24481.14
2019-03-16 21:27:00 2.38 3113.43 26190.48
-Upendra
------------------------------
*From:* dimensional.dba@xxxxxxxxxxx <dimensional.dba@xxxxxxxxxxx>
*Sent:* Wednesday, March 20, 2019 2:22 AM
*To:* nupendra@xxxxxxxxxxx; gogala.mladen@xxxxxxxxx;
oracle-l@xxxxxxxxxxxxx
*Subject:* RE: Server is swapping with free memory available
You are not in a swapping problem now where the si column is non-zero.
You are in a situation where 3GB of memory swapped to disk because of some
event. Memory is slowly swapping back in (notice as the si column
increments in your output the swapd column is decreasing.
According to you output you have 6GB of free memory and 477GB of memory in
the file system cache.
Does your SA store 5 minute or 15 minute data related to the OS
performance parameters like the different pointers for memory?
You could look at the historical graphs and determine when the original
event happened.
What is your AWR collection frequency?
15min, 30min, 1hr?
And what length of time do you keep your AWR information?
What is your pga_aggrgate_target and pga_aggregate_limit set to
A memory overwhelm of your system or an event that put the system under
memory pressure could have caused the processes to be swapped out.
There are a variety of events such as large backups running and being
written to file system disks, large amounts of archiving written to disk
instead of ASM, expdp run with large parallelism, Large parallel query with
enough threads to overwhelm the available free memory.
You can run a awr query to see your pga usage over time to see if the
spike in memory utilization was caused by internal versus external
processing.
*Matthew Parker*
*Chief Technologist*
*Dimensional DBA*
*Oracle Gold Partner*
*425-891-7934 (cell)*
*D&B *047931344
*CAGE *7J5S7
*Dimensional.dba@xxxxxxxxxxx <Dimensional.dba@xxxxxxxxxxx>*
*View Matthew Parker's profile on LinkedIn*
<http://www.linkedin.com/pub/matthew-parker/6/51b/944/>
www.dimensionaldba.com
*From:* oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> *On
Behalf Of *Upendra nerilla
*Sent:* Tuesday, March 19, 2019 8:43 PM
*To:* gogala.mladen@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx;
dimensional.dba@xxxxxxxxxxx
*Subject:* Re: Server is swapping with free memory available
For some reason it didn't make it to freelist, resending it..
-Upendra
------------------------------
*From:* Upendra nerilla <nupendra@xxxxxxxxxxx>
*Sent:* Tuesday, March 19, 2019 11:25 PM
*To:* gogala.mladen@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx;
dimensional.dba@xxxxxxxxxxx
*Subject:* Re: Server is swapping with free memory available
Hi Matt,
I see a couple of lines with non-zero values.
$ vmstat 5 200
procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu-----
r b swpd free buff cache si so bi bo in cs us sy id
wa st
18 2 2896520 61650056 297504 422056928 0 0 3249 1292 0 0 13
2 82 3 0
17 11 2896520 60938176 297580 422718112 0 0 93426 145306 54761 53940
37 5 55 3 0
7 1 2896520 60085008 297676 423562016 0 0 114398 95522 50818 53125
25 4 67 4 0
8 0 2896520 59325892 297740 424344736 0 0 111006 22760 42936 38073
21 2 76 1 0
14 0 2896520 58705060 297832 424983552 0 0 103842 53260 36364 29147
19 1 78 2 0
24 4 2896520 59547120 297880 425619168 0 0 105996 41287 39495 30955
22 2 74 2 0
*19 1 2896512 58881096 298220 426283584 6 0 100873 38490 47597
43847 24 4 70 3 0*
13 0 2896512 58131448 298328 427018048 0 0 103289 82774 49331 44816
23 3 71 3 0
10 0 2896512 57441492 298400 427761856 0 0 109158 47006 48221 46278
24 3 71 2 0
10 1 2896512 56595080 298488 428600608 0 0 127218 40490 47649 41787
26 3 68 3 0
* 8 3 2896504 55839884 298604 429366784 6 0 105850 139284 43915
46775 23 3 71 2 0*
9 3 2896504 54899176 298708 430249280 0 0 109007 162499 45853
42213 24 3 70 3 0
8 1 2896504 54204828 298980 430935968 0 0 105405 96881 41548 51483
23 3 72 3 0
7 0 2896504 55939912 299056 431646784 0 0 102390 104198 32704
33627 17 3 78 2 0
3 2 2896504 55290976 299148 432297600 0 0 96374 20283 29706 26223
14 2 83 2 0
...
$ free -g
total used free shared buffers cached
Mem: 504 452 52 160 0 412
-/+ buffers/cache: 39 465
Swap: 22 2 19
Here is the excerpt from "top":
[oinstall@mstrdevdb flatfiles]$ top
top - 22:59:24 up 31 days, 15:26, 1 user, load average: 7.28, 9.66, 11.51
Tasks: 1707 total, 6 running, 1701 sleeping, 0 stopped, 0 zombie
Cpu(s): 9.2%us, 1.0%sy, 0.0%ni, 88.7%id, 1.1%wa, 0.0%hi, 0.0%si,
0.0%st
Mem: 529186920k total, 528587760k used, 599160k free, 316824k buffers
Swap: 23633916k total, 2930128k used, 20703788k free, 477745372k cached
I don't believe we are overwhelming memory usage and start swapping..
OEM does show that the memory consumption is pretty low, however I do see
there is active paging..
I don't have root access now, I will share the output of the vmswap output
as soon as I have it.
Thanks
-Upendra
------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of dimensional.dba@xxxxxxxxxxx <dimensional.dba@xxxxxxxxxxx>
*Sent:* Tuesday, March 19, 2019 7:39 PM
*To:* gogala.mladen@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
*Subject:* RE: Server is swapping with free memory available
Is your database currently swapping as in active pages moving in and out
of swap?
vmstat 5 200
Does the si and so columns have anything besides 0?
Or do you just have swap being consumed by processes from some previous
over whelm of memory event?
What specific processes are using swap?
Need to run as root to see everything
for file in /proc/*/status ; do awk '/VmSwap|Name/{printf $2 " " $3}END{
print ""}' $file; done | awk '$3 == "kB" && $2 != 0 {print $1" "$2" "$3 }'
*Matthew Parker*
*Chief Technologist*
*Dimensional DBA*
*Oracle Gold Partner*
*425-891-7934 (cell)*
*D&B *047931344
*CAGE *7J5S7
*Dimensional.dba@xxxxxxxxxxx <Dimensional.dba@xxxxxxxxxxx>*
*View Matthew Parker's profile on LinkedIn*
<http://www.linkedin.com/pub/matthew-parker/6/51b/944/>
www.dimensionaldba.com
*From:* oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> *On
Behalf Of *Mladen Gogala
*Sent:* Tuesday, March 19, 2019 3:52 PM
*To:* oracle-l@xxxxxxxxxxxxx
*Subject:* Re: Server is swapping with free memory available
Have you tried with FILESYSYSTEMIO_OPTIONS=ALL?
On 3/19/19 4:54 PM, Upendra nerilla wrote:
Hello everyone -
We have a database server which is choosing to swap though there is plenty
of free memory.
Any pointers on what to check are much appreciated..
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
