If you are on Linux, I recommend iostat(1) and vmstat(8) over top.
Iostat will report I/O transfer statistics; it's how I discovered
that work_mem buffers were spilling over to disk files. For Vmstat,
look in particular at the load (ie., how many processes are competing
for the scheduler) in the first field ("r") and how many processes
are blocked by I/O waits ("b").
Alexander.
On Oct 5, 2006, at 14:35 , Peter Bauer wrote:
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@xxxxxxxxx>:
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@xxxxxxxxxxxxxxx>:
> 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@xxxxxxxxxxxxx>:
> >> Ray Stell <stellr@xxxxxxxxxx> 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
>
>
---------------------------(end of
broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
