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

[Tatsuo Ishii]

> 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