<div dir="ltr"><br><div class="gmail_extra"><br><br><div class="gmail_quote">On Wed, Jan 22, 2014 at 9:46 PM, Justin Cooper <span dir="ltr"><<a href="mailto:jcooper@vendwatchtelematics.com" target="_blank">jcooper@vendwatchtelematics.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Oh, it's entirely our application framework's fault! <div><br></div><div>It shouldn't be waiting for a session to become free if it is also possible for sessions to be running for 5 minutes...</div>
<div><br></div></div></blockquote><div><br></div><div>Or, if it's going to wait, it should close the DB connection to pgpool while it waits!</div><div><br></div><div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr"><div></div><div>Our fix is to make the "pinger" not require a session, since it is only looking for a lockfile and doesn't need session data to do that.</div><div><br></div><div><br></div><div>
Thanks again for your helpful suggestions, Tatsuo!</div><span class="HOEnZb"><font color="#888888">
<div><br></div><div>Justin</div><div><br></div></font></span></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><br><div class="gmail_quote">On Wed, Jan 22, 2014 at 7:21 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">Justin,<br>
<br>
Thank you for the follow up.<br>
<br>
It would be nice if we could avoid something like "ping" client to<br>
fill up all available sockets of pgpool-II. Any idea anyone?<br>
<div><div><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>
<br>
> Just to follow up on this, it turns out it was a problem in our<br>
> application...<br>
><br>
> We had a long running job that was tying up the user's session, paired with<br>
> a second browser window that was making a "ping" type call every 10<br>
> seconds. These pings were stacking up and after 300s, they would tie up<br>
> all of PGPool's available sockets to apache.<br>
><br>
><br>
> Thanks for the help. I did a lot of testing with PGPool and thought it was<br>
> the source of the problem, but the more I tested the more I became<br>
> convinced that PGPool is actually working great!<br>
><br>
><br>
> Cheers,<br>
> Justin<br>
><br>
><br>
><br>
><br>
><br>
><br>
><br>
><br>
><br>
> On Thu, Jan 16, 2014 at 10:24 PM, Tatsuo Ishii <<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>> wrote:<br>
><br>
>> When the lock up happens, what "select * from pg_stat_activity"<br>
>> and "select * from pg_locks" show?<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>
>><br>
>> > Thank you, Tatsuo.<br>
>> ><br>
>> > We are still experiencing the problem once or twice per day. I am making<br>
>> > incremental changes on our live cluster after testing them on the test<br>
>> > cluster. So far we have done the following:<br>
>> ><br>
>> > -Comment out unused 2nd backend in pgpool.conf<br>
>> > -Add a connect_timeout of 10 seconds to the pg_connect() connection<br>
>> string<br>
>> > in the PHP application<br>
>> > -set sysctl net.core.somaxconn = 1024<br>
>> ><br>
>> > We just did the last step today so we will see if there is any impact.<br>
>> ><br>
>> > When the fault happens, there is work being done in the database, yet<br>
>> > "select * from pg_stat_activity;" shows only a few running queries at the<br>
>> > time. To me, this says that Apache+PHP still has the connection open to<br>
>> > pgpool.<br>
>> ><br>
>> > I'll be sure to post back if we figure it out!<br>
>> ><br>
>> > Justin<br>
>> ><br>
>> ><br>
>> ><br>
>> ><br>
>> ><br>
>> ><br>
>> ><br>
>> > On Mon, Jan 13, 2014 at 7:55 PM, Tatsuo Ishii <<a href="mailto:ishii@postgresql.org" target="_blank">ishii@postgresql.org</a>><br>
>> wrote:<br>
>> ><br>
>> >> 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>
>> >><br>
>> >> > Greetings!<br>
>> >> ><br>
>> >> > We are having an issue with PGPool and I wanted to post my analysis to<br>
>> >> this<br>
>> >> > list to see if: A). My analysis seems correct to you all and B). To<br>
>> see<br>
>> >> if<br>
>> >> > you folks might have any advice on tuning.<br>
>> >> ><br>
>> >> ><br>
>> >> > For the last month plus, we have been experiencing an intermittent<br>
>> fault<br>
>> >> > state on our production cluster. When the fault occurs, any request<br>
>> to<br>
>> >> the<br>
>> >> > Apache+PHP web server will either time out connecting, or will connect<br>
>> >> but<br>
>> >> > return with a "Could not connect to DB" message from PHP. I've done<br>
>> 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<br>
>> one<br>
>> >> > web front end running Apache+PHP, which has a MaxClients setting of<br>
>> 256,<br>
>> >> > meaning that it's possible to have 256 concurrently running processes.<br>
>> >> The<br>
>> >> > PHP application is configured to connect to PGPool 3.2.1 for its<br>
>> database<br>
>> >> > connection. PGPool is configured with max_init_children of 32 and<br>
>> >> max_pool<br>
>> >> > of 8. The application runs on 10-12 different databases, all with the<br>
>> >> same<br>
>> >> > Postgres username+password.<br>
>> >> ><br>
>> >> > When the fault occurs, it looks like this: Apache has 256 running<br>
>> >> processes<br>
>> >> > and load on the web front end drops to near 0. PGPool has all 32<br>
>> sockets<br>
>> >> > that face Apache filled, and all 256 sockets that face Postgres<br>
>> filled.<br>
>> >> > Postgres has 256 connections and its load goes to near 0. If you<br>
>> try to<br>
>> >> > connect to PGPool from the command line, it will time out in<br>
>> connecting,<br>
>> >> or<br>
>> >> > sometimes partially connect and then receive a connection closed<br>
>> message.<br>
>> >> ><br>
>> >> > Using our test cluster, I ran some tests that give me high confidence<br>
>> >> that<br>
>> >> > PGPool is actually working correctly, as are Apache and Postgres, and<br>
>> >> that<br>
>> >> > the fundamental problem is just a badly tuned configuration. This is<br>
>> the<br>
>> >> > test that shows that best:<br>
>> >> ><br>
>> >> ><br>
>> >> > 1. Stop Apache, restart PGPool<br>
>> >> > 2. Start up 100 psql command line clients to connect to PGPool<br>
>> 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>
>> >> > after 30 seconds, but in this test we don't wait that long)<br>
>> >> > 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>
>> >> ><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>
>> >> > 1. Restart PGPool<br>
>> >> > 2. Start up 10-20 psql command line clients. These are simulating<br>
>> >> 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<br>
>> >> time<br>
>> >> > taken to service each request goes up to around 15s (from < 1s).<br>
>> Psql<br>
>> >> > command line client can get through most of the time, but it takes<br>
>> >> some<br>
>> >> > time to connect as it is contending for one of the 32 slots to<br>
>> PGPool<br>
>> >> with<br>
>> >> > all of the Apache processes.<br>
>> >> > 5. At 200 clients, response time goes up more and we start to see<br>
>> >> > failures in Apache, as well as "Could not connect to DB" responses.<br>
>> >> Psql<br>
>> >> > command line client often will timeout before it gets a connection<br>
>> to<br>
>> >> > PGPool.<br>
>> >> > 6. Once lots of failures are happening at the 200 clients level,<br>
>> load<br>
>> >> on<br>
>> >> > Postgres goes to near 0 as well as load on Apache.<br>
>> >> > 7. Failure will also happen with 250 siege clients and no psql<br>
>> command<br>
>> >> > line clients running.<br>
>> >> ><br>
>> >> ><br>
>> >> > In step 4, I believe the response time from Apache goes up due to<br>
>> PGPool<br>
>> >> > having to spend so much time managing incoming connections from<br>
>> Apache as<br>
>> >> > well as managing connections to Postgres. Database load is not high<br>
>> 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<br>
>> there<br>
>> >> > are more databases being used, and occasionally high load, long<br>
>> running<br>
>> >> > queries.<br>
>> >> ><br>
>> >> > It's also notable that restarting Apache has been our fix to get<br>
>> >> 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<br>
>> timeout<br>
>> >> > specified in its config, and closes all 32 sockets, which causes<br>
>> >> everything<br>
>> >> > to resume working again.<br>
>> >> ><br>
>> >> ><br>
>> >> > In the end, I believe the problem is that Apache is just sending too<br>
>> many<br>
>> >> > requests to PGPool, and PGPool spends all of its time managing<br>
>> >> connections,<br>
>> >> > causing it to be slow at everything. That slowness and contention<br>
>> for 32<br>
>> >> > slots among up to 256 Apache processes leads to connection timeouts<br>
>> (it<br>
>> >> > should be noted that Apache seems to have no connect timeout defined<br>
>> and<br>
>> >> > will wait for a connection until the PHP max execution time is<br>
>> reached).<br>
>> >> > Once a threshold is reached, we enter a state where no Apache<br>
>> process is<br>
>> >> > able to connect to PGPool in enough time and we see the browser<br>
>> 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<br>
>> >> 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<br>
>> careful<br>
>> >> as<br>
>> >> > we do this, as there is surely an upper limit to how many PGPool<br>
>> >> processes<br>
>> >> > can be sustained and increasing that increases overhead on Postgres<br>
>> 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<br>
>> 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<br>
>> >> current<br>
>> >> > setting of 256 is clearly more than the system can handle. I suggest<br>
>> we<br>
>> >> > drop it down to 128 to begin with and monitor the results. I'd like<br>
>> 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<br>
>> '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<br>
>> not<br>
>> >> > sure at this point what a reasonable value would be, but I'm thinking<br>
>> 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<br>
>> backedn_weight<br>
>> >> of<br>
>> >> > 0. I have confirmed that whenever a client connects to PGPool and<br>
>> >> 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<br>
>> can<br>
>> >> > run, and therefore how many sockets are available to Apache.<br>
>> Increasing<br>
>> >> > this number by one increases the number of persistent connections to<br>
>> the<br>
>> >> DB<br>
>> >> > by max_pool, currently 8. Postgres is currently configured to only<br>
>> allow<br>
>> >> > 300 connections maximum, so that would need to be changed as well.<br>
>> 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<br>
>> keeps<br>
>> >> > in its cache of persistent connections to Postgres. It is currently<br>
>> set<br>
>> >> 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<br>
>> >> PGPool<br>
>> >> > by Apache, and the PGPool process servicing Apache's request does not<br>
>> >> have<br>
>> >> > a connection to that database, it will drop one and use the slot to<br>
>> make<br>
>> >> a<br>
>> >> > new connection to the requested DB on Postgres. If max_pool was set<br>
>> to<br>
>> >> 12,<br>
>> >> > this would stop happening and there would always be a persistent<br>
>> >> connection<br>
>> >> > to the db requested ready to go when requested by apache. Postgres<br>
>> would<br>
>> >> > ideally get no new db connections. Increasing from 8 to 12 would mean<br>
>> >> 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,<br>
>> and<br>
>> >> > Postgres's connection limit must all be tuned to eachother, and kept<br>
>> 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<br>
>> >> prevent<br>
>> >> > PGPool being hammered past the point that it can handle. If PGPool<br>
>> 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<br>
>> >> 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>
>> >><br>
>><br>
</div></div></blockquote></div><br></div>
</div></div></blockquote></div><br></div></div>