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.
