[pgpool-general: 552] Re: Hibernate and pgpool-II

[David Kerr]

On 05/29/2012 05:03 AM, Guillaume Lelarge wrote:
> On Tue, 2012-05-29 at 07:49 +0900, Tatsuo Ishii wrote:
>>> On 05/27/2012 10:31 AM, David Kerr wrote:
>>>> On 5/27/2012 10:28 AM, Ludger Overbeck wrote:
>>>>> Hi,
>>>>>
>>>>> I would like to know if anybody has ever used pgpool-II (in
>>>>> Replication
>>>>> Mode or Master/Slave Mode) with Hibernate? Does this cause any
>>>>> problems?
>>>>>
>>>>> As far as I know in Hibernate SQL-statements (which are sent to the
>>>>> database) are always issued in an explicit transaction. And as the FAQ
>>>>> for pgpool says, that would mean that there is no load balancing in
>>>>> replication mode. Is that correct?
>>>>>
>>>>> Instead I am thinking about using the Master/Slave mode with
>>>>> synchronous
>>>>> streaming replication to avoid that selects deliver stale data (as
>>>>> with
>>>>> asynchronous replication). Would this work?
>>>>>
>>>>> Thanks for your replies.
>>>>
>>>> I've started some testing with Hibernate + PgPool II in load balance /
>>>> master server mode with async Streaming Replication and so far the
>>>> results have been positive.
>>>>
>>>> Dave
>>>
>>>
>>> Update on this:
>>>
>>> Even though my test appeared to be load balancing, the log files show
>>> that, in fact, it was not.
>>>
>>> I suspect it's as you mentioned the explicit transactions in
>>> hibernate. (I'm in Master/Slave mode.)
>>
>> I don't know what was your test case but I'm sure pgpool-II 3.0 or
>> later can do load balancing in streaming replication mode even in
>> explicit transactions.
>
> While I agree with you on this, it really depends on his workload. If he
> does mostly write requests in master/slave mode, all the write queries
> must be executed on the master, so most of the queries will happen on
> the master, making it hard to appreciate the load balancing.
>
>

Right, I'm aware that only reads can be offloaded to the standby.

If it should work then I must have a config error, I'll have a look and 
report back if I manage to fix it.

Dave