« Il se passe un truc bizarre... Je chante comme Francis Cabrel... »

VACUUM VERBOSE output analysis

Introduction

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.

Most of the examples of this page are generated using a log file kindly provided by Larry Rosenman (aka larryrtx).

For more information about the utility of the VACUUM command, consult the Vacuum the Dirt out of Your Database entry of the Pervasive knowledge base.

Usage

Obtaining a VACUUM log

The first step is to get a VACUUM log from your database cluster using the following command:

$ vacuumdb -afzv &> vacuum_full.log

This command performs a VACUUM FULL ANALYZE on all the databases of your cluster so use it carefully.

You can instead perform a VACUUM ANALYZE but it will provide you less information:

$ vacuumdb -azv &> vacuum.log

Standard report

To generate a VACUUM report, we use the following command:

$ pgfouine_vacuum.php -file vacuum_sample.log > sample_vacuum.html

Compose your own report

You can use the -report option to choose between the following reports:

overall: overall statistics;
fsm: FSM usage information;
vacuumedtables: list of the vacuumed tables;
details: for each table, the detailed information (table and indexes).

The default behaviour is to generate all the reports.

Filter the output

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

The FSM usage report is not filtered by the -filter option.

How to analyze the results

FSM usage

To understand why the FSM settings are so important, I recommend you to read the Is PostgreSQL remembering what you vacuumed? from the Pervasive knowledge base.

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:

If you want accurate information for the FSM usage, it is required to VACUUM all the databases at once. The vacuumdb command with the -a option is recommended.