Thread: oids on disk not in pg_class
We have a fairly large (1 TB) database we put on all SSDs because of a very high insert and update rate (). 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. What kinds of data are written to disk without being registered in pg_class? How can we determine which (if any) are safe to delete? Thanks. -- Guy Rouillier
On Oct 7, 2013, at 2:48 PM, Guy Rouillier <guy.rouillier@gmail.com> wrote: > We have a fairly large (1 TB) database we put on all SSDs because of a very high insert and update rate (). As our businesshas 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 correspondto an oid in the pg_class system catalog table. That amount of space would address our current space constraintproblems. Some of these tables are recent (from today), while others are quite old (large volume in August andMay, 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. Cheers, Steve
On 10/7/2013 5:58 PM, Steve Atkins wrote: > > On Oct 7, 2013, at 2:48 PM, Guy Rouillier <guy.rouillier@gmail.com> > 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. -- Guy Rouillier
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@gmail.com> - >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
On 10/7/2013 6:46 PM, David Kerr wrote: > On Mon, Oct 07, 2013 at 06:32:57PM -0400, Guy Rouillier wrote: - 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 David, thanks for the suggestion. I ran oid2name on all 3 databases in this PG instance (including postgres) and filenode 614804 is not reported in any of them. Judging by the size of the files and the number of them, I'm pretty sure this is one of our monthly stats tables. We record usage stats, keeping a separate table for each month; we keep just 3 months online because of the size. I ran cat against the first file in the series (by date), and the data I could see seems to confirm that it is a stats table. Somehow, it has gotten orphaned. Does the physical file have any useful information (in this scenario) in the first N bytes, and if so, is there a utility that can print out that header info? These huge stats files (about 260 GB per month, including index) have a single primary key. I'm guessing that the index files got orphaned at the same time the data files did; obviously, I'd like to remove those as well if I can identify which they are. Thanks. -- Guy Rouillier