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

Hi Kevin,

I got one more question, please help me out.

Question 1. How do we correlate our memory with kernel parameters, I mean
to say is there any connection between shared_buffer and kernel SHMMAX. For
example if I define my shared buffer more than my current SHMMAX value, it
would not allow me to use that ??or vice versa. Please throw some light.

Questions 2. I want to show the last result of last query before and after
changing the parameters, I found performance was degraded.

USED EXPLAIN ANALYZE

radius=# explain analyze select * from subsexpired where subsno between
5911 and 50911 and subsno not in (select subsno from subs where subsno
between 5911 and 50911);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ind_sub_new on subsexpired (cost=1943.39..6943.84
rows=30743 width=69) (actual time=124.628..142.203 rows=430 loops=1)
Index Cond: ((subsno >= 5911) AND (subsno <= 50911))
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 62079
SubPlan 1
-> Index Only Scan using subs_pkey on subs (cost=0.00..1876.77
rows=26647 width=4) (actual time=0.030..44.743 rows=27397 loops=1)
Index Cond: ((subsno >= 5911) AND (subsno <= 50911))
Heap Fetches: 27397
Total runtime: 142.812 ms
----------------------------------------------------------------------------------------------------------------------

After: using the parameters as suggested.

radius=# explain analyze select * from subsexpired where subsno between
5911 and 50911 and subsno not in (select subsno from subs where subsno
between 5911 and 50911);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ind_sub_new on subsexpired (cost=1943.39..6943.84
rows=30743 width=69) (actual time=128.351..144.532 rows=430 loops=1)
Index Cond: ((subsno >= 5911) AND (subsno <= 50911))
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 62079
SubPlan 1
-> Index Only Scan using subs_pkey on subs (cost=0.00..1876.77
rows=26647 width=4) (actual time=0.030..47.848 rows=27397 loops=1)
Index Cond: ((subsno >= 5911) AND (subsno <= 50911))
Heap Fetches: 27397
Total runtime: 145.127 ms
(9 rows)

Thanks

On Sat, Dec 15, 2012 at 1:50 AM, Kevin Grittner <kgrittn(at)mail(dot)com> wrote:

> Shams Khan wrote:
>
> > *PERFORMANCE WAS BOOSTED UP DRASTICALLY* ---when I edited the
> > work_mem to 100 MB---just look at the difference;
>
> You only showed EXPLAIN output, which only shows estimated costs.
> As already suggested, try running both ways with EXPLAIN ANALYZE --
> which will show both estimates and actual.
>
> > One more thing Kevin, could you please help me out to understand
> > how did calculate those parameters?
>
> My own experience and reading about the experiences of others. If
> you follow the pgsql-performance list, you will get a better "gut
> feel" on these issues as well as picking up techniques for problem
> solving. Speaking of which, that would have been a better list to
> post this on. The one actual calculation I did was to make sure
> work_mem was less than RAM * 0.25 / max_connections. I didn't go
> all the way to that number because 100MB is enough for most
> purposes and your database isn't very much smaller than your RAM.
> You know, the melding of a routine calculation with gut feel. :-)
>
> > Without more info, there's a bit of guesswork, but...
> > What exta info is required...please let me know...
>
> The main things I felt I was missing was a description of your
> overall workload and EXPLAIN ANALYZE output from a "typical" slow
> query.
>
> There's a page about useful information to post, though:
>
> http://wiki.postgresql.org/wiki/SlowQueryQuestions
>
> Now that you have somewhat reasonable tuning for the overall
> server, you can look at the EXPLAIN ANALYZE output of queries which
> don't run as fast as you thing they should be able to do, and see
> what adjustments to cost factors you might need to make. With the
> numbers you previously gave, a wild guess would be that you'll get
> generally faster run-times with these settings:
>
> seq_page_cost = 0.1
> random_page_cost = 0.1
> cpu_tuple_cost = 0.5
>
> Be sure to look at actual run times, not EXPLAIN cost estimates.
>
> -Kevin
>