Re: PostgreSQL insert speed tests

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