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 15762D1E5E7 for ; Mon, 10 May 2004 11:22:08 -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 33197-08 for ; Mon, 10 May 2004 11:21:51 -0300 (ADT) Received: from frodo.hserus.net (frodo.hserus.net [204.74.68.40]) by svr1.postgresql.org (Postfix) with ESMTP id 57AF8D1F17F for ; Mon, 10 May 2004 11:21:47 -0300 (ADT) Received: from concord.pspl.co.in ([202.54.11.72]:65027 helo=frodo.hserus.net) by frodo.hserus.net with asmtp (Cipher TLSv1:DHE-RSA-AES256-SHA:256) (Exim 4.33 #1) id 1BNBfC-000AB6-94 by authid with plain; Mon, 10 May 2004 19:51:50 +0530 Message-ID: <409F8FFB.1010708@frodo.hserus.net> Date: Mon, 10 May 2004 19:51:47 +0530 From: Shridhar Daithankar User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.6b) Gecko/20031205 Thunderbird/0.4 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Anderson Boechat Lopes Cc: pgsql-performance@postgresql.org Subject: Re: Why queries takes too much time to execute? References: <00b901c43697$333ec5d0$5f00a8c0@suport01> In-Reply-To: <00b901c43697$333ec5d0$5f00a8c0@suport01> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit 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/57 X-Sequence-Number: 6888 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