Обсуждение: Crashing DB or Server?

Hello group,

I've got a really bad problem with my postgres DB and server.
It is a linux machine with 1GB of RAM and 2 CPUs.
The postgres Version is 7.4.

The problem is, that once the postmaster has started, the System is just about useless. At least querys to the db are so slow, that it most often runs into timeouts. First I thought something was wrong with my querys or I forgot to close the connections. But this isn't the case, I cut all the connections to the server so that there are no incoming requests. Still, once I start the postmaster and look into the statistics of the top-command, the IOWAIT parameter of all CPUs are at about 100%.

This is really weird, just a few hours ago the machine run very smooth serving the data for a big portal.

Has anybody an idea what might have happened here?
I need a quick solution, since I'm talking about an live server that should be running 24 hours a day.

Thanks in advance,
Moritz

PS: I'm not a administrator so I don't know if I have wrote down all the relevant data. If not, please ask for it and give me a hint how to get them

On 12/16/05, Moritz Bayer <moritz.bayer@googlemail.com> wrote:
>  This is really weird, just a few hours ago the machine run very smooth
> serving the data for a big portal.

Can you log the statements that are taking a long time and post them
to the list with the table structures and indexes for the tables being
used.

To do this turn on logging for statements taking a long time, edit
postgresql.conf file and change the following two parameters.

log_min_duration_statement = 2000 # 2 seconds

Your log should now be catching the statements that are slow. Then use
the statements to get the explain plan ie

dbnamr=# explain [sql thats taking a long time]

We would also need to see the table structures.

dbname=# \d [table name of each table in above explain plan]

>  Has anybody an idea what might have happened here?
>  I need a quick solution, since I'm talking about an live server that should
> be running 24 hours a day.

It may be that the planner has started to pick a bad plan. This can
happen if the database is regularly changing and the stats are not up
to date. I believe it can happen even if the stats are up to date but
is much less likely to do so.

It might also be an idea to vacuum the database.

dbname=# VACUUM ANALYZE;

This will load the server up for a while though.

--
http://www.hjackson.org
http://www.uklug.co.uk

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

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