Re: Postgresql 8.2 and cayenne 2.0.3 - idle connections

From: Gilberto C Andrade (gilbertoc..ecad.to.gov.br)
Date: Mon Sep 10 2007 - 10:42:44 EDT

  • Next message: Michael Gentry: "Re: Postgresql 8.2 and cayenne 2.0.3 - idle connections"

    Michael Gentry wrote:
    > It sounds like when JBoss does the redeploy, it isn't actually fully
    > releasing the old application and therefore the connection count will
    > keep going up in your database until you restart JBoss.

    This happens even when we undeploy de webapp!
    Other thing shouldn't cayenne "connectionPool" respect the max parameter?

    > top - 10:33:28 up 27 days, 14:32, 1 user, load average: 2.09, 1.23, 1.48
    > Tasks: 126 total, 3 running, 123 sleeping, 0 stopped, 0 zombie
    > Cpu(s): 60.7% us, 18.7% sy, 0.0% ni, 13.9% id, 6.3% wa, 0.2% hi, 0.3% si
    > Mem: 1034264k total, 1021008k used, 13256k free, 41488k buffers
    > Swap: 2097144k total, 49428k used, 2047716k free, 810908k cached
    >
    > PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    > 23812 postgres 25 0 169m 96m 91m R 89.7 9.5 0:53.09 postgres: user_siconsig sic 10.121.0.11(3907) SELECT
    > 23998 postgres 23 0 164m 129m 128m R 31.8 12.8 0:00.96 postgres: dba sic 10.121.0.11(4142) SELECT
    > 6641 postgres 15 0 7920 704 216 S 19.9 0.1 627:23.20 postgres: stats collector process
    > 5032 postgres 15 0 165m 155m 154m S 11.3 15.4 3:46.42 postgres: sigesp sigesp 10.121.0.7(42767) idle
    > 28710 postgres 16 0 165m 64m 62m S 0.3 6.3 0:04.92 postgres: dba dbsecad 10.121.0.7(38019) idle
    > 6596 postgres 15 0 164m 888 748 S 0.0 0.1 43:42.41 /opt/pgsql8/bin/postmaster -D /opt/pgsql8/data
    > 6640 postgres 15 0 164m 131m 131m S 0.0 13.0 0:57.05 postgres: writer process
    > 23322 postgres 16 0 165m 156m 154m S 0.0 15.4 19:22.74 postgres: sadep_user bcoproducao 10.121.0.7(33065) idle
    > 25551 postgres 16 0 165m 154m 152m S 0.0 15.3 10:16.03 postgres: sadep_user bcoproducao 10.121.0.7(36196) idle
    > 28737 postgres 16 0 165m 7080 6848 S 0.0 0.7 0:00.30 postgres: survey_user bcoproducao 10.121.0.7(42572) idle
    > 28921 postgres 16 0 165m 6888 6628 S 0.0 0.7 0:00.26 postgres: survey_user bcoproducao 10.121.0.7(42593) idle
    > 29006 postgres 16 0 165m 6264 5980 S 0.0 0.6 0:00.24 postgres: survey_user bcoproducao 10.121.0.7(42603) idle
    > 29008 postgres 16 0 165m 6772 6376 S 0.0 0.7 0:00.25 postgres: survey_user bcoproducao 10.121.0.7(42604) idle
    > 29016 postgres 16 0 165m 7112 6440 S 0.0 0.7 0:00.26 postgres: survey_user bcoproducao 10.121.0.7(42605) idle
    > 10489 postgres 16 0 165m 6888 6428 S 0.0 0.7 0:21.27 postgres: survey_user bcoproducao 10.121.0.7(45230) idle
    > 10498 postgres 16 0 165m 5844 5464 S 0.0 0.6 0:21.23 postgres: survey_user bcoproducao 10.121.0.7(45241) idle
    > 10499 postgres 16 0 165m 6436 5768 S 0.0 0.6 0:21.31 postgres: survey_user bcoproducao 10.121.0.7(45242) idle
    > 10500 postgres 16 0 165m 6132 5308 S 0.0 0.6 0:21.19 postgres: survey_user bcoproducao 10.121.0.7(45243) idle
    > 10501 postgres 16 0 165m 6876 6244 S 0.0 0.7 0:21.33 postgres: survey_user bcoproducao 10.121.0.7(45244) idle
    > 11580 postgres 16 0 165m 4120 3912 S 0.0 0.4 0:00.04 postgres: survey_user bcoproducao 10.121.0.7(46394) idle
    > 11582 postgres 16 0 165m 3608 3344 S 0.0 0.3 0:00.04 postgres: survey_user bcoproducao 10.121.0.7(46395) idle
    > 11583 postgres 16 0 165m 3996 3612 S 0.0 0.4 0:00.04 postgres: survey_user bcoproducao 10.121.0.7(46396) idle
    > 11584 postgres 16 0 165m 3944 3416 S 0.0 0.4 0:00.04 postgres: survey_user bcoproducao 10.121.0.7(46397) idle
    > 11585 postgres 16 0 165m 3736 3444 S 0.0 0.4 0:00.04 postgres: survey_user bcoproducao 10.121.0.7(46398) idle
    > 11794 postgres 16 0 165m 7620 6916 S 0.0 0.7 0:00.20 postgres: survey_user bcoproducao 10.121.0.7(46711) idle
    > 11805 postgres 16 0 165m 6668 5980 S 0.0 0.6 0:00.15 postgres: survey_user bcoproducao 10.121.0.7(46732) idle
    > 11806 postgres 16 0 165m 8472 7664 S 0.0 0.8 0:00.17 postgres: survey_user bcoproducao 10.121.0.7(46733) idle
    > 11807 postgres 16 0 165m 7416 6756 S 0.0 0.7 0:00.16 postgres: survey_user bcoproducao 10.121.0.7(46734) idle
    > 11808 postgres 16 0 165m 8288 7604 S 0.0 0.8 0:00.19 postgres: survey_user bcoproducao 10.121.0.7(46735) idle
    > 7032 postgres 16 0 165m 4240 4104 S 0.0 0.4 0:00.04 postgres: survey_user bcoproducao 10.121.0.7(45898) idle
    > 7033 postgres 16 0 164m 3916 3472 S 0.0 0.4 0:00.04 postgres: survey_user bcoproducao 10.121.0.7(45899) idle
    > 7034 postgres 16 0 164m 4232 3428 S 0.0 0.4 0:00.03 postgres: survey_user bcoproducao 10.121.0.7(45900) idle
    > 7035 postgres 16 0 164m 4364 3448 S 0.0 0.4 0:00.03 postgres: survey_user bcoproducao 10.121.0.7(45901) idle
    > 7036 postgres 16 0 164m 3628 3244 S 0.0 0.4 0:00.02 postgres: survey_user bcoproducao 10.121.0.7(45902) idle
    > 7690 postgres 16 0 164m 5108 4188 S 0.0 0.5 0:00.10 postgres: survey_user bcoproducao 10.121.0.7(46053) idle
    > 7815 postgres 16 0 165m 5820 4996 S 0.0 0.6 0:00.11 postgres: survey_user bcoproducao 10.121.0.7(46070) idle
    > 7816 postgres 16 0 165m 5252 4348 S 0.0 0.5 0:00.09 postgres: survey_user bcoproducao 10.121.0.7(46071) idle
    > 7817 postgres 16 0 165m 4792 4148 S 0.0 0.5 0:00.11 postgres: survey_user bcoproducao 10.121.0.7(46072) idle

    Thanks!

    Gilberto

    This could be
    > a good argument for you to use JBoss's connection pools for your
    > production deployment. This page discusses how to use a JNDI-provided
    > connection pools from the container (even if not JBoss-specific):
    >
    > http://cayenne.apache.org/doc20/using-jndi.html
    >
    > Hope that helps some ...
    >
    > /dev/mrg
    >
    >
    > On 9/6/07, Gilberto C Andrade <gilbertoc..ecad.to.gov.br> wrote:
    >> Hi all!
    >>
    >> After following some tips from here:
    >> http://article.gmane.org/gmane.comp.java.cayenne.user/8360, we finally
    >> put our webapp in production:
    >>
    >> server: jboss-4.0.2
    >> server: postgresql 8.2
    >>
    >> PesquisaDataDominioNode.driver.xml:
    >>
    >> <?xml version="1.0" encoding="utf-8"?>
    >> <driver project-version="2.0" class="org.postgresql.Driver">
    >> <url value="jdbc:postgresql://hostname:5432/bcoproducao"/>
    >> <connectionPool min="5" max="10" />
    >> <login userName="pesquisa_user" password="senha"/>
    >> </driver>
    >>
    >> While using the application we see that the connections are superior
    >> than max=10. But this haven't caused any problem.
    >>
    >> So, after one second deploy (I think is redeploy) on jboss we see that
    >> those opened idle connections (about 30) stay there without been used
    >> and when the app starts we see new connections(5) which are used.
    >>
    >> Did anyone have seen this behavior before?
    >>
    >> Thanks for any tip!
    >>
    >> Gilberto
    >> www.secad.to.gov.br
    >



    This archive was generated by hypermail 2.0.0 : Mon Sep 10 2007 - 09:40:15 EDT