Обсуждение: connections slowing everything down?
Hi # ps -ef | grep idle | wc -l 87 # ps -ef | grep SELECT | wc -l 5 I have 2 web servers which connect to PGPool which connects to our postgres db. I have noticed that idle connections seem to take up CPU and RAM (according to top). Could this in any way cause things to slow down? -- Adrian Moisey Systems Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: adrian@careerjunction.co.za Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842
On Apr 21, 2008, at 4:50 AM, Adrian Moisey wrote: > Hi > > # ps -ef | grep idle | wc -l > 87 > # ps -ef | grep SELECT | wc -l > 5 > > > I have 2 web servers which connect to PGPool which connects to our > postgres db. I have noticed that idle connections seem to take up > CPU and RAM (according to top). Could this in any way cause things > to slow down? Dependant on how much memory you have in your system, yes. You can fix the constant use of memory by idle connections by adjusting the child_life_time setting in your pgpool.conf file. The default if 5 minutes which a bit long. Try dropping that down to 20 or 30 seconds. Erik Jones DBA | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
Hi >> # ps -ef | grep idle | wc -l >> 87 [...] >> I have 2 web servers which connect to PGPool which connects to our >> postgres db. I have noticed that idle connections seem to take up CPU >> and RAM (according to top). Could this in any way cause things to >> slow down? > > Dependant on how much memory you have in your system, yes. You can fix > the constant use of memory by idle connections by adjusting the > child_life_time setting in your pgpool.conf file. The default if 5 > minutes which a bit long. Try dropping that down to 20 or 30 seconds. We have 32GBs. If I get it to close the connections faster, will that actually help? Is there a way i can figure it out? -- Adrian Moisey Systems Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: adrian@careerjunction.co.za Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842
On Apr 21, 2008, at 9:15 AM, Adrian Moisey wrote: > Hi > >>> # ps -ef | grep idle | wc -l >>> 87 > [...] > >>> I have 2 web servers which connect to PGPool which connects to our >>> postgres db. I have noticed that idle connections seem to take up >>> CPU and RAM (according to top). Could this in any way cause >>> things to slow down? >> Dependant on how much memory you have in your system, yes. You can >> fix the constant use of memory by idle connections by adjusting the >> child_life_time setting in your pgpool.conf file. The default if 5 >> minutes which a bit long. Try dropping that down to 20 or 30 >> seconds. > > We have 32GBs. If I get it to close the connections faster, will > that actually help? Is there a way i can figure it out? First, sorry, I gave you the wrong config setting, I meant connection_life_time. child_life_time is the lifetime of an idle pool process on the client machine and the connection_life_time is the lifetime of an idle connection (i.e. no transaction running) on the server. With the default connection_life_time of 5 minutes it's easily possible to keep an connection open indefinitely. Imagine a client gets a connection and runs a single query, then nothing happens on that connection for 4:30 minutes at which point another single query is run. If that pattern continues that connection will never be relinquished. While the point of a pool is to cut down on the number of connections that need to be established, you don't necessarily want to go the extreme and never tear down connections as that will cause a degradation in available server resources. With a smaller, but not 0, connection life time, connections will stay open and available during periods of high work rates from the client, but will be relinquished when there isn't as much to do. Without more details on what exactly is happening on your system I can't say for sure that this is your fix. Are you tracking/monitoring your server's free memory? If not I'd suggest getting either Cacti or Monit in place to monitor system stats such as free memory (using vmstat), system IO (using iostat), db transaction rates (using db queries). Then you'll be able to draw correlations between application behavior (slowness, etc) and actual system numbers. I know that I had issues with connections being held open for long times (using the default 300s) causing our free memory to gradually decrease over the day and resetting our pools would clear it out so there was a direct cause and effect relationship there. When I dropped the connection_life_time to 30s the problem went away. Erik Jones DBA | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
On Mon, Apr 21, 2008 at 5:50 AM, Adrian Moisey <adrian@careerjunction.co.za> wrote: > Hi > > # ps -ef | grep idle | wc -l > 87 > # ps -ef | grep SELECT | wc -l > 5 > > > I have 2 web servers which connect to PGPool which connects to our postgres > db. I have noticed that idle connections seem to take up CPU and RAM > (according to top). Could this in any way cause things to slow down? Something is not quite with your assumptions. On an unloaded server, open a bunch of connections (like 500) from psql doing nothing, and cpu load will stay at zero. IOW, an 'idle' connection does not consume any measurable CPU resources once connected. It does consume some ram but that would presumably at least partly swap out eventually. What's probably going on here is your connections are not really idle. Top by default aggregates usage every three seconds and ps is more of a snapshot. During the top a single connection might accept and dispose 0, 1, 50, 100, or 1000 queries depending on various factors. Your sampling methods are simply not accurate enough. With statement level logging on (with pid on the log line), you can break out and measure query activity by connection. merlin