Re: PostgreSQL insert speed tests

From: Shridhar Daithankar <shridhar(at)frodo(dot)hserus(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: PostgreSQL insert speed tests
Date: 2004-02-28 13:26:09
Message-ID: 200402281856.09623.shridhar@frodo.hserus.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Saturday 28 February 2004 13:59, Sezai YILMAZ wrote:
> Tom Lane wrote:
> >Sezai YILMAZ <sezai(dot)yilmaz(at)pro-g(dot)com(dot)tr> writes:
> >>I changed the three hash indexes to btree.
> >>The performance is increased about 2 times (in PostgreSQL 7.3.4 1905
> >>rows/s).
> >>Concurrent inserts now work.
> >
> >Concurrent inserts should work with hash indexes in 7.4, though not 7.3.
>
> I notice this condition. I do not get dead locks with 7.4 on schema with
> hash indexes. 7.4 solves this problem but is very slow.
>
> >The slowdown you report probably is due to the rewrite of hash indexing
> >to allow more concurrency --- the locking algorithm is more complex than
> >it used to be. I am surprised that the effect is so large though.
> >Could you make your test program available?
>
> The test program and .SQL script is attached
>
> Comiple and link scenarios:
>
> without transactions (where each insert is a transaction)
> $ gcc -o tester tester.c -lpq
>
> with default 400 inserts per transaction blocks
> $ gcc -DTRANSACTION -o tester tester.c -lpq
>
> with 200 inserts per transaction blocks
> $ gcc -DTRANSACTION -DINSERTPERTRANSACTION=200 -o tester tester.c -lpq
>
> I do concurrent tests by starting seperate tester programs from
> different xterm windows.

Some tests on CVS head in case somebody finds the data interesting. It is a
single IDE disk system with linux 2.6.2 running. It has 512MB RAM and 2.66GHz
P-IV. The file system is reiserfs.

I pulled CVS head couple of days back.

Everything default except for shared_buffers=100 and effective cache=25000, I
got 1980 inserts/sec in a single run.

With checkpoint segments 10, I got 1923 inserts per sec.

With two concurrent processes and 10 checkpoint segments, I got 1673 req/sec.

I noted that in vmstat, the IO wasn't pushed really hard. The block out were
varying about 1000-5000 per sec. However occasionally that would spike to
18000 blocks. I guess that would be some checkpoint going on.

and I could not find sort_mem in postgresql.conf. Is work_mem new name for it?
I recall the discussion to sanitize the name but not the result of it..

Shridhar

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Geoffrey Kinnel 2004-02-28 13:54:08 Where are the backend/commands?
Previous Message Benson Lei 2004-02-28 12:47:40 installation -- help ?