Approche de réglage industriel de PostgreSQL: expériences de base de données Nikolay Samokhvalov

Je vous suggère de vous familiariser avec le décodage du rapport de Nikolay Samokhvalov "Approche industrielle de l'optimisation PostgreSQL: expériences sur les bases de données"

Shared_buffers = 25% - est-ce beaucoup ou peu? Ou bien? Comment comprendre si cette recommandation est assez dépassée, recommandation dans votre cas particulier?

Il est temps d'aborder la question de la sélection des paramètres postgresql.conf "de manière adulte". Pas avec l'aide d'autotuners aveugles ou de conseils désuets d'articles et de blogs, mais basés sur:

1. expériences de bases de données strictement calibrées effectuées automatiquement, en grandes quantités et dans des conditions aussi proches que possible de celles "de combat"
2. compréhension approfondie des fonctionnalités du SGBD et du système d'exploitation.

En utilisant la CLI de Nancy ( https://gitlab.com/postgres.ai/nancy ), nous considérerons un exemple spécifique - les notoires shared_buffers - dans différentes situations, dans différents projets et essayer de comprendre comment choisir le réglage optimal pour notre infrastructure, notre base de données et notre charge .

Il s'agira d'expériences sur des bases de données. C'est une histoire qui dure un peu plus de six mois.

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

Oui. La valeur par défaut est 5 000. Et cela suffit largement.

Généralement oui.

Vidéo:

PS J'ajouterai moi-même que si Postgres contient des données confidentielles et ne peut pas entrer dans l'environnement de test, vous pouvez utiliser PostgreSQL Anonymizer . Le schéma est approximativement le suivant: