Sugiro que vocĂŞ se familiarize com a decodificação do relatĂłrio de inĂcio de 2019 por Alexey Lesovsky - “Solução de problemas do pgCenter com o Postgres”
De tempos em tempos, surgem problemas ao usar o Postgres, e quanto mais rápidas as fontes do problema são encontradas e resolvidas, mais agradecidos os usuários. O pgCenter é um conjunto de utilitários da CLI que é uma ferramenta poderosa para identificar e corrigir problemas aqui e agora. Neste relatório, mostrarei como usar efetivamente o pgCenter para encontrar e corrigir problemas, em quais direções procurar e como responder a determinados problemas, em particular, como:
- verifique se está tudo bem com o Postgres;
- encontre rapidamente clientes ruins e elimine-os;
- identificar solicitações pesadas;
- e outros truques Ăşteis com o pgCenter.
, . Data Egret. . , PostgreSQL.
, pgCenter , .
. . Linux, , . - Postgres'. Postgres . PostgreSQL DBA. , Postgres . .
. : Slack, Telegram. - . .
, , Linix. . , Linux . , , .
, Postgres. Postgres. - Postgres'. Postgres. Postgres.
Postgres . Postgres Postgres. , Postgres (views), .
. . . - .
- , , , , , . - . : « ?», « ?» « ?». , .
, , , , . - pgCenter.
. , TOP- .
- , C . .. . Golang, , . .
Go pgCenter . . , , , targz, deb- rpm- . .. - , make, GCC Golang. , , .
pgCenter , top- ( ).
. , . wait_events. , -.
PSQL. Postgres, PSQL Postgres. pgCenter , «pgcenter top» postgres - (-, ).
, - , , , , PSQL, , , .
, UNIX-, . . go' , pgCenter , UNIX.
, libpq. , , pg_stat_statements pgCenter , . .
(pgcenter top). , , . , - . , , . . , pgCenter, top-, .
– . .
– , Postgres. - , Postgres .
– (views). stat-, Postgres, .
, . -, . . , , , , . .
, . (regexp). . , , - : .
, pgCenter. – - .
, USE . . - , .
. . , Top, . : , ..
, : , , .
, , swap. swap, , swap. – .
- : « - ?». . , .
, – - Postgres. uptime. , uptime Postgres – , , . , Postgres .
. , Postgres, . , , . . . .
, , . , , . , . , . - .
, Postgres MVCC . MVCC . , . .
'd'.
, pg_stat_database , «rollbacks». «ROLLBACK», /. (constraints), . . , .
pg_stat_database (conflicts) (deadlocks). , , , - .
, pgCenter. , . -, :
top
vmstat
iostat
nicstat
pg_stat_activity
pg_stat_statements
, .
, , - .
. , , . . . CPU usage – 85 %. , - . , . , Postgres. Postgres , .
, , 38 , - . state: waiting idle_xact. Waiting 0, . . . , 20 idle . , (xact_age) – . , . – 15 . , .
(: pg_stat_statements. pg_stat_statements "x", . "shift + x" pg_stat_statements_timings. "pg_stat_statements not available on this database", pg_stat_statements postgres postgres: create extension pg_stat_statements;)
, () . , . pg_stat_statements. contrib. : , . contrib , . , -. Postgres – pg_stat_statements.
, . , . . , CPU pg_stat_statements. – 2 . SELECT COUNT (*) FROM "game_competition_events". . . , , , , . , - , , -. , .
. total_time. total_time , , /: . , «t_cpu_t». . .
, . , , «cpu_t», , , . snapshot , , . . SELECT "courses_logs".* FROM course_logs. , 5 . , , , , .
«calls», , . . . . .
, . Top pg_stat_activity pg_stat_statements. pgCenter .
– -. , , , -.
. , - – 27 %. , -.
, «background worker». , .
«wait_event». , -. . . .
-. 'B' iostat. . , 99 %. – , NVME. , latency.
latency, latency 1 . .
, . , SSD NVME- - , , latency. latency, , - .
, -. pg_stat_statements , -. «t_read_t», . . , .
. «read_t». , -, .
/ track_io_timing.
, , . pgCenter queryid. . , pg_stat_statements. . . . . pgCenter . queryid. pgCenter .
:
– summary, , pg_stat_statements. , , -.
, , summary. .
, , .
, , .
, , , top, iostat pg_stat_activity, pg_stat_statements. , .
– , -. Postgres , .
:
Checkpointer pocess.
WAL writer process.
Autovacuum workers.
Background workers.
pgCenter , , .
, : - , . , , .
, , . - .
.
. , , 22 21 . , - .
wait_event , , . . . - - , , .
, (idle in transaction). , 6 . .
, , 10- , . , , 7 . 10- .
wait_etype, wait_event , , , (Client:Client Read). , - , - . , , - , , . , , .
– . : pg_cancel_backend pg_terminate_backend. , backend. pgCenter . backend pid, .
:
Pg_stat_activity.
Pg_stat_statements.
Pg_cancel_backend ().
Pg_terminate_backend ().
, . , , .
. , . - . , ( , , ). - , , idle transaction . — .
– . , (deadlocks) .
. ALTER TABLE, , , . . , , 11- . Postgres, . ALTER .
– CREATE INDEX CONCURRENTLY, - , , . .
, .. , production Postgres , .
pg_stat_replication. , Postgres, .
pgCenter pg_stat_replication. , , .
5 . .
, , . 2 walreceiver, . . 2 .
, , , . , , - . , , – - .
, pg_stat_replication , . 1,5 GB. replay_lag – 2 . . . 2- . 2 , . . .
, , 2 pg_basebackup 1 pg_receivewal. Pg_basebackup – . pg_stat_replication. pg_receivewal – , . . . , , . - , .
pg_stat_replication . . , 5 . : pending, write, flush, replay, total_lag. . . .
Pending – , . .
Write – , , . . .
Flush – , .
Replay – . .
Total_lag – .
, , , - , . , ; , ; , , .
, . . , , .
– , . . – , . Postgres, . , - .
:
pg_stat_replication.
pg_wal_lsn_diff().
pg_current_wal_lsn().
pg_last_commited_xact().
, .
, top . – Seq Scan, update, delete, insert, .
. . .
. , , . , .
, , pg_stat_progress_vacuum 9.6. , , , , , . pg_stat_progress_vacuum – . , . , , , . , .
, . , , . . postgresql.conf, - reload. , , .
. , , . . , .
psql, . . psql , pgCenter. - , pgCenter, psql .
top- – , pgCenter. top , pgCenter. record report.
, . . report , , Top. — .
-. - , record. . .
wait_event’, , .
: SELECT . , , , 44 % , - , – - , .
: VACUUM FULL. , VACUUM FULL IO , 12 %. , , .
! . . . , , pg_stat_statements, query . - - ?
, . , , , , - , . , , - . . - . . . , , -. , , . , , .
. , rollbacks. rollbacks, rollbacks, .
, , , pg_stat_database . rollbacks .
pg_stat_statements rollbacks, .
, . . `SELECT FROM pg_stat_database JNOIN pg_stat_statements` pg_stat_statements, rollbacks , . . , 10-20 .
, ! Postgres ?
9.0-9.1, C . - , , , , . Go , 9.4. 9.4 , SELECT… where filter. . (9.3) . . , , . , , , , .
, ! , TOP. .
, , . , – . less ( ), – , .
. TOP ?
. , , , . , . , . , , .
! . , . , 85% CPU usage, , . pg_stat_statements. . , .
. CPU . – t_all_t. pg_stat_statements. , . , . t . . . «t». . . . , .
. , pg_stat_statements, ?
pg_stat_statements real time.
, pg_stat_statements , 85 CPU usage – .
, . , pg_stat_statements . , , , 10 , . 10 – , , , atop. atop ?
, . pg_stat_statements, CPU, , , pg_stat_statements.
. , Grafana, . . . pgCenter – , , , , .
. pgCenter atop , Grafana?
pgCenter report pgCenter record, . . . , , . . ., , pgCenter report , , pg_stat_database sar . . atop, .
, ! , Postgres, , , . , . ? ?
10 . «-f», . , , , 10 . , pg_stat_activity , . . , . , , 50 . , , 1-2 %. . . , , - 0,5-2 % .
. , - .
, . , , 0,04 % . , , . - .
.
, .
, ! . wait_event Running – ?
.
CPU.
, . . . , wait_events PID , , backend - , , - . Running, , . . .
CPU?
, CPU, . . - . .
! ! - , , Ubuntu?
C, . , , . PDGD Ubuntu. Launchpad , - . . , . Go -, dev-. . , travis-ci build, build . . . Realeses, . wget, , tar’ , .
Goreleaser, . .
, GO . , . , SOLID. , , Goreleaser , , , . C’ , . , Realeses. !
Updated. Goreleaser , !
, queryid. queryid. , . ?
, . , - . , . - . . . , . . . . , , .
, , queryid – .
Sim, inicialmente a largura da coluna aumentou e foi irritante. Corrigi-o no ramo dev, mas isso ainda não está no ramo mestre. Em meados de fevereiro, desejo lançar o Event Profiler. E apenas uma largura fixa da coluna será.
Ă“timo.
Sim, você pode ajustar a largura através das setas.
Obrigado Alexey!
Muito obrigado!
VĂdeo: