Thread: Pgpool questions

I have a couple Pgpool questions regarding running pgpool as a load balancer with Sony as the replicator.

 

Is there anyway to load balance selects to more than 2 servers? ex.  1 master with 2 slaves?  Run multiple instances?

Is there any way to tell a select (ex. with a function) to go only the master and not the slave?

Is there any way to tell a select query to only go to the slave?  

 

Thanks

 

 

Brian Maguire wrote:
> I have a couple Pgpool questions regarding running pgpool as a load balancer with Sony as the replicator.
>
>
>
> Is there anyway to load balance selects to more than 2 servers? ex.  1 master with 2 slaves?  Run multiple instances?

My understanding is that if you turn on the load balancing it will
automatically do so for all servers.

>
> Is there any way to tell a select (ex. with a function) to go only the master and not the slave?

Run it in a transaction with BEGIN;

>
> Is there any way to tell a select query to only go to the slave?

That i don't know.

Sincerely,

Joshua D. Drake
Command Prompt, Inc.

>
>
>
> Thanks
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------TIP 5: Have you checked our extensive FAQ?
>                http://www.postgresql.org/docs/faq

On Sat, Apr 23, 2005 at 08:37:02AM -0700, Joshua D. Drake wrote:
> Brian Maguire wrote:

> >Is there any way to tell a select query to only go to the slave?
>
> That i don't know.

I think Tatsuo said recently that it was an interesting suggestion and
the he'd look into implementing it.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"La persona que no quería pecar / estaba obligada a sentarse
 en duras y empinadas sillas    / desprovistas, por cierto
 de blandos atenuantes"                          (Patricio Vogel)

> I have a couple Pgpool questions regarding running pgpool as a load balancer with Sony as the replicator.
>
>
>
> Is there anyway to load balance selects to more than 2 servers? ex.  1 master with 2 slaves?  Run multiple instances?

It's on my TODO but will not be a trivial change.

> Is there any way to tell a select (ex. with a function) to go only the master and not the slave?

Pgpool will send any query not starting with "SELECT" or "select" to
the master. So you can insert a white space or a comment, for example
 "/* NO LOAD BALANCE */" at the beginning of the line to prevent the
 query to be sent to the slave.

> Is there any way to tell a select query to only go to the slave?

Set weight_secondary to 0.
--
Tatsuo Ishii

Tatsuo Ishii wrote:
>>
>>Is there anyway to load balance selects to more than 2 servers? ex.  1 master with 2 slaves?  Run multiple instances?
>
> It's on my TODO but will not be a trivial change.

Could I set up 2 pairs of pgpool-balanced servers, and use a
third pgpool to load balance across the two pools?
Or is that just silly.

On Sun, 2005-04-24 at 00:53, Ron Mayer wrote:
> Tatsuo Ishii wrote:
> >>
> >>Is there anyway to load balance selects to more than 2 servers? ex.  1 master with 2 slaves?  Run multiple
instances?
> >
> > It's on my TODO but will not be a trivial change.
>
> Could I set up 2 pairs of pgpool-balanced servers, and use a
> third pgpool to load balance across the two pools?
> Or is that just silly.

I've actually thought of doing the same thing.  I can't see a reason why
it wouldn't work.

> On Sun, 2005-04-24 at 00:53, Ron Mayer wrote:
> > Tatsuo Ishii wrote:
> > >>
> > >>Is there anyway to load balance selects to more than 2 servers? ex.  1 master with 2 slaves?  Run multiple
instances?
> > >
> > > It's on my TODO but will not be a trivial change.
> >
> > Could I set up 2 pairs of pgpool-balanced servers, and use a
> > third pgpool to load balance across the two pools?
> > Or is that just silly.
>
> I've actually thought of doing the same thing.  I can't see a reason why
> it wouldn't work.

Oh, cascading pgpool! Actually once I have tried it and it definitly
worked. The only concern is the overhead due to the cascading...
--
Tatsuo Ishii

Ok, I have added per query basis weight control to my TODO list.
--
Tatsuo Ishii

> Thanks.
>
>
>
> Is it on your todo list to something like this...?  It is the opposite
> of "/* NO LOAD BALANCE */".
>
>
>
> Make a "/* SLAVE */" type comment to force it to a slave? It would be
> helpful in forcing a query to a reporting server if you know your what
> your reporting queries are.
>
>
>
> "Set weight_secondary to 0."
>
>
>
> Did you mean Set weight_master to 0.  Unfortunately this would not help
> on a query by query basis, but the above would.
>
>
>
>
>
>
>
>
>
>
>
>
>
> -----Original Message-----
> From: Tatsuo Ishii [mailto:t-ishii@sra.co.jp]
> Sent: Saturday, April 23, 2005 7:23 PM
> To: Brian Maguire
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Pgpool questions
>
>
>
> > I have a couple Pgpool questions regarding running pgpool as a load
> balancer with Sony as the replicator.
>
> >
>
> >
>
> >
>
> > Is there anyway to load balance selects to more than 2 servers? ex.  1
> master with 2 slaves?  Run multiple instances?
>
>
>
> It's on my TODO but will not be a trivial change.
>
>
>
> > Is there any way to tell a select (ex. with a function) to go only the
> master and not the slave?
>
>
>
> Pgpool will send any query not starting with "SELECT" or "select" to
>
> the master. So you can insert a white space or a comment, for example
>
>  "/* NO LOAD BALANCE */" at the beginning of the line to prevent the
>
>  query to be sent to the slave.
>
>
>
> > Is there any way to tell a select query to only go to the slave?
>
>
>
> Set weight_secondary to 0.
>
> --
>
>
>
> Tatsuo Ishii
>