Op 21-07-11 08:16, Toshihiro Kitagawa schreef:
On Wed, 20 Jul 2011 11:58:45 -0400
Matt Solnit<[email protected]> 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
I was wondering what will happen in the following scenario, when
streaming replication is replayed between SELECT 2 and 3 on the slave,
altering the 'snapshot' view of the database from the users point of
view - this will possibly return inconsistent data, right?
1 BEGIN
2 SELECT -> Load-balance
Replay xlog on slave where next SELECT is executed
3 SELECT -> Load-balance
4 COMMIT
_______________________________________________
Pgpool-general mailing list
[email protected]
http://pgfoundry.org/mailman/listinfo/pgpool-general
