Обсуждение: to much process
Hello,
I use postgres with my servlet on a linux box. Sometimes when I do
a 'ps auxf' I saw that I have a lot (about 20) of postgres process like this:
postgres 19025 0.0 0.7 31124 988 ? SN 2000 4:59 /cassiope/usr/local/postgresql-7.0/bin/postmaster -D
/cassiope/var/pgdata2-p 5430 -i
postgres 25749 0.0 2.1 31472 2724 ? SN 09:18 0:00 \_ /cassiope/usr/local/postgresql-7.0/bin/postgres
127.0.0.1admintp truckplus_1 idle
postgres 26528 0.0 2.3 31680 2976 ? SN 10:00 0:00 \_ /cassiope/usr/local/postgresql-7.0/bin/postgres
127.0.0.1admintp truckplus_1 idle
postgres 26663 0.0 2.1 31492 2788 ? SN 10:09 0:00 \_ /cassiope/usr/local/postgresql-7.0/bin/postgres
127.0.0.1admintp truckplus_1 idle
And sometimes when the number of process is so big, I can't connect to postgresql with jdbc :o(.
Apparently, the connections opened are not close by the garbage collector ...
Is there a solution to avoid such problem ?
Thanks.
--
.------------------------------------------------.
.^. | Didier Bretin, France | dbr@informactis.com |
/V\ |-----------------------| www.informactis.com |
// \\ | `------------------------|
/( )\ | Visit: http://www.multimania.com/cieexcalibur/ |
^^-^^ `------------------------------------------------'
On Mon, 29 Jan 2001, Didier Bretin wrote:
> Apparently, the connections opened are not close by the garbage
> collector ...
>
> Is there a solution to avoid such problem ?
There are several solutions:
* Make postgresql start with a higher number of possible processes:
postmaster -i -B 200 -N 100
This won't get to the underlying cause of your problem, but it may
alleviate the symptoms enough to buy you time to fix the problem.
* Ensure that you close the connections every time after you open them:
Connection con = null;
Statement stmt = null;
try
{
con = DriverManager.getConnection (url, myUser, myPassword);
stmt = con.createStatement();
// The rest of your JDBC code that actually does work goes here...
}
catch (SQLException e)
{
e.printStackTrace();
throw e;
}
finally
{
if (con != null)
con.close();
}
This is safe, but has a performance penalty associated with creating the
new connection and associated postgres process every time.
* Use a connection pooling mechanism like PoolMan:
<http://poolman.sourceforge.net/index.shtml>
Using connection pooling will lead to higher performance for your
database application.
--
Richard Bullington-McGuire <rbulling@microstate.com>
Chief Technology Officer, The Microstate Corporation
Phone: 703-796-6446 URL: http://www.microstate.com/
PGP key IDs: RSA: 0x93862305 DH/DSS: 0xDAC3028E
Quoting Didier Bretin <dbr@informactis.com>: > Hello, > > I use postgres with my servlet on a linux box. Sometimes when I do > a 'ps auxf' I saw that I have a lot (about 20) of postgres process like > this: > postgres 19025 0.0 0.7 31124 988 ? SN 2000 4:59 > /cassiope/usr/local/postgresql-7.0/bin/postmaster -D > /cassiope/var/pgdata2 -p 5430 -i > postgres 25749 0.0 2.1 31472 2724 ? SN 09:18 0:00 \_ > /cassiope/usr/local/postgresql-7.0/bin/postgres 127.0.0.1 admintp > truckplus_1 idle > postgres 26528 0.0 2.3 31680 2976 ? SN 10:00 0:00 \_ > /cassiope/usr/local/postgresql-7.0/bin/postgres 127.0.0.1 admintp > truckplus_1 idle > postgres 26663 0.0 2.1 31492 2788 ? SN 10:09 0:00 \_ > /cassiope/usr/local/postgresql-7.0/bin/postgres 127.0.0.1 admintp > truckplus_1 idle > > And sometimes when the number of process is so big, I can't connect to > postgresql with jdbc :o(. > Apparently, the connections opened are not close by the garbage > collector ... > > Is there a solution to avoid such problem ? Is your application calling the close() method in Connection? The garbage collector will never close a connection. Also, you can not guarantee when garbage collection will occur (sometimes it never will). It all depends on how the JVM & Garbage Collector is implemented. Peter -- Peter Mount peter@retep.org.uk PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Hi
Not sure if it is easy or not given the translation in PSQLException, but
it would be nice if the paramIndex could be included in the error message
when it is out of range. An alternative would be to have one message for
paramIndex == 0 and another for paramIndex > inStrings.length.
This would help users find stupid bugs like using index=0, which is
likely to be a common bug IMHO. ( Yes, you guessed it, I just found an
index=0 bug in my code :)
Perhaps something like changing set from PreparedStatement.java to:
private void set(int paramIndex, String s) throws SQLException
{
if (paramIndex < 1)
throw new PSQLException("postgresql.prep.rangesmall");
if (paramIndex > inStrings.length)
throw new PSQLException("postgresql.prep.rangelarge");
inStrings[paramIndex - 1] = s;
}
And then have the messages be:
Parameter index out of range, less than one.
and
Parameter index out of range, too large.
or something.
Just a thought,
thanks,
Philip
PS some of the links on jdbc.postgres.org seem to be broken in the left
side navigation. For example Download points to
http://jdbc.postgresql.org/postgres/download.html
but it should be
http://jdbc.postgresql.org/download.html
Quoting Philip Crotwell <crotwell@seis.sc.edu>:
>
> Hi
>
> Not sure if it is easy or not given the translation in PSQLException,
> but
> it would be nice if the paramIndex could be included in the error
> message
> when it is out of range. An alternative would be to have one message
> for
> paramIndex == 0 and another for paramIndex > inStrings.length.
>
> This would help users find stupid bugs like using index=0, which is
> likely to be a common bug IMHO. ( Yes, you guessed it, I just found an
> index=0 bug in my code :)
>
> Perhaps something like changing set from PreparedStatement.java to:
>
> private void set(int paramIndex, String s) throws SQLException
> {
> if (paramIndex < 1)
> throw new PSQLException("postgresql.prep.rangesmall");
> if (paramIndex > inStrings.length)
> throw new PSQLException("postgresql.prep.rangelarge");
> inStrings[paramIndex - 1] = s;
> }
>
> And then have the messages be:
> Parameter index out of range, less than one.
> and
> Parameter index out of range, too large.
> or something.
>
> Just a thought,
> thanks,
> Philip
>
> PS some of the links on jdbc.postgres.org seem to be broken in the left
> side navigation. For example Download points to
> http://jdbc.postgresql.org/postgres/download.html
> but it should be
> http://jdbc.postgresql.org/download.html
Good idea. I'll implement this tonight (possibly earlier). Would it be useful
to put the index value in the string as well?
As for the url's, yes they are broken. This is in part on how the pages are
designed. I've just haven't had chance to fix it yet (aiming for the weekend
for this one).
Peter
--
Peter Mount peter@retep.org.uk
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/