From: Moritz Bayer <moritz.bayer@googlemail.com>
To: Harry Jackson <harryjackson@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Crashing DB or Server?
Date: Fri, 16 Dec 2005 15:10:51 +0100
Message-ID: <c244500b0512160610p2600fb1fy@mail.gmail.com> (raw)
In-Reply-To: <45b42ce40512160538n7b77a2aexe9d13616f6c25a69@mail.gmail.com>
References: <c244500b0512160515p23eafee2u@mail.gmail.com>
<45b42ce40512160538n7b77a2aexe9d13616f6c25a69@mail.gmail.com>
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");
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: moritz.bayer@googlemail.com, harryjackson@gmail.com
Subject: Re: Crashing DB or Server?
In-Reply-To: <c244500b0512160610p2600fb1fy@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox