Thread: 10 concurrent clients / pure insert SQL scripts (each 100000 lines)

Hi!

I tried to insert values into a table (created by
    CREATE TABLE gaga42 (a bigint, b char varying);
) (inserted by
    insert into gaga42 values (0,X);
    (where X varies between '' and '1...1' (1000 chars))
). I used the standard configuration of PostgreSQL 7.1.3 on a OpenBSD 3.1
box with 384 MB main memory and a SCSI RAID disc and the following command
(gaga41.1.sql contains the inserts:
    time csh -c "repeat 10 sh -c 'time psql -q gaga < gaga42.1.sql &'"

It took 20000 seconds and as I could see (top command) there were 12 postgres
processes which were disabling each other due to semwait conditions (I think
they used excessively semophores).

Is this bug fixed in 7.2.3?

Do you think OpenBSD 3.1 has problems with semaphores (should be an easy
feature (its a single processor machine))?

Thank you.

Bye
Arne

Arne Woerner <woerner@mediabase-gmbh.de> writes:
> Is this bug fixed in 7.2.3?

Try it and see.

FWIW, on HPUX with current CVS tip I could not measure any difference in
performance between a single client issuing INSERTs like yours and ten
clients doing so: I got essentially the same number of insertions/sec
either way.

            regards, tom lane

Hi Tom!

Thank you for your reply.

> > Is this bug fixed in 7.2.3?
>
> Try it and see.
>
I tried it on another box (RedHat Linux 8.0, much faster and
newer than the OpenBSD box) and it took 2000 seconds (10 times
faster than the 7.1.3 on the slower OpenBSD box).

Btw: MySQL on the slower box is able to insert 50 times faster
(I assume this is because of worse reliability).

Btw: PostgreSQL 7.1.3 was 4 times slower (160 seconds) with a
single client than MySQL with a single client. This factor
increased appr. by the factor 12 (from 4 to 50) when I used
10 concurrent clients.

Concluding I still have the impression that the semaphore
handling on i386 RedHat Linux and i386 OpenBSD in combination
with PostgreSQL leads to bad performance.

> FWIW, on HPUX with current CVS tip I could not measure any difference in
> performance between a single client issuing INSERTs like yours and ten
> clients doing so: I got essentially the same number of insertions/sec
> either way.
>
That sounds good... :-)) But I cannot afford HP UX. :-))

Due to the nature of our applications performance is not so
important for us. That means that I do not consider this bug
to be severe.

Bye
Arne