Thread: Disconnecting non active (IDLE ) users
I have installed POSTGRESQL 8.2 on W2K3 32bit , 100 users connecting from desktop applications and 200 users connecting thru web service from handheld computers I have problem with second groups of users. Often they do not disconnect from POSTGRE Server and with time passing thru I have lot of IDLE users and very much memory consumptions. From time to time that can crash server then only restarting server will kill all postgres.exe from memory and this annoying me, because that I build script to restart server every night. I could not get programmers to change program. Is there parameters which will disconnect IDLE users if they excided some time or some program which will purge memory from non active posgres.exe Thanks in advance gossa@disyu.com
I would like to know how postgresql defines that a connection is
inactive. I have the same problem but in the future I couldn't restart
my server every day.
Any explanation ?
Ezequias
2007/2/26, Goran Rakic <gossa@disyu.com>:
> I have installed POSTGRESQL 8.2 on W2K3 32bit , 100 users connecting from
> desktop applications and 200 users connecting thru web service from handheld
> computers
>
> I have problem with second groups of users.
>
> Often they do not disconnect from POSTGRE Server and with time passing thru
> I have lot of IDLE users and very much memory consumptions. From time to
> time that can crash server then only restarting server will kill all
> postgres.exe from memory and this annoying me, because that I build script
> to restart server every night. I could not get programmers to change
> program.
>
>
>
> Is there parameters which will disconnect IDLE users if they excided some
> time or some program which will purge memory from non active posgres.exe
>
>
>
> Thanks in advance
>
>
>
> gossa@disyu.com
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Atenciosamente (Sincerely)
Ezequias Rodrigues da Rocha
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
A pior das democracias ainda é melhor do que a melhor das ditaduras
The worst of democracies is still better than the better of dictatorships
http://ezequiasrocha.blogspot.com/
Ezequias Rodrigues da Rocha wrote: > I would like to know how postgresql defines that a connection is > inactive. I have the same problem but in the future I couldn't restart > my server every day. I believe IDLE = not currently processing a query / sending data back to client. > Any explanation ? > Ezequias > > 2007/2/26, Goran Rakic <gossa@disyu.com>: >> I have installed POSTGRESQL 8.2 on W2K3 32bit , 100 users connecting from >> desktop applications and 200 users connecting thru web service from >> handheld >> computers >> >> I have problem with second groups of users. >> >> Often they do not disconnect from POSTGRE Server and with time passing >> thru >> I have lot of IDLE users and very much memory consumptions. From time to >> time that can crash server then only restarting server will kill all >> postgres.exe from memory and this annoying me, because that I build >> script >> to restart server every night. I could not get programmers to change >> program. 200 web based users should be sharing a smaller number of connections (maybe 50 to 100). What are you using to generate the pages? php, asp, perl? Do you use persistent connections in the above or some sort of pooling between the two (like pgpool)? or some fancy tracking of each users very own connection? I would be looking into the web server connections and seeing if the number of connections from there grows and in so finding why they don't get disconnected when finished. >> Is there parameters which will disconnect IDLE users if they excided some >> time or some program which will purge memory from non active posgres.exe >> -- Shane Ambler pgSQL@Sheeky.Biz Get Sheeky @ http://Sheeky.Biz
>----- Original Message ----- >From: "Ezequias Rodrigues da Rocha" <ezequias.rocha@gmail.com> >To: "Goran Rakic" <gossa@disyu.com> >Cc: <pgsql-admin@postgresql.org> >Sent: Wednesday, February 28, 2007 11:01 AM >Subject: [Bulk] Re: [ADMIN] Disconnecting non active (IDLE ) users >I would like to know how postgresql defines that a connection is >inactive. I have the same problem but in the future I couldn't restart >my server every day. >Any explanation ? I would be quite interested in the answer to this, but it seems to me that there is a bigger problem. >2007/2/26, Goran Rakic <gossa@disyu.com>: >> I have installed POSTGRESQL 8.2 on W2K3 32bit , 100 users connecting from >> desktop applications and 200 users connecting thru web service from >> handheld >> computers >> >> I have problem with second groups of users. >> >> Often they do not disconnect from POSTGRE Server and with time passing >> thru >> I have lot of IDLE users and very much memory consumptions. From time to >> time that can crash server then only restarting server will kill all >> postgres.exe from memory and this annoying me, because that I build >> script >> to restart server every night. I could not get programmers to change >> program. Part of the solution here seems to be a management issue rather than a technological one. If the web application above is bought from a third party, I would expect management to diligently pursue the provider for a reasonable solution. It is simply unacceptable that a company server should crash on a regular basis because of slop in an application purchased from some other company. Similarly, if the application was developed in house, then, obviously, management ought to be aware of the server crash issue and ought to be directing the relevant programmers to fix the problem. It is outrageously unethical, IMHO, for a programmer, or group thereof, to refuse to fix a problem that has arisen from how their targetted users use their application. Of course, before becoming too hard on the programmers mentioned above, the onus should be on Goran to provide substantive proof to his management and the other programmers that their client code is responsible (or, better, management ought to have created a small team including both Goran and the programmers in question to study the problem in order to determine precisely what the cause of the problem is and how it is best addressed). After all, it may just be coincidence that the server is crashing when there are lots of users connecting using a web service, and the real cause may be something entirely different. Goran noted a correlation between memory consumption and the number of idle connections. He now needs to determine why, especially since an idle connection by itself ought not consume a significant amount of resources. What has happened previously during the establishment and use of these connections? Did, the user create, and not release, a significant number of temporary tables or even a single temporary table that was populated with a lot of data? I have known SQL programmers to do precisely this on the assumption the temporary table goes away once the session is terminated, but if the session doesn't get terminated the temporary table just hangs around. IMHO, such a programmer deserves a good reprimand, and my practice is to drop such temporaries the moment they're no longer needed. I have yet to find a fellow programmer that objected to this practice, even when that person is responsible for the kind of slop that depends on the server software, or middleware code in some cases, to free the resources so carelessly used. Over the years, I have encountered serious bugs even in commercial libraries in which resources weren't properly freed, and I found ways to use ANSI standard specifications to work around such bugs, to solve the problems they created for me, until such a time as the vendor fixed the bugs in question. Alas, too often fixing such bugs is given a low priority because only a small proportion of the vendor's clients are hurt by them because they develop applications that don't intensively use resources (mine, particularly my analysis and modelling applications, do tend to use a lot of memory so I am especially sensitive to memory leaks). One final observation I would make is that something is seriously wrong with either the design or the implementation of the client application if either the number of idle connections grows continuously without limit or if it gets very large. I would expect a few idle connections, the actual number of which being dependant on how you're doing connection pooling, but I would insist on the client code being very careful about closing excess idle connections. I can see a wide variety of programming blunders or slop whereby connections are leaked, and never closed until they cause something to crash. If there is evidence of such slop, even if it is not the proximate cause of the server crashing, the programmer responsible deserves a good reprimand. On this thread, there have been considerations given of why a few hundred idle connections should not consume enough resources to cause a server to crash. If so few of them could cause such a crash, that would raise doubts about whether or not the server software is ready for production use. In any event, I haven't seen enough information to begin to figure out the real cause of the problem. But, there has also been information provided about how one can disconnect idle connections from within PostgreSQL: the SQL looked simple enough. I might even use it, but only as a last resort since in my experience, these kinds of problems are often due to programming slop, and this slop ought to never be tolerated. To rely on this, without dealing with any programming slop, masks the real problem and strikes me as being as foolish as a programmer restarting his program after each task because it has a memory leak instead of fixing the memory leak (or any other favourite resource leak you may have experienced, if your programming language of choice doesn't support pointers or creation of objects on the heap instead of the stack). HTH Ted
Ted Byers wrote: [snip] > It is outrageously unethical, IMHO, for a programmer, or group thereof, > to refuse to fix a problem that has arisen from how their targetted > users use their application. Let's save the determination of outrageous behavior for others and see if we can help the user fix their problem. Two suggestions: Pgpool Change keepalive settings in kernel if the machine is only used for postgresql. If you disable keepalives, at some point the kernel will likely drop the connection. Postgresql turns keepalives on by default (which is usually a good thing). Your apps should login transparently though and know how to handle this issue. Prompting users repeatedly to login can be frustrating, the usual question is how to keep a connection open longer :) I'm not sure that postgresql would itself implement a timeout feature... Good luck. - August
The world rejoiced as augustz@augustz.com (August Zajonc) wrote:
> Ted Byers wrote:
>
> [snip]
>> It is outrageously unethical, IMHO, for a programmer, or group thereof,
>> to refuse to fix a problem that has arisen from how their targetted
>> users use their application.
>
> Let's save the determination of outrageous behavior for others and see
> if we can help the user fix their problem.
>
> Two suggestions:
> Pgpool
>
> Change keepalive settings in kernel if the machine is only used for
> postgresql. If you disable keepalives, at some point the kernel will
> likely drop the connection. Postgresql turns keepalives on by default
> (which is usually a good thing).
>
> Your apps should login transparently though and know how to handle this
> issue. Prompting users repeatedly to login can be frustrating, the usual
> question is how to keep a connection open longer :)
>
> I'm not sure that postgresql would itself implement a timeout feature...
This fits very nicely into the category of things that can reasonably
vary quite a lot based on local policy.
I'd quite like to be able to say:
"We can let some users in, with read-only access. And we can have
PostgreSQL enforce policies as to maximum connection times so that
we can ensure they do not hold open <IDLE> in transaction
connections that will destroy performance."
At present, I can only handle this via creating external utilities to
try to analyze things and look for connections that are breaking
connectivity policies. It would be rather nice to have something to
support this in the DB engine.
That may not fit your needs: your users may be in a position to tell
you "we don't care if performance suffers - we want our connections."
--
output = reverse("moc.liamg" "@" "enworbbc")
http://linuxdatabases.info/info/lsf.html
"Let me blow that up a bit more for you."
-- Colin Powell, Discussing a picture of the intelligence compound in
Iraq