The VACUUM VERBOSE command provides many information useful to understand the write activity of your database and to improve your VACUUM strategy.
However, this output is not really readable when you have a lot of databases and tables.
Starting from 0.7 release, pgFouine can help you to analyze this output thanks to a new command called pgfouine_vacuum.php.
The first step is to get a VACUUM log from your database cluster using the following command:
$ vacuumdb -afzv &> vacuum_full.log
You can instead perform a VACUUM ANALYZE but it will provide you less information:
$ vacuumdb -azv &> vacuum.log
To generate a VACUUM report, we use the following command:
$ pgfouine_vacuum.php -file vacuum_sample.log > sample_vacuum.html
You can use the -report option to choose between the following reports:
The default behaviour is to generate all the reports.
You can use the -filter option to filter the results on database or schema.
$ pgfouine_vacuum.php -file vacuum_sample.log -filter exilog.public > sample_vacuum_filtered.html
pgFouine displays the FSM usage information in the report:
As you can see it on the screenshot above, the max_fsm_pages setting is lower than the minimum required to track the free space.
Therefore, we set the max_fsm_settings higher than the minimum required and the next day, we had the following report:
The following report is the list of vacuumed tables sorted by number of pages truncated:
The database should be mostly read and we can see that three of the biggest tables of the database have a lot of truncated pages.
It is probably not a normal behaviour of the application and it can be interesting to analyze this behaviour more closely.
The problem here is that a VACUUM which takes only 20 minutes usually...:
... suddenly takes 4 hours:
We sort the list of vacuumed tables by duration:
We click on the Details link for the table #160:
The problem is the cleanup of an index probably locked by a long transaction.