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

Hi Kevin,

When I check Idle session running question, shows the many queries running
but end of the query it shows Rollback and commit which take lot of time. I
am little scared bcoz I made changes in memory parameter first time in
postgres and getting this result, earlier I have not seen this. Is that
fine? Which parameter impact on this? please help...

select now()-query_start as runtime,client_addr,pid,query from
pg_stat_activity where not query like '%IDLE%' order by 1;

00:00:51.314855 | 95.129.0.28 | 26052 | COMMIT
00:01:23.655743 | 95.129.0.28 | 26118 | COMMIT
00:00:16.707913 | 95.129.0.28 | 26567 | COMMIT
00:00:17.084691 | 95.129.0.28 | 26565 | COMMIT
00:00:20.118008 | 95.129.0.28 | 26378 | COMMIT
00:00:31.952375 | 95.129.0.28 | 26514 | COMMIT

On Mon, Dec 17, 2012 at 6:38 PM, Kevin Grittner <kgrittn(at)mail(dot)com> wrote:

> Shams Khan wrote:
>
> > 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.
>
> If SHMMAX is not large enough to allow the PostgreSQL service to
> acquire the amount of shared memory it needs based on your
> configuration settings, the PostgreSQL server will log an error and
> fail to start. Please see the docs for more information:
>
> http://www.postgresql.org/docs/current/static/kernel-resources.html
>
> > Questions 2. I want to show the last result of last query before and
> after
> > changing the parameters, I found performance was degraded.
>
> > Total runtime: 142.812 ms
>
> > Total runtime: 145.127 ms
>
> The plan didn't change and the times were different by less than
> 2%. There can easily be that much variation from one run to the
> next. If you try the same query many times (say, 10 or more) with
> each configuration and it is consistently faster with one than the
> other, then you will have pretty good evidence which configuration
> is better for that particular query. If the same configuration wins
> in general, use it.
>
> Since performance differences which are that small are often caused
> by very obscure issues, it can be very difficult to pin down the
> reason. It's generally not anything to fret over.
>
> -Kevin
>