On Mon, Oct 07, 2013 at 06:32:57PM -0400, Guy Rouillier wrote: - On 10/7/2013 5:58 PM, Steve Atkins wrote: - > - >On Oct 7, 2013, at 2:48 PM, Guy Rouillier <guy.rouillier@xxxxxxxxx> - >wrote: - > - >>We have a fairly large (1 TB) database we put on all SSDs because - >>of a very high insert and update rate (38 million rows/day). As - >>our business has grown, we've been running into space constraints, - >>so we went looking for files we might be able to delete. - >> - >>We found a large number (662 out of 1465 total ) and size (219 GB) - >>of files in the data directory whose name does not correspond to - >>an oid in the pg_class system catalog table. That amount of space - >>would address our current space constraint problems. Some of - >>these tables are recent (from today), while others are quite old - >>(large volume in August and May, with some smaller ones as far back - >>as February. - > - >You need to be looking at pg_class.relfilenode, not pg_class.oid. - >They're often the same value, but often not. - - Steve, thanks for the quick reply. I reran the calculations using - relfilenode instead of oid; they are now showing 214 unrecorded - filenodes consuming 163 GB. The older tables (on or before May) are no - longer showing as unrecorded. Of the filenodes that are still not - matching pg_class.relfilenode, I see just one that is consuming the - majority of the space: 614804 (153 files, 163 GB). - - Failed to mention our runtime platform: we are running Enterprise DB - 9.2.1.3 on 64-bit Linux (Oracle Linux Server release 6.3). EDB pointed - us to this note in the PG documentation - (http://www.postgresql.org/docs/9.2/static/storage-file-layout.html): - - Note that while a table's filenode often matches its OID, this is not - necessarily the case; some operations, like TRUNCATE, REINDEX, CLUSTER - and some forms of ALTER TABLE, can change the filenode while preserving - the OID. Avoid assuming that filenode and table OID are the same. Also, - for certain system catalogs including pg_class itself, - pg_class.relfilenode contains zero. The actual filenode number of these - catalogs is stored in a lower-level data structure, and can be obtained - using the pg_relation_filenode() function. - - So, I ran "select pg_relation_filenode(614804)" and got no results. Any - suggestions on how I can uncover the identify of this node? - - Thanks much. You could try oid2name: http://www.postgresql.org/docs/current/static/oid2name.html -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
