Hi, Good Morning To All of You.
Yesterday I had some research on my problems. As Scott rightly suggest
me to have pre information before posting in the list, I aggreed to him.
Here is my first doubt , that I explain as:
My application makes several connections to Database Server & done their
work :
During this process have a look on below output of free command :
[root@s8-mysd-2 ~]# free -m
total used free shared buffers cached
Mem: 15917 15826 90 0 101 15013
-/+ buffers/cache: 711 15205
Swap: 16394 143 16250
It means 15 GB memory is cached.
[root@s8-mysd-2 ~]# cat /proc/meminfo
MemTotal: 16299476 kB
MemFree: 96268 kB
Buffers: 104388 kB
Cached: 15370008 kB
SwapCached: 3892 kB
Active: 6574788 kB
Inactive: 8951884 kB
Active(anon): 3909024 kB
Inactive(anon): 459720 kB
Active(file): 2665764 kB
Inactive(file): 8492164 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal: 16787884 kB
SwapFree: 16640472 kB
Dirty: 1068 kB
Writeback: 0 kB
AnonPages: 48864 kB
Mapped: 4277000 kB
Slab: 481960 kB
SReclaimable: 466544 kB
SUnreclaim: 15416 kB
PageTables: 57860 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
WritebackTmp: 0 kB
CommitLimit: 24904852 kB
Committed_AS: 5022172 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 310088 kB
VmallocChunk: 34359422091 kB
HugePages_Total: 32
HugePages_Free: 32
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
DirectMap4k: 3776 kB
DirectMap2M: 16773120 kB
[root@s8-mysd-2 ~]#
Now Can I know why the cached memory is not freed after the connections
done their work & their is no more connections :
pdc_uima_dummy=# select datname,numbackends from pg_stat_database;
datname | numbackends
-------------------+-------------
template1 | 0
template0 | 0
postgres | 2
template_postgis | 0
pdc_uima_dummy | 11
pdc_uima_version3 | 0
pdc_uima_olap | 0
pdc_uima_s9 | 0
pdc_uima | 0
(9 rows)
Same output is when it has 100 connections.
Now I have to start more queries on Database Server and issue new
connections after some time. Why the cached memory is not freed.
Flushing the cache memory is needed & how it could use so much if I set
effective_cache_size = 4096 MB.
I think if i issue some new select queries on large set of data, it will
use Swap Memory & degrades Performance.
Please correct if I'm wrong.
Thanks & best Regards,
Adarsh Sharma
Raghavendra wrote:
Adarsh,
[root@s8-mysd-2 8.4SS]# iostat
-bash: iostat: command not found
/usr/bin/iostat
Our application runs by making connections to Postgres Server from
different servers and selecting data from one table & insert into
remaining tables in a database.
When you are doing bulk inserts you need to tune AUTOVACUUM parameters
or Change the autovacuum settings for those tables doing bulk INSERTs.
Insert's need analyze.
#autovacuum = on # Enable autovacuum
subprocess? 'on'
# requires track_counts to
also be on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all
actions and
# their durations, > 0 logs
only
# actions running at least
this number
# of milliseconds.
#autovacuum_max_workers = 3 # max number of autovacuum
subprocesses
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row
updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row
updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size
before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size
before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before
forced vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost
delay for
# autovacuum, in milliseconds;
# -1 means use
vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost
limit for
# autovacuum, -1 means use
# vacuum_cost_limit
These are all default AUTOVACUUM settings. If you are using PG 8.4 or
above, try AUTOVACUUM settings on bulk insert tables for better
performance. Also need to tune the 'autovacuum_naptime'
Eg:-
ALTER table <table name> SET (autovacuum_vacuum_threshold=xxxxx,
autovacuum_analyze_threshold=xxxx);
wal_buffers //max is 16MB
checkpoint_segment /// Its very less in your setting
checkpoint_timeout
temp_buffer // If application is using temp tables
These parameter will also boost the performance.
Best Regards
Raghavendra
EnterpriseDB Corporation.
Scott Marlowe wrote:
On Mon, Apr 4, 2011 at 5:34 AM, Adarsh Sharma <adarsh.sharma@xxxxxxxxxx> <mailto:adarsh.sharma@xxxxxxxxxx> wrote:
Mem: 16299476k total, 16198784k used, 100692k free, 73776k buffers
Swap: 16787884k total, 148176k used, 16639708k free, 15585396k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+
COMMAND
3401 postgres 20 0 4288m 3.3g 3.3g S 0 21.1 0:24.73
postgres
3397 postgres 20 0 4286m 119m 119m S 0 0.8 0:00.36
postgres
PLease help me to understand how much memory does 1 Connection Uses and how
to use Server parameters accordingly.
OK, first, see the 15585396k cached? That's how much memory your OS
is using to cache file systems etc. Basically that's memory not being
used by anything else right now, so the OS borrows it and uses it for
caching.
Next, VIRT is how much memory your process would need to load every
lib it might need but may not be using now, plus all the shared memory
it might need, plus it's own space etc. It's not memory in use, it's
memory that might under the worst circumstances, be used by that one
process. RES is the amount of memory the process IS actually
touching, including shared memory that other processes may be sharing.
Finally, SHR is the amount of shared memory the process is touching.
so, taking your biggest process, it is linked to enough libraries and
shared memory and it's own private memory to add up to 4288Meg. It is
currently actually touching 3.3G. Of that 3.3G it is touching 3.3G is
shared with other processes. So, the difference between RES and SHR
is 0, so the delta, or extra memory it's using besides shared memory
is ZERO (or very close to it, probably dozens or fewer of megabytes).
So, you're NOT running out of memory. Remember when I mentioned
iostat, vmstat, etc up above? Have you run any of those?
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
