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_at_hotmail.com> 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_at_comcast.net <dimensional.dba_at_comcast.net>
> *Sent:* Wednesday, March 20, 2019 2:22 AM
> *To:* nupendra_at_hotmail.com; gogala.mladen_at_gmail.com;
> oracle-l_at_freelists.org
> *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_at_comcast.net <Dimensional.dba_at_comcast.net>*
>
> *View Matthew Parker's profile on LinkedIn*
> <http://www.linkedin.com/pub/matthew-parker/6/51b/944/>
>
> www.dimensionaldba.com
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org <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; oracle-l_at_freelists.org;
> 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>
> *Sent:* Tuesday, March 19, 2019 11:25 PM
> *To:* gogala.mladen_at_gmail.com; oracle-l_at_freelists.org;
> 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 <oracle-l-bounce_at_freelists.org> on
> behalf of dimensional.dba_at_comcast.net <dimensional.dba_at_comcast.net>
> *Sent:* Tuesday, March 19, 2019 7:39 PM
> *To:* gogala.mladen_at_gmail.com; 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 <Dimensional.dba_at_comcast.net>*
>
> *View Matthew Parker's profile on LinkedIn*
> <http://www.linkedin.com/pub/matthew-parker/6/51b/944/>
>
> www.dimensionaldba.com
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org <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
> *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-l