[Pgpool-general] General pgpool locking issues

[Tatsuo Ishii]

> 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
> > > >
> >