Thread: what is lslowing me down? JDBC or Postgresql itself?

what is lslowing me down? JDBC or Postgresql itself?

From
jeff@amiel.net (Jeff Amiel)
Date:
java test program (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.


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?

more info....

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...


any help would be greatly appreciated.

Re: what is lslowing me down? JDBC or Postgresql itself?

From
jeff@amiel.net (Jeff Amiel)
Date:
End result is that no matter how many threads I run, still takes about
the same amount of time to process the 1000 items!!

Re: what is lslowing me down? JDBC or Postgresql itself?

From
Oliver Jowett
Date:
Jeff Amiel wrote:

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

Check the CPU usage of the client vs. server. Also check I/O on the
server. This may show you the bottleneck directly. If there's no obvious
bottleneck, you may be limited by your network; can you put the client
and server on the same machine to eliminate this?

 From your description you are doing many small transactions and
throwing more concurrent connections at it doesn't improve your
throughput. I'd hazard a guess that you'll probably find you are I/O
bound on the server as the backend will be doing a lot of fsyncs (or
equivalent).

What throughput do you get running the queries via psql?

-O

Re: what is lslowing me down? JDBC or Postgresql itself?

From
Markus Schaber
Date:
Hi, Jeff,

On 30 Mar 2004 07:42:32 -0800
jeff@amiel.net (Jeff Amiel) wrote:

> java test program (running on windows box)
>
> Program starts X threads.

[snip]

May be you are limited by I/O.

My mail at the archives
http://archives.postgresql.org/pgsql-jdbc/2004-06/msg00027.php and
possibly the whole thread may be of interest for you.

HTH,
Markus
--
markus schaber | dipl. informatiker
logi-track ag | rennweg 14-16 | ch 8001 zürich
phone +41-43-888 62 52 | fax +41-43-888 62 53
mailto:schabios@logi-track.com | www.logi-track.com