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 <kgri...@mail.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 >
