Обсуждение: Re: PostgreSQL Performance Tuning / Postgresql.conf and on OS Level

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

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