Enfoque de ajuste industrial de PostgreSQL: experimentos de bases de datos Nikolay Samokhvalov

Le sugiero que se familiarice con la decodificación del informe de Nikolay Samokhvalov "Enfoque industrial para el ajuste PostgreSQL: experimentos en bases de datos"

Shared_buffers = 25%: ¿es mucho o poco? O simplemente bien? ¿Cómo entender si esta recomendación está bastante desactualizada en su caso particular?

Es hora de abordar el problema de seleccionar los parámetros postgresql.conf "de forma adulta". No con la ayuda de "autoajustes" ciegos o consejos obsoletos de artículos y blogs, sino basados ​​en:

1. experimentos de bases de datos estrictamente calibrados realizados automáticamente, en grandes cantidades y en condiciones lo más cercanas posible a los de "combate"
2. comprensión profunda de las características del DBMS y el sistema operativo.

Usando la CLI de Nancy ( https://gitlab.com/postgres.ai/nancy ), consideraremos un ejemplo específico - los notorios shared_buffers - en diferentes situaciones, en diferentes proyectos e intentaremos descubrir cómo elegir la configuración óptima para nuestra infraestructura, base de datos y carga .

Se tratará de experimentos en bases de datos. Esta es una historia que dura un poco más 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, , , .

Si. El valor predeterminado es 5,000 y mucho de esto es suficiente.

Por lo general si.

Vídeo:

PD: Agregaré por mi cuenta que si Postgres contiene datos confidenciales y no puede ingresar al entorno de prueba, entonces puede usar el Anonimizador PostgreSQL . El esquema es aproximadamente como sigue: