Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to monitor the processing process of VACUUM in PostgreSQL". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to monitor the process of VACUUM in PostgreSQL".

Overview

PG's MVCC requires that "out-of-date" data can not be physically purged immediately, but instead marked as dead rows, and these dead rows will be cleaned up later through the vacuuming process.

Vacuuming keeps the database healthy in the following ways:

1. Marking dead rows can be used to store new data, which avoids unnecessary disk waste and can skip dead rows to improve sequential scan performance

two。 Update vm (used to track expired or obsolete data, reflected on pages). This can improve the performance of index-only scans.

3. Avoid transaction ID rollback failures.

PG provides an autovacuum mechanism to collect recently frequently updated data table statistics by running ANALYZE periodically.

Monitoring index

In order for VACUUMs to run smoothly, the following metrics should be monitored:

1.dead rows

2.table disk usage

Last execution time of 3.VACUUM/AUTOVACUUM

4. Monitoring vacuum full

Dead rows

PG provides a pg_stat_user_tables view for monitoring dead rows

[local:/data/run/pg12]: 5120 pg12@testdb=#\ d pg_stat_user_tables View "pg_catalog.pg_stat_user_tables" Column | Type | Collation | Nullable | Default-+-- -- +-relid | oid | schemaname | name | relname | name | | | seq_scan | bigint | seq_tup_read | bigint | idx_scan | bigint | idx_tup_fetch | bigint | | | n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | n_tup_hot_upd | | | bigint | n_live_tup | bigint | n_dead_tup | bigint | n_mod_since_analyze | bigint | last_ | Vacuum | timestamp with time zone | last_autovacuum | timestamp with time zone | last_analyze | timestamp with time zone | last_autoanalyze | timestamp with time zone | vacuum_count | bigint | autovacuum_count | | bigint | analyze_count | bigint | autoanalyze_count | bigint | [local:/data/run/pg12]: 5120 pg12@testdb=# update T1 set id = 2 | UPDATE 20000 [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT schemaname,relname, n_dead_tup FROM pg_stat_user_tables Schemaname | relname | n_dead_tup-+-public | tbl | 0 public | T2 | 0 public | b | 0 public | A | 0 public | rel | 0 public | t_count | 0 public | t_big_autovacuum_1 | 0 public | t_autovacuum_1 | 0 public | T1 | 20000 (9 rows)

Monitoring the dead rows of each table, especially on tables that are updated frequently, helps DBA to determine whether the VACUUM process has effectively periodically cleared these dead rows.

Table disk usage

When the dead rows appears, the disk space will gradually increase. After the vacuuming is executed, the dead rows can be marked as free space to monitor the space change.

[local:/data/run/pg12]: 5120 pg12@testdb=# SELECT relname AS "table_name", pg_size_pretty (pg_table_size (C.oid)) AS "table_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog',' information_schema') AND nspname! ~'^ pg_toast' AND relkind IN ('r') ORDER BY pg_table_size (C.oid) DESC Table_name | table_size-+-rel | 845 MB t_big_autovacuum_1 | 498 MB tbl | 100 MB a | 65 MB b | 65 MB T1 | 1456 kB t_autovacuum_1 | 504 kB T2 | 360 kB t_count | 64 kB (9 rows) [local:/data/run/pg12]: 5120 pg12@testdb=# update T1 set id = 4 UPDATE 20000 [local:/data/run/pg12]: 5120 pg12@testdb=# update T1 set id = 5 UPDATE 20000 [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT relname AS "table_name", pg_size_pretty (pg_table_size (C.oid)) AS "table_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog',' information_schema') AND nspname! ~'^ pg_toast' AND relkind IN ('r') ORDER BY pg_table_size (C.oid) DESC Table_name | table_size-+-rel | 845 MB t_big_autovacuum_1 | 498 MB tbl | 100 MB a | 65 MB b | 65 MB T1 | 2864 kB t_autovacuum_1 | 504 kB T2 | 360 kB t_count | 64 kB (9 rows)

Perform a full update on T1, and then insert the same amount of data again after executing vacuum T1

[local:/data/run/pg12]: 5120 pg12@testdb=# vacuum T1 / VACUUM [local:/data/run/pg12]: 5120 pg12@testdb=# select count (*) from T1 Count-20000 (1 row) [local:/data/run/pg12]: 5120 pg12@testdb=#\ d T1 Table "public.t1" Column | Type | Collation | Nullable | Default-+-id | integer | | | | [local:/data/run/pg12]: 5120 pg12@testdb=# insert into T1 select generate_series (1Jing 20000) | INSERT 0 20000 [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT relname AS "table_name", pg_size_pretty (pg_table_size (C.oid)) AS "table_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog',' information_schema') AND nspname! ~'^ pg_toast' AND relkind IN ('r') ORDER BY pg_table_size (C.oid) DESC Table_name | table_size-+-rel | 845 MB t_big_autovacuum_1 | 498 MB tbl | 100 MB a | 65 MB b | 65 MB T1 | 2864 kB t_autovacuum_1 | 504 kB T2 | 360 kB t_count | 64 kB (9 rows)

You can see that the space occupied by table has not changed because the new rows uses the space of dead rows.

If you do not perform vacuum direct insertion, you can clearly see the change in table size.

[local:/data/run/pg12]: 5120 pg12@testdb=# update T1 set id = 10 UPDATE 60000 [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT relname AS "table_name", pg_size_pretty (pg_table_size (C.oid)) AS "table_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog' 'information_schema') AND nspname! ~' ^ pg_toast' AND relkind IN ('r') ORDER BY pg_table_size (C.oid) DESC Table_name | table_size-+-rel | 845 MB t_big_autovacuum_1 | 498 MB tbl | 100 MB a | 65 MB b | 65 MB T1 | 4288 kB-> this is the original occupation Spatial t_autovacuum_1 | 504 kB T2 | 360 kB t_count | 64 kB (9 rows) [local:/data/run/pg12]: 5120 pg12@testdb=# insert into T1 select generate_series (1Jing 20000) INSERT 0 20000 [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT relname AS "table_name", pg_size_pretty (pg_table_size (C.oid)) AS "table_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog' 'information_schema') AND nspname! ~' ^ pg_toast' AND relkind IN ('r') ORDER BY pg_table_size (C.oid) DESC Table_name | table_size-+-rel | 845 MB t_big_autovacuum_1 | 498 MB tbl | 100 MB a | 65 MB b | 65 MB T1 | 4992 kB-- > New occupancy Spatial t_autovacuum_1 | 504 kB T2 | 360 kB t_count | 64 kB (9 rows)

Last time (auto) vacuum ran

PG provides a pg_stat_user_tables view to monitor the time of the last vacuum run.

[local:/data/run/pg12]: 5120 pg12@testdb=#\ d pg_stat_user_tables View "pg_catalog.pg_stat_user_tables" Column | Type | Collation | Nullable | Default-+-- -- +-relid | oid | schemaname | name | relname | name | | | seq_scan | bigint | seq_tup_read | bigint | idx_scan | bigint | idx_tup_fetch | bigint | | | n_tup_ins | bigint | n_tup_upd | bigint | n_tup_del | bigint | n_tup_hot_upd | | | bigint | n_live_tup | bigint | n_dead_tup | bigint | n_mod_since_analyze | bigint | last_ | Vacuum | timestamp with time zone | last_autovacuum | timestamp with time zone | last_analyze | timestamp with time zone | last_autoanalyze | timestamp with time zone | vacuum_count | bigint | autovacuum_count | | bigint | analyze_count | bigint | autoanalyze_count | bigint | [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT schemaname | Relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables Schemaname | relname | last_vacuum | last_autovacuum-+-+-- -public | tbl | | public | T2 | | public | b | | public | a | | | public | rel | | public | t_count | | public | t_big_autovacuum_1 | | public | t_autovacuum_1 | | | public | T1 | 2019-12-10 14-2931.420908-08 | 2019-12-10 14-14-32-12-10 22-12-10 22-12-10 14-12-10 22-12-10 14-12-10 14-12-10 22-12-10 14-12-10 14-12-10 22-12-10 14-12-10 14-12-10 14-32-12-10 22-12-10 14-12-10 22-12-10 14-12-10 14-- 02.63987-8 (9) |

You can see that the most recent vacumm is 14:29:31, while the most recent autovacuum is executed once by default at 60s for 14 PG 32 PG 02, and autovacuum is not executed for other tables that have not changed.

[local:/data/run/pg12]: 5120 pg12@testdb=# select name,setting from pg_settings where name like'% autovacuum%' Name | setting-- +-autovacuum | on autovacuum_analyze_scale_factor | 0.1 autovacuum_analyze_threshold | 50 autovacuum_freeze_max_age | 200000000 autovacuum_max_workers | 3 autovacuum_multixact_freeze_max_age | 400000000 autovacuum_naptime | 60-> 60s autovacuum_vacuum_cost_delay | 2 autovacuum_vacuum_cost_limit |-1 autovacuum_vacuum_scale_factor | 0.2 autovacuum_vacuum_threshold | 50 autovacuum_work_mem |-1 log_autovacuum_min_duration |-1 (13 rows)

Perform update operation, query again after 60s, and find that last_autovacuum has been updated.

[local:/data/run/pg12]: 5120 pg12@testdb=# update T1 set id = 1 update 80000 [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT schemaname, relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables Schemaname | relname | last_vacuum | last_autovacuum-+-+-- -public | tbl | | public | T2 | | public | b | | public | a | | | public | rel | | public | t_count | | public | t_big_autovacuum_1 | | public | t_autovacuum_1 | | | public | T1 | 2019-12-10 14-- 2931.420908-08 | 2019-12-10 14-14-38-48 (9 rows) |

Monitoring vacuum full

The progress of the vacuum full can be monitored through the view pg_stat_progress_vacuum

[local:/data/run/pg12]: 5120 pg12@testdb=#\ d pg_stat_progress_vacuum View "pg_catalog.pg_stat_progress_vacuum" Column | Type | Collation | Nullable | Default-+- -pid | integer | datid | oid | datname | name | relid | oid | phase | text | heap_blks_total | | bigint | heap_blks_scanned | bigint | heap_blks_vacuumed | bigint | index_vacuum_count | bigint | max_dead_tuples | bigint | num_dead_tuples | bigint | [local:/data/run/pg12]: 5120 pg12@testdb=# |

Related topics of VACUUM

If the above metrics indicate that VACUUMs is not performing properly, you can find the problem through query settings, including:

1.The autovacuum process is disabled on your database

2.The autovacuum process is disabled on one or more tables

3.Autovacuuming settings aren't keeping pace with updates

4.Lock conflicts

5.Long-running open transactions

1.The autovacuum process is disabled on your database

Monitor whether autovacuum is running by executing the command ps-axww | grep autovacuum

[root@localhost ~] # ps-axww | grep autovacuum55958? Ss 0:00 postgres: autovacuum launcher 56057 pts/4 S+ 0:00 grep-color=auto autovacuum [root@localhost ~] #

It can also be obtained by querying pg_settings.

[local:/data/run/pg12]: 5120 pg12@testdb=# SELECT name, setting FROM pg_settings WHERE name='autovacuum'; name | setting-+-autovacuum | on (1 row)

If autovacuum is turned on, but the result is not as we expected, then the problem may occur on statistics collector, where autovacuum relies on statistics collector to determine when and how often it should run. Generally speaking, statistics collector should be enabled, but if it is disabled, it will have a great impact on the normal operation of autovacuum. Check whether statistics collector is enabled by checking the track_counts configuration item.

[local:/data/run/pg12]: 5120 pg12@testdb=# SELECT name, setting FROM pg_settings WHERE name='track_counts'; name | setting-+-track_counts | on (1 row) [local:/data/run/pg12]: 5120 pg12@testdb=#

If track_counts is OFF, statistics collector does not update the dead rows information, which is the information on which autovacuum depends.

[local:/data/run/pg12]: 5120 pg12@testdb=# set track_counts=off;SET [local:/data/run/pg12]: 5120 pg12@testdb=# update T1 set id = 2 * update 80000 [local:/data/run/pg12]: 5120 pg12@testdb=# update T1 set id = 3 * * update 80000 [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables Schemaname | relname | n_dead_tup | last_vacuum | last_autovacuum-+ -- public | tbl | 0 | | public | T2 | 0 | | public | b | 0 | | | public | a | 0 | | public | rel | 0 | | public | t_count | 0 | | Public | t_big_autovacuum_1 | 0 | | public | t_autovacuum_1 | 0 | | public | T1 | 0 | 2019-12-10 14public 2931.4209082808 | 2019-12-10 14public 38VO2.771566V08 (9 rows) [local: / data/run/pg12]: 5120 pg12@testdb=#

Manually set track_counts to off, update T1, query pg_stat_user_tables found that n_dead_tup did not count dead rows, resulting in autovacuum did not "vacuum" the T1 table.

Manually set track_counts to on, but does not trigger the update of statistics. Log out of psql and log back in, and the new statistics will not appear until the data table is updated.

[local:/data/run/pg12]: 5120 pg12@testdb=# set track_counts=on;SET [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables Schemaname | relname | n_dead_tup | last_vacuum | last_autovacuum-+ -- public | tbl | 0 | | public | T2 | 0 | | public | b | 0 | | | public | a | 0 | | public | rel | 0 | | public | t_count | 0 | | Public | t_big_autovacuum_1 | 0 | | public | t_autovacuum_1 | 0 | | public | T1 | 0 | 2019-12-10 14public 2931.4209082808 | 2019-12-10 14public 38VO2.771566V08 (9 rows) [local: / data/run/pg12]: 5120 pg12@testdb=#\ Q [pg12@localhost ~] $psqlExpanded display is used automatically.psql (12.1) Type "help" for help. [local:/data/run/pg12]: 5120 pg12@testdb=# update T1 set id = 100 UPDATE 80000 [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables Schemaname | relname | n_dead_tup | last_vacuum | last_autovacuum-+ -- public | tbl | 0 | | public | T2 | 0 | | public | b | 0 | | | public | a | 0 | | public | rel | 0 | | public | t_count | 0 | | Public | t_big_autovacuum_1 | 0 | | public | t_autovacuum_1 | 0 | | public | T1 | 79868 | 2019-12-10 14public 2931.4209082208 | 2019-12-10 14public 38VO2.771566V08 (9 rows)

2.The autovacuum process is disabled on one or more tables

PG can set whether autovacuum is effective at the table level.

[local:/data/run/pg12]: 5120 pg12@testdb=# create table T2 (id int); CREATE TABLE [local:/data/run/pg12]: 5120 pg12@testdb=# alter table T2 SET (autovacuum_enabled = false) ALTER TABLE [local:/data/run/pg12]: 5120 pg12@testdb=#\ d T2 Table "public.t2" Column | Type | Collation | Nullable | Default-+-id | integer | [local:/data/ Run/pg12]: 5120 pg12@testdb=# SELECT reloptions FROM pg_class WHERE relname='t2' Reloptions-- {autovacuum_enabled=false} (1 row) [local:/data/run/pg12]: 5120 pg12@testdb=#

Insert data on T2 and update

[local:/data/run/pg12]: 5120 pg12@testdb=# insert into T2 select generate_series (1Jing 100000); INSERT 0 100000 [local:/data/run/pg12]: 5120 pg12@testdb=# update T2 set id = 1 politics update 100000 [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables Schemaname | relname | n_dead_tup | last_vacuum | last_autovacuum-+ -- public | tbl | 0 | | public | b | 0 | | public | a | 0 | | | public | rel | 0 | | public | T2 | 100000 | | public | t_count | 0 | | public | | | t_big_autovacuum_1 | 0 | | public | t_autovacuum_1 | 0 | | public | T1 | 0 | 2019-12-10 14t_autovacuum_1 2931.4209081908 | 2019-12-10 1414t_autovacuum_1 57.2692491908 (9 rows) |

The dead rows of T2 is 100000, but autovacuum does not vacuum the table after 60 seconds of timeout.

[local:/data/run/pg12]: 5120 pg12@testdb=#\! DateTue Dec 10 15:06:54 CST 2019 [local:/data/run/pg12]: 5120 pg12@testdb=#\! DateTue Dec 10 15:08:28 CST 2019 [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables Schemaname | relname | n_dead_tup | last_vacuum | last_autovacuum-+ -- public | tbl | 0 | | public | b | 0 | | public | a | 0 | | | public | rel | 0 | | public | T2 | 100000 | | public | t_count | 0 | | public | | | t_big_autovacuum_1 | 0 | | public | t_autovacuum_1 | 0 | | public | T1 | 0 | 2019-12-10 14 rows 2931.420908 local:/data/run/pg12 08 | 2019-12-10 14 public 59 public 57.269249 Portugal 08 (9 rows) [local:/data/run/pg12]: 5120 pg12@testdb=# |

Set the datasheet autovacuum_enabled to true, wait for 60s, and find that T2 has been vacuum

[local:/data/run/pg12]: 5120 pg12@testdb=# alter table T2 SET (autovacuum_enabled = true); ALTER TABLE [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables Schemaname | relname | n_dead_tup | last_vacuum | last_autovacuum-+ -- public | tbl | 0 | | public | b | 0 | | public | a | 0 | | | public | rel | 0 | | public | T2 | 100000 | | public | t_count | 0 | | public | | | t_big_autovacuum_1 | 0 | | public | t_autovacuum_1 | 0 | | public | T1 | 0 | 2019-12-10 14t_autovacuum_1 2931.4209082208 | 2019-12-10 14t_autovacuum_1 59t_autovacuum_1 57.2692491908 (9 rows) [local:/data] | / run/pg12]: 5120 pg12@testdb=#\! DateTue Dec 10 15:09:05 CST 2019 [local:/data/run/pg12]: 5120 pg12@testdb=# [local:/data/run/pg12]: 5120 pg12@testdb=#\! DateTue Dec 10 15:10:26 CST 2019 [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables Schemaname | relname | n_dead_tup | last_vacuum | last_autovacuum-+ -- public | tbl | 0 | | public | b | 0 | | public | a | 0 | | | public | rel | 0 | | public | T2 | 0 | | 2019-12-10 15 public 09 public 57.621123 008 public | t_count | 0 | | public | t_big_autovacuum_1 | 0 | public | t_autovacuum_1 | 0 | | public | T1 | 0 | 2019-12-10 14 public 2931.420908 | 2019-12-10 14: 59Rank 57.2692494008 (9 rows) [local:/data/run/pg12]: 5120 pg12@testdb=#

3.Autovacuuming settings aren't keeping pace with updates

If autovacuum is enabled, but not as often as we thought, you need to adjust the default configuration options.

[local:/data/run/pg12]: 5120 pg12@testdb=# SELECT name,setting,boot_val,pending_restart from pg_settings where category like 'Autovacuum' Name | setting | boot_val | pending_restart-- +-autovacuum | On | on | f autovacuum_analyze_scale_factor | 0.1 | f autovacuum_analyze_threshold | 50 | 50 | f autovacuum_freeze_max_age | 200000000 | 200000000 | f autovacuum_max_workers | 3 | 3 | f autovacuum_multixact_freeze_max_age | 400000000 | 400000000 | f autovacuum_naptime | | 60 | 60 | f autovacuum_vacuum_cost_delay | 2 | 2 | f autovacuum_vacuum_cost_limit |-1 |-1 | f autovacuum_vacuum_scale_factor | 0.2 | 0.2 | f autovacuum_vacuum_threshold | 50 | 50 | f (11 rows) |

Query pg_settings, where setting is the value of the current configuration and boot_val is the default. You can see that the configuration of the current library is the same as the default value.

The parameters that determine the running frequency of autovacuum are:

1.autovacuum_vacuum_threshold, trigger threshold, default is 50

2.autovacuum_vacuum_scale_factor, trigger dead rows rate. Default is 0.2, that is, 20%.

3. The estimated number of rows of the table, stored in pg_class.reltuples

PG combines the above three parameters to determine whether autovacuum needs to be executed. The calculation formula is as follows:

Autovacuuming threshold = autovacuum_vacuum_threshold + (autovacuum_vacuum_scale_factor * estimated number of rows in the table)

VACUUMs can be triggered to run more frequently by adjusting parameters, such as reducing autovacuum_vacuum_scale_factor.

PG also provides a log_autovacuum_min_duration parameter to diagnose the running interval of autovacuum, which will be recorded in the log if the time setting is exceeded, which helps to diagnose whether the setting of autovacuum is reasonable.

4.Lock conflicts

The execution of vacuum requires holding SHARE UPDATE EXCLUSIVE lock. If the locks held by session (SHARE UPDATE EXCLUSIVE,SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE) conflict with them, vacuum cannot be executed.

Update T2

[local:/data/run/pg12]: 5120 pg12@testdb=# begin;BEGIN [local:/data/run/pg12]: 5120 pg12@testdb=#* update T2 set id = 10; update 100000 [local:/data/run/pg12]: 5120 pg12@testdb=#* commit;COMMIT [local:/data/run/pg12]: 5120 pg12@testdb=# [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables Schemaname | relname | n_dead_tup | last_vacuum | last_autovacuum-+ -- public | tbl | 0 | | public | b | 0 | | public | a | 0 | | | public | rel | 0 | | public | T2 | 100000 | | 2019-12-10 15 public 09public 57.621123 008 public | t_count | 0 | | | | public | t_big_autovacuum_1 | 0 | public | t_autovacuum_1 | 0 | | public | T1 | 0 | 2019-12-10 14public 2931.4209081908 | 2019-12-10 14VOV 59Vera 57.26924914008 (9 rows) |

Open another window, lock table

[local:/data/run/pg12]: 5120 pg12@testdb=# begin;BEGIN [local:/data/run/pg12]: 5120 pg12@testdb=#* lock t2 in SHARE UPDATE EXCLUSIVE mode;LOCK TABLE [local:/data/run/pg12]: 5120 pg12@testdb=#*

Autovacuum cannot vacuum the table because it cannot acquire the lock

[local:/data/run/pg12]: 5120 pg12@testdb=# select pid,locktype,relation::regclass,mode,granted from pg_locks where pid pg_backend_pid () Pid | locktype | relation | mode | granted-+-+-58050 | virtualxid | | ExclusiveLock | t 58050 | relation | T2 | ShareUpdateExclusiveLock | t (2) Rows) [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT schemaname Relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables Schemaname | relname | n_dead_tup | last_vacuum | last_autovacuum-+ -- public | tbl | 0 | | public | b | 0 | | public | a | 0 | | | public | rel | 0 | | public | T2 | 100000 | | 2019-12-10 15 public 09public 57.621123 008 public | t_count | 0 | | | | public | t_big_autovacuum_1 | 0 | public | t_autovacuum_1 | 0 | | public | T1 | 0 | 2019-12-10 14public 2931.4209081908 | 2019-12-10 14VOV 59Vera 57.26924914008 (9 rows) |

Release lock

[local:/data/run/pg12]: 5120 pg12@testdb=#* commit;COMMIT [local:/data/run/pg12]: 5120 pg12@testdb=#

Autovacuum can be executed normally, last_autovacuum has been updated

[local:/data/run/pg12]: 5120 pg12@testdb=#\! Date Tue Dec 10 15:33:01 CST 2019 [local:/data/run/pg12]: 5120 pg12@testdb=#\! Date Tue Dec 10 15:33:40 CST 2019 [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables Schemaname | relname | n_dead_tup | last_vacuum | last_autovacuum-+ -- public | tbl | 0 | | public | b | 0 | | public | a | 0 | | | public | rel | 0 | | public | T2 | 0 | | 2019-12-10 15 public 32 public 58.743764 32 public | t_count | 0 | | | public | t_big_autovacuum_1 | 0 | public | t_autovacuum_1 | 0 | | public | T1 | 0 | 2019-12-10 14 public 2931.420908 | 2019-12-10 14: 59Rank 57.2692494008 (9 rows) [local:/data/run/pg12]: 5120 pg12@testdb=#

It can also be diagnosed by process status.

-- session 1 [local:/data/run/pg12]: 5120 pg12@testdb=# begin;BEGIN [local:/data/run/pg12]: 5120 pg12@testdb=#* lock T2 in SHARE UPDATE EXCLUSIVE mode;LOCK TABLE [local:/data/run/pg12]: 5120 pg12@testdb=#*-- session 2 [local:/data/run/pg12]: 5120 pg12@testdb=# vacuum T2 -- console [pg12@localhost ~] $ps-ef | grep 'waiting'pg12 56540 55944 0 14:59? 00:00:01 postgres: pg12 testdb [local] VACUUM waitingpg12 58502 53760 0 15:36 pts/2 00:00:00 grep-- color=auto waiting [pg12@localhost ~] $

The process is displayed as VACUUM waiting

5.Long-running open transactions

One side effect of MVCC is that vacuum cannot clean up expired dead rows that other transactions need to access. Therefore, if it is not necessary to ensure the normal completion of the transaction.

The status of the transaction can be monitored through the view pg_stat_activity

[local:/data/run/pg12]: 5120 pg12@testdb=# SELECT pid,xact_start, state, usename FROM pg_stat_activity Pid | xact_start | state | usename-+-55958 | 55960 | | | | pg12 56540 | 2019-12-10 15 pg12 42pg12 47.210597 pg12 08 | active | pg12 58050 | | idle | pg12 55956 | 55955 | | 55957 | (7 rows) |

If the state column is displayed as disabled, check the system parameter track_activities

[local:/data/run/pg12]: 5120 pg12@testdb=# show track_activities; track_activities-on (1 row) [local:/data/run/pg12]: 5120 pg12@testdb=# set track_activities=off;SET [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT pid,xact_start, state, usename FROM pg_stat_activity Pid | xact_start | state | usename-+-55958 | 55960 | pg12 56540 | | disabled | pg12 58050 | | idle | pg12 55956 | 55955 | | | 55957 | (7 rows) [local:/data/run/pg12]: 5120 pg12@testdb=# [local:/data/run/pg12]: 5120 pg12@testdb=# set track_activities=on | SET [local:/data/run/pg12]: 5120 pg12@testdb=# SELECT pid,xact_start, state, usename FROM pg_stat_activity Pid | xact_start | state | usename-+-55958 | 55960 | | | | pg12 56540 | 2019-12-10 15 pg12 52pg12 19.5000171408 | active | pg12 58050 | | idle | pg12 55956 | | 55955 | | 55957 | (7 rows) |

For session,PG that has been idle for a long time, the parameter idle_in_transaction_session_timeout is provided to control these session, and after the time configured by this parameter (in ms units), PG automatically terminates these session.

Thank you for your reading, the above is the content of "how to monitor the process of VACUUM in PostgreSQL". After the study of this article, I believe you have a deeper understanding of how to monitor the process of VACUUM in PostgreSQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.