[Pgpool-general] General pgpool locking issues

[Tatsuo Ishii]

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