Re: Crashing DB or Server?

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