Обсуждение: Severe performance degradation when using the 9.2-1000 JDBC 4 driver

Hi,

after the upgrade the latest PortgreSQL 9.2-1000 JDBC 4 driver, we
discovered a severe performance degradation when connecting to 8.4,
9.0, 9.1, and 9.2 servers.  In a test application the
DriverManager#getConnection(String, String, String) call constantly
took > 4500 ms compared to < 20 ms with the previous 9.1-903 JDBC 4
driver. This makes the driver virtually unusable in production
environments.

Client: Windows 2008 R2, Windows 7
Server: PostgreSQL 8.4, 9.0, 9.1, 9.2 on Linux 2.6

Regards,
Alex

emergency.shower@gmail.com wrote on 05.10.2012 21:27:
> after the upgrade the latest PortgreSQL 9.2-1000 JDBC 4 driver, we
> discovered a severe performance degradation when connecting to 8.4,
> 9.0, 9.1, and 9.2 servers.  In a test application the
> DriverManager#getConnection(String, String, String) call constantly
> took > 4500 ms compared to < 20 ms with the previous 9.1-903 JDBC 4
> driver. This makes the driver virtually unusable in production
> environments.
>
> Client: Windows 2008 R2, Windows 7
> Server: PostgreSQL 8.4, 9.0, 9.1, 9.2 on Linux 2.6

I cannot confirm that.
I actually have the impression that it's faster (at least using "localhost", can't test a remote server right now).

On Fri, 5 Oct 2012, emergency.shower@gmail.com wrote:

> after the upgrade the latest PortgreSQL 9.2-1000 JDBC 4 driver, we
> discovered a severe performance degradation when connecting to 8.4,
> 9.0, 9.1, and 9.2 servers.  In a test application the
> DriverManager#getConnection(String, String, String) call constantly
> took > 4500 ms compared to < 20 ms with the previous 9.1-903 JDBC 4
> driver. This makes the driver virtually unusable in production
> environments.

Can you show us the connection URL that you are using?  Are you using SSL?
It appears that some of the 9.2 changes may require a DNS lookup that they
didn't before and you might be getting a timeout there.  Any DNS issues on
your side?

Kris Jurka

It's actually pretty easy to compile in eclipse.

Load the project from source, then you have to remove all of the
concrete implementations that you don't need.


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Sat, Oct 6, 2012 at 8:23 AM, emergency.shower@gmail.com
<emergency.shower@gmail.com> wrote:
> Hi  Dave,
>
>> Any chance you can run this in debug mode to find out exactly what is
>> taking so long ?
>
> I will try to dig deeper into the problem when I'm back in the office
> next week (the problem is not reproducible in my home network).
>
> Is there a chance or even a guide how to compile the driver within an
> IDE, preferably Eclipse?
>
> -Alex

Hi Kris,

On Sat, Oct 6, 2012 at 2:43 AM, Kris Jurka <books@ejurka.com> wrote:
>
> Can you show us the connection URL that you are using?

Other as stated before, only connection URLs with IPv4 Addresses were
involved in our tests.

>  Are you using SSL?

No SSL.

> It appears that some of the 9.2 changes may require a DNS lookup that they
> didn't before and you might be getting a timeout there.  Any DNS issues on
> your side?

Actually there are two distinct problems:

 - a reverse lookup failure with a subdomain in the local network that
contains some of the test hosts, and

 - a reverse lookup failure with hosts that are located in our DMZ.

The first seems to be repairable to me, for the second I don't know.


-Alex

The problem comes from the getHostName() call in
org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(InetSocketAddress[],
String, String, Properties, Logger).

 doAuthentication(newStream, address.getHostName(), user, info, logger);

I can confirm the problem, it's taking about 4000ms to get a Connection. I did a simple Java class to test that:


/*
 * JavaApplication1.java
 *
 * Criado em Oct 18, 2012, 12:40:56 AM
 *
 * Copyright(c) 2012 Sérgio Ernesto Diniz Saquetim. Todos os direitos reservados.
 */
package javaapplication1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * [Insira a descrição da classe aqui]
 *
 * @author Sérgio Saquetim <sergiosaquetim@gmail.com>
 * @version 0.1
 * @since 0.1
 */
public class JavaApplication1 {

  /**
   * Função de entrada de execução do programa.
   *
   * @param args os argumentos da linha de comando
   */
  public static void main(String[] args) throws ClassNotFoundException, SQLException {
    Class.forName("org.postgresql.Driver");

    String url = "jdbc:postgresql://192.168.1.100/frames?user=postgres&password=postgres@mac";

    long time = System.currentTimeMillis();
    try (Connection conn = DriverManager.getConnection(url)) {
      System.out.println("Elapsed Time: "+(System.currentTimeMillis()-time));
      try (PreparedStatement pst = conn.prepareStatement("SELECT version();")) {
        try (ResultSet rs = pst.executeQuery()) {
          while (rs.next()) {
            System.out.println(rs.getString(1));
          }
        }
      }
    }
  }
}

The result using the : 9.2-1000 JDBC 4
run:
Elapsed Time: 5172
PostgreSQL 9.2.1 on x86_64-apple-darwin, compiled by i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc.
build5658) (LLVM build 2336.9.00), 64-bit 

Now using the 9.1-903 JDBC 4:
Elapsed Time: 50
PostgreSQL 9.2.1 on x86_64-apple-darwin, compiled by i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc.
build5658) (LLVM build 2336.9.00), 64-bit 

I'm getting the same results in my company what makes this Driver unusable in production. Is there any workaround? Will
Iget to much trouble if I use 9.1-903 JDBC 4 version driver with Postgres 9.2? 

Thanks a lot!








Em sexta-feira, 5 de outubro de 2012 16h27min21s UTC-3, "emergency.shower@gmail.com"  escreveu:
> Hi,
>
>
>
> after the upgrade the latest PortgreSQL 9.2-1000 JDBC 4 driver, we
>
> discovered a severe performance degradation when connecting to 8.4,
>
> 9.0, 9.1, and 9.2 servers.  In a test application the
>
> DriverManager#getConnection(String, String, String) call constantly
>
> took > 4500 ms compared to < 20 ms with the previous 9.1-903 JDBC 4
>
> driver. This makes the driver virtually unusable in production
>
> environments.
>
>
>
> Client: Windows 2008 R2, Windows 7
>
> Server: PostgreSQL 8.4, 9.0, 9.1, 9.2 on Linux 2.6
>
>
>
> Regards,
>
> Alex
>
>
>
>
>
> --
>
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>
> To make changes to your subscription:
>
> http://www.postgresql.org/mailpref/pgsql-jdbc

On 10/18/2012 12:16 PM, Sérgio Saquetim wrote:
> I can confirm the problem, it's taking about 4000ms to get a Connection.

That sounds like a broken DNS or reverse DNS setup in your environment,
it's about right for your average DNS timeout.

--
Craig Ringer

I think the DNS lookup is part of the JDBC failover connection patch that I created.
I initially used a InetSocketAddress.getHostString() that does not do any DNS lookups, but since that method was added
inJava7 I had to revert to getHostName() method which does. 

I see following options:
1) modify the code so that is uses reflection and if Java7 is detected it will use the no-lookup method
2) wait 4 months until Oracle drops Java6 support (Java6 was supposed to be EOL already few months back) and use the
no-lookupvariant 
3) let the code stay as is and let users fix their dns servers

-Mikko
________________________________________
From: pgsql-jdbc-owner@postgresql.org [pgsql-jdbc-owner@postgresql.org] on behalf of Craig Ringer
[ringerc@ringerc.id.au]
Sent: 18 October 2012 12:03
To: Sérgio Saquetim
Cc: pgsql-jdbc@postgresql.org; Rafael dos Santos Silva
Subject: Re: [JDBC] Severe performance degradation when using the 9.2-1000 JDBC 4 driver

On 10/18/2012 12:16 PM, Sérgio Saquetim wrote:
> I can confirm the problem, it's taking about 4000ms to get a Connection.

That sounds like a broken DNS or reverse DNS setup in your environment,
it's about right for your average DNS timeout.

--
Craig Ringer


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

On Thu, Oct 18, 2012 at 5:17 AM, Mikko Tiihonen
<Mikko.Tiihonen@nitorcreations.com> wrote:
> I think the DNS lookup is part of the JDBC failover connection patch that I created.
> I initially used a InetSocketAddress.getHostString() that does not do any DNS lookups, but since that method was
addedin Java7 I had to revert to getHostName() method which does. 
>
> I see following options:
> 1) modify the code so that is uses reflection and if Java7 is detected it will use the no-lookup method
> 2) wait 4 months until Oracle drops Java6 support (Java6 was supposed to be EOL already few months back) and use the
no-lookupvariant 
> 3) let the code stay as is and let users fix their dns servers
>
> -Mikko

Number 1 above is probably the best option. Even if Oracle EOL's Java6
we will still have to support it for a while. 3 is probably
indefensible.


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 10/18/2012 05:17 PM, Mikko Tiihonen wrote:
> I think the DNS lookup is part of the JDBC failover connection patch that I created.
> I initially used a InetSocketAddress.getHostString() that does not do any DNS lookups, but since that method was
addedin Java7 I had to revert to getHostName() method which does. 
>
> I see following options:

0) Revert the JDBC failover patch or require a connection parameter to
enable it

> 1) modify the code so that is uses reflection and if Java7 is detected it will use the no-lookup method

Gah! no! Not only will this not work in most SecurityManager contexts,
but it's slow and horrid.

> 2) wait 4 months until Oracle drops Java6 support (Java6 was supposed to be EOL already few months back) and use the
no-lookupvariant 

... then another two years or so until it's actually adopted. People
still use Java 1.4, and 1.5 remains very wide-spread. Sadly.

> 3) let the code stay as is and let users fix their dns servers

While sometimes reasonable, in this case it's also a performance
regression for functionality most will never need, so -1 to that.

--
Craig Ringer

On Thu, Oct 18, 2012 at 8:18 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> On 10/18/2012 05:17 PM, Mikko Tiihonen wrote:
>>
>> I think the DNS lookup is part of the JDBC failover connection patch that
>> I created.
>> I initially used a InetSocketAddress.getHostString() that does not do any
>> DNS lookups, but since that method was added in Java7 I had to revert to
>> getHostName() method which does.
>>
>> I see following options:
>
>
> 0) Revert the JDBC failover patch or require a connection parameter to
> enable it
>
>
>> 1) modify the code so that is uses reflection and if Java7 is detected it
>> will use the no-lookup method
>
>
> Gah! no! Not only will this not work in most SecurityManager contexts, but
> it's slow and horrid.

Surely there must be a way to detect that you are on Java7 without reflection ?


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 10/18/2012 08:43 PM, Dave Cramer wrote:
> On Thu, Oct 18, 2012 at 8:18 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:
>> On 10/18/2012 05:17 PM, Mikko Tiihonen wrote:
>>>
>>> I think the DNS lookup is part of the JDBC failover connection patch that
>>> I created.
>>> I initially used a InetSocketAddress.getHostString() that does not do any
>>> DNS lookups, but since that method was added in Java7 I had to revert to
>>> getHostName() method which does.
>>>
>>> I see following options:
>>
>>
>> 0) Revert the JDBC failover patch or require a connection parameter to
>> enable it
>>
>>
>>> 1) modify the code so that is uses reflection and if Java7 is detected it
>>> will use the no-lookup method
>>
>>
>> Gah! no! Not only will this not work in most SecurityManager contexts, but
>> it's slow and horrid.
>
> Surely there must be a way to detect that you are on Java7 without reflection ?

Detect, trivially, but if code that refers to a method that doesn't
exist in Java 7 is loaded it'll cause a link error from memory - so you
can't actually get to the point where you test the condition when
running in Java 6.

I'll write a toy test class to confirm that and check back.

--
Craig Ringer

> 0) [...] require a connection parameter to enable it

+1 for this.

Mikko,

Thanks for the support, adding a DNS to the server's IP address in my hosts file solved it, but I'm curious, since I
wasdirectly using my server's IP address in the connection string, there shouldn't be any DNS requests at all. What am
Imissing? 

About your suggestions I agree with Dave Cramer, although using Java 7 it's not a problem for me. The best solution
wouldbe number 1. 

Thanks again.

Em quinta-feira, 18 de outubro de 2012 06h17min30s UTC-3, Mikko Tiihonen  escreveu:
> I think the DNS lookup is part of the JDBC failover connection patch that I created.
>
> I initially used a InetSocketAddress.getHostString() that does not do any DNS lookups, but since that method was
addedin Java7 I had to revert to getHostName() method which does. 
>
>
>
> I see following options:
>
> 1) modify the code so that is uses reflection and if Java7 is detected it will use the no-lookup method
>
> 2) wait 4 months until Oracle drops Java6 support (Java6 was supposed to be EOL already few months back) and use the
no-lookupvariant 
>
> 3) let the code stay as is and let users fix their dns servers
>
>
>
> -Mikko
>
> ________________________________________
>
> From: pgsql-jdbc-owner@postgresql.org [pgsql-jdbc-owner@postgresql.org] on behalf of Craig Ringer
[ringerc@ringerc.id.au]
>
> Sent: 18 October 2012 12:03
>
> To: S�rgio Saquetim
>
> Cc: pgsql-jdbc@postgresql.org; Rafael dos Santos Silva
>
> Subject: Re: [JDBC] Severe performance degradation when using the 9.2-1000 JDBC 4 driver
>
>
>
> On 10/18/2012 12:16 PM, S�rgio Saquetim wrote:
>
> > I can confirm the problem, it's taking about 4000ms to get a Connection.
>
>
>
> That sounds like a broken DNS or reverse DNS setup in your environment,
>
> it's about right for your average DNS timeout.
>
>
>
> --
>
> Craig Ringer
>
>
>
>
>
> --
>
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>
> To make changes to your subscription:
>
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>
>
>
>
> --
>
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>
> To make changes to your subscription:
>
> http://www.postgresql.org/mailpref/pgsql-jdbc

I agree, +1 for require a connection parameter to enable it

Em quinta-feira, 18 de outubro de 2012 10h10min30s UTC-3, "emergency.shower@gmail.com"  escreveu:
> > 0) [...] require a connection parameter to enable it
>
>
>
> +1 for this.
>
>
>
>
>
> --
>
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>
> To make changes to your subscription:
>
> http://www.postgresql.org/mailpref/pgsql-jdbc

> From: pgsql-jdbc-owner@postgresql.org
> On Thu, Oct 18, 2012 at 8:18 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> > On 10/18/2012 05:17 PM, Mikko Tiihonen wrote:
> >>
> >> I think the DNS lookup is part of the JDBC failover connection patch
> >> that I created.
> >> I initially used a InetSocketAddress.getHostString() that does not do
> >> any DNS lookups, but since that method was added in Java7 I had to
> >> revert to
> >> getHostName() method which does.
> >>
> >> I see following options:
> >
> >
> > 0) Revert the JDBC failover patch or require a connection parameter to
> > enable it
> >
> >
> >> 1) modify the code so that is uses reflection and if Java7 is
> >> detected it will use the no-lookup method
> >
> >
> > Gah! no! Not only will this not work in most SecurityManager contexts,
> > but it's slow and horrid.
> 
> Surely there must be a way to detect that you are on Java7 without reflection ?

System.getProperty("java.version") will do.

http://docs.oracle.com/javase/tutorial/essential/environment/sysprop.html

+1 to choice 1.


Regards

On Thu, 18 Oct 2012, Mikko Tiihonen wrote:

> I think the DNS lookup is part of the JDBC failover connection patch
> that I created. I initially used a InetSocketAddress.getHostString()
> that does not do any DNS lookups, but since that method was added in
> Java7 I had to revert to getHostName() method which does.
>

Is there any functionality that we need that is provided by using
InetSocketAddress or is it just a convenient object to pass around host
and port together?  The simplest option to me seems to be reverting to
passing simple strings/ints around (possibly in some kind of our own
container).

Kris Jurka

> > I think the DNS lookup is part of the JDBC failover connection patch
> > that I created. I initially used a InetSocketAddress.getHostString()
> > that does not do any DNS lookups, but since that method was added in
> > Java7 I had to revert to getHostName() method which does.
> >
>
> Is there any functionality that we need that is provided by using
> InetSocketAddress or is it just a convenient object to pass around host
> and port together?  The simplest option to me seems to be reverting to
> passing simple strings/ints around (possibly in some kind of our own
> container).

It is just a convenient container. And it seems on older JVMs it cannot be used for such purposes.
I agree that we should just replace it with our own container that contains the host/port pair.

-Mikko

On 10/19/2012 02:36 PM, Mikko Tiihonen wrote:
>>> I think the DNS lookup is part of the JDBC failover connection patch
>>> that I created. I initially used a InetSocketAddress.getHostString()
>>> that does not do any DNS lookups, but since that method was added in
>>> Java7 I had to revert to getHostName() method which does.
>>>
>>
>> Is there any functionality that we need that is provided by using
>> InetSocketAddress or is it just a convenient object to pass around host
>> and port together?  The simplest option to me seems to be reverting to
>> passing simple strings/ints around (possibly in some kind of our own
>> container).
>
> It is just a convenient container. And it seems on older JVMs it cannot be used for such purposes.
> I agree that we should just replace it with our own container that contains the host/port pair.

If that's all you're using it for, then that's the simplest and best
choice. +1 from me on that.

--
Craig Ringer

On Fri, 19 Oct 2012, Craig Ringer wrote:

> On 10/19/2012 02:36 PM, Mikko Tiihonen wrote:
>>>> I think the DNS lookup is part of the JDBC failover connection patch
>>>> that I created. I initially used a InetSocketAddress.getHostString()
>>>> that does not do any DNS lookups, but since that method was added in
>>>> Java7 I had to revert to getHostName() method which does.
>>>>
>>>
>>> Is there any functionality that we need that is provided by using
>>> InetSocketAddress or is it just a convenient object to pass around host
>>> and port together?  The simplest option to me seems to be reverting to
>>> passing simple strings/ints around (possibly in some kind of our own
>>> container).
>>
>> It is just a convenient container. And it seems on older JVMs it cannot be
>> used for such purposes.
>> I agree that we should just replace it with our own container that contains
>> the host/port pair.
>
> If that's all you're using it for, then that's the simplest and best choice.
> +1 from me on that.

Could the new container contain the localAddress (null for "any") as well
as the target host and port? I'm using a simple patch that allows binding
my JDBC connections to specific localAddress (unit tests & docs still
to-do), and I can envision scenarios where a client with multiple NICs
might need to initiate connections to failover hostA from localAddress X
and to failover hostB from localAddress Y, in environments where iptables
SNAT may not be an option.

By creating an appropriate factory class for this laddr/caddr/cport
container we could select the best implementation (Java7-only or
pre-Java7) in a static initializer and avoid having to resort to
reflection to avoid the unwanted DNS calls as discussed earlier in this
thread. Want me to take a stab at this this weekend? I'm not git-skilled
yet so I'll just have to email a patch.

On 10/23/2012 02:41 AM, Віталій Тимчишин wrote:
> AFAIR class is not loaded until after it is accessed (very same line),
> so something like this should work:
> if (isJava7()) {
>  return new Java7Resolver();
> } else {
>  return new Java6Resolver();
> }

Good point - that should work just fine.

--
Craig Ringer

On Fri, 19 Oct 2012, Scott Harrington wrote:

> On Fri, 19 Oct 2012, Craig Ringer wrote:
>
>> On 10/19/2012 02:36 PM, Mikko Tiihonen wrote:
>>>>> I think the DNS lookup is part of the JDBC failover connection patch
>>>>> that I created. I initially used a InetSocketAddress.getHostString()
>>>>> that does not do any DNS lookups, but since that method was added in
>>>>> Java7 I had to revert to getHostName() method which does.
>>>>>
>>>>
>>>> Is there any functionality that we need that is provided by using
>>>> InetSocketAddress or is it just a convenient object to pass around host
>>>> and port together?  The simplest option to me seems to be reverting to
>>>> passing simple strings/ints around (possibly in some kind of our own
>>>> container).
>>>
>>> It is just a convenient container. And it seems on older JVMs it cannot be
>>> used for such purposes.
>>> I agree that we should just replace it with our own container that
>>> contains the host/port pair.
>>
>> If that's all you're using it for, then that's the simplest and best
>> choice. +1 from me on that.
>
> Could the new container contain the localAddress (null for "any") as well as
> the target host and port? I'm using a simple patch that allows binding my
> JDBC connections to specific localAddress (unit tests & docs still to-do),
> and I can envision scenarios where a client with multiple NICs might need to
> initiate connections to failover hostA from localAddress X and to failover
> hostB from localAddress Y, in environments where iptables SNAT may not be an
> option.
>
> By creating an appropriate factory class for this laddr/caddr/cport container
> we could select the best implementation (Java7-only or pre-Java7) in a static
> initializer and avoid having to resort to reflection to avoid the unwanted
> DNS calls as discussed earlier in this thread. Want me to take a stab at this
> this weekend? I'm not git-skilled yet so I'll just have to email a patch.

Hi everyone, please look over this patch.

By introducing a simple org.postgresql.util.HostSpec to contain the
orignal caller-provided host and port, we never even need to call
InetSocketAddress.getHostName() or getHostString().

If this is accepted, then I will resubmit the localAddress patch based on
this; will be quite simple to put localAddress in HostSpec and then it's
available where needed at the time of Socket.connect in PGStream without
having to touch a dozen other constructor signatures to get it there.

On 10/22/2012 7:56 PM, Scott Harrington wrote:
> Hi everyone, please look over this patch.
>
> By introducing a simple org.postgresql.util.HostSpec to contain the
> orignal caller-provided host and port, we never even need to call
> InetSocketAddress.getHostName() or getHostString().
>
>

I think this is exactly what we want.  Attached is a slightly modified
version that includes fixes to the MakeSSL classes.

Kris Jurka

On Mon, 22 Oct 2012, Kris Jurka wrote:

> On 10/22/2012 7:56 PM, Scott Harrington wrote:
>> Hi everyone, please look over this patch.
>>
>> By introducing a simple org.postgresql.util.HostSpec to contain the
>> orignal caller-provided host and port, we never even need to call
>> InetSocketAddress.getHostName() or getHostString().
>
> I think this is exactly what we want.  Attached is a slightly modified
> version that includes fixes to the MakeSSL classes.

Thanks Kris, not sure how I missed those.