<html><head><style type='text/css'>p { margin: 0; }</style></head><body><div style='font-family: Times New Roman; font-size: 14pt; color: #000000'>What are the reasons for analysing system catalogs on primary server?<br><br>Regards.<br><br><hr id="zwchr"><blockquote style="border-left:2px solid rgb(16, 16, 255);margin-left:5px;padding-left:5px;color:#000;font-weight:normal;font-style:normal;text-decoration:none;font-family:Helvetica,Arial,sans-serif;font-size:12pt;">> Tatsuo, when pgpool needs to send query to system catalogs on primary to analyze user queries? <br><br>Yes.<br><br>> What is the relation of this behavior with the configuration parameter relcache_expire? <br><br>relcache_expire control the life time of the cache(relcache) for those<br>queries above. Note that relcache's life time is per session.<br>--<br>Tatsuo Ishii<br>SRA OSS, Inc. Japan<br>English: http://www.sraoss.co.jp/index_en.php<br>Japanese: http://www.sraoss.co.jp<br><br>> Regards. <br>> <br>> ----- Mensaje original -----<br>> <br>>> > On Mon, May 21, 2012 at 6:13 PM, Lonni J Friedman<br>>> > <netllama@gmail.com> wrote:<br>>> >> On Mon, May 21, 2012 at 5:24 PM, Tatsuo Ishii<br>>> >> <ishii@postgresql.org> wrote:<br>>> >>>> On Mon, May 21, 2012 at 3:57 PM, Tatsuo Ishii<br>>> >>>> <ishii@postgresql.org> wrote:<br>>> >>>>>> Greetings,<br>>> >>>>>> I'm running pgpool-II-3.1.2 purely for load balancing in front<br>>> >>>>>> of a 4<br>>> >>>>>> node postgresql-9.1.3 cluster (all running on Linux-x86_64).<br>>> >>>>>> I'm<br>>> >>>>>> using streaming replication with 3 hot standby servers. I have<br>>> >>>>>> the<br>>> >>>>>> weighting distributed such that the master weight is set to<br>>> >>>>>> '1' and<br>>> >>>>>> the three standby servers are set to '33' each. My intent was<br>>> >>>>>> that<br>>> >>>>>> this would nearly eliminate all read queries going to the<br>>> >>>>>> master,<br>>> >>>>>> however what I'm observing is that the overall performance<br>>> >>>>>> seems to be<br>>> >>>>>> bottlenecked by the performance of the master, even when<br>>> >>>>>> queries<br>>> >>>>>> aren't ultimately getting sent to the master.<br>>> >>>>>><br>>> >>>>>> For example, there are times when the load on the master is<br>>> >>>>>> much<br>>> >>>>>> higher than on the standby servers, and the master's overall<br>>> >>>>>> performance is noticeably degraded. When this occurs, I've<br>>> >>>>>> found that<br>>> >>>>>> establishing a database connection through pgpool with a SQL<br>>> >>>>>> query<br>>> >>>>>> that should normally go to one of the standby servers has very<br>>> >>>>>> poor<br>>> >>>>>> performance. It seems like pgpool is silently doing something<br>>> >>>>>> with<br>>> >>>>>> the master (which is under load and slower to respond), before<br>>> >>>>>> passing<br>>> >>>>>> the query itself off to a standby server.<br>>> >>>>>><br>>> >>>>>> Is this expected behavior?<br>>> >>>>><br>>> >>>>> Yes. Pgpool needs to send query to system catalogs on primary<br>>> >>>>> to<br>>> >>>>> analyze user queries (for example, if the refereed table is a<br>>> >>>>> temporary table or not).<br>>> >>>><br>>> >>>> Thanks, that's good to know, but also disappointing. This<br>>> >>>> behavior is<br>>> >>>> serving as a huge bottleneck in my environment. It basically<br>>> >>>> means<br>>> >>>> that no standby server can respond any faster than the master,<br>>> >>>> which<br>>> >>>> defeats much of the benefit of load balancing.<br>>> >>>><br>>> >>>> Is there any way to work around this, or mitigate it to some<br>>> >>>> degree?<br>>> >>>> For example, I'm not using temp tables for anything in my<br>>> >>>> environment,<br>>> >>>> so not performing that check wouldn't have any impact on me.<br>>> >>><br>>> >>> I thought the access to system catalogs are not big pain because<br>>> >>> pgpool has session life time caches for this. However it seems<br>>> >>> they<br>>> >>> might be bottle neck in your case. I would like to know what kind<br>>> >>> of<br>>> >>> quries are major bottle neck in your case. Could you show me the<br>>> >>> query<br>>> >>> log on the primary?<br>>> >><br>>> >> I don't have a query log, as we're not logging queries. I guess I<br>>> >> could enable query logging in pgpool if you think that's going to<br>>> >> help<br>>> >> you investigate this further, but I'm concerned that's going to<br>>> >> create<br>>> >> significant disk activity on the pgpool server.<br>>> >><br>>> >> But the issue isn't that specific queries are a bottleneck but<br>>> >> rather<br>>> >> that if there's a large amount of write activity, the load on the<br>>> >> master will be much greater than on the standby/slaves, thereby<br>>> >> reducing the performance of both the master & the standby/slaves.<br>>> ><br>>> > Does the lack of reply mean that I'm basically out of luck on this?<br>>> > Personally, I see this behavior as a significant flaw in pgpool's<br>>> > design. Its not true load balancing if every query is gated by the<br>>> > performance of 1 server.<br>> <br>>> Any idea how to solve this?<br>>> --<br>>> Tatsuo Ishii<br>>> SRA OSS, Inc. Japan<br>>> English: http://www.sraoss.co.jp/index_en.php<br>>> Japanese: http://www.sraoss.co.jp<br>>> _______________________________________________<br>>> pgpool-general mailing list<br>>> pgpool-general@pgpool.net<br>>> http://www.pgpool.net/mailman/listinfo/pgpool-general<br>> <br>>> 10mo. ANIVERSARIO DE LA CREACION DE LA UNIVERSIDAD DE LAS CIENCIAS<br>>> INFORMATICAS...<br>>> CONECTADOS AL FUTURO, CONECTADOS A LA REVOLUCION<br>> <br>>> http://www.uci.cu<br>>> http://www.facebook.com/universidad.uci<br>>> http://www.flickr.com/photos/universidad_uci<br>> <br>> <br>> 10mo. ANIVERSARIO DE LA CREACION DE LA UNIVERSIDAD DE LAS CIENCIAS INFORMATICAS...<br>> CONECTADOS AL FUTURO, CONECTADOS A LA REVOLUCION<br>> <br>> http://www.uci.cu<br>> http://www.facebook.com/universidad.uci<br>> http://www.flickr.com/photos/universidad_uci<br><br>10mo. ANIVERSARIO DE LA CREACION DE LA UNIVERSIDAD DE LAS CIENCIAS INFORMATICAS...<br>CONECTADOS AL FUTURO, CONECTADOS A LA REVOLUCION<br><br>http://www.uci.cu<br>http://www.facebook.com/universidad.uci<br>http://www.flickr.com/photos/universidad_uci<br></blockquote><br></div>
<br><html>
<body>
<a href="http://www.uci.cu/">
<img src="http://universidad.uci.cu/email.gif"/>
</body>
</html>
<br></body></html>