And I propose to read the transcript of the report Alexey Lesovsky from Data Egret "Basics of PostgreSQL monitoring"
In this report, Alexey Lesovsky will talk about the key points of post-gross statistics, what they mean and why they should be present in monitoring; about which graphs should be monitored, how to add them and how to interpret them. The report will be useful to database administrators, system administrators and developers who are interested in Postgres Troubleshoot.
My name is Alexei Lesovsky, I represent Data Egret.
A few words about yourself. I started once upon a time as a system administrator.
Linux, , Linux, . . , , . . - , PostgreSQL. . - PostgreSQL . PostgreSQL DBA.
, , . , Zabbix. zabbix-extensions. . , Linux, .
PostgreSQL. , PostgreSQL-. pgCenter ( — ).
. , ? - , . , : «, , - , ». , – PostgreSQL, MySQL - . : « , - . ». . , - , PostgreSQL - . - GitHub, , , - . .
, PostgreSQL, . , , - , , - , .
, , , noSQL. PostgreSQL, , PostgreSQL. , , PostgreSQL . , , , .
, . - . .
, - . , . .
, . – , . . - , . , , , , - .
, , - , . .
– , , – . , . – . . .
PostgreSQL, , . . PostgreSQL , , Stats Collector, , .
(view). . . . psql , select , - PostgreSQL.
, , .
, . -- , .
, ? , – , , .
, , , , . .
, , , , : , . . . (workload), .
, , . , , , , , , - , .
, – . , . , . , .
. , , . , , .
- , , - - . , .
– . ? – , . . , , , . . .
, . , . – .
, , . - , , - , , drilldown-, .
. . , , . « ». , , , . - , , , . , . . , , . .
? . PostgreSQL pg_stat_statements. , , . , .
, , . . – , . , PostgreSQL . - , , . . 'select 1' , , .
, . pg_stat_database. xact_rollback. rollback, , . , . , , , .
, . . , , , , , - backend . , , - , , , .
, pg_stat_database. commit rollback .
, ? TPS QPS .
pg_stat_statements . , , , , .
, – - downtime.
– uptime. uptime PostgreSQL – . , . PostgreSQL , uptime. - , , - , OOM-killer PostgreSQL, PostgreSQL , shared . , , . . , downtime. uptime , postmaster . .
. , autovacuum PostgreSQL? PostgreSQL. , . , , . . . , , , .
? . .
pg_stat_activity, . . , . , , PostgreSQL - .
PostgreSQL , PostgreSQL . , , . , , stat idle-in-transaction. , . – , . , , , . , , . - , , 10-20-30 OLTP-, , , . 10-20-30 – , .
. , , .
, , PostgreSQL, . , .
. . , , . .
, , , . idle.
. , , , - , , , , . . . . - (Exception), . PostgreSQL. PostgreSQL . . , .
. - . , : , - , - , . , , , - . , . , . , . . .
. . . DB connection – 8 . . , , idle, . , . . , . .
, , pg_stat_activity. PostgreSQL, , , PostgreSQL, . . . , : , , , , , . , , stat. , stats-, , stat- . .
. , , . xact_start query_start. , , . now(), timestamp . , .
, . OLTP- – 1-2-3 . OLAP- , , , - .
, . , , . , .
, , «». , , «» - SSD . , - , - «» , SATA , .
. , - . , . , . , update delete. .
«» . ? PostgreSQL . . ? , PostgreSQL , . : , NULL , .
, .
, «». , - , . . , , . , - , .. 100 ( limit 100), . .
. , explain, , . . .
. , , . Prometheus? Prometheus? , PostgreSQL – postgres_exporter Prometheus. .
. unity , . . 5 . Insert data, Update data, Delete data, Fetch data Return data. unit . , PostgreSQL tuple (). , , – , , tuple – , tuple – , . . . tuples – . , , , .
? PostgreSQL . – pg_stat_user_tables. User_tables – , , . , PostgreSQL. Alltables, , . , .
insert, update delete. , , . . , tuples, , .
, , TopN-. , Top-5, Top-10. , . , 5 «» . TopN- , .
, , , , , . .
. , ? ?
. ? . . , . , host , , . Top, PostgreSQL, - . Top , .
, , , , , , PostgreSQL , . – 80-85-90 %. . , , , , , . - , .
, . . . , , , . , . , , , . .
pg_stat_statements. . , , . . , . , , - .
, . . , . , -. total_time, mean_time, blk_write_time blk_read_time.
, , , , , - .
. , . , - , . .
, .
. – , autovacuum .
. Maintenance, , - . , . , , .
, , «» shared , . , PostgreSQL .
, «» , . , , – . - , . . .
, pg_stat_bgwriter . - ( 10-15-20 , ) , , 3-4-5, . , , . , - . , . , .
autovacuum, , , , , autovacuum.
autovacuum . , , , autovacuum , , autovacuum .
. , DBA - , . - . , . PostgreSQL – . , , .
, , . , , , , , . , (3-4 ), . , . – . , , . , , .
PostgreSQL, . – .
PostgreSQL . . , . .
, pg_stat_replication. . 10 . -, . . 10- , . . 10 . 10- , . . , – . .
, , . pg_stat_replication. , pg_xlog_location_diff() . . .
10- pg_wal_lsn_diff(). , , , , «xlog», «wal». , . .
10- , . write lag, flush lag, replay lag. . . . , , , , .
. , . , , swap, . .
, . , . iops . latency . , , . latency, , - . , , . , .
, /proc, . , . .
. , , latency , .
. , - . , , , - .
, , .
:
- , , , .
- , , .
- , . .
- , , . , , , . .
- , , .
- , , .
, .
http://bit.do/stats_collector — . . , . , .
:
http://bit.do/dataegret_sql
http://bit.do/lesovsky_sql
. . select* from - . , , , , . . , . , , , , .
: , , - – ?
: -. , . , . Zabbi. TopN-. Okmeter, . PostgreSQL . pet-project, Prometheus Grafana. Prometheus Grafana.
: - AWR- … ? - ?
: , , AWR, . , . baselines PostgreSQL . , . sql.ru PostgreSQL. . , , . pgCenter , .
P.S.1 c postgres_exporter, ? . . m ?
P.S.2 pganalyze, is a proprietary SaaS offering which focuses on performance monitoring and automated tuning suggestions.