Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_attribute growing extremely

pg_attribute growing extremely

From
hamann.w@t-online.de
Date:
Hi,

on a server running 8.4 I observe that, since a while, the pg_attribute table is growing
enormously.
Soon after reloading I have one file
ls -s 1249
1048580 1249
a day later this is
1048580 1249
1048580 1249.1
1048580 1249.10
1048580 1249.11
1048580 1249.12
1048580 1249.13
1048580 1249.14
1048580 1249.15
 682212 1249.16
1048580 1249.2
1048580 1249.3
1048580 1249.4
1048580 1249.5
1048580 1249.6
1048580 1249.7
1048580 1249.8
1048580 1249.9
   4316 1249_fsm
     24 1249_vm
and 5 days later the system had arrived at 102 files

The server is running since quite some time, without any major change.
The workload is mostly reading, a little text updates and a little more binary updates.
Every two weeks or so the database is recreated and reloaded
The total size of data is growing slowly (say a rate of 10% per year)

Regards
Wolfgang Hamann

Re: pg_attribute growing extremely

From
David G Johnston
Date:
hamann.w wrote
> Hi,
>
> on a server running 8.4 I observe that, since a while, the pg_attribute
> table is growing
> enormously.
> Soon after reloading I have one file
> ls -s 1249
> 1048580 1249
> a day later this is
> 1048580 1249
> 1048580 1249.1
> 1048580 1249.10
> 1048580 1249.11
> 1048580 1249.12
> 1048580 1249.13
> 1048580 1249.14
> 1048580 1249.15
>  682212 1249.16
> 1048580 1249.2
> 1048580 1249.3
> 1048580 1249.4
> 1048580 1249.5
> 1048580 1249.6
> 1048580 1249.7
> 1048580 1249.8
> 1048580 1249.9
>    4316 1249_fsm
>      24 1249_vm
> and 5 days later the system had arrived at 102 files
>
> The server is running since quite some time, without any major change.
> The workload is mostly reading, a little text updates and a little more
> binary updates.
> Every two weeks or so the database is recreated and reloaded
> The total size of data is growing slowly (say a rate of 10% per year)
>
> Regards
> Wolfgang Hamann

With the little info provided I'm not certain that this isn't a
mis-diagnosis.  Regardless, if you think pg_attribute is the culprit then
setting log_statement to 'ddl' will provide evidence of all statements that
would affect that table.

And while 10% a year does sound small if you have a 2TB database that would
amount to a considerable absolute amount of data...

You should probably re-run, and then share, your exploratory queries and
shell commands.  Also, increase logging and monitor pg_stat_activity to see
who is connecting and sending so much data to your system.  Generating 100GB
of data through CREATE/ALTER table commands alone is highly unlikely.

David J.






--
View this message in context:
http://postgresql.1045698.n5.nabble.com/pg-attribute-growing-extremely-tp5806296p5806299.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: pg_attribute growing extremely

From
Martijn van Oosterhout
Date:
On Fri, Jun 06, 2014 at 03:56:23AM -0000, hamann.w@t-online.de wrote:
>
> Hi,
>
> on a server running 8.4 I observe that, since a while, the pg_attribute table is growing
> enormously.
> Soon after reloading I have one file
> ls -s 1249
> 1048580 1249
> a day later this is
> 1048580 1249
> 1048580 1249.1
> 1048580 1249.10
> 1048580 1249.11
> 1048580 1249.12
> 1048580 1249.13
> 1048580 1249.14
> 1048580 1249.15
>  682212 1249.16
> 1048580 1249.2
> 1048580 1249.3
> 1048580 1249.4
> 1048580 1249.5
> 1048580 1249.6
> 1048580 1249.7
> 1048580 1249.8
> 1048580 1249.9
>    4316 1249_fsm
>      24 1249_vm
> and 5 days later the system had arrived at 102 files

Is autovacuum enabled? Are you using a lot of temporary tables? Do you
have long running transactions?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
   -- Arthur Schopenhauer
Attachment