From: | David Welton <davidw(at)dedasys(dot)com> |
---|---|
To: | Paul Tilles <paul(dot)tilles(at)noaa(dot)gov> |
Cc: | Postgres general mailing list <pgsql-general(at)postgresql(dot)org>, Brad Mccune <bradley(dot)mccune(at)noaa(dot)gov> |
Subject: | Re: V8.4 TOAST table problem |
Date: | 2013-07-12 08:09:22 |
Message-ID: | CA+b9R_u=WuiEO=Vm7NMzxmw2XvB0AjZf7tkcTjcbeTQ10nnjmw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I have a very similar problem... details below.
On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles <paul(dot)tilles(at)noaa(dot)gov> wrote:
> Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order to
> take advantage of autovacuum features. This server exists in a very closed
> environment (isolated network, limited root privileges; this explains the
> older software in use) and runs on RHEL5.5 (i686). After the upgrade, the
> database has constantly been growing to the tune of 5-6 GB a day. Normally,
> the database, as a whole, is ~20GB; currently, it is ~89GB. We have a couple
> other servers which run equivalent databases and actually synchronize the
> records to each other via a 3rd party application (one I do not have access
> to the inner workings). The other databases are ~20GB as they should be.
Our machine is an Ubuntu 12.04 system running on AWS, so it's a 64 bit system:
PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
> Running the following SQL, it's fairly obvious there's an issue with a
> particular table, and, more specifically, its TOAST table.
Same thing here: we have a table with around 2-3 megs of data that is
blowing up to *10 gigs*.
> This TOAST table is for a table called "timeseries" which saves large
> records of blobbed data. ASUM(LENGTH(blob)/1024./1024.) of all the records
> in timeseries yields ~16GB for that column. There should be [b]no reason[/b]
> this table's TOAST table should be as large as it is.
Similar situation: it's a bytea column that gets "a lot" of updates;
in the order of 10's of thousands a day.
> I've performed a VACUUM FULL VERBOSE ANALYZE timeseries, and the vacuum runs
> to completion with no errors.
VACUUM FULL fixes the problem for us by recouping all the wasted disk
space. I don't have the knowledge to investigate much further on my
own, but I'd be happy to try out a few things. The database is,
unfortunately, sensitive data that I can't share, but I could probably
script a similar situation...
--
David N. Welton
From | Date | Subject | |
---|---|---|---|
Next Message | Amit Langote | 2013-07-12 09:05:25 | Re: Full text search |
Previous Message | itishree sukla | 2013-07-12 07:26:53 | Re: Full text search |