I suggest that you familiarize yourself with the decoding of the report of the beginning of 2019 by Alexei Lesovsky - “Troubleshooting Postgres with pgCenter”
From time to time, problems arise during the operation of Postgres, and the faster the sources of the problem are found and resolved, the more grateful the users are. pgCenter is a set of CLI utilities that is a powerful tool for identifying and fixing problems here and now. In this report, I will tell you how to effectively use pgCenter to find and fix problems, in what areas to search and how to respond to certain problems, in particular, how:
- check if everything is okay with Postgres;
- quickly find bad customers and eliminate them;
- identify heavy requests;
- and other useful tricks with 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 – .
Yes, initially the column width jumped and it was annoying. I fixed it in the dev-branch, but this is not yet in the Master-branch. In mid-February, I want to release Event Profiler. And just a fixed column width will be.
Great.
Yes, you can adjust the width through the arrows.
Thank you Alexey!
Thank you very much!
Video: