[pgpool-general: 5395] Re: pgpool-II and JDBC pool

Tatsuo Ishii ishii at sraoss.co.jp
Mon Mar 27 22:54:58 JST 2017


It looks Pgpool-II works as expected. From your pgpool.log, I found 38
SELECTs were sent to DB node 0, while 70 SELECTs were sent to DB node
1.  Many of SELECTs sent to DB node 0 is "SELECT COUNT(*) FROM
pg_catalog.pg_roles" (33 out of 38), which will be always sent to DB
node 0 since it is a query against the system catalog. In summary,
most of user SELECTs were sent to DB node 1 according to the
pgpool.log.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Hi Tatsuo,
> as attachments of this email you can find logs and configuration file.
> 
> Very kind regards.
> 
> 
> Meph
> 
> On 26 March 2017 at 04:38, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
>> > Hi there,
>> > at the moment, I am using pgpool-II as load balancer in master-slave mode
>> > with streaming replication with two postgres nodes. Pgpool is accessed
>> by a
>> > java server application that use a JDBC pool in Payara (a Glassfish fork)
>> > application server.
>> >
>> > During startup phase, the application execute about 1800 queries that are
>> > almost all readonly stored functions, but pgpool send queries almost
>> > exclusively toward primary node. This is the situation after some tries:
>> >
>> >
>> >
>> > postgres at postgres=# show pool_nodes;
>> >  node_id |        hostname         | port  | status | lb_weight |  role
>>  |
>> > select_cnt | load_balance_node | replication_delay
>> > ---------+-------------------------+-------+--------+-------
>> > ----+---------+------------+-------------------+-------------------
>> >  0       | citrix2.redevogames.com | 15004 | up     | 0.250000  |
>> primary |
>> > 9520       | true              | 0
>> >  1       | 10.0.0.3                | 5997  | up     | 0.750000  |
>> standby |
>> > 10         | false             | 0
>> >
>> > The question now is: is it a good idea to use a JDBC pool to connect to
>> > pgpgool or is it better without it?
>> >
>> >
>> > It can be other reason for this behavior of balancer?
>>
>> There may be something which could explain this (for example
>> replication delay is too much). Can you show me the pgpool log, along
>> with log_per_node_statement = on?
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>


More information about the pgpool-general mailing list