Обсуждение: My DataBase can't accept conntecion
we have e test db server, we use jdbc to contect.
but after a whie, The test db server can not accept connect.
use ps aux | grep postgres command :
postgres 16904 0.0 0.0 46036 3948 ? S 17:03 0:00 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
postgres 16906 0.0 0.0 46144 2192 ? Ss 17:03 0:00 postgres: writer process
postgres 16907 0.0 0.0 46036 1088 ? Ss 17:03 0:00 postgres: wal writer process
postgres 16908 0.0 0.0 46840 2072 ? Ss 17:03 &nb sp; 0:00 postgres: autovacuum launcher process
postgres 16909 0.0 0.0 14248 1012 ? Ss 17:03 0:00 postgres: stats collector process
postgres 16910 0.0 0.0 46972 2992 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40608) idle
postgres 16911 0.0 0.0 46708 2656 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40609) idle
postgres 16912 0.0 0.0 46708 2628 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40610) idle
postgres 16913 0.0 0.0 46708 2628 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40611) idle
postgres 16914 0.0 0.0 46708 2632 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40612) idle
postgres 16915 0.0 0.0 46708 2636 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40613) idle
postgres 16916 0.0 0.0 46708 2632 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40614) idle
postgres 16917 0.0 0.1 47088 4136 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40615) idle
postgres 16918 0.0 0.1 47060 4236 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40616) idle
postgres 16919 0.0 0.1 47052 4136 ? Ss 17:03 0:02 postgres: postgres edu 172.29.0.40(40617) idle
postgres 21619 0.0 0.0 46708 2636 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38803) idle
postgres 21620 0.0 0.0 46708 2636 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38804) idle
postgres 21621 0.0 0.0 46708 2640 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38805) idle
postgres 2162 2 0.0 0.0 46708 2644 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38806) idle
postgres 21623 0.0 0.0 46708 2644 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38807) idle
postgres 21624 0.0 0.0 46708 2636 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38808) idle
postgres 21625 0.0 0.0 46708 2640 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38809) idle
postgres 21626 0.0 0.0 46708 2644 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38810) idle
po stgres 21627 0.0 0.0 46708 2640 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38811) idle
postgres 21628 0.0 0.1 47104 4836 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38812) INSERT
...
kill -9 pid command to kill all of them in the past? and the test db server is shut down.
how to analyze the reason of these? and how to solve?
On 2011-01-12 13.13, zab08 wrote: > we have e test db server, we use jdbc to contect. > but after a whie, The test db server can not accept connect. > > > useps aux | grep postgrescommand : > postgres 16904 0.0 0.0 46036 3948 ? S 17:03 0:00 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data > postgres 16906 0.0 0.0 46144 2192 ? Ss 17:03 0:00 postgres: writer process > postgres 16907 0.0 0.0 46036 1088 ? Ss 17:03 0:00 postgres: wal writer process > postgres 16908 0.0 0.0 46840 2072 ? Ss 17:03 0:00 postgres: autovacuum launcher process > postgres 16909 0.0 0.0 14248 1012 ? Ss 17:03 0:00 postgres: stats collector process > postgres 16910 0.0 0.0 46972 2992 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40608) idle > postgres 16911 0.0 0.0 46708 2656 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40609) idle > postgres 16912 0.0 0.0 46708 2628 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40610) idle > postgres 16913 0.0 0.0 46708 2628 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40611) idle > postgres 16914 0.0 0.0 46708 2632 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40612) idle > postgres 16915 0.0 0.0 46708 2636 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40613) idle > postgres 16916 0.0 0.0 46708 2632 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40614) idle > postgres 16917 0.0 0.1 47088 4136 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40615) idle > postgres 16918 0.0 0.1 47060 4236 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40616) idle > postgres 16919 0.0 0.1 47052 4136 ? Ss 17:03 0:02 postgres: postgres edu 172.29.0.40(40617) idle > postgres 21619 0.0 0.0 46708 2636 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38803) idle > postgres 21620 0.0 0.0 46708 2636 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38804) idle > postgres 21621 0.0 0.0 46708 2640 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38805) idle > postgres 21622 0.0 0.0 46708 2644 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38806) idle > postgres 21623 0.0 0.0 46708 2644 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38807) idle > postgres 21624 0.0 0.0 46708 2636 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38808) idle > postgres 21625 0.0 0.0 46708 2640 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38809) idle > postgres 21626 0.0 0.0 46708 2644 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38810) idle > postgres 21627 0.0 0.0 46708 2640 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38811) idle > postgres 21628 0.0 0.1 47104 4836 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38812) INSERT Seems like your application never disconnect. Continously creating new connections without disconnecting previously made ones will eventually exhaust the number of connections configured. > ... > > > kill -9 pidcommand to kill all of them in the past? and the test db server is shut down. > how to analyze the reason of these? and how to solve? -- Regards, Robert "roppert" Gravsjö
In response to zab08 <zab08@126.com>: > we have e test db server, we use jdbc to contect. > but after a whie, The test db server can not accept connect. > > > useps aux | grep postgrescommand : > postgres 16904 0.0 0.0 46036 3948 ? S 17:03 0:00 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data > postgres 16906 0.0 0.0 46144 2192 ? Ss 17:03 0:00 postgres: writer process > postgres 16907 0.0 0.0 46036 1088 ? Ss 17:03 0:00 postgres: wal writer process > postgres 16908 0.0 0.0 46840 2072 ? Ss 17:03 0:00 postgres: autovacuum launcher process > postgres 16909 0.0 0.0 14248 1012 ? Ss 17:03 0:00 postgres: stats collector process > postgres 16910 0.0 0.0 46972 2992 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40608) idle > postgres 16911 0.0 0.0 46708 2656 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40609) idle > postgres 16912 0.0 0.0 46708 2628 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40610) idle > postgres 16913 0.0 0.0 46708 2628 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40611) idle > postgres 16914 0.0 0.0 46708 2632 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40612) idle > postgres 16915 0.0 0.0 46708 2636 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40613) idle > postgres 16916 0.0 0.0 46708 2632 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40614) idle > postgres 16917 0.0 0.1 47088 4136 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40615) idle > postgres 16918 0.0 0.1 47060 4236 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40616) idle > postgres 16919 0.0 0.1 47052 4136 ? Ss 17:03 0:02 postgres: postgres edu 172.29.0.40(40617) idle > postgres 21619 0.0 0.0 46708 2636 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38803) idle > postgres 21620 0.0 0.0 46708 2636 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38804) idle > postgres 21621 0.0 0.0 46708 2640 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38805) idle > postgres 21622 0.0 0.0 46708 2644 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38806) idle > postgres 21623 0.0 0.0 46708 2644 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38807) idle > postgres 21624 0.0 0.0 46708 2636 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38808) idle > postgres 21625 0.0 0.0 46708 2640 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38809) idle > postgres 21626 0.0 0.0 46708 2644 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38810) idle > postgres 21627 0.0 0.0 46708 2640 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38811) idle > postgres 21628 0.0 0.1 47104 4836 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38812) INSERT > ... > > > kill -9 pidcommand to kill all of them in the past? and the test db server is shut down. Are you insane? If your car doesn't start do you throw hand grenades at it? What you're doing is an excellent way to corrupt the data in your database. Use pg_ctl to stop/start/restart the database -- or better, use whatever tool is provided by your distribution/operating system. > how to analyze the reason of these? and how to solve? Have you ever considered looking in the PostgreSQL log files? If I had to make a guess, I would suspect that you've got the connection limit too low for the number of connections required by whatever you're doing and a simple bump of the config parameter will fix the problem. More serious issues are possible, such as client programs going idle and never releasing the connection, but I wouldn't assume such problems until you have evidence. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
At 2011-01-12 23:22:40，"Bill Moran" <wmoran@potentialtech.com> wrote: >In response to zab08 <zab08@126.com>: > >> we have e test db server, we use jdbc to contect. >> but after a whie, The test db server can not accept connect. >> >> >> useps aux | grep postgrescommand : >> postgres 16904 0.0 0.0 46036 3948 ? S 17:03 0:00 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >> postgres 16906 0.0 0.0 46144 2192 ? Ss 17:03 0:00 postgres: writer process >> postgres 16907 0.0 0.0 46036 1088 ? Ss 17:03 0:00 postgres: wal writer process >> postgres 16908 0.0 0.0 46840 2072 ? Ss 17:03 0:00 postgres: autovacuum launcher process >> postgres 16909 0.0 0.0 14248 1012 ? Ss 17:03 0:00 postgres: stats collector process >> postgres 16910 0.0 0.0 46972 2992 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40608) idle >> postgres 16911 0.0 0.0 46708 2656 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40609) idle >> postgres 16912 0.0 0.0 46708 2628 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40610) idle >> postgres 16913 0.0 0.0 46708 2628 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40611) idle >> postgres 16914 0.0 0.0 46708 2632 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40612) idle >> postgres 16915 0.0 0.0 46708 2636 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40613) idle >> postgres 16916 0.0 0.0 46708 2632 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40614) idle >> postgres 16917 0.0 0.1 47088 4136 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40615) idle >> postgres 16918 0.0 0.1 47060 4236 ? Ss 17:03 0:00 postgres: postgres edu 172.29.0.40(40616) idle >> postgres 16919 0.0 0.1 47052 4136 ? Ss 17:03 0:02 postgres: postgres edu 172.29.0.40(40617) idle >> postgres 21619 0.0 0.0 46708 2636 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38803) idle >> postgres 21620 0.0 0.0 46708 2636 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38804) idle >> postgres 21621 0.0 0.0 46708 2640 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38805) idle >> postgres 21622 0.0 0.0 46708 2644 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38806) idle >> postgres 21623 0.0 0.0 46708 2644 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38807) idle >> postgres 21624 0.0 0.0 46708 2636 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38808) idle >> postgres 21625 0.0 0.0 46708 2640 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38809) idle >> postgres 21626 0.0 0.0 46708 2644 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38810) idle >> postgres 21627 0.0 0.0 46708 2640 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38811) idle >> postgres 21628 0.0 0.1 47104 4836 ? Ss 19:43 0:00 postgres: postgres edu 172.29.0.40(38812) INSERT >> ... >> >> >> kill -9 pidcommand to kill all of them in the past? and the test db server is shut down. > >Are you insane? If your car doesn't start do you throw hand grenades at >it? What you're doing is an excellent way to corrupt the data in your >database. > >Use pg_ctl to stop/start/restart the database -- or better, use whatever >tool is provided by your distribution/operating system. > >> how to analyze the reason of these? and how to solve? > >Have you ever considered looking in the PostgreSQL log files? If I had >to make a guess, I would suspect that you've got the connection limit too >low for the number of connections required by whatever you're doing and >a simple bump of the config parameter will fix the problem. More serious >issues are possible, such as client programs going idle and never releasing >the connection, but I wouldn't assume such problems until you have >evidence. > >-- >Bill Moran >http://www.potentialtech.com >http://people.collaborativefusion.com/~wmoran/ > >-- >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-general
client program shut down, but the conetion isnt' close on the test server.
we have 6 clients and , 10 contections each. The test db sever 's max
conections is 100. pg_ctl to stop/start/restart the database can't excute because of the
db the shut down.