blocking? locking? Concurrent connections slows things down....

From: jeff(at)amiel(dot)net (Jeff Amiel)
To: "pgsql-general(at)postgresql(dot)org(dot)pgsql-novice"(at)postgresql(dot)org
Subject: blocking? locking? Concurrent connections slows things down....
Date: 2004-03-31 10:22:30
Message-ID: e368dd0e.0403310222.2175a41b@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-novice

Postgresql 7.4 running on FreeBSD
jdbc driver version 74_213

single table----

Table "public.table"
Column | Type | Modifiers
--------+---------------+-----------
id | integer | not null
data | character(15) | not null
Indexes:
"id" primary key, btree (id)
"tableid" unique, btree (id)

populate initially with 1000 entries...

java test program (sdk 1.4.2 running on windows box)

Program starts X threads.

Each thread does it's own classForName and has OWN connection
(autocommit is turned off)

inside each thread, they use a shared queue to perform work on 1000
items. Each thread will only grab one item (and hence only work on
single row in table)

Each of the 1000 items requires the thread to perform:
1 select for update (single row)
5 select statements (not for update) on single row (uses
index...low/no cost..."select count(*) from table where id=2".
Prepared statement...but do not pass in paremeter...hardcode the id
value)
1 update statement (the one we selected for update)
1 commit

I put timing logging around the 5 select statements section only
(after checking everything else)

When running a single thread....takes 0 milliseconds
(mostly...sometimes takes 16)
when running 10 threads...takes between 15 and 150 milliseconds.
when running 20 threads....takes between 15 and 250 milliseconds.

End result is that no matter how many threads I launch, it takes the
same amount of time to 'process' the 1000 items.

I clear the table and vacuum full between each test.

I have tweaked the postgresql.conf settings only slightly from the
default install (increased shared buffers some)

Why is this happening? Is the jdbc driver the limiting factor? Is it
the database? Can anyone give me any suggestions to troubleshoot?

any help would be greatly appreciated.

Browse pgsql-general by date

  From Date Subject
Next Message Durai 2004-03-31 10:44:26 Warings in Log: could not resolve "localhost": host nor service provided, or not known
Previous Message Manfred Koizar 2004-03-31 09:17:35 Re: Large DB

Browse pgsql-novice by date

  From Date Subject
Next Message János 2004-03-31 20:07:57 create user
Previous Message Paul Semenick 2004-03-30 22:10:32 dotnet stored procedures with postgresql