Обсуждение: Unexplained growth of tables
This is subsequent to the previous issue which I am expperiencing. In my monitoring application, I have a few tables which contain few rows but are constantly pounded with updates. These tables are growing at very high rates. For example, a table with less than 4K rows, which when reloaded was about 8 Mb, is now 1.4GB! As these tables grow, the performance of the application - which only looks at these previously relativele small tables - is exremely slow. The huge tables are only used to calculate some statistical values on a nightly basis. I took another table which just started growing and ran analyze on it. This is the result: INFO: analyzing "public.tblkstests" INFO: "tblkstests": scanned 3000 of 81837 pages, containing 109 live rows and 10512 dead rows; 109 rows in sample, 2973 estimated total rows Total query runtime: 52702 ms. The actual number of physical rows in this table is 3404. Row width is 361. Table size = 639 MB. What can be causing this growth? Not vacuuming often enough? I hav pg_autovacuum running every 60 seconds. These tables have 10-15 insert/update statements per second. Any assistance or guidance will be deeply appreciated. I am pulling hairs on this one. > > >
Benjamin Krajmalnik wrote: > What can be causing this growth? Not vacuuming often enough? The is exactly the reason. > I hav > pg_autovacuum running every 60 seconds. These tables have 10-15 > insert/update statements per second. You should probably run VACUUM FULL to get the table back to a normal size and then closely monitor what pg_autovacuum actually does. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Hi, On Sun, 23 Jul 2006, Benjamin Krajmalnik wrote: <snipp/> > The actual number of physical rows in this table is 3404. Row width is > 361. Table size = 639 MB. > What can be causing this growth? Not vacuuming often enough? I hav > pg_autovacuum running every 60 seconds. These tables have 10-15 > insert/update statements per second. > > Any assistance or guidance will be deeply appreciated. I am pulling > hairs on this one. something might be stopping vacuum from reclaiming the dead rows. We had a similar situation with a DBD::Pg based perl script that was monitoring the database for new jobs to perform. The script started once, connceted to the db, prepared its statemens and then just slept waiting for notifies. We now have the script regularly exit and reopen a new db connection which magically allows vacuum to reclaim rows. Could be related to prepared staments and locks on system tables or something of that sort. I would recommend comparing the output of vacuum full with and without your application connected. Greetings Christian -- Christian Kratzer ck@cksoft.de CK Software GmbH http://www.cksoft.de/ Phone: +49 7452 889 135 Fax: +49 7452 889 136
After a long battle with technology, kraj@illumen.com ("Benjamin Krajmalnik"), an earthling, wrote:
> Any assistance or guidance will be deeply appreciated. I am pulling
> hairs on this one.
It needs to be vacuumed a lot more aggressively. If you're using
pg_autovacuum, the defaults seem to be not quite aggressive enough.
Mind you, now that the table has grown to huge size, it won't be cut
down to size by a mere VACUUM ANALYZE.
To draw it back down to size, you need to run VACUUM FULL, which will
cut the number of tuples down to size, and then reindex the table,
which will "do a number" on the indices.
Alternatively, "CLUSTER [some index] on [table];" will reorganize the
whole thing at once, reshaping the table to conform with the specified
index, and regenerating all indices, at the cost that it (at present,
up to 8.1; I'm not sure about 8.2) doesn't follow MVCC and keep old,
but still-visible obsolete copies of tuples.
CLUSTER is the fast answer, assuming you haven't any old transactions
you need to keep in flight...
--
(format nil "~S@~S" "cbbrowne" "gmail.com")
http://linuxfinances.info/info/slony.html
The difference between a child and a hacker is the amount he flames
about his toys. -- Ed Schwalenberg
Peter Eisentraut <peter_e@gmx.net> writes:
> Benjamin Krajmalnik wrote:
>> What can be causing this growth? Not vacuuming often enough?
> The is exactly the reason.
>> I hav
>> pg_autovacuum running every 60 seconds. These tables have 10-15
>> insert/update statements per second.
> You should probably run VACUUM FULL to get the table back to a normal
> size and then closely monitor what pg_autovacuum actually does.
CLUSTER might be a better answer, since VACUUM FULL won't do anything
to help shrink the indexes.
As for the pg_autovacuum frequency, realize that pg_autovacuum processes
one database per firing. So if you have N databases, any one database
is going to be looked at every N*60 seconds, not every 60 seconds.
You might need a shorter autovacuum cycle.
Lastly, what PG version is this? We had some bugs that kept autovacuum
from being fully informed in some cases, but AFAIK they're all fixed in
the latest minor releases.
regards, tom lane
I am running 8.1.4 on Windows. Quick question - I assume CLUSTER will lock the tables, correct? In my scenario where the number of live rows is very small but the number of dead rows is huge, how long will tis take? I have a problem in that this database is a live monitoring system which I cannot take down for too long. The only database on this server (other than the postgres database) is this database. From your experience, based on the activity of this database, how often should I run autovacuum? > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Sunday, July 23, 2006 11:48 AM > To: Peter Eisentraut > Cc: pgsql-admin@postgresql.org; Benjamin Krajmalnik > Subject: Re: [ADMIN] Unexplained growth of tables > > Peter Eisentraut <peter_e@gmx.net> writes: > > Benjamin Krajmalnik wrote: > >> What can be causing this growth? Not vacuuming often enough? > > > The is exactly the reason. > > >> I hav > >> pg_autovacuum running every 60 seconds. These tables have 10-15 > >> insert/update statements per second. > > > You should probably run VACUUM FULL to get the table back > to a normal > > size and then closely monitor what pg_autovacuum actually does. > > CLUSTER might be a better answer, since VACUUM FULL won't do > anything to help shrink the indexes. > > As for the pg_autovacuum frequency, realize that > pg_autovacuum processes one database per firing. So if you > have N databases, any one database is going to be looked at > every N*60 seconds, not every 60 seconds. > You might need a shorter autovacuum cycle. > > Lastly, what PG version is this? We had some bugs that kept > autovacuum from being fully informed in some cases, but AFAIK > they're all fixed in the latest minor releases. > > regards, tom lane >
Thaks to all who helped on this one, for I was going insane. I am now auto_vacuuming every 30 seconds, and have cheanged the para,eters so the vacuuming takes place much more aggressively. Performance has increased and table size appears to have been addressed. For the newbies like myself who may make the same mistake ..... I assumed that auto_vacuum was going to adversely affect perfromance if it ran often, so I extended the time between atovacuum cycles. BIG MISTAKE! I assume in many applicatios the net effet may not be huge, but in our application where currently we have more than 10 million insert/update transactions per day, it definitaly made a difference. I pulled my hair long on this one until the light bulb came on and I figured out it must be related to MVCC and the vast amount of change to the database at any given time. Once again, thanks to all of you who were kind enough to point me in the right direction. > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of > Benjamin Krajmalnik > Sent: Sunday, July 23, 2006 2:06 PM > To: Tom Lane; Peter Eisentraut > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Unexplained growth of tables > > I am running 8.1.4 on Windows. > Quick question - I assume CLUSTER will lock the tables, correct? > In my scenario where the number of live rows is very small > but the number of dead rows is huge, how long will tis take? > I have a problem in that this database is a live monitoring > system which I cannot take down for too long. > > The only database on this server (other than the postgres > database) is this database. > From your experience, based on the activity of this database, > how often should I run autovacuum? > > > > -----Original Message----- > > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > Sent: Sunday, July 23, 2006 11:48 AM > > To: Peter Eisentraut > > Cc: pgsql-admin@postgresql.org; Benjamin Krajmalnik > > Subject: Re: [ADMIN] Unexplained growth of tables > > > > Peter Eisentraut <peter_e@gmx.net> writes: > > > Benjamin Krajmalnik wrote: > > >> What can be causing this growth? Not vacuuming often enough? > > > > > The is exactly the reason. > > > > >> I hav > > >> pg_autovacuum running every 60 seconds. These tables have 10-15 > > >> insert/update statements per second. > > > > > You should probably run VACUUM FULL to get the table back > > to a normal > > > size and then closely monitor what pg_autovacuum actually does. > > > > CLUSTER might be a better answer, since VACUUM FULL won't > do anything > > to help shrink the indexes. > > > > As for the pg_autovacuum frequency, realize that pg_autovacuum > > processes one database per firing. So if you have N databases, any > > one database is going to be looked at every N*60 seconds, > not every 60 > > seconds. > > You might need a shorter autovacuum cycle. > > > > Lastly, what PG version is this? We had some bugs that kept > > autovacuum from being fully informed in some cases, but > AFAIK they're > > all fixed in the latest minor releases. > > > > regards, tom lane > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend >