[pgpool-general: 530] Re: load balancing seems to be bottlenecked by performance of master

Mon May 28 22:59:29 JST 2012

What are the reasons for analysing system catalogs on primary server? 

Regards. 

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

> > Tatsuo, when pgpool needs to send query to system catalogs on
> > primary to analyze user queries?

> Yes.

> > What is the relation of this behavior with the configuration
> > parameter relcache_expire?

> relcache_expire control the life time of the cache(relcache) for
> those
> queries above. Note that relcache's life time is per session.
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp

> > Regards.
> >
> > ----- Mensaje original -----
> >
> >> > On Mon, May 21, 2012 at 6:13 PM, Lonni J Friedman
> >> > <netllama at gmail.com> wrote:
> >> >> On Mon, May 21, 2012 at 5:24 PM, Tatsuo Ishii
> >> >> <ishii at postgresql.org> wrote:
> >> >>>> On Mon, May 21, 2012 at 3:57 PM, Tatsuo Ishii
> >> >>>> <ishii at postgresql.org> wrote:
> >> >>>>>> Greetings,
> >> >>>>>> I'm running pgpool-II-3.1.2 purely for load balancing in
> >> >>>>>> front
> >> >>>>>> of a 4
> >> >>>>>> node postgresql-9.1.3 cluster (all running on
> >> >>>>>> Linux-x86_64).
> >> >>>>>> I'm
> >> >>>>>> using streaming replication with 3 hot standby servers. I
> >> >>>>>> have
> >> >>>>>> the
> >> >>>>>> weighting distributed such that the master weight is set to
> >> >>>>>> '1' and
> >> >>>>>> the three standby servers are set to '33' each. My intent
> >> >>>>>> was
> >> >>>>>> that
> >> >>>>>> this would nearly eliminate all read queries going to the
> >> >>>>>> master,
> >> >>>>>> however what I'm observing is that the overall performance
> >> >>>>>> seems to be
> >> >>>>>> bottlenecked by the performance of the master, even when
> >> >>>>>> queries
> >> >>>>>> aren't ultimately getting sent to the master.
> >> >>>>>>
> >> >>>>>> For example, there are times when the load on the master is
> >> >>>>>> much
> >> >>>>>> higher than on the standby servers, and the master's
> >> >>>>>> overall
> >> >>>>>> performance is noticeably degraded. When this occurs, I've
> >> >>>>>> found that
> >> >>>>>> establishing a database connection through pgpool with a
> >> >>>>>> SQL
> >> >>>>>> query
> >> >>>>>> that should normally go to one of the standby servers has
> >> >>>>>> very
> >> >>>>>> poor
> >> >>>>>> performance. It seems like pgpool is silently doing
> >> >>>>>> something
> >> >>>>>> with
> >> >>>>>> the master (which is under load and slower to respond),
> >> >>>>>> before
> >> >>>>>> passing
> >> >>>>>> the query itself off to a standby server.
> >> >>>>>>
> >> >>>>>> Is this expected behavior?
> >> >>>>>
> >> >>>>> Yes. Pgpool needs to send query to system catalogs on
> >> >>>>> primary
> >> >>>>> to
> >> >>>>> analyze user queries (for example, if the refereed table is
> >> >>>>> a
> >> >>>>> temporary table or not).
> >> >>>>
> >> >>>> Thanks, that's good to know, but also disappointing. This
> >> >>>> behavior is
> >> >>>> serving as a huge bottleneck in my environment. It basically
> >> >>>> means
> >> >>>> that no standby server can respond any faster than the
> >> >>>> master,
> >> >>>> which
> >> >>>> defeats much of the benefit of load balancing.
> >> >>>>
> >> >>>> Is there any way to work around this, or mitigate it to some
> >> >>>> degree?
> >> >>>> For example, I'm not using temp tables for anything in my
> >> >>>> environment,
> >> >>>> so not performing that check wouldn't have any impact on me.
> >> >>>
> >> >>> I thought the access to system catalogs are not big pain
> >> >>> because
> >> >>> pgpool has session life time caches for this. However it seems
> >> >>> they
> >> >>> might be bottle neck in your case. I would like to know what
> >> >>> kind
> >> >>> of
> >> >>> quries are major bottle neck in your case. Could you show me
> >> >>> the
> >> >>> query
> >> >>> log on the primary?
> >> >>
> >> >> I don't have a query log, as we're not logging queries. I guess
> >> >> I
> >> >> could enable query logging in pgpool if you think that's going
> >> >> to
> >> >> help
> >> >> you investigate this further, but I'm concerned that's going to
> >> >> create
> >> >> significant disk activity on the pgpool server.
> >> >>
> >> >> But the issue isn't that specific queries are a bottleneck but
> >> >> rather
> >> >> that if there's a large amount of write activity, the load on
> >> >> the
> >> >> master will be much greater than on the standby/slaves, thereby
> >> >> reducing the performance of both the master & the
> >> >> standby/slaves.
> >> >
> >> > Does the lack of reply mean that I'm basically out of luck on
> >> > this?
> >> > Personally, I see this behavior as a significant flaw in
> >> > pgpool's
> >> > design. Its not true load balancing if every query is gated by
> >> > the
> >> > performance of 1 server.
> >
> >> Any idea how to solve this?
> >> --
> >> 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

> 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/20120528/ef2ca827/attachment.html>