pgFouine - a PostgreSQL log analyzer

« 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:

FSM settings too low

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:

Satisfying FSM settings
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.

Detect a weird behaviour of your application

The following report is the list of vacuumed tables sorted by number of pages truncated:

Too many truncated pages

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.

Understand why your VACUUM is suddenly so long

The problem here is that a VACUUM which takes only 20 minutes usually...:

VACUUM takes 20 minutes

... suddenly takes 4 hours:

VACUUM takes 3 hours

We sort the list of vacuumed tables by duration:

List of tables

We click on the Details link for the table #160:

Details of table #160

The problem is the cleanup of an index probably locked by a long transaction.