Thread: Beginning tuning

Phillip,

You'd have to give us a lot more information than this to help you.

machine specs ?
Postgresql version and jdbc version
postgresql configuration
are you using a db pool ?

Dave

On 6-Nov-07, at 9:09 AM, Phillip Mills wrote:

> I'm just getting started on the process of analyzing performance for
> a Java Enterprise application.  (The main negative symptom at the
> moment is that it doesn't use more than three processors on an eight-
> processor system, regardless of load.)
>
> One of the first things I've noticed out of a number of thread dumps
> is that there's about an 80% chance that the stack points to I/O
> requests from PGStream.ReceiveChar().  I'm wondering about any hints
> or pointers that would help me understand whether that's expected
> behavior, or something that needs fixing, or just generally how to
> evaluate/improve JDBC performance.
>
> (Regarding the main problem, none of my threads are reported in a
> blocked state, leading to my focus on I/O.)

On Tue, 6 Nov 2007, Phillip Mills wrote:

> One of the first things I've noticed out of a number of thread dumps is that
> there's about an 80% chance that the stack points to I/O requests from
> PGStream.ReceiveChar().  I'm wondering about any hints or pointers that
> would help me understand whether that's expected behavior, or something that
> needs fixing, or just generally how to evaluate/improve JDBC performance.
>

The ReceiveChar call is waiting for data from the backend.  I'm not sure
how you're identifying blocked threads, but ReceiveChar is a blocking call
underneath the hood.  So this just shows that the driver is waiting while
queries are executing which is perfectly expected.

Kris Jurka

>> One of the first things I've noticed out of a number of
>> thread dumps is that there's about an 80% chance that
>> the stack points to I/O requests from PGStream.ReceiveChar().
>> I'm wondering about any hints or pointers that would help
>> me understand whether that's expected behavior, or something that
>> needs fixing, or just generally how to evaluate/improve
>> JDBC performance.
>
> The ReceiveChar call is waiting for data from the backend.
> I'm not sure how you're identifying blocked threads, but
> ReceiveChar is a blocking call underneath the hood.
> So this just shows that the driver is waiting while
> queries are executing which is perfectly expected.

I'd like to add to this that you should only start tuning if
you experience a performance problem.

In your case, if most of the time is spent waiting for database
results, you could check if the SQL statements can be
improved. Also, I have seen cases where much time gets
lost in client-server chat because a program performs 1000s
of nearly identical short queries in a loop instead of
performing one bigger query that would have the database server
perform the loop more efficiently.

As I said, only tune when you have a problem.

Yours,
Laurenz Albe

Phillip Mills wrote:
>> I'd like to add to this that you should only start tuning if
>> you experience a performance problem.
>
> I agree and in this case the top-level problem statement was
> that the application was not making use of available
> processing resources regardless of the load applied.

Available processing resources where?
On the application server?

The problem statement is bad, because why is it a
problem if the resources are not consumed?
The question is: is the response time and the throughput ok?
If not, that would be a real problem.

Are your top-level problem staters just pissed off
because they cannot get the machine in trouble no matter
how much test load they apply?

If you have a performance problem, the first thing you must find
out is where the performance bottleneck is: network,
CPU, I/O, or memory? And on which machine, if there
are more than one. You can continue once you know that.

Yours,
Laurenz Albe

Phillip Mills wrote:

> Since memory problems (other than outright failures) usually present as
> CPU and disk activity, we can eliminate that.  It's not CPU, because
> that's where I'm trying to bottleneck and not getting there.  So whether
> network or non-network, that leaves I/O.  Which is why I started this
> conversation by asking about the I/O routines that I saw on the thread
> stacks.

My guesses would be:

(1) you've run out of disk bandwidth. Have you measured disk I/O rate on
the server vs. query rate as a starting point?

(2) you're hitting your hardware's limit on the rate at which it can
sync your disks (which in turn is related to physical disk access time).
A simple test for that is to turn off fsync (danger, danger, testing
purposes only) and see if that removes the performance cap. Or run off a
purely in-memory filesystem if that's practical for your dataset.

(3) you don't have enough concurrency in your test setup to soak up
query latency. Try more concurrent queries (= more threads in Java land)

All of the above would show up as "JDBC client blocking waiting for the
server to respond".

You'll probably find a more suitable audience on the pgsql-performance
list, though, unless you have something pointing the finger at the JDBC
driver specifically.

-O

Phillip Mills wrote:

> (Regarding the main problem, none of my threads are reported in a
> blocked state, leading to my focus on I/O.)

BTW, this is because Sun JVM thread dumps and JVMPI/JVMTI generally
consider a thread currently in a native method to be "running"
regardless of what it's actually doing. In the case of a network read
(implemented as a native method), it's actually most likely to be
blocked in the kernel waiting on more data from the server. Makes for
fun when profiling, I've had to build tools to explicitly exclude "is
probably blocked" native methods from profile results to get anything
useful on systems that are mostly I/O bound :(

-O