How to check for autovacuum settings and events in your BOSH Director's Postgres database

Products

• VMware Tanzu Application Service for VMs

Issue/Introduction

This articles covers how to check for autovacuum settings and events in your BOSH Director's Postgres database (DB).

Environment

Product Version: 2.10

Resolution

Autovacuum in BOSH Director's Postrgres DB is enabled by default on Postgres DB version 8.0+.

These are the steps on how you can check your Postgres DB autovacuum settings and check if there were any autovacuum events.

1. SSH into the BOSH Director:

ssh vcap@bosh-director-ip

Go to your BOSH Director tile > Credentials tab > VM Credentials to get the password.

2. Find your Postgres binaries:

cd /var/vcap/packages/postgres-XX/bin

Where XX is your Postgres version.

3. Login using psql:

./psql -h localhost -U postgres -W

Go to your BOSH Director tile > Credentials tab > Postgres Credential to get the password.

4. To display autovacuum settings, run this query:

select * from pg_settings where name like '%autovacuum%';

In the results below, it includes information if row autovacuum is enabled. The time between autovacuum is determined by autovacuum_naptime.

postgres=> select * from pg_settings where name like '%autovacuum%';
                name                 |  setting  | unit |              category               |                                        short_desc                                         |            
            extra_desc                         |  context   | vartype | source  | min_val |  max_val   | enumvals | boot_val  | reset_val | sourcefile | sourceline | pending_restart 
-------------------------------------+-----------+------+-------------------------------------+-------------------------------------------------------------------------------------------+------------
-----------------------------------------------+------------+---------+---------+---------+------------+----------+-----------+-----------+------------+------------+-----------------
 autovacuum                          | on        |      | Autovacuum                          | Starts the autovacuum subprocess.                                                         |            
                                               | sighup     | bool    | default |         |            |          | on        | on        |            |            | f
 autovacuum_analyze_scale_factor     | 0.1       |      | Autovacuum                          | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples. |            
                                               | sighup     | real    | default | 0       | 100        |          | 0.1       | 0.1       |            |            | f
 autovacuum_analyze_threshold        | 50        |      | Autovacuum                          | Minimum number of tuple inserts, updates, or deletes prior to analyze.                    |            
                                               | sighup     | integer | default | 0       | 2147483647 |          | 50        | 50        |            |            | f
 autovacuum_freeze_max_age           | 200000000 |      | Autovacuum                          | Age at which to autovacuum a table to prevent transaction ID wraparound.                  |            
                                               | postmaster | integer | default | 100000  | 2000000000 |          | 200000000 | 200000000 |            |            | f
 autovacuum_max_workers              | 3         |      | Autovacuum                          | Sets the maximum number of simultaneously running autovacuum worker processes.            |            
                                               | postmaster | integer | default | 1       | 262143     |          | 3         | 3         |            |            | f
 autovacuum_multixact_freeze_max_age | 400000000 |      | Autovacuum                          | Multixact age at which to autovacuum a table to prevent multixact wraparound.             |            
                                               | postmaster | integer | default | 10000   | 2000000000 |          | 400000000 | 400000000 |            |            | f
 autovacuum_naptime                  | 60        | s    | Autovacuum                          | Time to sleep between autovacuum runs.                                                    |            
                                               | sighup     | integer | default | 1       | 2147483    |          | 60        | 60        |            |            | f
 autovacuum_vacuum_cost_delay        | 20        | ms   | Autovacuum                          | Vacuum cost delay in milliseconds, for autovacuum.                                        |            
                                               | sighup     | integer | default | -1      | 100        |          | 20        | 20        |            |            | f
 autovacuum_vacuum_cost_limit        | -1        |      | Autovacuum                          | Vacuum cost amount available before napping, for autovacuum.                              |            
                                               | sighup     | integer | default | -1      | 10000      |          | -1        | -1        |            |            | f
 autovacuum_vacuum_scale_factor      | 0.2       |      | Autovacuum                          | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples.            |            
                                               | sighup     | real    | default | 0       | 100        |          | 0.2       | 0.2       |            |            | f
 autovacuum_vacuum_threshold         | 50        |      | Autovacuum                          | Minimum number of tuple updates or deletes prior to vacuum.                               |            
                                               | sighup     | integer | default | 0       | 2147483647 |          | 50        | 50        |            |            | f
 autovacuum_work_mem                 | -1        | kB   | Resource Usage / Memory             | Sets the maximum memory to be used by each autovacuum worker process.                     |            
                                               | sighup     | integer | default | -1      | 2147483647 |          | -1        | -1        |            |            | f
 log_autovacuum_min_duration         | -1        | ms   | Reporting and Logging / What to Log | Sets the minimum execution time above which autovacuum actions will be logged.            | Zero prints
 all actions. -1 turns autovacuum logging off. | sighup     | integer | default | -1      | 2147483647 |          | -1        | -1        |            |            | f
(13 rows)

5. To display autovacuum events, run this query:

select * from pg_stat_activity where query like 'autovacuum:%';