Thread: thousand unrelated data files in pg_default tablespace
Hello I found a PostgreSQL 8.3 server (Linux) used for large OLAP where the data directory is bloating. There are more than one hundred thousand files - 8KB or 0KB long. The filenames are not transformable to names via oid2name. Does somebody know about similar bug? Regards Pavel Stehule
Pavel Stehule <pavel.stehule@gmail.com> writes:
> I found a PostgreSQL 8.3 server (Linux) used for large OLAP where the
> data directory is bloating. There are more than one hundred thousand
> files - 8KB or 0KB long. The filenames are not transformable to names
> via oid2name. Does somebody know about similar bug?
1. 8.3.what?
2. Any signs of distress in the postmaster log? I'm wondering about
being unable to complete checkpoints, or repeated backend crashes that
might cause leakage of temp tables.
3. What's in the files --- do they appear to be tables, indexes, random
temp files from sorts/hashes, or what? pg_filedump might help you here.
regards, tom lane
2010/8/30 Tom Lane <tgl@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> I found a PostgreSQL 8.3 server (Linux) used for large OLAP where the >> data directory is bloating. There are more than one hundred thousand >> files - 8KB or 0KB long. The filenames are not transformable to names >> via oid2name. Does somebody know about similar bug? > > 1. 8.3.what? > postgres=# select version(); version ----------------------------------------------------------------------------------------------------------PostgreSQL 8.3.6on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42) (1 row) > 2. Any signs of distress in the postmaster log? I'm wondering about > being unable to complete checkpoints, or repeated backend crashes that > might cause leakage of temp tables. No, there are nothing > > 3. What's in the files --- do they appear to be tables, indexes, random > temp files from sorts/hashes, or what? pg_filedump might help you here. > I have to contact admin tomorrow. For now - one half was zero length, second half was almost empty. These files are in directory related to pg_default tablespace. Regards Pavel Stehule > regards, tom lane >
Hello there is a dump from 8KB files Regard Pavel Stehule > > ******************************************************************* > * PostgreSQL File/Block Formatted Dump Utility - Version 8.3.0 > * > * File: /srv/postgresql/data/base/3400014/27059918 > * Options used: None > * > * Dump created on: Tue Aug 31 12:57:23 2010 > ******************************************************************* > > Block 0 ******************************************************** > <Header> ----- > Block Offset: 0x00000000 Offsets: Lower 40 (0x0028) > Block: Size 8192 Version 4 Upper 8000 (0x1f40) > LSN: logid 0 recoff 0x00000000 Special 8192 (0x2000) > Items: 4 Free Space: 7960 > TLI: 0x0000 Prune XID: 0x00000000 Flags: 0x0000 () > Length (including item array): 40 > > <Data> ------ > Item 1 -- Length: 47 Offset: 8144 (0x1fd0) Flags: NORMAL > Item 2 -- Length: 47 Offset: 8096 (0x1fa0) Flags: NORMAL > Item 3 -- Length: 47 Offset: 8048 (0x1f70) Flags: NORMAL > Item 4 -- Length: 47 Offset: 8000 (0x1f40) Flags: NORMAL > > > *** End of File Encountered. Last Block Read: 0 *** > $ ls -l /srv/postgresql/data/base/3400014/27059918 > -rw------- 1 postgres postgres 8192 Jul 1 06:28 /srv/postgresql/data/base/3400014/27059918 > > > > $ ./pg_filedump /srv/postgresql/data/base/3400014/27059926 > > ******************************************************************* > * PostgreSQL File/Block Formatted Dump Utility - Version 8.3.0 > * > * File: /srv/postgresql/data/base/3400014/27059926 > * Options used: None > * > * Dump created on: Tue Aug 31 13:00:17 2010 > ******************************************************************* > > Block 0 ******************************************************** > <Header> ----- > Block Offset: 0x00000000 Offsets: Lower 48 (0x0030) > Block: Size 8192 Version 4 Upper 8176 (0x1ff0) > LSN: logid 0 recoff 0x00000000 Special 8176 (0x1ff0) > Items: 6 Free Space: 8128 > TLI: 0x0001 Prune XID: 0x00000000 Flags: 0x0000 () > Length (including item array): 48 > > BTree Meta Data: Magic (0x00053162) Version (2) > Root: Block (0) Level (0) > FastRoot: Block (0) Level (0) > > <Special Section> ----- > BTree Index Section: > Flags: 0x0008 (META) > Blocks: Previous (0) Next (0) Level (0) CycleId (0) > > > *** End of File Encountered. Last Block Read: 0 *** > > $ ls -l /srv/postgresql/data/base/3400014/27059918 > -rw------- 1 postgres postgres 8192 Jul 1 06:28 /srv/postgresql/data/base/3400014/27059918 > > > > Pavel Stehule <pavel.stehule@gmail.com> > > 31.08.2010 09:32 > > To > robert.moucha@lmc.eu > cc > Subject > Re: [HACKERS] thousand unrelated data files in pg_default tablespace > > > > > Zdar, > > preposilam ti report > > 2010/8/30 Tom Lane <tgl@sss.pgh.pa.us>: > > Pavel Stehule <pavel.stehule@gmail.com> writes: > >> I found a PostgreSQL 8.3 server (Linux) used for large OLAP where the > >> data directory is bloating. There are more than one hundred thousand > >> files - 8KB or 0KB long. The filenames are not transformable to names > >> via oid2name. Does somebody know about similar bug? > > > > 1. 8.3.what? > > > > 2. Any signs of distress in the postmaster log? I'm wondering about > > being unable to complete checkpoints, or repeated backend crashes that > > might cause leakage of temp tables. > > > > 3. What's in the files --- do they appear to be tables, indexes, random > > temp files from sorts/hashes, or what? pg_filedump might help you here. > > > > muzes se na ten pg_filedump podivat a projet to tim, myslim, ale ze se > to bude muset od nekud stahnout a prelozit > > Pavel > > > > regards, tom lane > > >
Pavel Stehule <pavel.stehule@gmail.com> writes:
> there is a dump from 8KB files
Well, those certainly look like tables/indexes not temp files.
So we can rule out one theory.
You're *certain* these aren't referenced from pg_class.relfilenode
of any of the databases in the server?
regards, tom lane
hello 2010/8/31 Tom Lane <tgl@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> there is a dump from 8KB files > > Well, those certainly look like tables/indexes not temp files. > So we can rule out one theory. > > You're *certain* these aren't referenced from pg_class.relfilenode > of any of the databases in the server? I have a info, so these files are not in pg_class.relfilenode. More - these files are three months old, and in this time was server two times restarted. Regards Pavel Stehule > regards, tom lane >
On 03/09/10 11:16, Pavel Stehule wrote: > 2010/8/31 Tom Lane<tgl@sss.pgh.pa.us>: >> Pavel Stehule<pavel.stehule@gmail.com> writes: >>> there is a dump from 8KB files >> >> Well, those certainly look like tables/indexes not temp files. >> So we can rule out one theory. >> >> You're *certain* these aren't referenced from pg_class.relfilenode >> of any of the databases in the server? > > I have a info, so these files are not in pg_class.relfilenode. More - > these files are three months old, and in this time was server two > times restarted. Maybe they're tables that were created in a transaction, but the process crashed hard before committing? Like: BEGIN; CREATE TABLE foo (...); COPY foo FROM ...; kill -9 postgres That will leave behind a file like that. Do you do something like that in the application? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
2010/9/3 Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>: > On 03/09/10 11:16, Pavel Stehule wrote: >> >> 2010/8/31 Tom Lane<tgl@sss.pgh.pa.us>: >>> >>> Pavel Stehule<pavel.stehule@gmail.com> writes: >>>> >>>> there is a dump from 8KB files >>> >>> Well, those certainly look like tables/indexes not temp files. >>> So we can rule out one theory. >>> >>> You're *certain* these aren't referenced from pg_class.relfilenode >>> of any of the databases in the server? >> >> I have a info, so these files are not in pg_class.relfilenode. More - >> these files are three months old, and in this time was server two >> times restarted. > > Maybe they're tables that were created in a transaction, but the process > crashed hard before committing? Like: > > BEGIN; > CREATE TABLE foo (...); > COPY foo FROM ...; > kill -9 postgres yes, it's possible - but there are not any record about server crash - sometimes client crashes. Regards Pavel > > That will leave behind a file like that. Do you do something like that in > the application? > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com >