Re: 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(dot)stehule(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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.6 on 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(dot)stehule(at)gmail(dot)com>
>
> 31.08.2010 09:32
>
> To
> robert(dot)moucha(at)lmc(dot)eu
> cc
> Subject
> Re: [HACKERS] thousand unrelated data files in pg_default tablespace
>
>
>
>
> Zdar,
>
> preposilam ti report
>
> 2010/8/30 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> > Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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(dot)stehule(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us>:
>> Pavel Stehule<pavel(dot)stehule(at)gmail(dot)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(dot)linnakangas(at)enterprisedb(dot)com>:
> On 03/09/10 11:16, Pavel Stehule wrote:
>>
>> 2010/8/31 Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>>>
>>> Pavel Stehule<pavel(dot)stehule(at)gmail(dot)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
>