Para mantener una base de datos en un estado saludable, es necesario buscar periódicamente "bajo el capó", "investigar" los síntomas tempranos; en otras palabras, hacer un estudio preventivo, también es una auditoría técnica de la base de datos, también es un chequeo de salud.
Hoy hablaremos de la salud de las bases de datos.
Brevemente sobre mi:
- Postgres . - .
- , , , , , , , production.
- , .
, , Cloud Vendors . - , Kubernetes . . provisioning, , , failover, . – DBA, . .
. .
.
Postgres ? , . , - , Postgres? . .
Open source , , . , . , Postgres , plain English. , .
. Postgres.
HA, downtime , autofailover, .
, .
pet, . . , , cattle . .
, , - , , backend engineers. , , , . - . .
. - , – . , , . . .
. , . , , , - -.
– . . . , . , , . .
, ?
, , , . .
, 100 Postgres. , . , . , .
, , . , . . , . .
, . cloud , instance, . self-management cloud , , . , , . failover , , .
, , - .
, - , . . , , . , , , , - .
- performance. , workload , .
- . - - , , , , fsync . , hard reset . .
, , .
. open source , . 1.1, 3-4 1.0, . . production , . open source, GitLab . , - .
.
. , , CPU . , .
. , , . . Datadog, . Grafana - pg_stat_statements. Okmeter, . .
. - . , , bloat. 10-20 . . 5 – . . , .
Postgres-checkup – , , .
, - . . - , . . Postgres-checkup .
, . . . , - , , . , .
. – , , - .
, , , - - . , , , . . , – . , , , , , . .
– . , Postgres-checkup. . Postgres-checkup . .
Postgres-checkup' - , , , 30 - , .
. - toolset. , . , .
https://github.com/NikolayS/postgres_dba
https://www.youtube.com/watch?v=V-cwPLtDtSY
Postgres_dba, GitHub. , PSQL . : «, , ». . - .
. . , , PSQL-, , . . . .
pg-utils, pgsql-bloat-estimation, pgx_scripts, pgcluu, check_postgres.pl, pghero, sqlcheck, heroku-pg-extras
. . , pghero, Ruby. , .
, check_postgres.pl, Perl . .
, :
- .
- .
- multi-node analysis.
Postgres-checkup.
?
https://gitlab.com/postgres-ai/postgres-checkup
GitLab. Postgres_dba, .
, , . : , . , , .
30 , . . , - - . , bloat-. statement_timeout . . , , . , , . , .
, application-name checkup. , , .
, , multi-node analysis. .
, , , , 4 . – 10 . 10 – , 2 . , , . .
?
, . ssh-.
. , DBA . , DBA, . remote-ssh PSQL . .
– . RDS Postgres, , , . .
RDS , , instances, . .
, , JSON-. . Postgres. , . . . . JSON, - . , machine friendly.
markdown report. Markdown reports , , , GitHub issues GitLab issues. , . , , report GitHub issues, : «, - , - », . markdown , - .
markdown PDF html. html, JSON , . . , . machine friendly human friendly. , , , .
. . docker-, , GitLab. . .
. . , . Gitlab.com. checkups, reports CI/CD . Kubernetes runner, . , . , .
.
. 50 , . issues , . . , .
. . , . , . . , - autofailover . , . . . .
. , bloat, , , . .
.
– . runner GitLab’ . , , . 1 VCP . , . .
Conclusions, recommendations , , .
. , 3 :
- 1 – , . . , . , , . , .
- 2 – conclusions, . . . plain English Russian. : « , - . , ».
- 3 – action item, . . , , , , , .
. , .
conclusions, recommendations , . 5 , -, . conclusions, recommendations . . checkup@postgres.ai.
. , .
, , – , – , . . .
? switchover failover, . , .
https://why-upgrade.depesz.com/
, , GNU , , . , , , community . , , , , , , .
, , . : why-upgrade.depesz.com. , . – - .
, -, . . – .
-, , , , , . . 9.5 9.6, , failover , . 9.6 – .
, . , fsync. Linux fsync , , fsync , , . Postgres , , .
. , : «, , , , corruption , , ? , , corruption?». , – , – , .
. , . « ». , , . current database. . , .
, , . . ALTER EXTENSTION .
. . , , , , 100. . , , , .
, , . . -, log_distination, . . csv-, .
, . . , . , failover, , . .
Work_mem . . HA, . , work_mem , , . – . .
, – ALTER SYSTEM. ALTER SYSTEM, postgresql.auto.conf. , - ALTER SYSTEM. , , . , , , , , , Ansible, Puppet . .
, 3 . , Git, , .
– Postgres- . PGDATA. PGDATA, WAL directory, – , . .
, mount point . , . , rotation disc. , .
. SSD. .
stats_temp_directory. . .
, . . . , , , .
. HighLoad Backend Conf . YouTube. , , Postgres -. bloat, . . , .
, . , . . , , . . observations , autovacuum , , , , . Postgres 12- .
, 12- - 10 .
12- , , - . , bloat .
, , transaction id wraparound.
mailchimp, . . , transaction id wraparound. .
, 50 % . capacity used . . 10 %, Postgres, 10 % autovacuum .
Heap bloat – table bloat. human friendly , . . , . , . , 50 %. , . , bloat.
, index bloat – , , , index scan index only scan. .
, index bloat – . 12- , 12- . , .
. 40 %, . 50 % — . 90 % — , 10 . 90 % — bloat, 10 % — . 10 . , .
90 % 99 %, 9 % , 10 100 . . .
. bloat , .
: , estimated. , . .
, , , bloat, .
int4, int8, int2, int8. , internals, , panting aliment. int4 int8 4 , . . - bloat . .
. 1 000 000 . bloat , 0. bloat , 23 % bloat.
, 31 %. , .
. checkup’ , database lab , . . – , vacuum full. . . . , bloat.
, , estimates . .
Index analysis – . , . 20-30 % . , , .
– 2: 1 – , 2 – .
– 2 . , .
, , . Postgres-checkup , , . .
. , , . , .
, , , , , , . , .
, , . . , . . Postgres, . Reference table, . . . , , Seq Scan , , .
, , , - . - , .
. . . , . . .
. Pg_stat_statements . , , . pg_stat_statements . , – .
. Pg_stat_statements. ? Postgres . , buffers buffer pool. . , . read, . . buffer pool , . , Postgres , , , . , .
, pg_stat_kcache, pg_stat_statements. , . , overhead – .
- , , .
. . . , - . , - SELECT, INSERT.
Pg_stat_statements , . . SELECT * FROM TABLE WHERE id = 10, id =$1 , .
. , - .
– total time. .
, checkup , , . .
, 10 15 , . . , . .
, total time , . , . . 50 % , .
? 4 . . duration, . , 10 , 15 , .
, - . , . Total time 1,848 s/sec. , , , . . . , , . .
– , . Calls. 1.00/call, , , . . , , , 10ms/call . average latency.
, pg_stat_statements , average, , – . .
, – 10 . total time, calls – 171,21/sec ( ). . . , . , 4,03 blk/call ( call), .. I/O , . . . . . , , CPU bound.
– workload, . . Total time – 19.95 % , . . 1/5. – 19,06 %. .
30 %, , .
. , , , 7,63 % .
. , . . , . . , Okmeter , -, .
? : . , workload 9,264s/sec ( ), 12 . . , load average . .
. , , first word analysis. SELECT , INSERT, UPDATE. . , , .
– int4. (Ruby, Java) int8 . int4 2,1 .
, , primary key int4, . , . . . .
:
- ,
- .
Questions
! ! . , , , , Postgres , . . . Postgres : « slave, ». , ?
. . -, , . , - , . , .
, rolling update – , . - . . . , . , . . . . checkups , .
.
. , . , Ansible, Puppet Kubernetes . , , . . , .
! , , , ?
. observations, recommendations. , , . . – , . . USS enterprise, , .
5 . , , . .
observations . , – , , , . , .
, - . , DBA. Postgres , , ?
. , , - . . observations human friendly. , , 5 ( ), , . – . , , , . . .
, ?
, USA market enterprise. , open source. , - . .