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

[Lazaro Ruben Garcia Martinez]

Would you try a test with pgbench? 

Regards. 

----- Mensaje original -----

> > 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
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general

> 10mo. ANIVERSARIO DE LA CREACION DE LA UNIVERSIDAD DE LAS CIENCIAS
> INFORMATICAS...
> CONECTADOS AL FUTURO, CONECTADOS A LA REVOLUCION

> http://www.uci.cu
> http://www.facebook.com/universidad.uci
> http://www.flickr.com/photos/universidad_uci


10mo. ANIVERSARIO DE LA CREACION DE LA UNIVERSIDAD DE LAS CIENCIAS INFORMATICAS...
CONECTADOS AL FUTURO, CONECTADOS A LA REVOLUCION

http://www.uci.cu
http://www.facebook.com/universidad.uci
http://www.flickr.com/photos/universidad_uci
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20120601/3b9647ce/attachment.html>