pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
How to clean/truncate / VACUUM FULL pg_largeobject without (much) downtime? 2+ messages / 2 participants [nested] [flat]
* How to clean/truncate / VACUUM FULL pg_largeobject without (much) downtime? @ 2015-05-11 09:55 Muthusamy, Sivaraman <sivaraman.muthusamy@in.verizon.com> 2015-05-22 21:17 ` Re: How to clean/truncate / VACUUM FULL pg_largeobject without (much) downtime? Jim Nasby <Jim.Nasby@BlueTreble.com> 0 siblings, 1 reply; 2+ messages in thread From: Muthusamy, Sivaraman @ 2015-05-11 09:55 UTC (permalink / raw) To: pgsql-performance Hi Group, Facing a problem where pg_catalog.pg_largetobject has been growing fast recently, in last two weeks. The actual data itself, in user tables, is about 60GB, but pg_catalog.pg_largeobject table is 200GB plues. Please let me know how to clean/truncate this table without losing any user data in other table. With regards to this pg_largeobject, I have the following questions: - What is this pg_largetobject ? - what does it contain ? tried PostgreSQL documentation and lists, but could not get much from it. - why does it grow ? - Was there any configuration change that may have triggered this to grow? For last one year or so, there was no problem, but it started growing all of sudden in last two weeks. The only change we had in last two weeks was that we have scheduled night base-backup for it and auto-vacuum feature enabled. - pg_largeobject contains so many duplicate rows (loid). Though there are only about 0.6 million rows (LOIDs), but the total number of rows including duplicates are about 59million records. What are all these ? Kindly help getting this information and getting this issue cleared, and appreciate your quick help on this. Thanks and Regards M.Shiva ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: How to clean/truncate / VACUUM FULL pg_largeobject without (much) downtime? 2015-05-11 09:55 How to clean/truncate / VACUUM FULL pg_largeobject without (much) downtime? Muthusamy, Sivaraman <sivaraman.muthusamy@in.verizon.com> @ 2015-05-22 21:17 ` Jim Nasby <Jim.Nasby@BlueTreble.com> 0 siblings, 0 replies; 2+ messages in thread From: Jim Nasby @ 2015-05-22 21:17 UTC (permalink / raw) To: Muthusamy, Sivaraman <sivaraman.muthusamy@in.verizon.com>; pgsql-performance On 5/11/15 4:55 AM, Muthusamy, Sivaraman wrote: > Hi Group, > > Facing a problem where pg_catalog.pg_largetobject has been growing fast > recently, in last two weeks. The actual data itself, in user tables, is > about 60GB, but pg_catalog.pg_largeobject table is 200GB plues. Please > let me know how to clean/truncate this table without losing any user > data in other table. Autovacuum should be taking care of it for you, though you could also try a manual vacuum (VACUUM pg_largeobject;). > With regards to this pg_largeobject, I have the following questions: > > -What is this pg_largetobject ? It stores large objects http://www.postgresql.org/docs/9.4/static/lo-interfaces.html > -what does it contain ? tried PostgreSQL documentation and lists, but > could not get much from it. > > -why does it grow ? > > -Was there any configuration change that may have triggered this to > grow? For last one year or so, there was no problem, but it started > growing all of sudden in last two weeks. The only change we had in last > two weeks was that we have scheduled night base-backup for it and > auto-vacuum feature enabled. Changes to autovacuum settings could certainly cause changes. Long-running transactions would prevent cleanup, as would any prepared transactions (which should really be disabled unless you explicitly need them). > -pg_largeobject contains so many duplicate rows (loid). Though there are > only about 0.6 million rows (LOIDs), but the total number of rows > including duplicates are about 59million records. What are all these ? Each row can only be ~2KB wide, so any LO that's larger than that will be split into multiple rows. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2015-05-22 21:17 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2015-05-11 09:55 How to clean/truncate / VACUUM FULL pg_largeobject without (much) downtime? Muthusamy, Sivaraman <sivaraman.muthusamy@in.verizon.com> 2015-05-22 21:17 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox