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.

Autovacuum log information

This section describes the vacuum-related parameters in the configuration parameters, including log_autovacuum_min_duration, autovacuum_naptime, and so on.

Log_autovacuum_min_duration

If the execution time of autovacuum exceeds the time configured by this parameter (in ms), it will be recorded in the log.

Set to a lower threshold: 10ms

[pg12@localhost pg121db] $grep 'log_autovacuum' postgresql.conf log_autovacuum_min_duration = 10 #-1 disables, 0 logs all actions and

Execute SQL

[local:/data/run/pg12]: 5120 pg12@testdb=# [local:/data/run/pg12]: 5120 pg12@testdb=# drop table tasking autotransium.DROP TABLE [local:/data/run/pg12]: 5120 pg12@testdb=# create table t_autovacuum_1 (id int,c1 varchar (20), c2 varchar (20)) CREATE TABLE [local:/data/run/pg12]: 5120 pg12@testdb=# [local:/data/run/pg12]: 5120 pg12@testdb=# insert into t_autovacuum_1 select [local:/data/run/pg12]: 5120 pg12@testdb=# [local:/data/run/pg12]: 5120 pg12@testdb=# drop table t_big_autovacuum_1 | | x from generate_series (1x10 000) as x insert 0 10000 [local:/data/run/pg12]: 5120 pg12@testdb=# [local:/data/run/pg12]: 5120 pg12@testdb=# [local:/data/run/pg12]: 5120 pg12@testdb=# drop table t_big_autovacuum_1 DROP TABLE [local:/data/run/pg12]: 5120 pg12@testdb=# create table t_big_autovacuum_1 (id int,c1 varchar (20), c2 varchar (20)); CREATE TABLE [local:/data/run/pg12]: 5120 pg12@testdb=# [local:/data/run/pg12]: 5120 pg12@testdb=# insert into t_big_autovacuum_1 select x from generate_series c2' | | x from generate_series (1mem1000000) as x INSERT 0 1000000 [local:/data/run/pg12]: 5120 pg12@testdb=# [local:/data/run/pg12]: 5120 pg12@testdb=# explain (analyze,verbose) select * from t_autovacuum_1 a join t_big_autovacuum_1 b on a.id = b.id QUERY PLAN- -Merge Join (cost=63985.35..155911.33 rows=6068861 width=240) (actual time=480.678..492.453 rows=10000 loops=1) Output: a.id A.c1, a.c2, b.id, b.c1, b.c2 Merge Cond: (a.id = b.id)-> Sort (cost=301.37..310.03 rows=3465 width=120) (actual time=3.061..3.835 rows=10000 loops=1) Output: a.id, a.c1 A.c2 Sort Key: a.id Sort Method: quicksort Memory: 1213kB-> Seq Scan on public.t_autovacuum_1 a (cost=0.00..97.65 rows=3465 width=120) (actual time=0.020..1.796 rows=10000 loops=1) Output: a.id, a.c1, a.c2-> Materialize (cost=63683.99..65435.46 rows=350295 width=120) (actual time=477.603..481.446 rows=10001 loops=1) Output: b.id B.c1, b.c2-> Sort (cost=63683.99..64559.72 rows=350295 width=120) (actual time=477.598..479.173 rows=10001 loops=1) Output: b.id, b.c1 B.c2 Sort Key: b.id Sort Method: external merge Disk: 31144kB-> Seq Scan on public.t_big_autovacuum_1 b (cost=0.00..9871.95 rows=350295 width=120) (actual time=0.019..176.859 rows=1000000 loops=1) Output: b.id, b.c1 B.c2 Planning Time: 0.315 ms Execution Time: 498.336 ms (19 rows) [local:/data/run/pg12]: 5120 pg12@testdb=#

The log shows the analyze operation of autovacuum

2019-12-11 12 automatic analyze of table 31 CST,57525,5df07134.e0b5,1,2019-12-11 12:31:48 CST,4/3,3091,LOG,00000, "automatic analyze of table"testdb.public.t_autovacuum_1"system usage: user: 0.09s, system: 0.00s, elapsed: 0.10s", "2019-12-11 1215 31MA 49.073 CST,57525,5df07134.e0b5,2" 2019-12-11 12:31:48 CST,4/5,3092,LOG,00000, "automatic analyze of table"testdb.public.t_big_autovacuum_1"system usage: CPU: user: 0.32s, system: 0.00s, elapsed: 0.40s", ""

Autovacuum_naptime

This parameter controls the scheduling time of autovacuum. If there are multiple databases, each database starts autovacuum at certain intervals (the number of autovacuum_naptime/ databases) for processing.

It can be seen from the source code of PG, through the function rebuild_database_list to build the changed DatabaseList, the database in the linked list should appear in pgstats, evenly distributed within the time interval set by autovacuum_naptime.

For example, if autovacuum_naptime = 60s and there are 4 databases db1- > db4, then every 60s/4 will launch an autovacuum worker to process the corresponding DB.

A possible processing time series is: db1- > XX (hours): XX (minutes): 18 (seconds), db4- > XX:XX:33,db4- > XX:XX:48,db4- > XX:XX:03

If you need to vacuum db1- > db4 later, db1- > db4 will trigger autovacuum in the next 18 seconds, 33 seconds, 48 seconds, and 03 seconds.

Test scenario: create three databases, db1-> db3, each database performs full table updates every 10s, and observe the behavior of autovacuum through logs.

-- session 1\ c db1drop table autoengineer varchar db1 create table t_autovacuum_db1 (id int,c1 varchar (20), c2 varchar (20)); insert into t_autovacuum_db1 select x varchar c1' | | x from generate_series (1penny 500000) as x th as doormdeclarebegin for i in 1.. 20 loop update t_autovacuum_db1 set id = i; commit; perform pg_sleep (10); end loop;end$$;-- session 2\ c db2drop table t_autovacuum_db2 Create table t_autovacuum_db2 (id int,c1 varchar (20), c2 varchar (20)); insert into t_autovacuum_db2 select xpenalty c1' | | xmemencec2' | | x from generate_series (1Yue500000) as xomoclinic declarebegin for i in 1.. 20 loop update t_autovacuum_db2 set id = i; commit; perform pg_sleep (10); end loop;end$$;-- session 3\ c db3drop table tincture autoengineer DB3create table t_autovacuum_db3 (id int,c1 varchar (20), c2 varchar (20)) Insert into t_autovacuum_db3 select XMagne C1' | | XMagne C2' | | x from generate_series (1Mem 500000) as x doctoring declarebegin for i in 1.. 20 loop update t_autovacuum_db3 set id = i; commit; perform pg_sleep (10); end loop;end$$

Corresponding log output

2019-12-11 15 db2.public.t_autovacuum_db2 34 CST,1870,5df09bec.74e,1,2019 05.298 CST,1870,5df09bec.74e,1,2019-12-11 15:34:04 CST,6/37,3406,LOG,00000, "automatic analyze of table"db2.public.t_autovacuum_db2"system usage: user: 0.38s, system: 0.01s, elapsed: 0.52s", "2019-12-11 1515 system usage 20.528 CST,1884,5df09bfb.75c,1" 2019-12-11 15:34:19 CST,6/41,3412,LOG,00000, "automatic analyze of table"db1.public.t_autovacuum_db1"system usage: CPU: user: 0.51s, system: 0.01s, elapsed: 0.71s", "2019-12-11 1515 system usage 35.482 CST,1897,5df09c0a.769,1,2019-12-11 15:34:34 CST,6/45,3416,LOG,00000 "automatic analyze of table"db3.public.t_autovacuum_db3"system usage: CPU: user: 0.40s, system: 0.01s, elapsed: 0.61s", "2019-12-11 15system usage 35CPU 05.904 CST,1924,5df09c28.784,1,2019-12-11 15:35:04 CST,6/51,0,LOG,00000," automatic vacuum of table "" db2.public.t_autovacuum_db2 ": index scans: 0pages: 0 removed, 25467 remain 0 skipped due to pins, 0 skipped frozentuples: 500149 removed, 500000 remain, 0 are dead but not yet removable, oldest xmin: 3422buffer usage: 50964 hits, 2 misses, 6 dirtiedavg read rate: 0.015 MB/s, avg write rate: 0.044 MB/ssystem usage: CPU: user: 0.43s, system: 0.00s, elapsed: 1.07s "," 2019-12-11 1535removed 21.297 CST,1937,5df09c37.791,1,2019-12-11 15:35:19 CST Index scans: 0pages: 0 removed, 31832 remain, 0 skipped due to pins, 0 skipped frozentuples: 500270 removed, 500000 remain, 0 are dead but not yet removable, oldest xmin: 3427buffer usage: 63695 hits, 2 misses, 6 dirtiedavg read rate: 0.010 MB/s, avg write rate: 0.031 MB/ssystem usage: CPU: user: 0.51s, system: 0.04s, elapsed: 1.52s " "2019-12-11 15 db3.public.t_autovacuum_db3 35 CST,1950,5df09c46.79e,1,2019 CST,1950,5df09c46.79e,1,2019-12-11 15:35:34 CST,6/59,0,LOG,00000," automatic vacuum of table "" db3.public.t_autovacuum_db3 ": index scans: 0pages: 0 removed, 29875 remain, 1 skipped due to pins, 0 skipped frozentuples: 270 removed, 499874 remain, 0 are dead but not yet removable, oldest xmin: 3430buffer usage: 59780 hits, 2 misses, 6 dirtiedavg read rate: 0.011 MB/s Avg write rate: 0.034 MB/ssystem usage: CPU: user: 0.43s, system: 0.03s, elapsed: 1.38s ","2019-12-11 15purge 387.146 CST,2081,5df09cdc.821,1,2019-12-11 15:38:04 CST,6/81,0,LOG,00000," automatic vacuum of table "" db2.public.t_autovacuum_db2 "": index scans: 0pages: 0 removed, 44551 remain, 0 skipped due to pins " 0 skipped frozentuples: 500509 removed, 500000 remain, 0 are dead but not yet removable, oldest xmin: 3463buffer usage: 89140 hits, 1 misses, 5 dirtiedavg read rate: 0.003 MB/s, avg write rate: 0.017 MB/ssystem usage: CPU: user: 0.58s, system: 0.11s, elapsed: 2.28s "," 2019-12-11 15 Switzerland 3807.963 CST,2081,5df09cdc.821,2,2019-12-11 15:38:04 CST,6/82,3463,LOG,00000 "automatic analyze of table"db2.public.t_autovacuum_db2"system usage: CPU: user: 0.38s, system: 0.00s, elapsed: 0.81s", "" 2019-12-11 15Fran 25.836 CST,2094,5df09ceb.82e,1,2019-12-11 15:38:19 CST,6/86,0,LOG,00000, "automatic vacuum of table"db1.public.t_autovacuum_db1": index scans: 0pages: 0 removed, 38185 remain 0 skipped due to pins, 0 skipped frozentuples: 500585 removed, 500000 remain, 0 are dead but not yet removable, oldest xmin: 3464buffer usage: 76407 hits, 1 misses, 15391 dirtiedavg read rate: 0.001 MB/s, avg write rate: 19.978 MB/ssystem usage: CPU: user: 0.66s, system: 0.03s, elapsed: 6.01s "," 2019-12-11 1538Switzerland 26.593 CST,2094,5df09ceb.82e,2,2019-12-11 15:38:19 CST "automatic analyze of table", "db1.public.t_autovacuum_db1", "system usage: CPU: user: 0.35s, system: 0.00s, elapsed: 0.75s", "2019-12-11 15 system usage 38 user 36.546 CST,2107,5df09cfa.83b,1,2019-12-11 15:38:34 CST,6/91,0,LOG,00000 "automatic vacuum of table"db3.public.t_autovacuum_db3": index scans: 0pages: 0 removed, 41365 remain, 0 skipped due to pins, 0 skipped frozentuples: 500758 removed, 500000 remain, 0 are dead but not yet removable, oldest xmin: 3465buffer usage: 82768 hits, 1 misses, 2 dirtiedavg read rate: 0.005 MB/s, avg write rate: 0.010 MB/ssystem usage: CPU: user: 0.58s, system: 0.03s, elapsed: 1.63s " "2019-12-11 15 Flux 38 automatic analyze of table-12-11 15:38:34 CST,6/92,3465,LOG,00000," automatic analyze of table "" db3.public.t_autovacuum_db3 "" system usage: CPU: user: 0.37s, system: 0.00s, elapsed: 0.72s ","

As you can see from the log, PG believes that there are currently four databases (60s/4) to be processed, with one database scheduled for each 15s.

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.