Re: Response time increases over time

Thanks for that Mario, I will check it out.

@All:
Anyway, I have compiled 9.1.2 from source, and unfortunately the
performance haven't got better at the same load, it is consistently quite
low (~70 ms average transaction time with 100 clients) on this Debian. I am
quite surprised about this, it is unrealistically high.
I have run pg_test_fsync, and showed about 2600 fsync/sec, which means HDD
has write caching on (it is a 7200 rpm drive, there is no HW RAID
controller). However my other machine, the simple Win7 one, on which
performance was so good and consistent, fsync/sec was a lot lower, only
about 100 as I can remember, so it probably really flushed each transaction
to disk.
I have also run load simulation on this Debian machine with InnoDb, and it
performed quite well, so the machine itself is good enough to handle this.
On the other hand it is quite poor on Win7, but that's another story...

So there seems to be something on this Debian machine that hinders
PostgreSQL to perform better. With 8.4 I logged slow queries (with 9.1 not
yet), and almost all were COMMIT, taking 10-20-30 or even more ms. But at
the same time the fsync rate can be quite high based on pg_test_fsync, so
probably not fsync is what makes it slow. Performance seems to degrade
drastically as I increase the concurrency, mainly concurrent commit has
problems as I can see.
I also checked that connection pooling works well, and clients don't
close/open connections.
I also have a graph about outstanding transaction count over time, and it
is quite strange: it shows that low performce (20-30 xacts at a time) and
high-performace (<5 xact at a time) parts are alternating quite frequently
instead of being more even.
Do anybody have any idea based on this info about what can cause such
behaviour, or what I could check or try?

Thanks in advance,
Otto

2011/12/7 Mario Splivalo <mario(dot)splivalo(at)megafon(dot)hr>

> On 12/07/2011 09:23 AM, Havasvölgyi Ottó wrote:
> > Thanks, Josh.
> > The only reason I tried 8.4 first is that it was available for Debian as
> > compiled package, so it was simpler for me to do it. Anyway I am going
> > to test 9.1 too. I will post about the results.
> >
>
> If you're using squeeze, you can get 9.1 from the debian backports.
>
> Mario
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>