idle connections

Hi,

Im seeing lots of idle connections (not idle in transaction) to my database
server. My front end is written with PHP and i couldnt find anything that
can cause this. If i do a kill proc-id every few minutes on my server via
cron, would this effect anything badly?

Thanks

Is there a way to compile postgres with static libraries instead of dynamic?

I know it will make the binaries bigger but I have a need to do this.

I've been reading through the make/automake, cc, gcc and ld man pages
AND googling a bit. Not getting it to run properly. When I run the
./configure it says cc cannot create a test object when I try -d n, -B
static or -static.

What am I missing?

I'm thinking this is something I need to do with ld and use LDFLAGS but
that's just a guess.

Thanks!

Aras Angelo wrote:
> Hi,
>
> Im seeing lots of idle connections (not idle in transaction) to my
> database server. My front end is written with PHP and i couldnt find
> anything that can cause this. If i do a kill proc-id every few minutes
> on my server via cron, would this effect anything badly?
>
> Thanks
Before you go killing things willy-nilly, try diagnosing the problem -
papering over problems is almost never a good "solution". In fact,
depending on your designs and workloads, this might be a good thing (if
intentional and understood).

Use (as root) "lsof -i :5432" or your favorite equivalent for your OS.

First run it on the database server to verify that the connections are
from your webserver. If so, run it on your webserver and verify that the
processes are what you think they are.

If PHP (which will probably show up as an Apache or whatever webserver
you are running process), you may have one or more scripts opening
persistent connections. Or a bug. I've seen PHP scripts hang leaving an
open connection to the database. Or it could be a persistent connection
opened by a Perl/Python/Ruby/whatever-else-you-have program so you may
have to check those as well. Perhaps look at the connection start time
in PG and try to correlate it with a request in your webserver log.

I assume you would have told us if you are running pgbouncer or similar
pooling solution as that would be an obvious cause.

Cheers,
Steve

On Mon, Oct 5, 2009 at 4:42 PM, Aras Angelo <araskoktas(at)gmail(dot)com> wrote:
> Hi,
>
> Im seeing lots of idle connections (not idle in transaction) to my database
> server. My front end is written with PHP and i couldnt find anything that
> can cause this. If i do a kill proc-id every few minutes on my server via
> cron, would this effect anything badly?

Are you using pg_pconnect?

pg_pconnect is a foot gun waiting to happen. It's an otherwise very
useful foot gun, but a foot gun none-the-less.

The problem is that by default apache is usually set up to have more
max connections / children / threads etc. than postgresql is to have
backends available. This just gets worse if you run < 1 apache server
machine.

The simple solution is to turn off pg_pconnect.

If things are then too slow then you can start planning for
connection pooling / pg_pconnect otherwise don't sweat it. For low
level intranet servers, regular pg_connect will work just fine.

Its a regular pg_connect()

When i kill the earliest idle process the others stop too. So i dont know
whats wrong really. All our apps use the same footer, with pg_close() at the
end.

I have done most of the things you guys suggested, so it seems to me that
something between php-apache-postgresql is not doing good, and it only
effects us at peak times, so just wondering if killing processes every 2-3
minutes, would do harm on our setup.

I have 3 apache servers reading from one single db server.

On Mon, Oct 5, 2009 at 4:48 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Mon, Oct 5, 2009 at 4:42 PM, Aras Angelo <araskoktas(at)gmail(dot)com> wrote:
> > Hi,
> >
> > Im seeing lots of idle connections (not idle in transaction) to my
> database
> > server. My front end is written with PHP and i couldnt find anything that
> > can cause this. If i do a kill proc-id every few minutes on my server via
> > cron, would this effect anything badly?
>
> Are you using pg_pconnect?
>
> pg_pconnect is a foot gun waiting to happen. It's an otherwise very
> useful foot gun, but a foot gun none-the-less.
>
> The problem is that by default apache is usually set up to have more
> max connections / children / threads etc. than postgresql is to have
> backends available. This just gets worse if you run < 1 apache server
> machine.
>
> The simple solution is to turn off pg_pconnect.
>
> If things are then too slow then you can start planning for
> connection pooling / pg_pconnect otherwise don't sweat it. For low
> level intranet servers, regular pg_connect will work just fine.
>

On Mon, Oct 5, 2009 at 5:50 PM, Aras Angelo <araskoktas(at)gmail(dot)com> wrote:
> Its a regular pg_connect()
>
> When i kill the earliest idle process the others stop too. So i dont know
> whats wrong really. All our apps use the same footer, with pg_close() at the
> end.
>
> I have done most of the things you guys suggested, so it seems to me that
> something between php-apache-postgresql is not doing good, and it only
> effects us at peak times, so just wondering if killing processes every 2-3
> minutes, would do harm on our setup.
>
> I have 3 apache servers reading from one single db server.

php automagically cleans up old connections etc upon script exit, so
either your scripts aren't exiting, or they're crashing before they
can exit I'd guess. Or you've got a REALLY busy apache server that
has that many connections open at once.

Anything in your apache or php logs that offers a clue?

On Mon, Oct 5, 2009 at 5:50 PM, Aras Angelo <araskoktas(at)gmail(dot)com> wrote:
> Its a regular pg_connect()
>
> When i kill the earliest idle process the others stop too. So i dont know
> whats wrong really. All our apps use the same footer, with pg_close() at the
> end.
>
> I have done most of the things you guys suggested, so it seems to me that
> something between php-apache-postgresql is not doing good, and it only
> effects us at peak times, so just wondering if killing processes every 2-3
> minutes, would do harm on our setup.
>
> I have 3 apache servers reading from one single db server.

So what's your max apache children / threads and what's your max pgsql
connections allowed? You may need to crank up pgsql connection limit
to keep up.

Aras Angelo wrote:
> ...
> When i kill the earliest idle process the others stop too. So i dont
> know whats wrong really. All our apps use the same footer, with
> pg_close() at the end....
> ...
> I have done most of the things you guys suggested, so it seems to me
> that something between php-apache-postgresql is not doing good, and it
> only effects us at peak times, so just wondering if killing processes
> every 2-3 minutes, would do harm on our setup....

Are you killing web processes or postgresql processes? If web, you will
probably end up impacting at least some of you users directly. If
PostgreSQL, then you will undoubtedly end up yanking the rug out from
under a process that expected a working database connection. So yes,
you might cause harm. Worse still, you may not solve the problem.

When I hear symptoms like this, especially when they tend to happen
under load, I start looking for a process that is slowing/blocking the
other processes.

Consider a fairly typical PHP script. It might start out opening a
database connection and then do various queries interspersed with
processing. If one process hogs sufficient resources or is holding some
resource or lock that slows or blocks the other PHP processes then you
could easily see numerous idle PG processes. Kill the "offending"
process and the others will often rapidly complete thus releasing their
connections.

If you have enough traffic to justify three webservers then you probably
have enough traffic to deplete your available pg connections in seconds,
not minutes so keep digging.

Cheers,
Steve

Thank you for the informative post.

We believe this was caused by a network issue yesterday after checking our
network speeds. We were maxing our ethernet port at 100 mbps, i believe some
apache processes were hanging because of this issue, having no available
bandwidth left. Considering an upgrade to a gbps port and we will see how
this will effect.

On Tue, Oct 6, 2009 at 9:28 AM, Steve Crawford <
scrawford(at)pinpointresearch(dot)com> wrote:

> Aras Angelo wrote:
>
>> ...
>> When i kill the earliest idle process the others stop too. So i dont know
>> whats wrong really. All our apps use the same footer, with pg_close() at the
>> end....
>> ...
>> I have done most of the things you guys suggested, so it seems to me that
>> something between php-apache-postgresql is not doing good, and it only
>> effects us at peak times, so just wondering if killing processes every 2-3
>> minutes, would do harm on our setup....
>>
>
> Are you killing web processes or postgresql processes? If web, you will
> probably end up impacting at least some of you users directly. If
> PostgreSQL, then you will undoubtedly end up yanking the rug out from under
> a process that expected a working database connection. So yes, you might
> cause harm. Worse still, you may not solve the problem.
>
> When I hear symptoms like this, especially when they tend to happen under
> load, I start looking for a process that is slowing/blocking the other
> processes.
>
> Consider a fairly typical PHP script. It might start out opening a database
> connection and then do various queries interspersed with processing. If one
> process hogs sufficient resources or is holding some resource or lock that
> slows or blocks the other PHP processes then you could easily see numerous
> idle PG processes. Kill the "offending" process and the others will often
> rapidly complete thus releasing their connections.
>
> If you have enough traffic to justify three webservers then you probably
> have enough traffic to deplete your available pg connections in seconds, not
> minutes so keep digging.
>
> Cheers,
> Steve
>
>

Aras Angelo wrote:
> Thank you for the informative post.
>
> We believe this was caused by a network issue yesterday after checking
> our network speeds. We were maxing our ethernet port at 100 mbps....
Ah, yes. In a previous life we once had a very successful internal
load-test turn into a rapid and complete meltdown in real life because
all the modem users kept processes occupied for long periods while the
data dribbled back and the server maxed out on available threads/processes.

Cheers,
Steve

So I compiled postgres with Solaris 10 and have problems running it.

# ./pg_ctl
ld.so.1: pg_ctl: fatal: relocation error: R_AMD64_32: file
/usr/local/postgres64/lib/libpq.so.5: symbol (unknown): value
0xfffffd7fff1cf210 does not fit
Killed

# ldd pg_ctl
libpq.so.5 => /usr/local/postgres64/lib/libpq.so.5
libm.so.2 => /usr/lib/64/libm.so.2
libxml2.so.2 => /usr/lib/64/libxml2.so.2
libz.so.1 => /usr/lib/64/libz.so.1
libreadline.so.6 => /usr/local/lib/libreadline.so.6
libcurses.so.1 => /usr/lib/64/libcurses.so.1
librt.so.1 => /usr/lib/64/librt.so.1
libsocket.so.1 => /usr/lib/64/libsocket.so.1
libc.so.1 => /usr/lib/64/libc.so.1
libpthread.so.1 => /usr/lib/64/libpthread.so.1
libnsl.so.1 => /lib/64/libnsl.so.1
libgcc_s.so.1 => /usr/sfw/lib/amd64/libgcc_s.so.1
libaio.so.1 => /lib/64/libaio.so.1
libmd.so.1 => /lib/64/libmd.so.1
libmp.so.2 => /lib/64/libmp.so.2
libscf.so.1 => /lib/64/libscf.so.1
libdoor.so.1 => /lib/64/libdoor.so.1
libuutil.so.1 => /lib/64/libuutil.so.1
libgen.so.1 => /lib/64/libgen.so.1

# file /usr/local/postgres64/lib/libpq.so.5
/usr/local/postgres64/lib/libpq.so.5: ELF 64-bit LSB dynamic lib AMD64
Version 1 [SSE CMOV], dynamically linked, not stripped


What am I missing???

Here's my environment.

Solaris 10 x86_64 with postgres 8.3.8 and openssl 98k using gcc version
3.4.3 (csl-sol210-3_4-branch+sol_rpath)
, sunstudio12.1 and GNU Make 3.80

Thanks!

On Tue, Oct 6, 2009 at 11:46 PM, u235sentinel <u235sentinel(at)gmail(dot)com>wrote:

> So I compiled postgres with Solaris 10 and have problems running it.
>
> # ./pg_ctl
> ld.so.1: pg_ctl: fatal: relocation error: R_AMD64_32: file
> /usr/local/postgres64/lib/libpq.so.5: symbol (unknown): value
> 0xfffffd7fff1cf210 does not fit
> Killed
>
> # ldd pg_ctl
> libpq.so.5 => /usr/local/postgres64/lib/libpq.so.5
> libm.so.2 => /usr/lib/64/libm.so.2
> libxml2.so.2 => /usr/lib/64/libxml2.so.2
> libz.so.1 => /usr/lib/64/libz.so.1
> libreadline.so.6 => /usr/local/lib/libreadline.so.6
> libcurses.so.1 => /usr/lib/64/libcurses.so.1
> librt.so.1 => /usr/lib/64/librt.so.1
> libsocket.so.1 => /usr/lib/64/libsocket.so.1
> libc.so.1 => /usr/lib/64/libc.so.1
> libpthread.so.1 => /usr/lib/64/libpthread.so.1
> libnsl.so.1 => /lib/64/libnsl.so.1
> libgcc_s.so.1 => /usr/sfw/lib/amd64/libgcc_s.so.1
> libaio.so.1 => /lib/64/libaio.so.1
> libmd.so.1 => /lib/64/libmd.so.1
> libmp.so.2 => /lib/64/libmp.so.2
> libscf.so.1 => /lib/64/libscf.so.1
> libdoor.so.1 => /lib/64/libdoor.so.1
> libuutil.so.1 => /lib/64/libuutil.so.1
> libgen.so.1 => /lib/64/libgen.so.1
>
> # file /usr/local/postgres64/lib/libpq.so.5
> /usr/local/postgres64/lib/libpq.so.5: ELF 64-bit LSB dynamic lib AMD64
> Version 1 [SSE CMOV], dynamically linked, not stripped
>
>
> What am I missing???
>
> Here's my environment.
>
> Solaris 10 x86_64 with postgres 8.3.8 and openssl 98k using gcc version
> 3.4.3 (csl-sol210-3_4-branch+sol_rpath)
> , sunstudio12.1 and GNU Make 3.80
>
> Thanks!
>
>

could you please try to start the Postgres service using below steps

cd <installed postgres directory>/bin

./pg_ctl -D < data directory path> start

Thanks & Regards
Raghu

raghu ram wrote:
>
>
>
>
> could you please try to start the Postgres service using below steps
>
> cd <installed postgres directory>/bin
>
> ./pg_ctl -D < data directory path> start
>
>
> Thanks & Regards
> Raghu

I've tried to initially start it and am getting the same results with
the above command :/