Обсуждение: Hibernate over postgresql

Hi,

I'm using Hibernate to perform SELECT, INSERT queries over a postgresql
database server 9.2.7 version. I'm seeing ROLLBACK, S_2 queries being run
after SELECT for which the client is consuming 20-30ms to send the ACK. I
understand this is the extended query protocol feature of the v3 JDBC
driver. And for the INSERT query there's a COMMIT being called for which the
server responds immediately but again the client takes another 20-30ms to
send the ACK.

I think the problem can be solved by switching to protocolVersion to 2 (It
was mentioned as a solution in one of the threads but anyways didn't work
for me). But I guess this should be the last resort.

So the question is, is there a way to make my client not wait to send the
ACK and make it asynchronous so that my round-trip time will be less.
Basically I want to know if I'm making a blocking call in my hibernate
client.

Surprisingly by running the same client on a 9.2.4 postgresql server these
calls seem to be asynchronous and I'm getting much better RTT. Does it mean
that the server can control whether client can be asynchronous or not?

I'd very much appreciate your help on this.

Thanks.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Hibernate-over-postgresql-tp5806233.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Dave Cramer-8 wrote
> Is the server machine different ?
>
> Are there any other differences ?
>
> You mentioned that others have run into this problem. Is there an email
> thread anywhere ?
>
> Dave Cramer

Yes, the server machines are different. No other differences except the
server versions.
The thread that I was referring to is this.
http://postgresql.1045698.n5.nabble.com/PostgreSQL-protocol-3-JDBC-drivers-sub-protocols-and-latency-td5003982.html



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Hibernate-over-postgresql-tp5806233p5806473.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Dave Cramer-8 wrote
> Can you do me a favour since you are the only one with the machines. Can
> you install 9.2.7 on the faster server and test it ? Is that possible ?
>
> Dave Cramer

I installed 9.2.7 keeping the client the same. The performance was even
better than before (100%-150% better) and the ACKs were still being treated
as asynchronous (Deduced by looking at tcpdump)
But the problem remains with the other 9.2.7 server (With the same client)
where ACKs are treated as synchronous and hence my round trip time is more.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Hibernate-over-postgresql-tp5806233p5806785.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

amithbm wrote
> I installed 9.2.7 keeping the client the same. The performance was even
> better than before (100%-150% better) and the ACKs were still being
> treated as asynchronous (Deduced by looking at tcpdump)
> But the problem remains with the other 9.2.7 server (With the same client)
> where ACKs are treated as synchronous and hence my round trip time is
> more.

It may be important to mention here that the machine with the issue has a
virtual IP configured. So clients connect to it using this virtual IP. The
other machine which works fine doesn't have virtual IP configured.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Hibernate-over-postgresql-tp5806233p5806789.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Well, it sounds to me like TCP_NODELAY flag problem.

Vitalii Tymchyshyn-2 wrote
> Well, it sounds to me like TCP_NODELAY flag problem.

Can you please elaborate? Has anyone else faced a similar issue with this
postgresql/hibernate combination? If yes, can you please provide the link to
it.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Hibernate-over-postgresql-tp5806233p5806824.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Well, I don't know anyone facing this problem with postgresql, but at tcp level data sending delays may indicate that Naggle's algorithm is in effect. See http://en.m.wikipedia.org/wiki/Nagle's_algorithm

Vitalii Tymchyshyn-2 wrote
> Well, I don't know anyone facing this problem with postgresql, but at tcp
> level data sending delays may indicate that Naggle's algorithm is in
> effect. See http://en.m.wikipedia.org/wiki/Nagle's_algorithm

Thanks for the information. I read through the Nagle's algorithm and
TCP_NODELAY concepts cursorily and my situation seems to be similar. i.e.
the machine which is having the issue seems to be sending ACK for every
request-response and not buffering it. This means that TCP_NODELAY seems to
be enabled (i.e. Nagle's algorithm disabled). But in the machine that is not
having issues, ACKs seem to be buffered. This means TCP_NODELAY is disabled
(i.e. Nagle's algorithm enabled). Am I right in this conslusion?

If so, how do I now disable TCP_NODELAY in hibernate/postgresql? There
doesn't seem to be much information on the internet about this.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Hibernate-over-postgresql-tp5806233p5806943.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

On Thu, Jun 12, 2014 at 6:58 AM, amithbm <amithbm@gmail.com> wrote:
> If so, how do I now disable TCP_NODELAY in hibernate/postgresql? There
> doesn't seem to be much information on the internet about this.
>

Try getting the output of:

cat /proc/sys/net/ipv4/tcp_low_latency

for both systems. This gives a hint to the TCP stack to enable = 0 or
disable = 1 Nagle's algorithm.

Stephen Nelson wrote
> Try getting the output of:
>
> cat /proc/sys/net/ipv4/tcp_low_latency
>
> for both systems. This gives a hint to the TCP stack to enable = 0 or
> disable = 1 Nagle's algorithm.

I tried this and it didn't have any effect. This setting only gives a hint
like you mention to the TCP stack. But is there way you can completely
disable TCP_NODELAY, either through hibernate or in the OS?




--
View this message in context: http://postgresql.1045698.n5.nabble.com/Hibernate-over-postgresql-tp5806233p5806979.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

An update:

It turns out that my client (A) which was talking to the application (B)
(Which inturn talks to the DB server(C) over plain text) did not have SSL
connection caching enabled. Once I turned this on, I started getting way
better performance numbers. But I'm not sure why it is behaving this way
since the connection between client and the application AND application and
the DB server are totally independent.

So anyway, once I turned SSL caching on and I analysed the tcpdump it looked
similar to the other machine in which I was getting better performance. i.e.
the ACKs all started to buffer and also started to behave asynchronously.

Now, the question before me is why. Why are these two connections i.e. A->B
and B->C seem to be dependent.

Thanks,
Amith.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Hibernate-over-postgresql-tp5806233p5807188.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.