Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 2CA7B1337BD9 for ; Mon, 4 Apr 2011 09:34:17 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 22097-09 for ; Mon, 4 Apr 2011 12:33:58 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp160.iad.emailsrvr.com (smtp160.iad.emailsrvr.com [207.97.245.160]) by mail.postgresql.org (Postfix) with ESMTP id 014331337B67 for ; Mon, 4 Apr 2011 09:33:57 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp46.relay.iad1a.emailsrvr.com (SMTP Server) with ESMTP id 556F4E8286; Mon, 4 Apr 2011 08:33:58 -0400 (EDT) X-Virus-Scanned: OK Received: by smtp46.relay.iad1a.emailsrvr.com (Authenticated sender: adarsh.sharma-AT-orkash.com) with ESMTPSA id 08F7EE8238 for ; Mon, 4 Apr 2011 08:33:56 -0400 (EDT) Message-ID: <4D99BAB2.7030807@orkash.com> Date: Mon, 04 Apr 2011 18:03:54 +0530 From: Adarsh Sharma User-Agent: Thunderbird 2.0.0.22 (X11/20090625) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Postgres Performance Tuning References: <4D999211.9080507@orkash.com> <4D99ACC0.5090009@orkash.com> <4D99B0B1.4020702@orkash.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------000409090606030003050705" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201104/20 X-Sequence-Number: 43082 This is a multi-part message in MIME format. --------------000409090606030003050705 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Thank U all, I know some things to work on & after some work & study on them , I will continue this discussion tomorrow . Best Regards, Adarsh 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 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 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? >> > > --------------000409090606030003050705 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit

Thank U all,

I know some things to work on & after some work & study on them , I will continue this discussion tomorrow .


Best  Regards,
Adarsh

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> 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?



--------------000409090606030003050705--