Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order to take advantage of autovacuum features and be in line with 30ish other PGSQL servers. This was done by a separate IT group who administrates the hardware, so we don't have much choice on any other upgrades (won't see 9+ for a while). The server exists in a very closed environment (isolated network, limited root privileges) 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.

Running the following SQL, it's fairly obvious there's an issue with a particular table, and, more specifically, its TOAST table.

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 20;

Which produces:

              relation              |  size  
------------------------------------+---------  
  pg_toast.pg_toast_16874           | 89 GB  
  fews00.warmstates                 | 1095 MB  
  ...  
(20 rows)

This TOAST table is for a table called "timeseries" which saves large records of blobbed data. A SUM(LENGTH(blob)/1024./1024.) of all the records in timeseries yields ~16GB for that column. There should be no reason this table's TOAST table should be as large as it is.

I've performed a VACUUM FULL VERBOSE ANALYZE timeseries, and the vacuum runs to completion with no errors.

INFO: vacuuming "pg_toast.pg_toast_16874"
INFO: "pg_toast_16874": found 22483 removable, 10475318 nonremovable row versions in 10448587 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 37 to 2036 bytes long.
There were 20121422 unused item pointers.
Total free space (including removable row versions) is 0 bytes. 4944885 pages are or will become empty, including 0 at the end of the table. 4944885 pages containing 0 free bytes are potential move destinations.
CPU 75.31s/29.59u sec elapsed 877.79 sec.
INFO: index "pg_toast_16874_index" now contains 10475318 row versions in 179931 pages
DETAIL: 23884 index row versions were removed.
101623 index pages have been deleted, 101623 are currently reusable.
CPU 1.35s/2.46u sec elapsed 21.07 sec.

REINDEXed the table which freed some space (~1GB). I can't CLUSTER the table as there isn't enough space on disk for the process, and I'm waiting to rebuild the table entirely as I'd like to find out why it is so much bigger than equivalent databases we have.

Ran a query from the PostgreSQL wiki here - "Show Database Bloat", and this is what I get:

current_database | schemaname |           tablename            | tbloat | wastedbytes |              iname              | ibloat | wastedibytes  
-----------------+------------+--------------------------------+--------+-------------+---------------------------------+--------+--------------  
ptrdb04          | fews00     | timeseries                     |    1.0 |           0 | idx_timeseries_synchlevel       |    0.0 |            0  
ptrdb04          | fews00     | timeseries                     |    1.0 |           0 | idx_timeseries_localavail       |    0.0 |            0  
ptrdb04          | fews00     | timeseries                     |    1.0 |           0 | idx_timeseries_expirytime       |    0.0 |            0  
ptrdb04          | fews00     | timeseries                     |    1.0 |           0 | idx_timeseries_expiry_null      |    0.0 |            0  
ptrdb04          | fews00     | timeseries                     |    1.0 |           0 | uniq_localintid                 |    0.0 |            0  
ptrdb04          | fews00     | timeseries                     |    1.0 |           0 | pk_timeseries                   |    0.1 |            0  
ptrdb04          | fews00     | idx_timeseries_expiry_null     |    0.6 |           0 | ?                               |    0.0 |            0

It looks like the database doesn't consider this space as "empty," at all, but I just don't see where all the disk space is coming from!

I suspect that this database server is deciding to use 4-5x as much disk space to save the same records pulled from the other data servers. My question is this: Is there a way I can verify the physical disk size of a row? I'd like to compare the size of one row on this database to another "healthy" database.

Thanks for any help you can provide!

UPDATE 1

I ended up rebuilding the table from a dumped schema due to its size (couldn't leave it alone for another day). After synchronizing the data, via the software synch process, the TOAST table was ~35GB; however, I could only account for ~9GB of it from that blob column which should be the longest in terms of values. Not sure where the other 26GB is coming from. CLUSTERed, VACUUM FULLed, and REINDEXed to no avail. The postgresql.conf files between the local and remote data servers are exactly the same. Is there any reason this database might be trying to store each record with a larger space on disk?

UPDATE 2 - Fixed

I finally decided to just completely rebuild the database from the ground up- even going as far as to reinstall the PostgreSQL84 packages on the system. The database path was reinitialized and tablespaces wiped clean. The 3rd party software synchronization process repopulated the tables, and the final size came out to be ~12GB! Unfortunately, this, in no way, helps to solve what the exact source of the issue was here. I'm going to watch it for a day or two and see if there are any major differences with how the revitalized database is handling the TOAST table and post those results here.

Relation Size


ptrdb04=> SELECT nspname || '.' || relname AS "relation",
ptrdb04->     pg_size_pretty(pg_relation_size(C.oid)) AS "size"
ptrdb04->   FROM pg_class C
ptrdb04->   LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
ptrdb04->   WHERE nspname NOT IN ('pg_catalog', 'information_schema')
ptrdb04->   ORDER BY pg_relation_size(C.oid) DESC
ptrdb04->   LIMIT 2;

        relation         |  size   
-------------------------+---------
 pg_toast.pg_toast_17269 | 18 GB
 fews00.warmstates       | 1224 MB
(2 rows)

VACUUM VERBOSE ANALYZE timeseries;

INFO:  "timeseries": found 12699 removable, 681961 nonremovable row versions in 58130 out of 68382 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 105847 unused item pointers.
0 pages are entirely empty.
CPU 0.83s/2.08u sec elapsed 33.36 sec.
INFO:  vacuuming "pg_toast.pg_toast_17269"
INFO:  scanned index "pg_toast_17269_index" to remove 2055849 row versions
DETAIL:  CPU 0.37s/2.92u sec elapsed 13.29 sec.
INFO:  "pg_toast_17269": removed 2055849 row versions in 518543 pages
DETAIL:  CPU 8.60s/3.21u sec elapsed 358.42 sec.
INFO:  index "pg_toast_17269_index" now contains 7346902 row versions in 36786 pages
DETAIL:  2055849 index row versions were removed.
10410 index pages have been deleted, 5124 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  "pg_toast_17269": found 1286128 removable, 2993389 nonremovable row versions in 1257871 out of 2328079 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 18847 unused item pointers.
0 pages are entirely empty.
CPU 26.56s/13.04u sec elapsed 714.97 sec.
INFO:  analyzing "fews00.timeseries"
INFO:  "timeseries": scanned 30000 of 68382 pages, containing 360192 live rows and 0 dead rows; 30000 rows in sample, 821022 estimated total rows

The only noticeable difference after the rebuild (other than disk usage) is

INFO:  "pg_toast_17269": found 1286128 removable, 2993389 nonremovable row versions
as @CraigRinger mentioned in a comment. The nonremovable row count is much smaller than before.

New question: Can other tables affect the size of another table? (via foreign keys and such) Rebuilding the table did nothing, yet rebuilding the whole database proved to fix the problem.

share|improve this question
    
Why didn't you upgrade straight to 9.2? It has even more improvements in the vaccum area than 8.4 (and 8.4 is going EOL next year anyway) –  a_horse_with_no_name Jul 4 '13 at 6:44
    
I've updated the post. Upgrading wasn't done by our shop and not necessarily by our request. Unfortunately, we don't have that option to upgrade to 9+. –  Brad Jul 4 '13 at 13:43
    
OK. I just wanted to make sure you weren't overlooking the obvious ;) –  a_horse_with_no_name Jul 4 '13 at 13:48

2 Answers 2

This:

INFO: "pg_toast_16874": found 22483 removable, 10475318 nonremovable row versions in 10448587 pages 22483 removable, 10475318 nonremovable row versions in 10448587 pages

suggests that the underlying issue is that something can still "see" those rows so they can't be removed.

The candidates for that are:

  • Lost prepared transactions. Check pg_catalog.pg_prepared_xact; it should be empty, and SHOW max_prepared_transactions should report zero.

  • Long-running sessions with an open, idle transaction. In PostgreSQL 8.4 and above this should only be an issue for SERIALIZABLE transactions. Check pg_catalog.pg_stat_activity for <IDLE> in transaction sessions.

Most likely you have a client that's failing to commit or rollback transactions during long idle periods.

If this doesn't turn out to be it, the next thing I'd check would be to do a sum of the octet_size of each column of the table of interest. Compare that to the pg_relation_size of the table and its TOAST side-table. If there's a big difference then the space consumed is likely by no longer visible rows and you probably do have table bloat issues. If they're quite similar, you can start narrowing down where the space use is by summing up the octet sizes per column, getting the top 'n' values, etc.

share|improve this answer
    
1) pg_prepared_xacts and max_prepared_transactions did indeed come back empty. 2) There are definitely some IDLE transactions from SELECT * FROM pg_stat_activity WHERE current_query LIKE '<IDLE>%'; which brings back about 30-40 results; however, this seems pretty normal. I checked a few "healthy" servers, and they were the same. –  Brad Jul 4 '13 at 16:00
    
3) Here is what I did. Looped through timeseries columns, pulling octet_length(column). Multiplied each value by the row count and summed them. For timeseries, I got ~430MB (close to the 493MB from pg_relation_size) and 438MB for the TOAST table (using columns chunk_id, chunk_seq, chunk_data). The estimations look correct, and the TOAST table is WAY off the relation_size by about 2 orders of magnitude (60GB today). It looks as if I have bloat, but not the traditional kind (unused bloat). Otherwise, a FULLVAC should take care of the problem. –  Brad Jul 4 '13 at 16:45
    
@Brad Idle sessions are fine, it's only idle sessions with open transactions that're an issue, ie <IDLE> in transaction, and only if they've (a) been idle for a while and (b) are using SERIALIZABLE isolation or you're on 8.3 or older. –  Craig Ringer Jul 4 '13 at 23:15
    
@Brad It's interesting that only the TOAST table appears to be bloated though. BTW, if you've been using VACUUM FULL a lot on a pre-9.0 server you'll want to REINDEX as VACUUM FULL on those versions could cause significant index bloat. I'm now wondering if someone's set an absurd FILLFACTOR on the toast table, though that should not let you go past 10x space consumption. –  Craig Ringer Jul 4 '13 at 23:20
    
Thanks for the IDLE clarification. I figured that's what you meant, but it's good to know for sure. As for FILLFACTOR, the table is using the default. FYI - According to the 8.4 CREATE TABLE documentation the default is 100, and you cannot set a FILLFACTOR for the TOAST table. –  Brad Jul 5 '13 at 13:24

I don't have any insight as to why it's bloated. But I did some searching and maybe this link has some insight: http://postgresql.1045698.n5.nabble.com/A-154-GB-table-swelled-to-527-GB-on-the-Slony-slave-How-to-compact-it-td5543034.html ... It's not your exact situation but perhaps it's close enough to help you get to the bottom of the phantom bloat.

However, I think the only way to compact that table at this point is to CLUSTER it. Since you're low on disk space, that's a problem.

Here's my suggestion for that: create a tablespace on a different drive with a lot of extra space, then assign your problem table to that tablespace. PostgreSQL will copy the table over to the new tablespace (probably table-locking it in the process, so you'll need a maintenance window). Then VACFULL the table (clears out most of the old space consumed by the table in the default tablespace). Then CLUSTER the table and it should compact itself. Then put it back in the default tablespace and run VACFULL again (to clear out the unused space in the new tablespace).

share|improve this answer
    
I actually ended up rebuilding the table (dumping the schema and rebuilding from that) and pulling the data directly over from one of the remote databases. After the process was done, the database was still 35GB with only 9GB of it accounted for by "wide" blob column. CLUSTERed, VACUUM FULLed, REINDEXed, and I'm still sitting on a ton of mysterious disk usage. –  Brad Jul 3 '13 at 23:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.