<html><head><style type='text/css'>p { margin: 0; }</style></head><body><div style='font-family: Times New Roman; font-size: 14pt; color: #000000'>Tatsuo, when pgpool needs to send query to system catalogs on primary to analyze user queries?<br><br>What is the relation of this behavior with the configuration parameter relcache_expire?<br><br>Regards.<br><br><br><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;">> On Mon, May 21, 2012 at 6:13 PM, Lonni J Friedman <netllama@gmail.com> wrote:<br>>> On Mon, May 21, 2012 at 5:24 PM, Tatsuo Ishii <ishii@postgresql.org> wrote:<br>>>>> On Mon, May 21, 2012 at 3:57 PM, Tatsuo Ishii <ishii@postgresql.org> wrote:<br>>>>>>> Greetings,<br>>>>>>> I'm running pgpool-II-3.1.2 purely for load balancing in front of a 4<br>>>>>>> node postgresql-9.1.3 cluster (all running on Linux-x86_64). I'm<br>>>>>>> using streaming replication with 3 hot standby servers. I have the<br>>>>>>> weighting distributed such that the master weight is set to '1' and<br>>>>>>> the three standby servers are set to '33' each. My intent was that<br>>>>>>> this would nearly eliminate all read queries going to the master,<br>>>>>>> however what I'm observing is that the overall performance seems to be<br>>>>>>> bottlenecked by the performance of the master, even when queries<br>>>>>>> aren't ultimately getting sent to the master.<br>>>>>>><br>>>>>>> For example, there are times when the load on the master is much<br>>>>>>> higher than on the standby servers, and the master's overall<br>>>>>>> performance is noticeably degraded. When this occurs, I've found that<br>>>>>>> establishing a database connection through pgpool with a SQL query<br>>>>>>> that should normally go to one of the standby servers has very poor<br>>>>>>> performance. It seems like pgpool is silently doing something with<br>>>>>>> the master (which is under load and slower to respond), before 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 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 behavior is<br>>>>> serving as a huge bottleneck in my environment. It basically means<br>>>>> that no standby server can respond any faster than the master, 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 degree?<br>>>>> For example, I'm not using temp tables for anything in my 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 they<br>>>> might be bottle neck in your case. I would like to know what kind of<br>>>> quries are major bottle neck in your case. Could you show me the 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 help<br>>> you investigate this further, but I'm concerned that's going to create<br>>> significant disk activity on the pgpool server.<br>>><br>>> But the issue isn't that specific queries are a bottleneck but 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 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></body>
<br><html>
<body>
<a href="http://www.uci.cu/">
<img src="http://universidad.uci.cu/email.gif"/>
</body>
</html>
<br></html>