Search Postgresql Archives

AW: running vacuumlo periodically

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I run "vacuumlo" in batches (-l) which worked well.

I found table "pg_catalog.pg_largeobjects" to be massively bloated afterwards.

I tried "vacuum full pg_catalog.pg_largeobjects" but run out of diskspace (although having 250G diskspace free; database size = 400G).


Question: 
Will "vacuum full pg_catalog.pg_largeobjects" need less diskspace when "maintenance_work_mem" is increased?




> -----Ursprüngliche Nachricht-----
> Von: Zwettler Markus (OIZ) <Markus.Zwettler@xxxxxxxxxx>
> Gesendet: Donnerstag, 28. Januar 2021 18:04
> An: Laurenz Albe <laurenz.albe@xxxxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx
> Betreff: AW: running vacuumlo periodically?
> 
> > -----Ursprüngliche Nachricht-----
> > Von: Laurenz Albe <laurenz.albe@xxxxxxxxxxx>
> > Gesendet: Donnerstag, 28. Januar 2021 17:39
> > An: Zwettler Markus (OIZ) <Markus.Zwettler@xxxxxxxxxx>; pgsql-
> > general@xxxxxxxxxxxxxx
> > Betreff: Re: running vacuumlo periodically?
> >
> > On Thu, 2021-01-28 at 13:18 +0000, Zwettler Markus (OIZ) wrote:
> > > Short question. Is it recommended  - or even best practice – to run
> > > vacuumlo
> > periodically as a routine maintenance task?
> > >
> > > We don't do it. I think if this would be recommended it would have
> > > been
> > implemented as an autotask like autovacuum. No?
> >
> > It is recommended to run it regularly if
> > - you are using large objects
> > - you don't have a trigger in place that deletes large objects that you don't
> >   need any more
> >
> > Only a small minority of people do that, so it wouldn't make sense to
> > automatically run that on all databases.
> >
> > Avoid large objects if you can.
> >
> > Yours,
> > Laurenz Albe
> > --
> > Cybertec | https://www.cybertec-postgresql.com
> [Zwettler Markus (OIZ)]
> 
> 
> 
> We didn't recognize that an application is using large objects and didn't delete
> them.
> Now we found >100G dead large objects within the database. :-(
> 
> Is there any _GENERIC_ query which enables monitoring for orphaned objects
> (dead LO)?
> 
> select oid from pg_largeobject_metadata m where not exists  (select 1 from
> ANY_EXISTING_TABLE_WITHIN_THE_DATABASE  where m.oid =
> ANY_COLUMN_CONTAINING_OIDs);
> 
> check_postgres.pl doesn't have any generic check for it. :-(
> 
> 
> Thanks, Markus
> 
> 





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux