On Tue, 2007-02-27 at 15:23, Goran Rakic wrote: > 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. There are a few issues here, and I have a few questions. Idle users don't normally use a lot of resources, and they certainly don't generally use increasing resources just sitting idle. However, idle in transaction users (a different thing) do cause problems in that the database cannot free up deleted tuples during vacuum. This can result in a bloated database store. If your server is crashing from 100 idle users, something is wrong. Are you running 8.2.0 or 8.2.3? If you're running a version of 8.2 from before 8.2.3 you should upgrade as soon as possible. Your programmers are writing broken programs if they are leaving connections idle in transaction. You have my permission to beat them. :) If they are just leaving connections idle, plain old idle, then that's probably no big deal. Can you run a shell script that just connects until all the connections are used up? Or does that crash the server? If it crashes it, then you've got something configured wrong, and need to either reduce the max number of connections, or increase the resources available to the postgresql db server. You might wanna post a bit more info, like what things you've changed in postgresql.conf, how much memory your machine has, and what the output of select * from pg_stat_activity ; says > Is there parameters which will disconnect IDLE users if they excided some > time or some program which will purge memory from non active postgres.exe The problem is it's hard to tell the difference between someone who's just taking a long time to commit a real transaction and someone who's forgotten that they logged in yesterday. The general solution here is to have a firewall that will time out idle connections after X time. However, such a configuration can be overcome by judicious use of tcp keepalives. You can use something like: select procpid, usename, now()-query_start from pg_stat_activity where current_query like '%IDLE%' and now()-query_start > interval '5 minutes'; to list all the users that have been idle over the interval in the list. Using some kind of scripting language, you could then issue kill signals to those procpids. Note that I'm a unix guy, so translating this to the (in my mind) insanity that is windows is up to you. :)
