Re: PostgreSQL Performance Tuning / Postgresql.conf and on OS Level

Can somebody help me this???

On Sat, Dec 15, 2012 at 12:53 AM, Shams Khan <shams(dot)khan22(at)gmail(dot)com> wrote:

> Hey Kevin,
>
> Thanks for such great help :
> I analyzed on query before changing parameters;
>
> explain select count(distinct a.subsno ) from subsexpired a where
> a.subsno not in (select b.subsno from subs b where b.subsno>75043 and
> b.subsno<=112565) and a.subsno>75043 and a.subsno<=112565;
> QUERY PLAN
>
> --------------------------------------------------------------------------------------------------------
> Aggregate (cost=99866998.67..99866998.68 rows=1 width=4)
> -> Index Only Scan using ind_sub_new on subsexpired a
> (cost=0.00..99866908.74 rows=35969 width=4)
> Index Cond: ((subsno > 75043) AND (subsno <= 112565))
> Filter: (NOT (SubPlan 1))
> SubPlan 1
> -> Materialize (cost=0.00..2681.38 rows=37977 width=4)
> -> Index Only Scan using subs_pkey on subs b
> (cost=0.00..2342.49 rows=37977 width=4)
> Index Cond: ((subsno > 75043) AND (subsno <=
> 112565))
>
>
> *AFTER APPLYING YOUR SUGGESTED SETTINGS:*
>
> explain select count(distinct a.subsno ) from subsexpired a where
> a.subsno not in (select b.subsno from subs b where b.subsno>75043 and
> b.subsno<=112565) and a.subsno>75043 and a.subsno<=112565;
> QUERY PLAN
>
> ------------------------------------------------------------------------------------------------------
> Aggregate (cost=7990.70..7990.71 rows=1 width=4)
> -> Index Only Scan using ind_sub_new on subsexpired a
> (cost=2437.43..7900.78 rows=35969 width=4)
> Index Cond: ((subsno > 75043) AND (subsno <= 112565))
> Filter: (NOT (hashed SubPlan 1))
> SubPlan 1
> -> Index Only Scan using subs_pkey on subs b
> (cost=0.00..2342.49 rows=37977 width=4)
> Index Cond: ((subsno > 75043) AND (subsno <= 112565))
>
> *PERFORMANCE WAS BOOSTED UP DRASTICALLY* ---when I edited the work_mem to
> 100 MB---just look at the difference;
>
> One more thing Kevin, could you please help me out to understand how did
> calculate those parameters?
>
>
> Without more info, there's a bit of guesswork, but...
> What exta info is required...please let me know...
>
> Thanks again...
>
>
> On Sat, Dec 15, 2012 at 12:20 AM, Kevin Grittner <kgrittn(at)mail(dot)com> wrote:
>
>> Shams Khan wrote:
>>
>> > *Need to increase the response time of running queries on
>> > server...*
>>
>> > 8 CPU's and 16 cores
>>
>> > [64GB RAM]
>>
>> > HDD 200GB
>> > Database size = 40GB
>>
>> Without more info, there's a bit of guesswork, but...
>>
>> > maintenance_work_mem = Not initialised
>>
>> I would say probably 1GB
>>
>> > effective_cache_size = Not initialised
>>
>> 48GB
>>
>> > work_mem = Not initialised
>>
>> You could probably go 100MB on this.
>>
>> > wal_buffers = 8MB
>>
>> 16BM
>>
>> > checkpoint_segments = 16
>>
>> Higher. Probably not more than 128.
>>
>> > shared_buffers = 32MB (have read should 20% of Physical memory)
>>
>> 16GB to start. If you have episodes of high latency, where even
>> queries which normally run very quickly all pause and then all
>> complete close together after a delay, you may need to reduce this
>> and/or increase the aggressiveness of the background writer. I've
>> had to go as low as 1GB to overcome such latency spikes.
>>
>> > max_connections = 100
>>
>> Maybe leave alone, possibly reduce. You should be aiming to use a
>> pool to keep about 20 database connections busy. If you can't do
>> that in the app, look at pgbouncer.
>>
>> > checkpoint_completion_target = Not initialised
>>
>> It is often wise to increase this to 0.8 or 0.9
>>
>> If I read this right, you have one 200GB drive for writes? That's
>> going to be your bottleneck if you write much data. You need a RAID
>> for both performance and reliability, with a good controller with
>> battery-backed cache configured for write-back. Until you have one
>> you can be less crippled on preformance by setting
>> synchronous_commit = off. The trade-off is that there will be a
>> slight delay between when PostgreSQL acknoleges a commit and when
>> the data is actually persisted.
>>
>> -Kevin
>>
>
>