<div dir="ltr">Thank you, Tatsuo.<div><br></div><div>We are still experiencing the problem once or twice per day. I am making incremental changes on our live cluster after testing them on the test cluster. So far we have done the following:</div>
<div><br></div><div>-Comment out unused 2nd backend in pgpool.conf</div><div>-Add a connect_timeout of 10 seconds to the pg_connect() connection string in the PHP application</div><div>-set sysctl net.core.somaxconn = 1024</div>
<div><br></div><div>We just did the last step today so we will see if there is any impact.</div><div><br></div><div>When the fault happens, there is work being done in the database, yet "select * from pg_stat_activity;" shows only a few running queries at the time. To me, this says that Apache+PHP still has the connection open to pgpool. </div>
<div><br></div><div>I'll be sure to post back if we figure it out!</div><div><br></div><div>Justin</div><div><br></div><div><br></div><div> <br><div><br></div><div><br></div></div></div><div class="gmail_extra"><br><br>
<div class="gmail_quote">On Mon, Jan 13, 2014 at 7:55 PM, Tatsuo Ishii <span dir="ltr"><<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Thanks for posting detailed analythis. It looks really interesting.<br>
I need more time to understanding full details.<br>
<br>
In the mean time I wonder if you care about listen queue<br>
setting. Currently pgpool listens up to num_init_children*2 (which 64,<br>
in your case). However Apache connects to pgpool up to 256, which is<br>
way too low compared with 64. Also Linux allows max the listen queue<br>
to up 128 by default on most systems. You can check it by looking at:<br>
<br>
$ sysctl net.core.somaxconn<br>
net.core.somaxconn = 128<br>
<br>
128 is too low compared with 256, of course.<br>
<br>
If the allowed listen queue length (backlog) is too low, lots of retry<br>
happens in kernel's TCP layer.<br>
<br>
Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href="http://www.sraoss.co.jp/index_en.php" target="_blank">http://www.sraoss.co.jp/index_en.php</a><br>
Japanese: <a href="http://www.sraoss.co.jp" target="_blank">http://www.sraoss.co.jp</a><br>
<div><div class="h5"><br>
> Greetings!<br>
><br>
> We are having an issue with PGPool and I wanted to post my analysis to this<br>
> list to see if: A). My analysis seems correct to you all and B). To see if<br>
> you folks might have any advice on tuning.<br>
><br>
><br>
> For the last month plus, we have been experiencing an intermittent fault<br>
> state on our production cluster. When the fault occurs, any request to the<br>
> Apache+PHP web server will either time out connecting, or will connect but<br>
> return with a "Could not connect to DB" message from PHP. I've done some<br>
> analysis on the problem and this is what I've found.<br>
><br>
> First let me describe the cluster as it is configured today. We have one<br>
> web front end running Apache+PHP, which has a MaxClients setting of 256,<br>
> meaning that it's possible to have 256 concurrently running processes. The<br>
> PHP application is configured to connect to PGPool 3.2.1 for its database<br>
> connection. PGPool is configured with max_init_children of 32 and max_pool<br>
> of 8. The application runs on 10-12 different databases, all with the same<br>
> Postgres username+password.<br>
><br>
> When the fault occurs, it looks like this: Apache has 256 running processes<br>
> and load on the web front end drops to near 0. PGPool has all 32 sockets<br>
> that face Apache filled, and all 256 sockets that face Postgres filled.<br>
> Postgres has 256 connections and its load goes to near 0. If you try to<br>
> connect to PGPool from the command line, it will time out in connecting, or<br>
> sometimes partially connect and then receive a connection closed message.<br>
><br>
> Using our test cluster, I ran some tests that give me high confidence that<br>
> PGPool is actually working correctly, as are Apache and Postgres, and that<br>
> the fundamental problem is just a badly tuned configuration. This is the<br>
> test that shows that best:<br>
><br>
><br>
</div></div>> 1. Stop Apache, restart PGPool<br>
> 2. Start up 100 psql command line clients to connect to PGPool with a<br>
> single database<br>
> 3. The first 32 psql clients connect and work fine<br>
> 4. The 33rd psql client blocks waiting to connect (it will time out<br>
<div class="im">> after 30 seconds, but in this test we don't wait that long)<br>
</div>> 5. fg the psql client #1, then exit the client, freeing up one of<br>
> PGPool's connections<br>
> 6. One of the 68 blocking psql clients now gets through and can run<br>
> queries<br>
> 7. Any of the 32 connected psql clients can get through as well<br>
<div class="im">><br>
> This shows that PGPool is working as expected.<br>
><br>
> Now we try a test that is more like the real world:<br>
><br>
</div>> 1. Restart PGPool<br>
> 2. Start up 10-20 psql command line clients. These are simulating long<br>
> running php processes.<br>
> 3. Start siege web testing tool with 100-200 concurrent requests to<br>
> Apache.<br>
> 4. At 100 clients, the response time from Apache slows down and the time<br>
<div class="im">> taken to service each request goes up to around 15s (from < 1s). Psql<br>
> command line client can get through most of the time, but it takes some<br>
> time to connect as it is contending for one of the 32 slots to PGPool with<br>
> all of the Apache processes.<br>
</div>> 5. At 200 clients, response time goes up more and we start to see<br>
<div class="im">> failures in Apache, as well as "Could not connect to DB" responses. Psql<br>
> command line client often will timeout before it gets a connection to<br>
> PGPool.<br>
</div>> 6. Once lots of failures are happening at the 200 clients level, load on<br>
<div class="im">> Postgres goes to near 0 as well as load on Apache.<br>
</div>> 7. Failure will also happen with 250 siege clients and no psql command<br>
<div class="HOEnZb"><div class="h5">> line clients running.<br>
><br>
><br>
> In step 4, I believe the response time from Apache goes up due to PGPool<br>
> having to spend so much time managing incoming connections from Apache as<br>
> well as managing connections to Postgres. Database load is not high in<br>
> this case, so the slowness is not due to Postgres being overloaded.<br>
><br>
> I believe that on the live cluster the load is even more severe as there<br>
> are more databases being used, and occasionally high load, long running<br>
> queries.<br>
><br>
> It's also notable that restarting Apache has been our fix to get everything<br>
> running again. I believe that this is because PGPool gets a chance to<br>
> catch up, which it does fairly quickly, and resumes with 32 available<br>
> sockets for Apache. If we do nothing, PGPool reaches a 10 minute timeout<br>
> specified in its config, and closes all 32 sockets, which causes everything<br>
> to resume working again.<br>
><br>
><br>
> In the end, I believe the problem is that Apache is just sending too many<br>
> requests to PGPool, and PGPool spends all of its time managing connections,<br>
> causing it to be slow at everything. That slowness and contention for 32<br>
> slots among up to 256 Apache processes leads to connection timeouts (it<br>
> should be noted that Apache seems to have no connect timeout defined and<br>
> will wait for a connection until the PHP max execution time is reached).<br>
> Once a threshold is reached, we enter a state where no Apache process is<br>
> able to connect to PGPool in enough time and we see the browser requests<br>
> either timing out entirely or returning the "Could not connect to DB"<br>
> message.<br>
><br>
><br>
> The proposed solution to all of this is to adjust the configuration of<br>
> PGPool and Apache to ensure that we can never reach this overwhelmed state.<br>
> Specifically, we need to increase the number of PGPool processes and<br>
> decrease the maximum number of Apache processes. We need to be careful as<br>
> we do this, as there is surely an upper limit to how many PGPool processes<br>
> can be sustained and increasing that increases overhead on Postgres since<br>
> it increases the number of persistent open connections between it and<br>
> PGPool. The same for Apache, we need to lower MaxClients but not so low<br>
> that it turns away requests that could have been handled.<br>
><br>
><br>
> There are a few other adjustments that I believe will help that I'll<br>
> describe below.<br>
><br>
> Apache MaxClients:<br>
> This is how many concurrent Apache processes can run at once. The current<br>
> setting of 256 is clearly more than the system can handle. I suggest we<br>
> drop it down to 128 to begin with and monitor the results. I'd like to<br>
> make this change before the others.<br>
><br>
> Apache PHP DB connection timeout:<br>
> I can see that it's waiting as long as 150s before returning with 'Could<br>
> not connect to DB' at times, which indicates that no timeout is being<br>
> specified. This must be sent as part of the connection string, like:<br>
> "pgsql:host=127.0.0.1;port=5432;dbname=vw_bepensa;timeout=10". I'm not<br>
> sure at this point what a reasonable value would be, but I'm thinking 10<br>
> seconds is a good start.<br>
><br>
> PGPool backends:<br>
> We currently have 2 backends specified in the config. One has<br>
> backend_weight of 1 and the other, that is not used, has backedn_weight of<br>
> 0. I have confirmed that whenever a client connects to PGPool and requests<br>
> a connection to a database, for example, PGPool opens a persistent<br>
> connection to both backends. We will comment out the backend that<br>
> specifies the backup server, which should help PGPool a lot.<br>
><br>
><br>
> PGPool max_init_children:<br>
> This is the config parameter that specifies how many PGPool processes can<br>
> run, and therefore how many sockets are available to Apache. Increasing<br>
> this number by one increases the number of persistent connections to the DB<br>
> by max_pool, currently 8. Postgres is currently configured to only allow<br>
> 300 connections maximum, so that would need to be changed as well. More<br>
> research and testing is needed to find the sweet spot.<br>
><br>
> PGPool max_pool:<br>
> This parameter specifies how many different DBs each PGPool process keeps<br>
> in its cache of persistent connections to Postgres. It is currently set to<br>
> 8, yet we have more than 8 different databases in production (I see 12<br>
> connected right now). If a connection to a database is requested of PGPool<br>
> by Apache, and the PGPool process servicing Apache's request does not have<br>
> a connection to that database, it will drop one and use the slot to make a<br>
> new connection to the requested DB on Postgres. If max_pool was set to 12,<br>
> this would stop happening and there would always be a persistent connection<br>
> to the db requested ready to go when requested by apache. Postgres would<br>
> ideally get no new db connections. Increasing from 8 to 12 would mean that<br>
> total connections to Postgres would be 32*12 = 384, which is above<br>
> Postgres's connection limit. So this parameter, max_init_children, and<br>
> Postgres's connection limit must all be tuned to eachother, and kept low<br>
> enough to not overwhelm Postgres.<br>
><br>
><br>
> I suggest that we begin by commenting out the second backend in<br>
> pgpool.conf, and lowering MaxClients on Apache to 128. This should prevent<br>
> PGPool being hammered past the point that it can handle. If PGPool does<br>
> fall behind, only 128 Apache connections will be hitting PGPool and it<br>
> seems to be able to handle that many in an orderly fashion.<br>
><br>
> I also think adding a PHP connection timeout will help keep the system from<br>
> grinding to a stop.<br>
><br>
><br>
> Thank you for reading and any help or insight you can provide!<br>
><br>
> Justin Cooper<br>
</div></div></blockquote></div><br></div>