Re: Crashing DB or Server?

[Moritz Bayer <moritz.bayer@xxxxxxxxxxxxxx>]

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");