Re: Major Performance decrease after some hours

I forgot to mention that top does not show a noticeable increase of
CPU or system load during the pgbench runs (postmaster has 4-8% CPU).
Shouldn't the machine be busy during such a test?

thx,
Peter

2006/10/5, Peter Bauer <peter.m.bauer@gmail.com>:
> 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
> >
> >
>