Thread: Severe performance degradation when using the 9.2-1000 JDBC 4 driver
Severe performance degradation when using the 9.2-1000 JDBC 4 driver
From
"emergency.shower@gmail.com"
Date:
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
Re: Severe performance degradation when using the 9.2-1000 JDBC 4 driver
From
"emergency.shower@gmail.com"
Date:
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
Re: Severe performance degradation when using the 9.2-1000 JDBC 4 driver
From
"emergency.shower@gmail.com"
Date:
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
Re: Severe performance degradation when using the 9.2-1000 JDBC 4 driver
From
"emergency.shower@gmail.com"
Date:
> 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
Re: Severe performance degradation when using the 9.2-1000 JDBC 4 driver
From
"Tsunakawa, Takayuki"
Date:
> 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
Re: Severe performance degradation when using the 9.2-1000 JDBC 4 driver
From
Scott Harrington
Date:
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
Re: Severe performance degradation when using the 9.2-1000 JDBC 4 driver
From
Scott Harrington
Date:
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.
Attachment
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
Attachment
Re: Severe performance degradation when using the 9.2-1000 JDBC 4 driver
From
Scott Harrington
Date:
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.