[pgpool-general: 580] Re: Load balancing does not work

Tatsuo Ishii ishii at postgresql.org
Fri Jun 1 21:16:56 JST 2012


> On 06/01/2012 01:44 AM, Tatsuo Ishii wrote:
>>> I am trying to load balance with pgpool (3.1.1-1) over two DB nodes
>>> (PostgreSQL 9.0): 1 master & 1 slave, configured for streaming replication.
>>>
>>> The thing is that all the queries are going to the master node. I
>>> created a simple shell script in order to simulate dozens of connections
>>> querying simultaneously in a loop, then disconnecting, connecting again,
>>> querying, etc., but there's no query sent to the slave node at all.
>>>
>>> At the same time, when browsing through pgpoolAdmin, queries like
>>> "SELECT pg_is_in_recovery()", and "SELECT * FROM pg_settings" are
>>> arriving successfully on both places, so there's no connection issue
>>> involved.
>>>
>>> I have even set the weight to 0 for the master:
>>>
>>> backend_weight0 = 0
>>> backend_weight1 = 1
>>>
>>> But still, every query is sent there and not on the slave.
>>>
>>> Any thoughts why load balancing is not working in that case?
>> For SELECT pg_is_in_recovery() itself, there's no reason not to load
>> balance. Maybe delay_threshold?
>>
>> For SELECT * FROM pg_settings, it is not load balanced since it is
>> accessing system catalog.
> 
> In my case delay_threshold = 0, so it is disabled.
> 
> What I meant was that those two queries appear in the PostgreSQL logs of
> both servers - the master and the slave, which is an indication that
> pgpool is connected to both.
> 
> However, when issuing simple SELECT query, it appears in the log of the
> master only. It is not load balanced. I am running the following script
> from 20 terminals in parallel:
> 
> #!/bin/bash
> for a in `seq 1 10`; do
>   echo -n "$a: "
>   for usr in `psql -U postgres -d test -h localhost -p 5433 -t -c
> "SELECT id FROM test ORDER BY RANDOM() LIMIT 10;"`
>     do
>     echo -n -e "$usr "
>   done
> 
>   sleep 1;
> done
> 
> 
> It makes a new connection on every loop iteration, so when running it
> from 20 places, it is expected to do 20 * 10 = 200 connections. Still
> everything goes to the master and nothing to the slave.

Hum. Recently we discovered that Solaris platform has problem with
load balance. Is this your case?

If you replace your "SELECT...;" to "SELECT 1";, still you see everything goes to master?
--
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