Обсуждение: 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
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@gmail.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.
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.marlowe@gmail.com> wrote:
Are you using pg_pconnect?On Mon, Oct 5, 2009 at 4:42 PM, Aras Angelo <araskoktas@gmail.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?
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@gmail.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@gmail.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.
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@pinpointresearch.com> wrote:
Aras Angelo wrote:...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....
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....
...
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@gmail.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 :/