Fehlerbehebung bei Postgres mit pgCenter. Alexey Lesovsky

Ich schlage vor, dass Sie sich mit der Entschlüsselung des Berichts von Anfang 2019 durch Alexei Lesovsky - „Fehlerbehebung bei Postgres mit pgCenter“ vertraut machen.

Von Zeit zu Zeit treten Probleme während des Betriebs von Postgres auf. Je schneller die Ursachen des Problems gefunden und behoben werden, desto dankbarer sind die Benutzer. pgCenter ist eine Reihe von CLI-Dienstprogrammen, mit denen Sie hier und jetzt Probleme identifizieren und beheben können. In diesem Bericht werde ich Ihnen erklären, wie Sie pgCenter effektiv verwenden können, um Probleme zu finden und zu beheben, in welche Richtungen gesucht werden muss und wie auf bestimmte Probleme reagiert werden kann, insbesondere:

• Überprüfen Sie, ob mit Postgres alles in Ordnung ist.
• schnell schlechte Kunden finden und beseitigen;
• schwere Anfragen identifizieren;
• und andere nützliche Tricks mit 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 – .

Ja, anfangs sprang die Spaltenbreite und es war nervig. Ich habe es im Dev-Zweig behoben, aber dies ist noch nicht im Master-Zweig. Mitte Februar möchte ich Event Profiler veröffentlichen. Und nur eine feste Spaltenbreite wird sein.

Großartig.

Ja, Sie können die Breite über die Pfeile anpassen.

Vielen Dank, Alexey!

Vielen Dank!

Video: