PG sys function

2016-05-04 15:54 DataBases 阅读(590) 评论(0) 编辑收藏举报

PG(>=9.4) look archive status;

PG check Master-Slave lag time:

master monitor
postgres=# select
postgres-# pid, client_addr,
postgres-# pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(),sent_location)) as
postgres-# pending_xlog,
postgres-# pg_size_pretty(pg_xlog_location_diff(sent_location,write_location)) as write,
postgres-# pg_size_pretty(pg_xlog_location_diff(write_location,flush_location)) as flush,
postgres-# pg_size_pretty(pg_xlog_location_diff(flush_location,replay_location)) as replay,
postgres-# pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(),replay_location)) as
postgres-# total_lag
postgres-# from pg_stat_replication;
pid | client_addr | pending_xlog | write | flush | replay | total_lag
-------+-----------------+--------------+---------+---------+---------+-----------
20608 | 192.168.230.129 | 0 bytes | 0 bytes | 0 bytes | 0 bytes | 0 bytes
(1 row)

standby
pg_current_xlog_location() - pg_last_xlog_receive_location();

PG processer:

[root@localhost data]# ps auxf|grep postgres
root 4575 0.0 0.1 5028 1284 pts/2 S 18:42 0:00 | \_ su - postgres
postgres 4579 0.0 0.1 4764 1516 pts/2 S 18:42 0:00 | \_ -bash
postgres 20619 0.0 0.1 5252 1612 pts/2 S+ 19:56 0:00 | \_ ./psql -h localhost -p 5432 -U postgres
root 21345 0.0 0.0 4028 656 pts/3 S+ 20:30 0:00 \_ grep postgres
postgres 4737 0.0 1.7 164472 18564 pts/2 S 18:48 0:00 /usr/local/pg952/bin/postgres -D ../data
postgres 4738 0.0 0.0 9984 796 ? Ss 18:48 0:00 \_ postgres: logger process
postgres 4740 0.0 0.2 164604 2348 ? Ss 18:48 0:00 \_ postgres: checkpointer process
postgres 4741 0.0 0.1 164604 2056 ? Ss 18:48 0:00 \_ postgres: writer process
postgres 4742 0.0 0.4 164472 5140 ? Ss 18:48 0:00 \_ postgres: wal writer process
postgres 4743 0.0 0.1 165004 1752 ? Ss 18:48 0:00 \_ postgres: autovacuum launcher process
postgres 4744 0.0 0.0 10024 732 ? Ss 18:48 0:00 \_ postgres: archiver process
postgres 4745 0.0 0.0 10024 916 ? Ss 18:48 0:00 \_ postgres: stats collector process
postgres 20608 0.0 0.2 165284 2288 ? Ss 19:55 0:00 \_ postgres: wal sender process postgres 192.168.230.129(41845) streaming 0/9001100
postgres 20620 0.0 0.5 165820 5360 ? Ss 19:56 0:00 \_ postgres: postgres postgres 127.0.0.1(34950) idle

[postgres@localhost ~]$ ps hf -u postgres -o cmd
-bash
\_ ps hf -u postgres -o cmd
/usr/local/pg952/bin/postgres -D /usr/local/pg952/data
\_ postgres: logger process
\_ postgres: checkpointer process
\_ postgres: writer process
\_ postgres: wal writer process
\_ postgres: autovacuum launcher process
\_ postgres: archiver process last was 000000010000000000000010
\_ postgres: stats collector process

pg_stat_database

Cache hit ratio

blks_read | 1266
blks_hit | 91553

postgres=# select
postgres-# sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
postgres-# from pg_stat_database;
-[ RECORD 1 ]------------------
hit_ratio | 98.6382557626735794

More is better, and not less than 90%

Anomalies

xact_commit | 1767
xact_rollback | 13

conflicts | 0

temp_files | 0
temp_bytes | 0
deadlocks | 0

postgres=# select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_files, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;
ERROR: division by zero

Conflicts: pg_stat_database_conflicts, server logs.
Deadlocks: server logs + log_lock_waits.
Temp files: server logs + log_temp_files, pg_stat_statements.

pg_stat_bgwriter

Checkpoints

checkpoints_req > checkpoints_timed = bad
postgresql.conf:
● min_wal_size, max_wal_size (checkpoint_segments, >= 9.4)
● checkpoint_timeout
● checkpoint_completion_target

buffers_clean | 0
maxwritten_clean | 0
buffers_backend_fsync | 0

Clean dirty pages for backends.
Helps checkpointer.
postgresql.conf:
● bgwriter_delay
● bgwriter_lru_maxpages
● bgwriter_lru_multiplier

pg_stat_replication

0/110004D0 — location in transaction log (WAL)

All values are equal = ideal

Replication lag

Lag causes:
Networking
Storage
CPU

How many bytes written in WAL
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');

postgres=# select
postgres-# pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
-[ RECORD 1 ]---------+----------
pg_xlog_location_diff | 285214112

Replication lag in bytes
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;

postgres=# select
postgres-# client_addr,
postgres-# pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
postgres-# from pg_stat_replication;
-[ RECORD 1 ]---------+----------------
client_addr | 192.168.230.129
pg_xlog_location_diff | 0

Replication lag in seconds
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());

postgres=# select
postgres-# extract(epoch from now() - pg_last_xact_replay_timestamp());
-[ RECORD 1 ]
date_part |

Replication lag

postgres=# select
client_addr as client,
pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(),sent_location)) as pending,
pg_size_pretty(pg_xlog_location_diff(sent_location,write_location)) as write,
pg_size_pretty(pg_xlog_location_diff(write_location,flush_location)) as flush,
pg_size_pretty(pg_xlog_location_diff(flush_location,replay_location)) as replay,
pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(),replay_location)) as total
from pg_stat_replication;
-[ RECORD 1 ]------------
client | 192.168.230.129
pending | 0 bytes
write | 0 bytes
flush | 0 bytes
replay | 0 bytes
total | 0 bytes

pg_stat_all_tables

Sequential scans

postgres=# select
postgres-# relname,
postgres-# pg_size_pretty(pg_relation_size(relname::regclass)) as size,
postgres-# seq_scan, seq_tup_read,
postgres-# seq_scan / seq_tup_read as seq_tup_avg
postgres-# from pg_stat_user_tables
postgres-# where seq_tup_read > 0 order by 3,4 desc limit 5;
-[ RECORD 1 ]+-----------
relname | test
size | 8192 bytes
seq_scan | 1
seq_tup_read | 5
seq_tup_avg | 0

Tables size

postgres=# select
postgres-# relname,
postgres-# pg_size_pretty(pg_total_relation_size(relname::regclass)) as
postgres-# full_size,
postgres-# pg_size_pretty(pg_relation_size(relname::regclass)) as
postgres-# table_size,
postgres-# pg_size_pretty(pg_total_relation_size(relname::regclass) -
postgres(# pg_relation_size(relname::regclass)) as index_size
postgres-# from pg_stat_user_tables
postgres-# order by pg_total_relation_size(relname::regclass) desc limit 10;
-[ RECORD 1 ]----------
relname | test
full_size | 8192 bytes
table_size | 8192 bytes
index_size | 0 bytes

Write activity

UPDATE = DELETE + INSERT
UPDATE = TABLE UPDATE + INDEX UPDATE

What is Heap-Only Tuples?
HOT does not cause index update.
HOT is only for non-indexed columns.
Big n_tup_hot_upd = good.
How to increase n_tup_hot_upd?

Fillfactor determines page packing ratio (between 10 and 100).
Tables (and indexes) size overhead.
ALTER TABLE table_name SET (fillfactor = 70);

postgres=# select
postgres-# s.relname,
postgres-# pg_size_pretty(pg_relation_size(relid)),
postgres-# coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
postgres-# coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
postgres-# (coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
postgres(# then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
postgres-# (select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\\d+)') as
postgres(# r(v) limit 1) AS fillfactor
postgres-# from pg_stat_all_tables s
postgres-# join pg_class c ON c.oid=relid
postgres-# order by total_writes desc limit 50;
-[ RECORD 1 ]--+------------------------
relname | pg_attribute
pg_size_pretty | 344 kB
total_writes | 7
hot_rate | 0.00
fillfactor |
-[ RECORD 2 ]--+------------------------
relname | test
pg_size_pretty | 8192 bytes
total_writes | 5
hot_rate | 0.00
fillfactor |
-[ RECORD 3 ]--+------------------------
relname | pg_depend
pg_size_pretty | 408 kB
total_writes | 3
hot_rate | 0.00
fillfactor |
-[ RECORD 4 ]--+------------------------
relname | pg_type
pg_size_pretty | 64 kB
total_writes | 2
hot_rate | 0.00
fillfactor |
-[ RECORD 5 ]--+------------------------
relname | pg_class
pg_size_pretty | 104 kB
total_writes | 1
hot_rate | 0.00
fillfactor |
-[ RECORD 6 ]--+------------------------
relname | pg_toast_1255
pg_size_pretty | 0 bytes
total_writes | 0
hot_rate | 0.00
fillfactor |
-[ RECORD 7 ]--+------------------------
relname | pg_toast_2618
pg_size_pretty | 368 kB
total_writes | 0
hot_rate | 0.00
fillfactor |

Autovacuum queue

postgres=# select c.relname,
postgres-# current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
postgres-# current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
postgres-# (current_setting('autovacuum_vacuum_threshold')::int +
postgres(# (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples)) as
postgres-# av_thresh,
postgres-# s.n_dead_tup
postgres-# from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
postgres-# where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
postgres(# + (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));
relname | av_base_thresh | av_scale_factor | av_thresh | n_dead_tup
---------+----------------+-----------------+-----------+------------
(0 rows)

Experience

https://github.com/eshkinkot/avito/blob/master/munin/vacuum_queue

pg_stat_all_indexes

Unused indexes

Unused indexes are bad.
Uses storage.
Slow down UPDATE, DELETE, INSERT operations.
Extra work for VACUUM.

pg_stat_activity

Summary activity

Used connections ratio

postgres=# select
count(*)*100/(select current_setting('max_connections')::int) as connections_ratio
from pg_stat_activity;
-[ RECORD 1 ]-----+--
connections_ratio | 0

Long queries and xacts

postgres=# select backend_start,xact_start,query_start,state_change from pg_stat_activity;
-[ RECORD 1 ]-+------------------------------
backend_start | 2016-05-04 19:18:04.728819-07
xact_start | 2016-05-04 20:35:04.571186-07
query_start | 2016-05-04 20:35:04.571186-07
state_change | 2016-05-04 20:35:04.571192-07
-[ RECORD 2 ]-+------------------------------
backend_start | 2016-05-04 20:28:45.068657-07
xact_start |
query_start | 2016-05-04 20:28:51.161971-07
state_change | 2016-05-04 20:28:51.187181-07

postgres=# select
postgres-# client_addr, usename, datname,
postgres-# clock_timestamp() - xact_start as xact_age,
postgres-# clock_timestamp() - query_start as query_age,
postgres-# query
postgres-# from pg_stat_activity order by xact_start, query_start;
-[ RECORD 1 ]--------------------------------------------------------
client_addr |
usename | postgres
datname | postgres
xact_age | 00:00:00.001886
query_age | 00:00:00.045951
query | select +
| client_addr, usename, datname, +
| clock_timestamp() - xact_start as xact_age, +
| clock_timestamp() - query_start as query_age, +
| query +
| from pg_stat_activity order by xact_start, query_start;
-[ RECORD 2 ]--------------------------------------------------------
client_addr |
usename | postgres
datname | postgres
xact_age |
query_age | 00:06:54.326014
query | select * from test ;

clock_timestamp() for calculating query or transaction age.
Long queries: remember, terminate, optimize.

Bad xacts

idle in transaction, idle in transaction (aborted) = bad
Warning value: > 5
clock_timestamp() for calculate xact age.
Bad xacts: remember, terminate, optimize app.

Waiting clients

waiting = true = bad.
clock_timestamp() for calculating query or xact age.
Enable log_lock_waits GUC, examine server logs.
Use pg_locks for searching blocking query or xact.
Waiting queries: remember, terminate, optimize app.

postgres=# select * from pg_stat_activity where waiting;
(0 rows)

Blocked queries

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/c4_06_show
_locked_queries.sql

http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/

pg_stat_statements

postgres=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query | create extension pg_stat_statements ;
calls | 1
total_time | 1081.141
rows | 0
hit_percent | 86.4864864864864865
-[ RECORD 2 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query | /* contrib/pg_stat_statements/pg_stat_statements--1.3.sql */ +
| +
| -- complain if script is sourced in psql, rather than via CREATE EXTENSION +
| +
| +
| -- Register functions. +
| CREATE FUNCTION pg_stat_statements_reset() +
| RETURNS void +
| AS '$libdir/pg_stat_statements' +
| LANGUAGE C; +
| +
| CREATE FUNCTION pg_stat_statements(IN showtext boolean, +
| OUT userid oid, +
| OUT dbid oid, +
| OUT queryid bigint, +
| OUT query text, +
| OUT calls int8, +
| OUT total_time float8, +
| OUT min_time float8, +
| OUT max_time float8, +
| OUT mean_time float8, +
| OUT stddev_time float8, +
| OUT rows int8, +
| OUT shared_blks_hit int8, +
| OUT shared_blks_read int8, +
| OUT shared_blks_dirtied int8, +
| OUT shared_blks_written int8, +
| OUT local_blks_hit int8, +
| OUT local_blks_read int8, +
| OUT local_blks_dirtied int8, +
| OUT local_blks_written int8, +
| OUT temp_blks_read int8, +
| OUT temp_blks_written int8, +
| OUT blk_read_time float8, +
| OUT blk_write_time float8 +
| ) +
| RETURNS SETOF record +
| AS '$libdir/pg_stat_statements', 'pg_stat_statements_1_3' +
| LANGUAGE C STRICT VOLATILE; +
| +
| -- Register a view on the function for ease of use. +
| CREATE VIEW pg_stat_statements AS +
| SELECT * FROM pg_stat_statements(true); +
| +
| GRANT SELECT ON pg_stat_statements TO PUBLIC; +
| +
| -- Don't want this to be available to non-superusers. +
| REVOKE ALL ON FUNCTION pg_stat_statements_reset() FROM PUBLIC; +
|
calls | 5
total_time | 880.587
rows | 0
hit_percent | 89.3992932862190813
-[ RECORD 3 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query | SELECT pg_catalog.quote_ident(name) FROM pg_catalog.pg_available_extensions WHERE substring(pg_catalog.quote_ident(name),?,?)=? AND installed_version IS NULL +
| LIMIT ?
calls | 1
total_time | 196.745
rows | 1
hit_percent | 0.00000000000000000000
-[ RECORD 4 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query | select * from pg_stat_statements +
| where blk_read_time <> ? order by blk_read_time desc;
calls | 2
total_time | 3.211
rows | 0
hit_percent | 100.0000000000000000
-[ RECORD 5 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query | SELECT query, calls, total_time, rows, ? * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, ?) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT ?;
calls | 1
total_time | 0.342
rows | 4
hit_percent |

Statements average time in ms

postgres=# select (sum(total_time) / sum(calls))::numeric(6,3)
postgres-# from pg_stat_statements;
-[ RECORD 1 ]----
numeric | 196.562

The most writing (to shared_buffers) queries

postgres=# select query, shared_blks_dirtied
postgres-# from pg_stat_statements
postgres-# where shared_blks_dirtied > 0 order by 2 desc;
-[ RECORD 1 ]-------+---------------------------------------------------------------------------
query | create extension pg_stat_statements ;
shared_blks_dirtied | 41
-[ RECORD 2 ]-------+---------------------------------------------------------------------------
query | /* contrib/pg_stat_statements/pg_stat_statements--1.3.sql */ +
| +
| -- complain if script is sourced in psql, rather than via CREATE EXTENSION+
| +
| +
| -- Register functions. +
| CREATE FUNCTION pg_stat_statements_reset() +
| RETURNS void +
| AS '$libdir/pg_stat_statements' +
| LANGUAGE C; +
| +
| CREATE FUNCTION pg_stat_statements(IN showtext boolean, +
| OUT userid oid, +
| OUT dbid oid, +
| OUT queryid bigint, +
| OUT query text, +
| OUT calls int8, +
| OUT total_time float8, +
| OUT min_time float8, +
| OUT max_time float8, +
| OUT mean_time float8, +
| OUT stddev_time float8, +
| OUT rows int8, +
| OUT shared_blks_hit int8, +
| OUT shared_blks_read int8, +
| OUT shared_blks_dirtied int8, +
| OUT shared_blks_written int8, +
| OUT local_blks_hit int8, +
| OUT local_blks_read int8, +
| OUT local_blks_dirtied int8, +
| OUT local_blks_written int8, +
| OUT temp_blks_read int8, +
| OUT temp_blks_written int8, +
| OUT blk_read_time float8, +
| OUT blk_write_time float8 +
| ) +
| RETURNS SETOF record +
| AS '$libdir/pg_stat_statements', 'pg_stat_statements_1_3' +
| LANGUAGE C STRICT VOLATILE; +
| +
| -- Register a view on the function for ease of use. +
| CREATE VIEW pg_stat_statements AS +
| SELECT * FROM pg_stat_statements(true); +
| +
| GRANT SELECT ON pg_stat_statements TO PUBLIC; +
| +
| -- Don't want this to be available to non-superusers. +
| REVOKE ALL ON FUNCTION pg_stat_statements_reset() FROM PUBLIC; +
|
shared_blks_dirtied | 31

Replication status

Master

postgres=# select pg_is_in_recovery();
-[ RECORD 1 ]-----+--
pg_is_in_recovery | f

postgres=# select pg_current_xlog_insert_location(),pg_current_xlog_location();
-[ RECORD 1 ]-------------------+-----------
pg_current_xlog_insert_location | 0/1202E470
pg_current_xlog_location | 0/1202E470

Standby

postgres=# select pg_last_xlog_replay_location(),pg_last_xlog_receive_location();
-[ RECORD 1 ]-----------------+-----------
pg_last_xlog_replay_location | 0/1202E540
pg_last_xlog_receive_location | 0/1202E540

postgres=# select pg_is_in_recovery();
-[ RECORD 1 ]-----+--
pg_is_in_recovery | t

Detect the most bloated tables (no need of extensions):

SELECT relname,
    seq_scan,
    idx_scan,
    n_live_tup,
    n_dead_tup,
    to_char(n_dead_tup/n_live_tup::real, '999D99')::real AS ratio,
    pg_size_pretty(pg_relation_size(relid))
FROM pg_stat_all_tables
WHERE pg_relation_size(relid) > 1024 * 1024 AND
    n_live_tup > 0
ORDER BY n_dead_tup/n_live_tup::real DESC LIMIT 10;


List the unused indexes:

SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    idx_scan as index_scans
FROM pg_stat_user_indexes ui
    JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 1024 * 1024
ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
    pg_relation_size(i.indexrelid) DESC;

会员力量，点亮园子希望

刷新页面返回顶部

DataBases

PG sys function

About