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@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@orkash.com> <mailto:adarsh.sharma@orkash.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?
>>
>
>