[Pgpool-general] Confused about JDBC and load balancing

Thu Jul 28 08:39:48 UTC 2011

Hi,

> The source code for the PostgreSQLValidConnectionChecker class includes the following validity check:
> 
>       try
>       {
>          stmt = cn.createStatement();
>          stmt.execute("");
>       }
>       // etc..
> 
> This empty-string query is exactly what's causing the problem.  When I disable the connection checker by commenting out the XML element shown above, everything works correctly.

I committed the patch to avoid this problem.
Load balancing will work correctly with next releases(3.0.5, 3.1.0)
even if JBoss sends empty-queries.

-- 
Toshihiro Kitagawa
SRA OSS, Inc. Japan

On Fri, 22 Jul 2011 02:37:55 -0400
Matt Solnit <msolnit at soasta.com> wrote:

> On Jul 21, 2011, at 11:04 PM, Matthew Solnit wrote:
> 
> > On Jul 20, 2011, at 11:16 PM, Toshihiro Kitagawa wrote:
> > 
> >> On Wed, 20 Jul 2011 11:58:45 -0400
> >> Matt Solnit <msolnit at soasta.com> wrote:
> >> 
> >>> Hi everyone.  I'm trying to understand why load balancing doesn't work with the PostgreSQL JDBC driver, and what (if anything) can be done about it.  I have to admit, I find the documentation a bit confusing.  Here is what it currently says at http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html:
> >>> 
> >>>   "Note: the JDBC driver has an autocommit option. If autocommit is false, the JDBC driver sends "BEGIN" and "COMMIT" by itself. So pgpool cannot do any load balancing. You need to call setAutoCommit(true) to enable autocommit."
> >> 
> >> I think we have to revise the manual more clearly...
> >> 
> >> It's a description about load balancing in replication mode.
> >> In the case of Master/Slave mode, please watch "Streaming Replication" 
> >> section:
> >> 
> >> In an explicit transaction:
> >> 
> >> - Transaction starting commands such as BEGIN are sent to the primary
> >> node.
> >> - Following SELECT and some other queries that can be sent to both
> >> primary or standby are executed in the transaction or on the standby
> >> node.
> >> - Commands which cannot be executed on the standby such as INSERT are
> >> sent to the primary. After one of these commands, even SELECTs are
> >> sent to the primary node, This is because these SELECTs might want
> >> to see the result of an INSERT immediately. This behavior continues
> >> until the transaction closes or aborts.
> >> 
> >>> And yet, a recent post to this mailing list (http://pgfoundry.org/pipermail/pgpool-general/2011-July/003819.html) contradicts this, and says that recent versions of pgpool-II can handle explicit transactions.  And the flow chart (http://pgpool.projects.postgresql.org/pgpool-II/doc/where_to_send_queries.pdf) seems to say the same thing.
> >>> 
> >>> Even more confusing is the fact that it *does* seem to work once in a while.  I'm currently using pgpool-II 3.0.4, with PostgreSQL 9.0.2 in streaming replication mode, and JDBC driver.  When I enable statement logging in both pgpool-II and PostgreSQL, I can see that a very small number queries do go to the slave.  I have not been able to figure out any pattern to this.
> >>> 
> >>> I would really love to understand more about what is going on.  Any help would be sincerely appreciated.
> >> 
> >> I guess that the last condition mentioned above is related.
> >> 
> >> For example:
> >> 
> >> BEGIN
> >> SELECT -> load balancing
> >> INSERT etc.(write-query)
> >> SELECT -> not load balancing
> >> SELECT -> not load balancing
> >> COMMIT 
> >> 
> >> -- 
> >> Toshihiro Kitagawa
> >> SRA OSS, Inc. Japan
> > 
> > 
> > Thank you very much for the detailed response.  I'm starting to dig into this more, and here's what I'm seeing:
> > 1.  Using the JDBC driver with no connection pool (i.e. using java.sql.DriverManager), everything seems to work as expected.
> > 2.  Using the c3p0 JDBC connection pool inside Tomcat, everything seems to work as expected.
> > 3.  Using the JBoss connection pool inside JBoss 4.2.2, things do *not* work as expected.
> > 
> > It just so happens that up until now, I've been using case 3 exclusively.  So I thought that there was a fundamental issue with JDBC and pgpool-II load balancing, but in fact it seems that it's isolated to the JBoss connection pool.
> > 
> > Specifically, what I'm seeing is the message "Parse: Unable to parse the query:" show up inside the pgpool-II log file.  Once this shows up, all queries appear to go to the master.  I'm still trying to narrow down what the specific relationship is between the "Unable to parse the query" message and load balancing, but there definitely appears to something going on here.
> > 
> > I'm also trying to figure out what it is about the JBoss connection pool that makes the "Unable to parse the query" happen in the first place.
> > 
> > Any comments would be appreciated :-).
> > 
> > -- Matt
> > 
> > P.S.  It seems like any query using a system table (e.g. pg_class, pg_catalog, etc.) goes to the master, no matter what.  I've found this to be true even when using a "regular" client like psql.
> 
> Found the problem.  It's in the PostgreSQL "valid connection checker" class provided by JBoss.  A typical JBoss data source configuration file for PostgreSQL
> includes the following XML:
> 
>     <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.PostgreSQLValidConnectionChecker</valid-connection-checker-class-name>
> 
> The source code for the PostgreSQLValidConnectionChecker class includes the following validity check:
> 
>       try
>       {
>          stmt = cn.createStatement();
>          stmt.execute("");
>       }
>       // etc..
> 
> This empty-string query is exactly what's causing the problem.  When I disable the connection checker by commenting out the XML element shown above, everything works correctly.
> 
> Apparently, this JBoss behavior is intentional, for performance reasons that came from discussing with PostgreSQL developers.  See https://issues.jboss.org/browse/JBAS-3133 and http://community.jboss.org/message/298853#298853 for details.
> 
> At any rate, I *strongly* recommend including this information in the pgpool-II documentation.  The current version of JBoss (AS 7) still has this code (see https://source.jboss.org/browse/JBossAS/connector/src/main/java/org/jboss/as/connector/adapters/jdbc/extensions/postgres/PostgreSQLValidConnectionChecker.java?r=6c64725de60a324628dc648baae080a9a7516dca).
> 
> Hope this is helpful,
> Matt