Re: Postgres Performance Tuning

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(at)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(at)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(at)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(at)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(dot)sharma(at)orkash(dot)com> <mailto:adarsh(dot)sharma(at)orkash(dot)com> 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?
>>
>
>