pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCrashing DB or Server?
4+ messages / 3 participants
[nested] [flat]
* Crashing DB or Server?
@ 2005-12-16 13:15 Moritz Bayer <moritz.bayer@googlemail.com>
2005-12-16 13:38 ` Re: Crashing DB or Server? Harry Jackson <harryjackson@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Moritz Bayer @ 2005-12-16 13:15 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Crashing DB or Server?
2005-12-16 13:15 Crashing DB or Server? Moritz Bayer <moritz.bayer@googlemail.com>
@ 2005-12-16 13:38 ` Harry Jackson <harryjackson@gmail.com>
2005-12-16 14:10 ` Re: Crashing DB or Server? Moritz Bayer <moritz.bayer@googlemail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Harry Jackson @ 2005-12-16 13:38 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Crashing DB or Server?
2005-12-16 13:15 Crashing DB or Server? Moritz Bayer <moritz.bayer@googlemail.com>
2005-12-16 13:38 ` Re: Crashing DB or Server? Harry Jackson <harryjackson@gmail.com>
@ 2005-12-16 14:10 ` Moritz Bayer <moritz.bayer@googlemail.com>
2005-12-16 14:32 ` Re: Crashing DB or Server? Csaba Nagy <nagy@ecircle-ag.com>
0 siblings, 1 reply; 4+ messages in thread
From: Moritz Bayer @ 2005-12-16 14:10 UTC (permalink / raw)
To: Harry Jackson <harryjackson@gmail.com>; +Cc: pgsql-performance
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");
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Crashing DB or Server?
2005-12-16 13:15 Crashing DB or Server? Moritz Bayer <moritz.bayer@googlemail.com>
2005-12-16 13:38 ` Re: Crashing DB or Server? Harry Jackson <harryjackson@gmail.com>
2005-12-16 14:10 ` Re: Crashing DB or Server? Moritz Bayer <moritz.bayer@googlemail.com>
@ 2005-12-16 14:32 ` Csaba Nagy <nagy@ecircle-ag.com>
0 siblings, 0 replies; 4+ messages in thread
From: Csaba Nagy @ 2005-12-16 14:32 UTC (permalink / raw)
To: Moritz Bayer <moritz.bayer@googlemail.com>; +Cc: Harry Jackson <harryjackson@gmail.com>; pgsql-performance
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");
>
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-12-16 14:32 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-16 13:15 Crashing DB or Server? Moritz Bayer <moritz.bayer@googlemail.com>
2005-12-16 13:38 ` Harry Jackson <harryjackson@gmail.com>
2005-12-16 14:10 ` Moritz Bayer <moritz.bayer@googlemail.com>
2005-12-16 14:32 ` Csaba Nagy <nagy@ecircle-ag.com>
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