X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id B10309FB381 for ; Mon, 7 Aug 2006 22:27:47 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 46280-05 for ; Tue, 8 Aug 2006 01:27:38 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from web57211.mail.re3.yahoo.com (web57211.mail.re3.yahoo.com [216.252.111.227]) by postgresql.org (Postfix) with SMTP id 95B459FB22B for ; Mon, 7 Aug 2006 22:27:38 -0300 (ADT) Received: (qmail 20617 invoked by uid 60001); 8 Aug 2006 01:27:36 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.ca; h=Message-ID:Received:Date:From:Reply-To:Subject:To:MIME-Version:Content-Type; b=o34aoWgjbR2bjW2ZBa5YMjFobav4UYIo3VnG/rCG5iGdzqCm1giQUMToV8ZIrZ18nhH5ncKyvmq/tL2AJGUp7dgnushyDtGvwCj+2tygPHWJElWC6tDxEYZPW3dxVVEeO3IQ0PzYfzi82oaLAQbUrJ35Gl6V+kwIRi7y40vnjp0= ; Message-ID: <20060808012736.20614.qmail@web57211.mail.re3.yahoo.com> Received: from [203.144.27.9] by web57211.mail.re3.yahoo.com via HTTP; Mon, 07 Aug 2006 18:27:36 PDT Date: Mon, 7 Aug 2006 18:27:36 -0700 (PDT) From: Geoff Parker Reply-To: Geoff Parker Subject: disk usage spike To: pgsql admin MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1653046801-1155000456=:18097" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200608/80 X-Sequence-Number: 22624 --0-1653046801-1155000456=:18097 Content-Type: text/plain; charset=us-ascii I have a database with about 155GB of binary data in large objects and 15GB of data in other tables. In compressed form, it consumes about 60GB on disk. On friday, the amount of disk usage increased to 240GB (over a period of 5 minutes), stayed at 240GB for 2 days, and then returned to 60GB (again over a period of about 5 minutes). What would cause such a spike in disk usage? And why would it stay at such a high level for 2 days? This is postgresql 8.1.4 on a slightly modified RHEL4 system (2.6.17 kernel). At the time the spike occurred (about 2pm), I may have been running some simple queries against various tables to determine usage statistics (eg, how many rows in table a, sum of column b in table c, etc (maximum of 100,000 rows in the tables, <10 columns), no nasty joins or anything). Also, I may have extracted a couple of large objects in full (not more than 200MB). Otherwise, the database processes a few (< 20) small (< 1k data) inserts/updates/deletes per minute. This would have been the only traffic to the database after friday afternoon when I went home. Autovacuum is turned on and the system had finished importing a large amount of data (50GB) earlier (4am) that morning. Is it possible the spike was caused by autovacuum re-indexing the pg_largeobject table? Thanks, Geoff. --0-1653046801-1155000456=:18097 Content-Type: text/html; charset=us-ascii
I have a database with about 155GB of binary data in large objects and 15GB of data in other tables.  In compressed form, it consumes about 60GB on disk.  On friday, the amount of disk usage increased to 240GB (over a period of 5 minutes), stayed at 240GB for 2 days, and then returned to 60GB (again over a period of about 5 minutes).

What would cause such a spike in disk usage? And why would it stay at such a high level for 2 days? 

This is postgresql 8.1.4 on a slightly modified RHEL4 system (2.6.17 kernel).

At the time the spike occurred (about 2pm), I may have been running some simple queries against various tables to determine usage statistics (eg, how many rows in table a, sum of column b in table c, etc (maximum of 100,000 rows in the tables,  <10 columns), no nasty joins or anything).  Also, I may have extracted a couple of large objects in full (not more than 200MB). 

Otherwise, the database processes a few (< 20) small (< 1k data) inserts/updates/deletes per minute.  This would have been the only traffic to the database after friday afternoon when I went home.

Autovacuum is turned on and the system had finished importing a large amount of data (50GB) earlier (4am) that morning.    Is it possible the spike was caused by autovacuum re-indexing the pg_largeobject table?

Thanks,
Geoff.




--0-1653046801-1155000456=:18097--