Обсуждение: pg_attribute growing extremely
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
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.
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