From teouique@terra.com.br Mon May 10 14:00:12 2004 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 26E23D1F0D7 for ; Mon, 10 May 2004 11:00:12 -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 38544-08 for ; Mon, 10 May 2004 10:59:53 -0300 (ADT) Received: from ibague.terra.com.br (ibague.terra.com.br [200.154.55.225]) by svr1.postgresql.org (Postfix) with ESMTP id 23260D1E6A7 for ; Mon, 10 May 2004 10:59:49 -0300 (ADT) Received: from pasto.terra.com.br (pasto.terra.com.br [200.154.55.137]) by ibague.terra.com.br (Postfix) with ESMTP id CF49AEC161 for ; Mon, 10 May 2004 10:59:52 -0300 (BRT) Received: from suport01 (mlsrj200152101p142.mls.com.br [200.152.101.142]) (authenticated user teouique) by pasto.terra.com.br (Postfix) with ESMTP id 06A5F3C00A for ; Mon, 10 May 2004 10:59:51 -0300 (BRT) Message-ID: <00b901c43697$333ec5d0$5f00a8c0@suport01> From: "Anderson Boechat Lopes" To: Subject: Why queries takes too much time to execute? Date: Mon, 10 May 2004 11:00:43 -0300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00B6_01C4367E.0A0A8FA0" 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.5 tagged_above=0.0 required=5.0 tests=HTML_40_50, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200405/56 X-Sequence-Number: 6887 This is a multi-part message in MIME format. ------=_NextPart_000_00B6_01C4367E.0A0A8FA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi. I=B4m new here and i=B4m not sure if this is the right email to solve m= y 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. =20=20=20=20 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 mor= e to perform. If i make a backup, drop and recreate the DB, everything goes back norm= al. Could anyone give me any guidance? ------=_NextPart_000_00B6_01C4367E.0A0A8FA0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
    Hi.
 
    I=B4m n= ew here and=20 i=B4m not sure if this is the right email to solve my problem.
 
    Well, i= have a=20 very large database, with vary tables and very registers. Every day, t= oo=20 many operations are perfomed in that DB, with queries that insert, delete a= nd=20 update. Once a week some statistics are collected using vacuum=20 analyze.
    =
    The pro= blem is=20 after a period of time (one month, i think), the queries takes too muc= h=20 time to perform. A simple update can take 10 seconds or more to=20 perform.
 
    If i ma= ke a=20 backup, drop and recreate the DB, everything goes back=20 normal.
 
    Could= =20 anyone give me any guidance?
------=_NextPart_000_00B6_01C4367E.0A0A8FA0-- From shridhar@frodo.hserus.net Mon May 10 14:22:08 2004 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 From chriskl@familyhealth.com.au Mon May 10 14:24:39 2004 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 E3BC5D1E6A7 for ; Mon, 10 May 2004 11:24:39 -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 41256-04 for ; Mon, 10 May 2004 11:24:23 -0300 (ADT) Received: from vscan02.westnet.com.au (vscan02.westnet.com.au [203.10.1.132]) by svr1.postgresql.org (Postfix) with ESMTP id 21891D1E60E for ; Mon, 10 May 2004 11:24:19 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by localhost (Postfix) with ESMTP id 153F5101408; Mon, 10 May 2004 22:24:21 +0800 (WST) Received: from [192.168.1.9] (dsl-202-72-133-22.wa.westnet.com.au [202.72.133.22]) by vscan02.westnet.com.au (Postfix) with ESMTP id 9C2F010112F; Mon, 10 May 2004 22:24:20 +0800 (WST) Message-ID: <409F9098.20700@familyhealth.com.au> Date: Mon, 10 May 2004 22:24:24 +0800 From: Christopher Kings-Lynne User-Agent: Mozilla Thunderbird 0.6 (Windows/20040502) 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=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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/58 X-Sequence-Number: 6889 > 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. Have vacuum analyze running once an HOUR if it's very busy. If you are using 7.4, run the pg_autovacuum daemon that's in contrib/pg_autovacuum. > 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. If running vacuum analyze once an hour doesn't help, try running a vacuum full once a week or something to fix the problem. Also, try upgrading to 7.4 which has indexes that won't suffer from bloat. Chris From teouique@terra.com.br Mon May 10 17:36:14 2004 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 > > From cbbrowne@acm.org Mon May 10 19:17:47 2004 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 5618DD1B1CE for ; Mon, 10 May 2004 16:17:47 -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 18268-03 for ; Mon, 10 May 2004 16:17:28 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 561DCD1B196 for ; Mon, 10 May 2004 16:17:26 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i4AJHPSx045023 for ; Mon, 10 May 2004 19:17:25 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i4AJA9eB039439 for pgsql-performance@postgresql.org; Mon, 10 May 2004 19:10:10 GMT From: Chris Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: Why queries takes too much time to execute? Date: Mon, 10 May 2004 14:11:21 -0400 Organization: cbbrowne Computing Inc Lines: 43 Message-ID: <60llk05f06.fsf@dev6.int.libertyrms.info> References: <00b901c43697$333ec5d0$5f00a8c0@suport01> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.110002 (No Gnus v0.2) XEmacs/21.4 (Security Through Obscurity, linux) Cancel-Lock: sha1:yhcih0Taht1Fj6m7FEZOe9EMeP0= To: pgsql-performance@postgresql.org 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/62 X-Sequence-Number: 6893 teouique@terra.com.br ("Anderson Boechat Lopes") writes: >     I´m new here and i´m not sure if this is the right email to > solve my problem. This should be OK... >     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. > >     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. It seems fairly likely that two effects are coming in... -> The tables that are being updated have lots of dead tuples. -> The vacuums aren't doing much good because the number of dead tuples is so large that you blow out the FSM (Free Space Map), and thus they can't free up space. -> Another possibility is that if some tables shrink to small size, and build up to large size (we see this with the _rserv_log_1_ and _rserv_log_2_ tables used by the eRServ replication system), the statistics may need to be updated a LOT more often. You might want to consider running VACUUM a whole lot more often than once a week. If there is any regular time that the system isn't terribly busy, you might want to vacuum some or all tables at that time. pg_autovacuum might be helpful; it will automatically do vacuums on tables when they have been updated heavily. There may be more to your problem, but VACUUMing more would allow us to get rid of "too many dead tuples around" as a cause. -- "cbbrowne","@","acm.org" http://cbbrowne.com/info/x.html Would-be National Mottos: USA: "There oughta' be a law!" From scott.marlowe@ihs.com Mon May 10 20:00:20 2004 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 6F824D1B1E5 for ; Mon, 10 May 2004 17:00:20 -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 96326-02 for ; Mon, 10 May 2004 16:59:46 -0300 (ADT) Received: from mail1.ihs.com (mail1.ihs.com [170.207.70.222]) by svr1.postgresql.org (Postfix) with ESMTP id 4579AD1B1EE for ; Mon, 10 May 2004 16:59:44 -0300 (ADT) Received: from css120.ihs.com (css120.ihs.com [170.207.105.120]) by mail1.ihs.com (8.12.10/8.12.10) with ESMTP id i4AJxHuX027617; Mon, 10 May 2004 13:59:17 -0600 (MDT) Date: Mon, 10 May 2004 13:59:12 -0600 (MDT) From: "scott.marlowe" To: Anderson Boechat Lopes Cc: Subject: Re: Why queries takes too much time to execute? In-Reply-To: <00d401c436b5$5b03e960$5f00a8c0@suport01> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=ISO-8859-1 Content-Transfer-Encoding: 8BIT X-IHS-MailScanner: Found to be clean X-IHS-MailScanner-SpamCheck: X-IHS-MailScanner-Envelope-Sender: scott.marlowe@ihs.com 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/64 X-Sequence-Number: 6895 On Mon, 10 May 2004, Anderson Boechat Lopes wrote: > 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. Hold it right there, full stop. If you've got corruption you've either found a rare corner case in postgresql (unlikely, corruption is not usually a big problem for postgresql) OR you have bad hardware. Test your RAM, CPUs, and hard drives before going any further. Data corruption, 99% of the time, is not the fault of postgresql but the fault of the hardware.