[Pgpool-general] General pgpool locking issues

[Tatsuo Ishii]

> Sorry for the delay here.
> 
> We set weight to 9/1 and are testing using this configuration.

CVS HEAD now allows 0 weight.

> We also found out that we had some spaces before some of the SELECT
> statements, so things like "  SELECT..." or " SELECT. ..".    So we have
> fixed this (removed the spaces) and will see if we can replicate the
> mismatch again.

ignore_leading_white_space = true

might help you.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

> Will keep you posted.
> Thx,
> John
> 
> 
> On 8/1/06, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> >
> > > We turned debugging on and basically its finding a kind mismatch on a
> > > standard select.
> > >
> > > Ill try to pull together some logging data and send it your way.
> >
> > Ok.
> >
> > > Let me
> > > know what you find out about the weight distribution piece. Thanks.
> >
> > I found that pgpool does not allow weight = 0 and believe this is an
> > oversight. However load balance should work fine whatever weight is,
> > since in the load balance mode SELECT query should be sent to one of
> > the servers, not both and this means kind mismatch error should not
> > happen. I will wait for your logging data to find out what is wrong.
> > --
> > Tatsuo Ishii
> > SRA OSS, Inc. Japan
> >
> > > -John
> > >
> > > On 8/1/06, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> > > >
> > > > Hi,
> > > >
> > > > > Thank you very much for the reply.  Its helpful.
> > > >
> > > > You are welcome.
> > > >
> > > > > We have now turned on load_balancing and have set the weight to 1
> > and 0
> > > > (for
> > > > > db0 and db1).  And we still are getting "kind mismatches".
> > > > >
> > > > > Is this weight distribution of 1/0 valid?  Or does 1/0 overwrite the
> > > > load
> > > > > balancing parameter.    Is this not a recommended setting?
> > > >
> > > > Let me look into this.
> > > >
> > > > > We do direct SELECT connects to db1 via port 5432, thus we wanted
> > pgpool
> > > > to
> > > > > just select from db0 vs do a 50/50 load balancing.
> > > > >
> > > > > If the 1/0 setting is wrong let us know and we will change it so we
> > will
> > > > do
> > > > > something like 9/1 type load balancing.  If the 1/0 weight setting
> > is
> > > > valid
> > > > > why would we still be seeing "kind mismatches" on selects?
> > > > >
> > > > > Thanks in advance for any suggestions/input here.
> > > >
> > > > Can you please provide debugging out put of pgpool by turing on -d and
> > > > -n option? We need info for when "kind mismatches" erros occures in
> > > > your setting.
> > > > --
> > > > Tatsuo Ishii
> > > > SRA OSS, Inc. Japan
> > > >
> > > > > Thanks,
> > > > > John
> > > > >
> > > > >
> > > > >
> > > > > On 7/30/06, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> > > > > >
> > > > > > > We have been testing with pgpool for a little while here and
> > have
> > > > run
> > > > > > into
> > > > > > > various locking (or lack there of) issues.
> > > > > > >
> > > > > > > Our setup is 2 databases (pg v8.1.4) on seperate machines with
> > all
> > > > db
> > > > > > > connections connected via pgpool.  We have run into situations
> > where
> > > > we
> > > > > > the
> > > > > > > 1st or 2nd database was not getting the update or insert fast
> > > > enough,
> > > > > > and a
> > > > > > > select query hitting pgpool at the same time causing the a
> > > > "mismatch"
> > > > > > error
> > > > > > > causing pgpool to degrade and stop replicating.
> > > > > > >
> > > > > > > Although I assume this must be quite a normal issue, I do not
> > see
> > > > many
> > > > > > > people talking about it here.
> > > > > > >
> > > > > > > Can someone give me a basic rundown of the "best practices" for
> > pg
> > > > > > locking
> > > > > > > on a pretty database intensive web app?
> > > > > > >
> > > > > > > On Inserts the best practice is to lock the entire table (LOCK
> > TABLE
> > > > > > ...) ?
> > > > > > >
> > > > > > > On Updates the best practice is to do a row lock (SELECT ... FOR
> > > > > > UPDATE  or
> > > > > > > FOR SHARE) ?
> > > > > > > Should you do some sort of lock on SELECTS?
> > > > > > > Do you recommend everything be wrapped in Transactions?
> > > > > > >
> > > > > > > Any advice is appreciated.
> > > > > > >
> > > > > > > Thank you.
> > > > > >
> > > > > > What you have seen is a table updating timing difference between
> > > > > > master and secondary as yo suspected. Although there's no way to
> > avoid
> > > > > > the timing difference itself, I think there are some ways to avoid
> > > > > > this problem.
> > > > > >
> > > > > > 1) use load balancing. Since this will SELECT one of the servers,
> > > > > >    there will be no chance to see the "kind mismatch error".
> > > > > >
> > > > > > 2) use table locking. This will prevent the SELECT from seeing yet
> > > > > >    updated data in the secondary server.
> > > > > >
> > > > > > I don't think SELECT FOR UPDATE will work, since the lock does not
> > > > > > block SELECT.
> > > > > > --
> > > > > > Tatsuo Ishii
> > > > > > SRA OSS, Inc. Japan
> > > > > >
> > > >
> >