我建议您熟悉Nikolay Samokhvalov对报告的解码,“工业化PostgreSQL调优:数据库实验”
Shared_buffers = 25%-是很多还是一点?还是恰到好处?如何理解该建议是否过时,在您的特定情况下是建议?
现在该解决“以成人方式”选择postgresql.conf参数的问题了。不是基于盲目的“自动调谐器”或文章和博客中过时的提示,而是基于:
- 严格校准的数据库实验会自动进行,且数量尽可能大,且条件应尽可能接近“打击”实验
- 深入了解DBMS和OS的功能。
使用Nancy CLI(https://gitlab.com/postgres.ai/nancy),我们将考虑一个特定的示例-臭名昭著的shared_buffers-在不同的情况下,在不同的项目中,并尝试找出如何为我们的基础架构,数据库和负载选择最佳设置。
![](https://habrastorage.org/webt/qd/pg/8s/qdpg8shzqvosvdo6fzxcimjlkj0.png)
这将是关于数据库的实验。这个故事持续了六个多月。
![](https://habrastorage.org/webt/sv/p-/eg/svp-eg23opefncty1m_zgo0bilo.png)
. Postgres 14 . -. Postgres .
RuPostgres Meetup, 2- . 2 000 . RuPostgres.org.
, Highload, , Postgres .
![](https://habrastorage.org/webt/9e/g_/6d/9eg_6dcfifnxv_5ogejr1mshcgs.png)
Postgres- 11 .
![](https://habrastorage.org/webt/p1/1w/au/p11waubqt5zyc8868fboisek7na.png)
, Postgres, , 2010- . , DBA, - . , - , .
, . . . . . , , . . , DBA, .
![](https://habrastorage.org/webt/yq/qb/ae/yqqbaeczh1hjr4pvfftrpfvawlu.png)
:
- « » – 8 GB 25 % shared_buffers . shared_buffers .
- «».
![](https://habrastorage.org/webt/yn/ji/lw/ynjilw4yy0k9qnpbv3rzi6lkfnk.png)
?
- , . , , Open Source, . . Open Source. , Open Source. , , . .
- , : .
![](https://habrastorage.org/webt/nw/ke/nc/nwkencenuci6tep097c4s7xbr0q.png)
?
![](https://habrastorage.org/webt/db/ng/hr/dbnghrl6fm1fithr47n0jlhw2y4.png)
-, DBA instances, . ., — .
![](https://habrastorage.org/webt/a6/3q/o8/a63qo8e9k4ogrj0c-m41g1bh1mc.png)
. , - , - . , , .
![](https://habrastorage.org/webt/pq/wy/-4/pqwy-4nmoonge1h3zaesghswsdc.png)
. Pg_stat_statements – . Postgres pgBadger.
. . SELECT * FROM table where «?» «$» Postgres 10. – index scan seq scan. . , index scan. , 90 % , seq scan , Postgres . pg_stat_statements, - .
, «log_min_duration_statement = 0», . . , . - , , , , .
DBA ?
![](https://habrastorage.org/webt/u7/ww/bt/u7wwbto9_7zlvc0--36ms1tubie.png)
, - . ? , - - instance, . DBA, staging. . . , production. DBA production, . , , , , . . .
![](https://habrastorage.org/webt/sp/yn/wz/spynwzna6ek4c0-pe7r7jybup3y.png)
![](https://habrastorage.org/webt/b6/fl/hr/b6flhrzacge58mklwr8e-jwwsnc.png)
. Postgres. . Postgres, . , DBA , DBA . . . 10 , .
– . , , .
![](https://habrastorage.org/webt/or/_n/tk/or_ntkllb0epxjg4zrxailgjiy8.png)
, . , 1 000 default_statistict_target – . , production.
![](https://habrastorage.org/webt/mo/rs/-u/mors-umajinj1ezvzfeycz9ux2i.png)
, , , .
![](https://habrastorage.org/webt/zu/77/bg/zu77bgg_pg0r-n3gtwjhihpf9zy.png)
. .
- – . . - , , production. . instance Google, . . . . Postgres.
- – . . . .
- – . .
- – , , . . . , , . .
![](https://habrastorage.org/webt/82/ox/tj/82oxtjkgp3qh_vp6etswsoy63m0.png)
. pg_stat_statements. – , . – .
![](https://habrastorage.org/webt/mj/yd/nd/mjydnd1fu_teb-og-mqkvttaq5o.png)
default_statistics_target = 100, =1 000. , . 8 % .
![](https://habrastorage.org/webt/xw/-k/ya/xw-kyakkzuyp56hmdhbfzkv902g.png)
, pgBadger pg_stat_statements. . , - 88 %. . , , . , . .
![](https://habrastorage.org/webt/7c/gq/zt/7cgqztb-ifmexaxgkzoonmfd2aw.png)
, «ALTER TABLE … ALTER COLUMN» 100 . , . . , , .
![](https://habrastorage.org/webt/qy/st/bd/qystbdcypfgezpekm0xesitqonw.png)
![](https://habrastorage.org/webt/-m/7g/34/-m7g34dqtvctdwg4onytlmg-qae.png)
. CI- . .
![](https://habrastorage.org/webt/al/8s/ge/al8sged0fepxd2-okxjpgmpy0p8.png)
: , , , . - - . , .
.
![](https://habrastorage.org/webt/hb/kh/n_/hbkhn_jmxzktwbkq_jnh76ypqas.png)
-, . production, . , , . .
, , , . . , . Postgres. . production . , , auto_explain.
, , . , , .
Nancy CLI – « »
![](https://habrastorage.org/webt/vg/fw/x7/vgfwx7-hlrx-nxfxzbdphclf3eq.png)
. . . , . Open Source Nancy CLI. , .
![](https://habrastorage.org/webt/el/cy/uk/elcyukcwkmsilmdg9k2jsci_ugm.png)
Nancy — Open Source, Gitlab. , . . help .
, . . , . – delete 40 000 000 IO, , , . . . . , - , . production. .
![](https://habrastorage.org/webt/8k/0q/ja/8k0qjauzt14_qnvjfjv8qww93nu.png)
? , . . , MacBook . , . . - instance , , .
EC2 Instance, . . , 500 i3 instance, i3-16-xlarge. 500 64 . 15 . . . , , – 70 %, . . .
![](https://habrastorage.org/webt/z8/og/9w/z8og9w-6ojduuqkyaah5gm4vkcw.png)
Postgres . - 12- .
![](https://habrastorage.org/webt/5v/qx/rp/5vqxrpupmw50qgs-gwvlafp716w.png)
. :
- Dump/sql-.
- – PGDATA . -. , . , Google . production. .
- , , Postgres - . pgbench. pgbench. «db-pgbench». , scale. , .
![](https://habrastorage.org/webt/aa/xo/b4/aaxob4mfpxubq9y0b3b_zm3brbg.png)
:
- SQL . .
- . . . . , . pgreplay , Nancy.
- . , . , . pgbench .
![](https://habrastorage.org/webt/rb/8c/3x/rb8c3x_7yq_xxrdeprhbvjtkzeu.png)
- SQL - , . . - , , ANALAZE . , . , SQL.
- . , , , 100 . . , . , , . . pgdata . Postgres , . .
![](https://habrastorage.org/webt/lk/c2/se/lkc2se4ozhbzo8xwwf_bhrsygf0.png)
- , , pgstat***. – pg_stat_statements, pg_stat_kcacke. , . pg_stat_bgwriter pgwriter , checkpoint , . . , shared_buffers , , .
- Postgres. – .
- – FlameGraphs.
- , pgreplay pgbench , . latency TPS. , .
- .
- CPU IO. EC2 instance , 100 instances 100 , 10 000 . , instance, - . . . sysbench , , . . , CPU IO .
![](https://habrastorage.org/webt/ne/c7/gm/nec7gmglsgtvrqo-9m0wwa3js2w.png)
?
![](https://habrastorage.org/webt/ay/df/ft/aydfft2txkzvr-11ihpb21haxhy.png)
, . , Open Source pgreplay . . , , .
duration timestamp. . – ?
![](https://habrastorage.org/webt/ar/te/n7/arten7mzrb7wytqiyvkvnyzjgnc.png)
https://gist.github.com/NikolayS/08d9b7b4845371d03e195a8d8df43408
, . , . pg_stat_statements, ( ) , .
. , , , . , . , .
, 802 . , bytes_per sec – 300 kB/s . , , .
![](https://habrastorage.org/webt/-u/ih/lg/-uihlgkmfort8r2ixkcwj9x646y.png)
! , . «syslog».
![](https://habrastorage.org/webt/wg/g3/-e/wgg3-ev0pb4czij_jwspoyjvqwe.png)
syslog, . pgbench, , .
![](https://habrastorage.org/webt/4v/zs/z5/4vzsz5wjfozidqy6xnrqoo0ggws.png)
– . 161 000 TPS. syslog – Ubuntu 16.04 37 000 TPS. , . . . , , .
![](https://habrastorage.org/webt/nr/_j/cs/nr_jcsqzwacmfsua0a8jubdzm-c.png)
CentOS 7, journald , . ., , 44 TPS.
![](https://habrastorage.org/webt/uu/eg/35/uueg35znjvlp0quk4ku9mov1w0c.png)
, . , , . syslog, , .
![](https://habrastorage.org/webt/r9/kc/fy/r9kcfyfi3biyx77l8jurhmwp07w.png)
![](https://habrastorage.org/webt/7b/dx/xw/7bdxxwmc7vb9qrkhhiqv08gipvq.png)
pg_stat_statements. , . . pgbench – «-f».
«-f». «@» , . . . , 10 % , 20 %. , production.
![](https://habrastorage.org/webt/eh/qc/ui/ehqcuiurfwaezn9iyinszjamqo0.png)
, production? ? . postgres-checkup. Open Source. .
. , . . . , , , . , , health_check. DBA, health_check. . . OKmeter, . Postgres. OKmeter.io – , , . .
, , , , . CPU, IO , . . , , checkpoint, io checkpoint bgwriter . .
, - , - . OKmeter. , . - .
, , , . . production. , observing server, . : , , Postgres, - , production .
Postgres-checkup. -, . , – . , . ? . . ? . , checkup, .
. . , .
![](https://habrastorage.org/webt/n8/gp/76/n8gp76ar4ttnjlvppmaczwjfrak.png)
«» – 003 Postgres-checkup
. . 003. pg_stat_statements, total_time.
total_time , , , . . . ? . , , . . .
, , . .
, , , , , , 15 . , , .
![](https://habrastorage.org/webt/yz/xx/bb/yzxxbb7ygn6d4xepwbh6ev60rby.png)
? . Postgres_checkup : total-time, calls, rows, shared_blks_read . . , . pg_stat_statements , , reset. pg_stat_database , pg_stat_statements . , 1 000 000 , , .
![](https://habrastorage.org/webt/kb/c_/cb/kbc_cbd-xeb7o4n8f8nbp7-ltgu.png)
, . , 56 . . total_time . , . . duration. duration, .
total_time per second – . , , . . . , , . . , , , .
-, . – – . , CPU 100 %, , . . .
rows . , .
. shared_buffers shared_buffers. , , . , , , , , .
– . . – . , . 101 . , .
. 8 . . , . .
– . calls. , 1 000 000. , . , , 0,01 %. . . , . – 5 % . . . 5 % – .
total_time . 14 % . – 11 % . .
, . , , , , . . - , - . . 0, . . 20 %, .
![](https://habrastorage.org/webt/ar/73/85/ar7385ntqgq1yzrnnezoerh439q.png)
. workload. , 80 % 90 %. 10-20 . pgbench. random. , , . Postgres 12 .
80-90 % total_time. «@»? calls, , , - . , . pgbench .
![](https://habrastorage.org/webt/lr/8i/le/lr8ilepploa8n4ijzio_6xvbr3e.png)
001 002.
001 – . . . , , . . , . 75 % . . 10 , . .
002 – , . . SELECT, INSERT, UPDATE, DELETE. SELECT FOR UPDATE, .
, SELECT – 82 % , – 74 % total_time. . . , .
![](https://habrastorage.org/webt/hw/f6/4m/hwf64miullvqucv32ovd5yzdaoy.png)
: « shared_buffers?». , – , throughput , . . . TPS QPS.
. 311 select.
![](https://habrastorage.org/webt/7b/fv/ew/7bfvewgemuxmbhyhlengw_sv9_k.png)
. . . , . production, 100% CPU. , , - , .
, 20 , 50 %. . . . , latency 20%- , . , .
![](https://habrastorage.org/webt/zc/es/tf/zcestf97vgkot_hm63vr5uhx2nw.png)
:
- Database Lab.
- on demand, - – . , , . . standing.
- . - , , . Nancy , , , .
- .
- Postgres. , . , .
- , , Postgres-checkup.
![](https://habrastorage.org/webt/8e/ej/yn/8eejynlwaulcliqzffx-ydrsens.png)
! .
.
, . . Nancy , ?
- . . , , .
. ? , . , , . . .
, , . . . . . , . , , . , . . .
. , . , ?
. . . . , . . - , - - . , – .
, , . . !
! ! , . GSP?
. . , . . . run on localhost. instance . , . Getlab , GSP. , Google . ??? instances, . -, 70 % . 5-10 % , , . . . , . , , . Google . – 24 . 5 . , .
! ! checkup. stat_statements?
. . – , : . : total_time calls, . , . : . , .
, - ?
. . ?
, , , . - , ?
, .
. , , . , reset stat_statements , , , stat_statements . , , , .
-.
- .
, , – queryid pg_stat_statements . queryid, , .
, .
id?
.
. . . , , 0 …
, , , , , stat_statemetns .
Pg_stat_statements . , track_utility = on, .
, .
java hibernate, , -. , 50-100 . - . – pg_stat_statements.max .
, , . - . . . . pg_stat_statements.max. , 70 %. , , . reset. . 70, , , .
是。默认值为5,000,很多就足够了。
通常是。
视频:
附注:我将自己补充说,如果Postgres包含机密数据并且无法进入测试环境,则可以使用PostgreSQL Anonymizer。该方案大致如下:
![](https://habrastorage.org/webt/o8/w3/ax/o8w3axqxfal2lflat2sfj9ld-ga.png)