Did you have a long running trasnaction? Especially a prepared transaction, blocking the vacuum from reclaiming the space? On Fri, Jul 12, 2013 at 8:10 AM, Bradley McCune <bradley.mccune@xxxxxxxx> wrote: > David, > > (As a preface, I have already gone forward with completely rebuilding the > database which seems to have finally fixed the problem. Rebuilding the > table itself had no effect, and I couldn't wait much longer to move > forward.) > > Yes, this seems similar, however, the key difference being that VACUUM FULL > did not alleviate the problem. The extra "bloated" disk space was still > considered "in use" by the data server, and so it was never returned to the > system. I have a suspicion that the server was storing the table data in > pages in an inefficient manner (by unknown means) because we had roughly ~5x > the number of pages used on that TOAST table to store the same number of > tuples compared to other similar databases. > > Depending on how often you have to use VACUUM FULL, you might want to > consider tweaking the autovacuum to be more aggressive on that hot table to > keep it in check more often. (Recycling the disk space more efficiently > rather than sending it back to the server only to be reallocated to the > database again.) > > > On Fri, Jul 12, 2013 at 4:09 AM, David Welton <davidw@xxxxxxxxxxx> wrote: >> >> Hi, >> >> I have a very similar problem... details below. >> >> On Wed, Jul 3, 2013 at 5:19 PM, Paul Tilles <paul.tilles@xxxxxxxx> 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 >> >> http://www.dedasys.com/ > > > > > -- > Bradley D. J. McCune -- To understand recursion, one must first understand recursion. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
