PostgreSQL Industrial Tuning-Ansatz: Datenbankexperimente. Nikolay Samokhvalov

Ich schlage vor, dass Sie sich mit der Dekodierung des Berichts von Nikolay Samokhvalov "Industrieller Ansatz zur PostgreSQL-Optimierung: Experimente mit Datenbanken" vertraut machen.

Shared_buffers = 25% - ist es viel oder wenig? Oder genau richtig? Wie kann man verstehen, ob diese Empfehlung ziemlich veraltet ist, Empfehlung in Ihrem speziellen Fall?

Es ist Zeit, sich dem Problem der Auswahl von postgresql.conf-Parametern "auf erwachsene Weise" zu nähern. Nicht mit Hilfe von blinden "Autotunern" oder veralteten Tipps aus Artikeln und Blogs, sondern basierend auf:

1. Streng kalibrierte Datenbankexperimente werden automatisch durchgeführt, in großen Mengen und unter Bedingungen, die den "Kampf" -Experimenten so nahe wie möglich kommen
2. tiefes Verständnis der Funktionen von DBMS und Betriebssystem.

Anhand der Nancy-CLI ( https://gitlab.com/postgres.ai/nancy ) betrachten wir ein bestimmtes Beispiel - die berüchtigten shared_buffers - in verschiedenen Situationen, in verschiedenen Projekten und versuchen herauszufinden, wie die optimale Einstellung für unsere Infrastruktur, Datenbank und Last ausgewählt werden kann .

Es geht um Experimente mit Datenbanken. Dies ist eine Geschichte, die etwas mehr als sechs Monate dauert.

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

Ja. Der Standardwert ist 5.000. Und vieles davon reicht aus.

Normalerweise ja.

Video:

PS Ich füge selbst hinzu, dass Sie PostgreSQL Anonymizer verwenden können, wenn Postgres vertrauliche Daten enthält und nicht in die Testumgebung gelangen kann . Das Schema ist ungefähr wie folgt: