X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 989AB9DCD5D for ; Fri, 16 Dec 2005 10:33:48 -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 32029-10 for ; Fri, 16 Dec 2005 10:33:52 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail3.ecircle.de (mail3.ecircle.de [195.140.186.206]) by postgresql.org (Postfix) with ESMTP id 469E89DCCC6 for ; Fri, 16 Dec 2005 10:33:46 -0400 (AST) Received: from deimos.muc.ecircle.de (deimos.muc.ecircle.de [192.168.1.4]) by mail3.ecircle.de (READY) with ESMTP id 711FE11C410; Fri, 16 Dec 2005 15:33:49 +0100 (CET) Received: from [192.168.1.110] ([192.168.1.110]) by deimos.muc.ecircle.de with Microsoft SMTPSVC(5.0.2195.6713); Fri, 16 Dec 2005 15:32:46 +0100 Subject: Re: Crashing DB or Server? From: Csaba Nagy To: Moritz Bayer Cc: Harry Jackson , postgres performance list In-Reply-To: References: <45b42ce40512160538n7b77a2aexe9d13616f6c25a69@mail.gmail.com> Content-Type: text/plain Message-Id: <1134743566.14216.47.camel@coppola.muc.ecircle.de> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Fri, 16 Dec 2005 15:32:47 +0100 Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 16 Dec 2005 14:32:46.0966 (UTC) FILETIME=[95722160:01C6024D] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200512/397 X-Sequence-Number: 16218 Moritz, Is it possible that you use lots of temporary tables, and you don't vacuum the system tables ? That would cause such symptoms I guess... Try to make a "vacuum analyze" connected as the postgres super user, that will vacuum all your system tables too. Note that if you have a really big bloat, a simple vacuum might not help, so you might need to do "vacuum full analyze", and possibly reindex on some tables - I'm not an expert on this, so others might have better advice. Cheers, Csaba. On Fri, 2005-12-16 at 15:10, Moritz Bayer wrote: > Hi, > > actually every SELECT statements takes a couple of minutes. > For example > SELECT * FROM pg_stat_activity already takes 260 sec. > > And the IOWAIT value increases just after starting the postmaster, no > querys are processed. > > I started vacuumizing the tables of the DB. Still, it doesn't make a > difference. > > So I don't know if the structure of the tables are relevant. > For example, I have got about 30 of those: > > CREATE TABLE "public"."tbl_highscore_app4" ( > "id" BIGSERIAL, > "userid" INTEGER NOT NULL, > "score" INTEGER DEFAULT 0 NOT NULL, > "occured" DATE DEFAULT now() NOT NULL, > CONSTRAINT "tbl_highscore_app4_pkey" PRIMARY KEY("userid") > ) WITHOUT OIDS; > > the select-statements are done through functions, for example > > CREATE OR REPLACE FUNCTION "public"."getownrankingapp4" (integer, > integer) RETURNS integer AS' > DECLARE i_userid INTEGER; > DECLARE i_score INTEGER; > DECLARE i_rank INTEGER; > begin > i_userid := $1; > i_score := $2; > i_rank := 1; > if i_score <= 0 then > SELECT INTO i_rank max(id) FROM > tbl_highscore_app4_tmp; > if i_rank IS null then > i_rank = 1; > else > i_rank = i_rank +1; > end if; > else > SELECT INTO i_rank max(id) FROM tbl_highscore_app4_tmp WHERE > score>=i_score; if i_rank IS null then i_rank = 1; end if; end > if; > return (i_rank); > END > 'LANGUAGE 'plpgsql' VOLATILE RETURNS NULL ON NULL INPUT SECURITY > INVOKER; > > > The tmp table looks like this (and is filled once a night with the > current data): > > CREATE TABLE "public"."tbl_highscore_app4_tmp" ( > "id" INTEGER NOT NULL, > "userid" INTEGER NOT NULL, > "score" INTEGER NOT NULL > ) WITH OIDS; > > CREATE INDEX "tbl_highscore_app4_tmp_index" ON > "public"."tbl_highscore_app4_tmp" > USING btree ("score"); > > > >