RE: Server is swapping with free memory available
Date: Wed, 20 Mar 2019 12:07:35 -0700
Message-ID: <0c5e01d4df50$2dd8d5f0$898a81d0$_at_comcast.net>
The one command I gave you that needs to run as root will give you by process what portion of it is in swap, whether the process is active or not.
Matthew Parker
Chief Technologist
Dimensional DBA
Oracle Gold Partner
425-891-7934 (cell)
D&B 047931344
CAGE 7J5S7 Dimensional.dba_at_comcast.net <mailto:Dimensional.dba_at_comcast.net>
<http://www.linkedin.com/pub/matthew-parker/6/51b/944/> View Matthew Parker's profile on LinkedIn
www.dimensionaldba.com <http://www.dimensionaldba.com/>
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On
Behalf Of Upendra nerilla
Sent: Wednesday, March 20, 2019 11:45 AM
To: dimensional.dba_at_comcast.net; gogala.mladen_at_gmail.com;
oracle-l_at_freelists.org
Subject: Re: Server is swapping with free memory available
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_at_comcast.net <mailto:dimensional.dba_at_comcast.net>
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
<dimensional.dba_at_comcast.net <mailto:dimensional.dba_at_comcast.net> >
Sent: Wednesday, March 20, 2019 2:22 AM
To: nupendra_at_hotmail.com <mailto:nupendra_at_hotmail.com> ;
gogala.mladen_at_gmail.com <mailto:gogala.mladen_at_gmail.com> ;
oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
Subject: RE: Server is swapping with free memory available
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_at_comcast.net <mailto:Dimensional.dba_at_comcast.net>
<http://www.linkedin.com/pub/matthew-parker/6/51b/944/> View Matthew Parker's profile on LinkedIn
www.dimensionaldba.com <http://www.dimensionaldba.com/>
From: oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org>
<oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> > On
Behalf Of Upendra nerilla
Sent: Tuesday, March 19, 2019 8:43 PM
To: gogala.mladen_at_gmail.com <mailto:gogala.mladen_at_gmail.com> ;
oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> ;
dimensional.dba_at_comcast.net <mailto:dimensional.dba_at_comcast.net>
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_at_hotmail.com <mailto:nupendra_at_hotmail.com> >
Sent: Tuesday, March 19, 2019 11:25 PM
To: gogala.mladen_at_gmail.com <mailto:gogala.mladen_at_gmail.com> ;
oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org> ;
dimensional.dba_at_comcast.net <mailto:dimensional.dba_at_comcast.net>
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_at_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_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org>
<oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> > on
behalf of dimensional.dba_at_comcast.net <mailto:dimensional.dba_at_comcast.net>
<dimensional.dba_at_comcast.net <mailto:dimensional.dba_at_comcast.net> >
Sent: Tuesday, March 19, 2019 7:39 PM
To: gogala.mladen_at_gmail.com <mailto:gogala.mladen_at_gmail.com> ;
oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
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_at_comcast.net <mailto:Dimensional.dba_at_comcast.net>
<http://www.linkedin.com/pub/matthew-parker/6/51b/944/> View Matthew Parker's profile on LinkedIn
www.dimensionaldba.com <http://www.dimensionaldba.com/>
From: oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org>
<oracle-l-bounce_at_freelists.org <mailto:oracle-l-bounce_at_freelists.org> > On
Behalf Of Mladen Gogala
Sent: Tuesday, March 19, 2019 3:52 PM
To: oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
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 -- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 20 2019 - 20:07:35 CET