X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 3721DD1B179 for ; Mon, 10 May 2004 14:36:14 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 26290-06 for ; Mon, 10 May 2004 14:35:42 -0300 (ADT) Received: from leticia.terra.com.br (leticia.terra.com.br [200.154.55.226]) by svr1.postgresql.org (Postfix) with ESMTP id 459EAD1B181 for ; Mon, 10 May 2004 14:35:38 -0300 (ADT) Received: from talara.terra.com.br (talara.terra.com.br [200.154.55.136]) by leticia.terra.com.br (Postfix) with ESMTP id EBFC73C863 for ; Mon, 10 May 2004 14:35:33 -0300 (BRT) Received: from suport01 (mlsrj200152101p142.mls.com.br [200.152.101.142]) (authenticated user teouique) by talara.terra.com.br (Postfix) with ESMTP id 85A553C093 for ; Mon, 10 May 2004 14:35:32 -0300 (BRT) Message-ID: <00d401c436b5$5b03e960$5f00a8c0@suport01> From: "Anderson Boechat Lopes" To: References: <00b901c43697$333ec5d0$5f00a8c0@suport01> <409F8FFB.1010708@frodo.hserus.net> Subject: Re: Why queries takes too much time to execute? Date: Mon, 10 May 2004 14:36:39 -0300 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/59 X-Sequence-Number: 6890 Hum... now i think i´m beginning to understand. The vacuum analyse is recommended to perform at least every day, after adding or deleting a large number of records, and not vacuum full analyse. I´ve performed the vacuum full analyse every day and after some time i´ve noticed the database was corrupted. I couldn´t select anything any more. Do you think if i perform vacuum analyse once a day and perform vacuum full analyse once a week, i will get to fix this problem? Thanks for help me, folks. PS: Sorry for my grammar mistakes. My writting is not so good. :) ----- Original Message ----- From: "Shridhar Daithankar" To: "Anderson Boechat Lopes" Cc: Sent: Monday, May 10, 2004 11:21 AM Subject: Re: [PERFORM] Why queries takes too much time to execute? > Anderson Boechat Lopes wrote: > > Hi. > > > > I´m new here and i´m not sure if this is the right email to solve my > > problem. > > > > Well, i have a very large database, with vary tables and very > > registers. Every day, too many operations are perfomed in that DB, with > > queries that insert, delete and update. Once a week some statistics are > > collected using vacuum analyze. > > i guess you need to run it much more frequently than that. Thought you haven't > given actual size of data etc., once or twice per day should be much better. > > > > The problem is after a period of time (one month, i think), the > > queries takes too much time to perform. A simple update can take 10 > > seconds or more to perform. > > You need to vacuum full once in a while and setup FSM parameters correctly. > > > > If i make a backup, drop and recreate the DB, everything goes back > > normal. > > > > Could anyone give me any guidance? > > Check following for basic performance tuning > > http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html > http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html > > HTH > > Shridhar > >