Re: Major Performance decrease after some hours

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@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
>> >
>> >
>>
>
> ---------------------------(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