Hi all,
I am runnning PostgreSQL 8.3.1 on FreeBSD 6.0.
There are about 30 heavy readed / updated databases and very often is
occurred situation that there accumulate processes and waiting for each
other, for example:
when server starts, there are a few processes:
ps ax |grep postgres
50120 ?? Ss 0:03,61 /usr/local/bin/postmaster -D
/usr/local/pgsql/data -i (postgres)
50122 ?? S 0:01,11 postmaster: writer process (postgres)
50123 ?? S 0:22,20 postmaster: stats buffer process (postgres)
50125 ?? S 0:36,55 postmaster: stats collector process (postgres)
53605 ?? IW 0:00,00 postmaster: postgres b2c_91355 [local] idle
(postgres)
59643 ?? D 0:01,35 postmaster: b2c_101394 b2c_101394 ::1(53787)
SELECT (postgres)
59663 ?? D 0:00,11 postmaster: b2c_90273 b2c_90273 ::1(60017)
SELECT (postgres)
59801 ?? S 0:00,85 postmaster: b2c_90273 b2c_90273 ::1(65358)
SELECT (postgres)
60076 ?? D 0:00,67 postmaster: b2c_101924 b2c_101924 ::1(53070)
SELECT (postgres)
60145 ?? S 0:00,11 postmaster: b2c_develop b2c_develop ::1(63134)
idle (postgres)
60220 ?? D 0:00,49 postmaster: b2c_103703 b2c_103703 ::1(49875)
SELECT (postgres)
60362 ?? D 0:00,83 postmaster: b2c_95002 b2c_95002 ::1(56467)
SELECT (postgres)
60631 ?? D 0:00,01 postmaster: b2c_95398 b2c_95398 ::1(64013)
SELECT (postgres)
but after a while processes dramaticaly grow up and there are about tens
or hunderds processes.
Typical situation is, that ended sql commands not ends postmaster
processes. For example when I run vacuum full analyze some database, sql
command ends, but process not! Then I can see this:
62123 ?? D 0:22,24 postmaster: postgres b2c_100091 ::1(50512)
VACUUM (postgres)
64332 ?? I 0:00,05 postmaster: b2c_100091 b2c_100091 ::1(64940)
SELECT waiting (postgres)
64826 ?? S 0:00,06 postmaster: b2c_100091 b2c_100091 ::1(60703)
SELECT waiting (postgres)
64904 ?? S 0:00,06 postmaster: b2c_100091 b2c_100091 ::1(58878)
SELECT waiting (postgres)
Important is, that SQL command vacuum full analyze some database has
finished. I think, that when vacuum is finished, process
62123 ?? D 0:22,24 postmaster: postgres b2c_100091 ::1(50512)
VACUUM (postgres)
should end and dissapear.
Please, advice me where to look to solve this problem.
Ute