Reclaiming maas space

[Jim Tilander]

Hi Mike,

Thanks for looking around — I had not found that article. 

As for the PSQL versions I have, in my test environment I have these:

postgresql/xenial,xenial,now 9.5+173 all [installed,automatic]
postgresql-9.5/xenial-updates,now 9.5.5-0ubuntu0.16.04 amd64 [installed,automatic]
postgresql-client/xenial,xenial,now 9.5+173 all [installed,automatic]
postgresql-client-9.5/xenial-updates,now 9.5.5-0ubuntu0.16.04 amd64 [installed,automatic]
postgresql-client-common/xenial,xenial,now 173 all [installed,automatic]
postgresql-common/xenial,xenial,now 173 all [installed,automatic]
postgresql-contrib-9.5/xenial-updates,now 9.5.5-0ubuntu0.16.04 amd64 [installed,automatic]

The output from those commands were:


root at jtilander-uvm:~# sudo -u postgres vacuumlo -v maasdb
Connected to database "maasdb"
Checking content in public.maasserver_largefile
Successfully removed 0 large objects from database "maasdb".
root at jtilander-uvm:~# sudo -u postgres psql maasdb
psql (9.5.5)
Type "help" for help.

maasdb=# VACUUM FULL pg_largeobject;
VACUUM
maasdb=# REINDEX TABLE pg_largeobject;
REINDEX
maasdb=# \q


And here is the final tally of the used diskspace of the db files for pawl:

root at jtilander-uvm:~# du -hs /var/lib/postgresql/
19G     /var/lib/postgresql/

Which is pretty much the same as I started with. :(

This is quickly becoming a pretty serious problem for me as I’ve filled up the disk once already.

Is there any alternative ways to just nuke the table pg_largeobject ? Can maas recover from that?


> On Jan 27, 2017, at 10:14 PM, Mike Pontillo <mike.pontillo at canonical.com> wrote:
> 
> Hi Jim,
> 
>    Thanks for bringing this [back] to our attention. Unfortunately, it seems that it's tricky to fully rid the database of deleted large objects. I'm concerned that the proper procedure for purging these object might even vary across different versions of postgresql (or depending on other circumstances), which could make this issue even more complex.
> 
>    After I saw your e-mail, I looked around for any new information regarding this issue, and found this article:
> 
> https://access.redhat.com/solutions/56258 <https://access.redhat.com/solutions/56258>
> 
>    I had to adapt these commands to the Debian/MAAS environment, but to summarize, could you try the following:
> 
> $ sudo -u postgres vacuumlo -v maasdb
> $ sudo -u postgres psql maasdb
> maasdb=# VACUUM FULL pg_largeobject;
> maasdb=# REINDEX TABLE pg_largeobject;
> maasdb=# \q
> 
>    I would be interested to hear if this works for you or not.
> 
> Regards,
> Mike
> 
> 
> On Fri, Jan 27, 2017 at 8:40 PM, Jim Tilander <jim at tilander.org <mailto:jim at tilander.org>> wrote:
> I’m experimenting with maas and uploading new images for Windows, several new ~4Gb images each day. At some point I ran out of space on my maas server, inspecting it a little bit closer it looks like the pgqsl database is very very large. I found a relevant entry here: https://bugs.launchpad.net/maas/+bug/1459876 <https://bugs.launchpad.net/maas/+bug/1459876>
> 
> So doing a "maas-region db_vacuum_lobjects” doesn’t seem to clean out the overwritten image it seems like. Below I’m doing a du -hs of /var/lib/postgresql before and after an image upload as well as after the vacuum. 
> 
> 13G     /var/lib/postgresql
> Uploading
> 19G     /var/lib/postgresql
> Cleanup database
> vacuumdb: vacuuming database "maasdb"
> Database vacuumed successfully.
> 19G     /var/lib/postgresql
> 
> It seems though that the database is not shrinking. I’m iterating and making a new windows image over and over again, uploading it with:
> 
> maas admin boot-resources create name=windows/win2012r2 architecture=amd64/generic filetype=ddtgz content@=myimage.raw.tgz
> 
> I’ve already filled up one server with these commands, and I’m wondering what kind of regular maintenance I need to do to keep the database files under control and not explode as they do now.
> 
> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.ubuntu.com/archives/maas-devel/attachments/20170128/d6c942c0/attachment.html>