PostgreSQL 8.3.1 on FreeBSD 6.0 - accumulation of processes

From: ute <ute(at)centrum(dot)cz>
To: pgsql-admin(at)postgresql(dot)org
Subject: PostgreSQL 8.3.1 on FreeBSD 6.0 - accumulation of processes
Date: 2006-03-16 16:17:47
Message-ID: dvc33c$1d7u$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2006-03-16 16:27:31 Re: Curious run-away index build on upgrade to 8.1.3
Previous Message Jerry Sievers 2006-03-16 16:00:10 Re: Curious run-away index build on upgrade to 8.1.3