Decoding of the 2018 report by Alexei Lesovsky “Let's turn off the vacuum ?!”
Editor's Note: Any recommendations for changing parameters should always be compared in other reports.
Such a call often arises when problems arise in PostgreSQL, and the main suspect is vacuum(hereinafter simply “vacuum”). From experience, many are stepping on this rake, and my Data Egret colleagues and I often have to rake in the consequences, because then everything gets worse. But if you pay attention to the vacuum itself, then, perhaps, there is no such person who would use Postgres, and at the same time did not know anything about him. After all, the history of the vacuum begins relatively long ago, and on the Internet you can find a lot of both old and new posts about the vacuum, voluminous discussions on the mailing lists. Despite the fact that the topic of the vacuum is described in detail in the official documentation for PostgreSQL, new posts and new discussions will continue to appear. Perhaps that is why a lot of myths, tales, horror stories and misconceptions are associated with the vacuum. Meanwhile, vacuum is one of the most important components of PostgreSQL,and his work directly affects productivity. It is impossible to tell absolutely everything about the vacuum in one report, but I would like to reveal key points related to the vacuum, such as its internal structure, the main approaches to its tuning, monitoring performance, monitoring, and what to do when the vacuum is the main thing suspect of all troubles. Well, and, of course, I want to dispel common myths and misconceptions associated with the vacuum.I want to dispel common myths and misconceptions associated with the vacuum.I want to dispel common myths and misconceptions associated with the vacuum.
! . : , . , . , . , - , .
, , , . , . , , , .
, , ?
: « , . - . , ».
? , . , , , – 100 % ( ). , . - .
, . postgres’ . , . , . , . . - .
, . - , , , . . .
- / : « , ». , . - .
, , , iotop , . - . . .
. . , .
? , - . . . .
- - : « . – autovacuum. ».
, , , : , . . .
, .
- ( DBA) – , . , . , .
- , . . . .
- ,
shared buffers, ( , ), . . - , shared buffers. - . . – .
. . : https://github.com/lesovsky/ConferenceStuff/tree/master/2016.highload
? pgbench . pgbench . . , . . . , .
. , . , , , - . , Postgres, ( , ). , .
, MVCC.
MVCC (Multi-Version Concurrency Control) – "" , Postgres', . . .
? . . (snapshot).
: , . , ( COMMIT ROLLBACK).
COMMIT, , . , " ". : PostgreSQL.
. , . - , «delete», «update». .. "" — dead tuples.
. – xmin. , . . . insert, xmin .
, . xmax , .
delete. . — xmax , .
, "" . , xmax . — , Postgres , . - - , .. , .
, ? , , , .
. , . .
«delete», «update» , / . , , - .
, . .
, , , . .
:
- , , , "", , .
- shared buffers .
bloat , .. — .
. ?
- -, .
- Postgres, . . . , , - , .
- . . .
- – . . . , .
- - . , Postgres . , . , .
- , .
- ,
to prevent wraparound vacuum, . wraparound vacuum — . - , , . - . . . , . , .
- . Postgres , Postgres , , Pentium, - , . - . , , , , - . . .
- Postgres . . 9.6. , 9.6 , 9.6. ( , Postgres 12)
- . .
-, , .. — cost-based vacuum. .
, : . , ( ) — . .
, . .
. vacuum_cost_page_hit, vacuum_cost_page_miss, vacuum_cost_page_dirty, hit, miss, dirty.
Hit – , , shared buffers, . . . , .
Miss – shared buffers . . , . , shared buffers, (page cache) - . , .
Dirty – , , . .
. vacuum_cost_limit. .
vacuum_cost_delay. , cost limit . cost delay .
, , , . . .
-, . - , , .
- , 32 , . , , autovacuum_max_workers 10-15 % .
- autovacuum_naptime. , . 60 . . , 1 . , . , – . . . . , .
- – , vacuum_cost_limit, , , . , vacuum_cost_limit .
-, , . , . – .
. autovacuum_vacuum_scale_factor. scale factor . - scale_factor 0.2, . . 20 %.
autovacuum_vacuum_threshold. - – 50 . , , .
, - 20 %. , autovacuum_vacuum_threshold , , 1-2-5 %.
, autovacuum_vacuum_scale_factor . . , 1 % — . autovacuum_vacuum_scale_factor 0. autovacuum_vacuum_threshold, . . , , . autovacuum_vacuum_scale_factor , autovacuum_vacuum_threshold.
, . 4-5 , HDD , . HDD . , , . , , , .
SSD . SSD . , .
SSD . , . , (, ), . – , .
NVMe , , . I/O . , . , - – - . , IO, , .
- . vacuum_cost_delay vacuum_cost_limit. . . . . . . – . .
, . , , .
vacuum_cost_delay = 0
vacuum_cost_page_hit = 0
vacuum_cost_page_miss = 5
vacuum_cost_page_dirty = 5
vacuum_cost_limit = 200
--
autovacuum_max_workers = 10
autovacuum_naptime = 1s
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_delay = 5ms
autovacuum_vacuum_cost_limit = -1
SSD , . , , .
: SSD.
. - , - "" , .
, storage parameters. tablespaces, . , .
, — , , , Postgres. , , pgcompacttable pg_repack.
bloat .
, , . – , ( ). .
, . - .
Postgres , , .
pg_stat_activity. , (view) , , .
, , , . . . pg_stat_atctivity ().
, , . , prevent wraparound . (autovacuum_max_workers)– .
– . , , , . — . .
, , pg_stat_progress_vacuum 9.6. .
, , . pg_stat_activity , , , , pg_stat_progress_vacuum .
https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/vacuum_activity.sql
, , , . , 50 % . 3 , 3 .
— : , . , .
:
- . .
- . cost-based, cost-.
- – . , , .
! ! . 9.6 , . , , , . - ? , - ?
. 9.6 freeze-, . postgres’ , wraparound vacuum. , . , , . , , . , 9.5 . , . , .
, buffer cache, read?
, autovacuum worker , buffer-. 32 , -. , .
?
, , buffer cache, page cache, , . . , page cache, shared buffers .
, ! , . , . time . 5 1 , . . , 5 100 % CPU, storage. .
– . – . CPU, - . – ? Ubuntu. Ubuntu powersave. . ., , 3,4 GHz, – 1,2. . performance . . , , , , , , . , , .
. , , . . - , . - bloat - , . .
! , . : . - autovacuum_vacuum_scale_factor autovacuum_vacuum_threshold . , , , . , naptime , analyze. Postgres . pg_class reltuples
.
. .
.
dead tuples community , 2ndQuadrant. – . analyze reltuples .
, . – . . , «idle in transactions», , . - . , . . , .
, ?
, MVCC . - , .
reltuples?
. .
( ) . , . : update, insert . . : , . tuples. .
, . . , . . reltuples . , , , , . . . - – . .
, . - ?
There are separate functions in the postgres code that collect statistics about the distribution of data within tables. This is a separate autovacuum subsystem. It reads sample data of limited size from the table. And on the basis of it builds the distribution according to the data. And he saves this information in the system catalog pg_statisticor in the system view pg_stats. And when the planner builds query plans, it reads information from this directory. And on its basis makes plans. And then he chooses the best one.