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 9A4029DC84C for ; Fri, 16 Dec 2005 10:17:08 -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 31712-05 for ; Fri, 16 Dec 2005 10:17:11 -0400 (AST) X-Greylist: delayed 00:06:17.104624 by SQLgrey- Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.207]) by postgresql.org (Postfix) with ESMTP id D8CE19DCB7A for ; Fri, 16 Dec 2005 10:17:04 -0400 (AST) Received: by zproxy.gmail.com with SMTP id i11so695922nzi for ; Fri, 16 Dec 2005 06:17:09 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=googlemail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=O1S52Gvg3RsmYXia1d/tfakWFH1CR9A/JIImgIBH1PpHCDaAT51bRzjynL8nn1nsNbBA6HZ7FrQAcPRvrXN/r4t1HAoOmbhOZvayXYzedJr3JQfhtj2qaSg7LZG12gLBS59wuTNggIylakKuzcsA6w9QHaxpodwP2CHweixG51s= Received: by 10.64.242.14 with SMTP id p14mr1321900qbh; Fri, 16 Dec 2005 06:10:51 -0800 (PST) Received: by 10.64.233.5 with HTTP; Fri, 16 Dec 2005 06:10:51 -0800 (PST) Message-ID: Date: Fri, 16 Dec 2005 15:10:51 +0100 From: Moritz Bayer To: Harry Jackson Subject: Re: Crashing DB or Server? Cc: pgsql-performance@postgresql.org In-Reply-To: <45b42ce40512160538n7b77a2aexe9d13616f6c25a69@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5805_29498591.1134742251334" References: <45b42ce40512160538n7b77a2aexe9d13616f6c25a69@mail.gmail.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.442 required=5 tests=[AWL=0.441, HTML_MESSAGE=0.001, UPPERCASE_25_50=0] X-Spam-Score: 0.442 X-Spam-Level: X-Archive-Number: 200512/394 X-Sequence-Number: 16215 ------=_Part_5805_29498591.1134742251334 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 :=3D $1; i_score :=3D $2; i_rank :=3D 1; if i_score <=3D 0 then SELECT INTO i_rank max(id) FROM tbl_highscore_app4_tmp; if i_rank IS null then i_rank =3D 1; else i_rank =3D i_rank +1; end if; else SELECT INTO i_rank max(id) FROM tbl_highscore_app4_tmp WHERE score>=3Di_score; if i_rank IS null then i_rank =3D 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"); ------=_Part_5805_29498591.1134742251334 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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("use= rid")
) 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 :=3D $1; 
i_score :=3D $2; 
i_rank :=3D 1; 
 if i_score <=3D 0 then 
            &nb= sp; SELECT INTO i_rank max(id) FROM   tbl_highscore_app4_tmp; 
             if= i_rank IS null then   
            &nb= sp;     i_rank =3D 1; 
             el= se   
            &nb= sp;     i_rank =3D i_rank +1; 
            end if;&= nbsp;
 else 
        SELECT INTO i_rank max(id) FROM tbl_highscore_app4_tmp WHERE score>=3Di_score;  if i_rank IS null then    i_rank =3D 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");




------=_Part_5805_29498591.1134742251334--