Hi all, Reviving a thread from October 2010 about vacuuming in postgres.
Op 20-10-10 17:35, David Blewett schreef: > On Tue, Oct 19, 2010 at 1:05 AM, Noe Misael Nieto Arroyo > <n...@iservices.com.mx> wrote: >> And if yes, What kind of VACUUM is good? FULL, CLUSTER? > > VACUUM FULL is almost never a good idea; autovac should be sufficient > for most needs, and can be tweaked to meet particular performance > issues. On a test database of 524 MB I did a vacuum and this had no effect. A vacuum full resulted in a significant drop to 423 MB. But this is just a local test database on my laptop so it had probably never been autovacuumed. > I have had very good results doing a CLUSTER after an initial > zodbconvert run to significantly reduce the size of the database. I > highly recommend doing CLUSTER after any large data loads. CLUSTER does not do anything unless you first tell it which index to use for clustering a table. Using postgres 8.4 I have done this for the four table for which I think this is useful (the other tables had no content): cluster blob_chunk using blob_chunk_pkey; cluster current_object using current_object_pkey; cluster object_state using object_state_pkey ; cluster transaction using transaction_pkey; This did not have an effect on the above mentioned test database though. But that may be because it had already been fully vacuumed. Would these be the correct tables and indexes? Some of these tables have other indexes as well, but these are the primary ones. Would it make sense to add these lines to the postgres code in relstorage that creates the tables? Then users would only need to perform the 'CLUSTER;' command without any extra parameters and it would work. I am asking about this because I have a database of about 22 GB on which I have performed a migration to blob files. The size is now about 38 GB and I am pretty sure the autovacuum has already been done. BTW, I have no access to the filesystem on which this database is stored; the size is simply what the Database Control Panel in the Zope root reports. Cheers, -- Maurits van Rees Web App Programmer at Zest Software: http://zestsoftware.nl Personal website: http://maurits.vanrees.org/ _______________________________________________ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org https://mail.zope.org/mailman/listinfo/zodb-dev