X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 0D5289DC843 for ; Thu, 16 Mar 2006 12:41:55 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 43340-09 for ; Thu, 16 Mar 2006 12:41:53 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by postgresql.org (Postfix) with ESMTP id E47299DC80C for ; Thu, 16 Mar 2006 12:41:52 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id k2GGfpoB056227 for ; Thu, 16 Mar 2006 12:41:51 -0400 (AST) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id k2GGHnmn046348 for pgsql-admin@postgresql.org; Thu, 16 Mar 2006 12:17:49 -0400 (AST) (envelope-from news) From: ute X-Newsgroups: pgsql.admin Subject: PostgreSQL 8.3.1 on FreeBSD 6.0 - accumulation of processes Date: Thu, 16 Mar 2006 17:17:47 +0100 Organization: Hub.Org Networking Services Lines: 62 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-2; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Thunderbird 1.5 (Windows/20051201) To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=-1.302 required=5 tests=[ALL_TRUSTED=-1.44, AWL=0.137] X-Spam-Score: -1.302 X-Spam-Level: X-Archive-Number: 200603/137 X-Sequence-Number: 20894 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