阿列克谢·列索夫斯基(Alexei Lesovsky)2018年报告的解码“让我们关闭真空吗?!”
编者注:有关更改参数的任何建议应始终在其他报告中进行比较。
当PostgreSQL中出现问题时,通常会出现这样的调用,而主要的怀疑是 vacuum
(以下简称“真空”)。根据经验,许多人正在踩这种耙,我和我的Data Egret同事经常不得不对后果进行耙,因为那样会使一切变得更糟。但是,如果您注意真空本身,那么也许没有人会使用Postgres,但与此同时对他一无所知。毕竟,真空的历史是在很久以前开始的,在Internet上,您可以在邮件列表上找到许多有关真空的新旧帖子,其中包括冗长的讨论。尽管有关真空的主题在PostgreSQL的官方文档中有详细描述,但新的帖子和新的讨论将继续出现。也许这就是为什么许多神话,故事,恐怖故事和误解与真空有关的原因。同时,真空是PostgreSQL最重要的组件之一,他的工作直接影响了生产力。不可能在一个报告中绝对地说明有关真空的所有信息,但是我想揭示与真空有关的关键点,例如真空的内部结构,调优的主要方法,监视性能,监视以及以真空为主要内容时该怎么做。怀疑所有麻烦。好吧,当然,我想消除与真空相关的常见神话和误解。我想消除与真空相关的常见神话和误解。我想消除与真空相关的常见神话和误解。
![](https://habrastorage.org/webt/fx/sv/3m/fxsv3mqxulmnu4rdh_eipwrvvdm.png)
! . : , . , . , . , - , .
![](https://habrastorage.org/webt/k0/q0/k_/k0q0k_kbdrqxy8aamojouthl0xe.png)
, , , . , . , , , .
![](https://habrastorage.org/webt/0v/nw/uv/0vnwuvq_xpyl4z2f32zfkyragsg.png)
, , ?
![](https://habrastorage.org/webt/3w/rp/kp/3wrpkpqbjqzuz6k3q8h_s3rzafy.png)
: « , . - . , ».
? , . , , , – 100 % ( ). , . - .
![](https://habrastorage.org/webt/r0/bi/6q/r0bi6qtbpbdhpmh95itmamo5fny.png)
, . postgres’ . , . , . , . . - .
![](https://habrastorage.org/webt/ki/zn/id/kiznid6nupp9ofsryuotcz9olsk.png)
, . - , , , . . .
![](https://habrastorage.org/webt/7h/2u/3n/7h2u3nqb8mz019qfgwskfv3_23c.png)
- / : « , ». , . - .
![](https://habrastorage.org/webt/en/tn/lj/entnljkothqilsmlubxaxjmglsa.png)
, , , iotop
, . - . . .
. . , .
![](https://habrastorage.org/webt/5p/uh/zo/5puhzov2cqvzyprnnt2qpprrnme.png)
? , - . . . .
![](https://habrastorage.org/webt/jg/vf/g3/jgvfg34k-xk3l2xryqvjukmixd4.png)
- - : « . – autovacuum
. ».
![](https://habrastorage.org/webt/ed/ui/p6/eduip6msje8zxkrqnh4-8vabosg.png)
, , , : , . . .
, .
- ( DBA) – , . , . , .
- , . . . .
- ,
shared buffers
, ( , ), . . - , shared buffers. - . . – .
![](https://habrastorage.org/webt/lc/do/dt/lcdodt4jbsygkgp8jx041vcbfsw.png)
. . : https://github.com/lesovsky/ConferenceStuff/tree/master/2016.highload
? pgbench
. pgbench . . , . . . , .
![](https://habrastorage.org/webt/cb/xd/97/cbxd97cteefhstwlddgwori2ajc.png)
. , . , , , - . , Postgres, ( , ). , .
![](https://habrastorage.org/webt/we/ts/d9/wetsd9tb46ev3juckcwk9ezt2ta.png)
, MVCC.
MVCC (Multi-Version Concurrency Control) – "" , Postgres', . . .
![](https://habrastorage.org/webt/eo/qt/lc/eoqtlcif0e8ox_ihwmuqclbgwew.png)
? . . (snapshot).
![](https://habrastorage.org/webt/d-/zx/ja/d-zxjayq6qlxhpcu2bfxythgtqm.png)
: , . , ( COMMIT ROLLBACK).
![](https://habrastorage.org/webt/6z/ph/sb/6zphsbe6c9bckavrgr_fgbknwme.png)
COMMIT, , . , " ". : PostgreSQL.
. , . - , «delete», «update». .. "" — dead tuples
.
![](https://habrastorage.org/webt/iw/kx/w0/iwkxw0lsgd6tagswlyis5m65ouu.png)
. – xmin
. , . . . insert, xmin .
, . xmax
, .
delete. . — xmax
, .
, "" . , xmax . — , Postgres , . - - , .. , .
![](https://habrastorage.org/webt/8i/-c/cg/8i-ccgu38dh9ydkg7ue9sk1qqn0.png)
, ? , , , .
. , . .
![](https://habrastorage.org/webt/7r/cq/f1/7rcqf1nzi4dtbmownh9rqfchbz4.png)
«delete», «update» , / . , , - .
![](https://habrastorage.org/webt/fd/0h/fc/fd0hfcbvlzr83cdaf4ktpwlszoc.png)
, . .
![](https://habrastorage.org/webt/xh/pu/mb/xhpumb0tkvun4urwv534i0s1gje.png)
, , , . .
![](https://habrastorage.org/webt/17/fu/kg/17fukg6fvv_ttf4jdwub_mk27ho.png)
:
- , , , "", , .
- shared buffers .
bloat
, .. — .
![](https://habrastorage.org/webt/5u/sn/se/5usnsewb85uihdn5nenam9yjf2c.png)
. ?
- -, .
- Postgres, . . . , , - , .
- . . .
- – . . . , .
![](https://habrastorage.org/webt/ew/8v/dt/ew8vdtf5y49avskvvnunodanele.png)
- - . , Postgres . , . , .
- , .
- ,
. 32 , . 4294967296. , . , , ( ). .. to prevent wraparound vacuum
, . wraparound vacuum — . - , , . - . . . , . , .
![](https://habrastorage.org/webt/oo/kv/w0/ookvw0ol3x-fulqpcltgnhw9zwe.png)
- . Postgres , Postgres , , Pentium, - , . - . , , , , - . . .
- Postgres . . 9.6. , 9.6 , 9.6. ( , Postgres 12)
![](https://habrastorage.org/webt/ob/n6/cn/obn6cnkpbariycrvnlrqxloa2se.png)
- . .
![](https://habrastorage.org/webt/_w/bj/66/_wbj66wqi-nindu2znh5gsnjqna.png)
-, , .. — 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 .
, , , . . .
![](https://habrastorage.org/webt/km/tg/ci/kmtgcideecl9aqsljdkwouizedw.png)
-, . - , , .
- , 32 , . , , autovacuum_max_workers 10-15 % .
- autovacuum_naptime. , . 60 . . , 1 . , . , – . . . . , .
- – , vacuum_cost_limit, , , . , vacuum_cost_limit .
![](https://habrastorage.org/webt/oo/2e/47/oo2e47hrh4hxob8jvyjjucjc0ly.png)
-, , . , . – .
. autovacuum_vacuum_scale_factor. scale factor . - scale_factor 0.2, . . 20 %.
autovacuum_vacuum_threshold. - – 50 . , , .
![](https://habrastorage.org/webt/63/en/ti/63enti4gfce93el_fce3twqvrso.png)
, - 20 %. , autovacuum_vacuum_threshold , , 1-2-5 %.
![](https://habrastorage.org/webt/zi/1s/oq/zi1soqr-3vks3mqnxi1hcr1bsyo.png)
, autovacuum_vacuum_scale_factor . . , 1 % — . autovacuum_vacuum_scale_factor 0. autovacuum_vacuum_threshold, . . , , . autovacuum_vacuum_scale_factor , autovacuum_vacuum_threshold.
![](https://habrastorage.org/webt/rm/jj/r3/rmjjr3cxwqz4cxutagmm9gcnujo.png)
, . 4-5 , HDD , . HDD . , , . , , , .
SSD . SSD . , .
SSD . , . , (, ), . – , .
NVMe , , . I/O . , . , - – - . , IO, , .
![](https://habrastorage.org/webt/_w/ui/pp/_wuippkoa0tusm7tvz2tke2admk.png)
- . vacuum_cost_delay vacuum_cost_limit. . . . . . . – . .
, . , , .
![](https://habrastorage.org/webt/il/wp/7r/ilwp7rhrlmrv9kmau_4z2__plca.png)
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.
![](https://habrastorage.org/webt/ix/sd/ob/ixsdobjhrykz7d6mzhdorx66_n0.png)
. - , - "" , .
, storage parameters
. tablespaces
, . , .
![](https://habrastorage.org/webt/tf/qp/pf/tfqppfseyswfcbfn4fvxsv-74d4.png)
, — , , , Postgres. , , pgcompacttable pg_repack.
bloat
.
, , . – , ( ). .
![](https://habrastorage.org/webt/jj/ct/4r/jjct4rtt6pjarvfjpifbvfzt39i.png)
, . - .
Postgres , , .
pg_stat_activity
. , (view
) , , .
, , , . . . pg_stat_atctivity ().
![](https://habrastorage.org/webt/ad/s1/r3/ads1r32i9l8xq6p0yplfgalwuv8.png)
, , . , prevent wraparound
. (autovacuum_max_workers
)– .
– . , , , . — . .
![](https://habrastorage.org/webt/iv/kt/be/ivktbevhwtimzgspbkqf-x_y9we.png)
, , pg_stat_progress_vacuum
9.6. .
, , . pg_stat_activity
, , , , pg_stat_progress_vacuum
.
![](https://habrastorage.org/webt/rt/xk/fw/rtxkfw2t_nmyn3fuxgpa55eavag.png)
https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/vacuum_activity.sql
, , , . , 50 % . 3 , 3 .
— : , . , .
![](https://habrastorage.org/webt/f8/bo/db/f8bodbtuoqugyofhyzvcqqlrv40.png)
:
- . .
- . 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 . , , , , . . . - – . .
, . - ?
postgres代码中有单独的函数,用于收集有关表内数据分布的统计信息。这是一个独立的自动真空子系统。它从表中读取有限大小的样本数据。并在此基础上根据数据建立分布。然后,他将该信息保存在系统目录pg_statistic
或系统视图中pg_stats
。当计划者构建查询计划时,它会从该目录中读取信息。并在此基础上制定计划。然后他选择了最好的一个。