Abordagem de ajuste industrial do PostgreSQL: experiências com bancos de dados Nikolay Samokhvalov

Sugiro que você se familiarize com a decodificação do relatório de Nikolay Samokhvalov "Abordagem industrial do ajuste do PostgreSQL: experimentos com bancos de dados"

Shared_buffers = 25% - é muito ou pouco? Ou apenas certo? Como entender se esta recomendação está bastante desatualizada, recomendação no seu caso particular?

É hora de abordar a questão de selecionar os parâmetros do postgresql.conf "de maneira adulta". Não com a ajuda de "autotuners" cegos ou com dicas desatualizadas de artigos e blogs, mas com base em:

1. experimentos estritamente calibrados de bancos de dados realizados automaticamente, em grandes quantidades e em condições o mais próximo possível dos "combatentes"
2. profundo entendimento dos recursos do DBMS e do SO.

Usando a CLI Nancy ( https://gitlab.com/postgres.ai/nancy ), consideraremos um exemplo específico - os notórios compartilhados buffers - em diferentes situações, em diferentes projetos e tentar descobrir como escolher a configuração ideal para nossa infraestrutura, banco de dados e carga .

Será sobre experimentos em bancos de dados. Esta é uma história que dura um pouco mais de seis meses.

. Postgres 14 . -. Postgres .

RuPostgres Meetup, 2- . 2 000 . RuPostgres.org.

, Highload, , Postgres .

Postgres- 11 .

, Postgres, , 2010- . , DBA, - . , - , .

, . . . . . , , . . , DBA, .

:

• « » – 8 GB 25 % shared_buffers . shared_buffers .
• «».

?

• , . , , Open Source, . . Open Source. , Open Source. , , . .
• , : .

?

-, DBA instances, . ., — .

. , - , - . , , .

. 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 ?

, - . ? , - - instance, . DBA, staging. . . , production. DBA production, . , , , , . . .

• .
• .
• SQL- ( ).
• ( ).

. Postgres. . Postgres, . , DBA , DBA . . . 10 , .

– . , , .

, . , 1 000 default_statistict_target – . , production.

, , , .

. .

• – . . - , , production. . instance Google, . . . . Postgres.
• – . . . .
• – . .
• – , , . . . , , . .

. pg_stat_statements. – , . – .

default_statistics_target = 100, =1 000. , . 8 % .

, pgBadger pg_stat_statements. . , - 88 %. . , , . , . .

, «ALTER TABLE … ALTER COLUMN» 100 . , . . , , .

. CI- . .

: , , , . - - . , .

.

-, . production, . , , . .

, , , . . , . Postgres. . production . , , auto_explain.

, , . , , .

Nancy CLI – « »

. . . , . Open Source Nancy CLI. , .

Nancy — Open Source, Gitlab. , . . help .

, . . , . – delete 40 000 000 IO, , , . . . . , - , . production. .

? , . . , MacBook . , . . - instance , , .

EC2 Instance, . . , 500 i3 instance, i3-16-xlarge. 500 64 . 15 . . . , , – 70 %, . . .

Postgres . - 12- .

. :

• Dump/sql-.
• – PGDATA . -. , . , Google . production. .
• , , Postgres - . pgbench. pgbench. «db-pgbench». , scale. , .

:

• SQL . .
• . . . . , . pgreplay , Nancy.
• . , . , . pgbench .

• SQL - , . . - , , ANALAZE . , . , SQL.
• . , , , 100 . . , . , , . . pgdata . Postgres , . .

• , , 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 .

?

, . , Open Source pgreplay . . , , .

duration timestamp. . – ?

https://gist.github.com/NikolayS/08d9b7b4845371d03e195a8d8df43408

, . , . pg_stat_statements, ( ) , .

. , , , . , . , .

, 802 . , bytes_per sec – 300 kB/s . , , .

! , . «syslog».

syslog, . pgbench, , .

– . 161 000 TPS. syslog – Ubuntu 16.04 37 000 TPS. , . . . , , .

CentOS 7, journald , . ., , 44 TPS.

, . , , . syslog, , .

• IOPS .
• .
• , .

pg_stat_statements. , . . pgbench – «-f».

«-f». «@» , . . . , 10 % , 20 %. , production.

, 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, .

. . , .

«» – 003 Postgres-checkup

. . 003. pg_stat_statements, total_time.

total_time , , , . . . ? . , , . . .

, , . .

, , , , , , 15 . , , .

? . Postgres_checkup : total-time, calls, rows, shared_blks_read . . , . pg_stat_statements , , reset. pg_stat_database , pg_stat_statements . , 1 000 000 , , .

, . , 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 %, .

. workload. , 80 % 90 %. 10-20 . pgbench. random. , , . Postgres 12 .

80-90 % total_time. «@»? calls, , , - . , . pgbench .

001 002.

001 – . . . , , . . , . 75 % . . 10 , . .

002 – , . . SELECT, INSERT, UPDATE, DELETE. SELECT FOR UPDATE, .

, SELECT – 82 % , – 74 % total_time. . . , .

: « shared_buffers?». , – , throughput , . . . TPS QPS.

. 311 select.

. . . , . production, 100% CPU. , , - , .

, 20 , 50 %. . . . , latency 20%- , . , .

:

• Database Lab.
• on demand, - – . , , . . standing.
• . - , , . Nancy , , , .
• .
• Postgres. , . , .
• , , Postgres-checkup.

! .

.

, . . 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, , , .

Sim. O padrão é 5.000. E muito disso é suficiente.

Geralmente sim.

Vídeo:

PS acrescentarei por conta própria que, se o Postgres contiver dados confidenciais e não puder entrar no ambiente de teste, você poderá usar o PostgreSQL Anonymizer . O esquema é aproximadamente o seguinte: