Re: Postgres Performance Tuning

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