pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Csaba Nagy <nagy@ecircle-ag.com>
To: Moritz Bayer <moritz.bayer@googlemail.com>
Cc: Harry Jackson <harryjackson@gmail.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Crashing DB or Server?
Date: Fri, 16 Dec 2005 15:32:47 +0100
Message-ID: <1134743566.14216.47.camel@coppola.muc.ecircle.de> (raw)
In-Reply-To: <c244500b0512160610p2600fb1fy@mail.gmail.com>
References: <c244500b0512160515p23eafee2u@mail.gmail.com>
	<45b42ce40512160538n7b77a2aexe9d13616f6c25a69@mail.gmail.com>
	<c244500b0512160610p2600fb1fy@mail.gmail.com>

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




view thread (4+ messages)

Message-ID: <1134743566.14216.47.camel@coppola.muc.ecircle.de>
Permalink:  ../1134743566.14216.47.camel@coppola.muc.ecircle.de/
Also on:    postgresql.org/message-id/1134743566.14216.47.camel@coppola.muc.ecircle.de

 · 

reply

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: nagy@ecircle-ag.com, moritz.bayer@googlemail.com, harryjackson@gmail.com
  Subject: Re: Crashing DB or Server?
  In-Reply-To: <1134743566.14216.47.camel@coppola.muc.ecircle.de>

* 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