Re: Major Performance decrease after some hours

I finished the little benchmarking on our server and the results are
quite curios.
With the numbers from http://sitening.com/tools/postgresql-benchmark/
in mind i did
./pgbench -i pgbench
and then performed some pgbench tests, for example
./pgbench -c 1 -t 1000 -s 1 pgbench
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 1000
number of transactions actually processed: 1000/1000
tps = 50.703609 (including connections establishing)
tps = 50.709265 (excluding connections establishing)

So our server with two 3.00 GHz Xeon CPUs and 2GB has about 5% of the
performance of the server described in the article!

I did some tests on a Xen machine running on my workstation and the
results are about 400-500tps which seems to be quite reasonable.

I also tried to disable drbd and put the data directory elsewhere, but
the performance was the same.

any ideas?

thx,
Peter


2006/10/5, Alexander Staubo <alex@purefiction.net>:
> It appears to me that work_mem is a more significant configuration
> option than previously assumed by many PostgreSQL users, myself
> included. As with many database optimizations, it's an obscure
> problem to diagnose because you generally only observe it through I/O
> activity.
>
> One possibility would be to log a warning whenever work_mem is
> exceeded (or exceeded by a certain ratio). I would also love a couple
> of new statistics counters tracking the amount of work memory used
> and the amount of work memory that has spilled over into pgsql_tmp.
>
> Alexander.
>
> On Oct 5, 2006, at 10:48 , Peter Bauer wrote:
>
> > Hi all,
> >
> > inspired by the last posting "Weird disk write load caused by
> > PostgreSQL?" i increased the work_mem from 1 to 7MB and did some
> > loadtest with vacuum every 10 minutes. The system load (harddisk) went
> > down and everything was very stable at 80% idle for nearly 24 hours!
> > I am currently performing some pgbench runs to evaluate the hardware
> > and configuration for the system but i think the biggest problems are
> > solved so far.
> >
> > thx everybody,
> > Peter
> >
> > 2006/10/2, Tom Lane <tgl@sss.pgh.pa.us>:
> >> Ray Stell <stellr@cns.vt.edu> writes:
> >> > How would one determine the lock situation definitively?  Is there
> >> > an internal mechanism that can be queried?
> >>
> >> pg_locks view.
> >>
> >>                         regards, tom lane
> >>
> >> ---------------------------(end of
> >> broadcast)---------------------------
> >> TIP 2: Don't 'kill -9' the postmaster
> >>
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> >               http://archives.postgresql.org
>
>