pg_stat_activity
pg_stat_activity系统表记录了当前数据库活跃的连接信息，可以使用户清楚当前数据库系统正在做什么，包括使用的数据库和当前哪些用户是在连接和使用中。
例如通过查看pid,query_start,state_change,state,query几列，可以获取到当前哪些进程在执行何种查询，以及查询开始的时间和当前的状态。这样在看到一条糟糕的命令时，可以及时地中断掉它的执行。pg提供了pg_cancel_backend和pg_terminal_backend两个函数来执行中断查询的操作，正如函数名字面意思那样，pg_cancel_backend会取消查询操作，但不会断开用户的连接；而pg_terminal_backend更加暴力，直接将查询和数据库的连接全部清除。
其他pg_stat_activity中记录的数据如下所示。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
| ubuntu=> \d pg_stat_activity
View "pg_catalog.pg_stat_activity"
Column | Type | Collation | Nullable | Default
datid | oid | | |
datname | name | | |
pid | integer | | |
leader_pid | integer | | |
usesysid | oid | | |
usename | name | | |
application_name | text | | |
client_addr | inet | | |
client_hostname | text | | |
client_port | integer | | |
backend_start | timestamp with time zone | | |
xact_start | timestamp with time zone | | |
query_start | timestamp with time zone | | |
state_change | timestamp with time zone | | |
wait_event_type | text | | |
wait_event | text | | |
state | text | | |
backend_xid | xid | | |
backend_xmin | xid | | |
query_id | bigint | | |
query | text | | |
backend_type | text | | |
|
pg_stat_database
pg_stat_database系统表中记录了更底层的数据库的相关信息。通过列名大体可以知道表中记录的信息类型，比如blks_对应了数据块的信息；tup_记录了元组的信息，反映了系统中是否存在有大数据量的读写操作；sessions_是会话层面的信息。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
| ubuntu=> \d pg_stat_database
View "pg_catalog.pg_stat_database"
Column | Type | Collation | Nullable | Default
datid | oid | | |
datname | name | | |
numbackends | integer | | |
xact_commit | bigint | | |
xact_rollback | bigint | | |
blks_read | bigint | | |
blks_hit | bigint | | |
tup_returned | bigint | | |
tup_fetched | bigint | | |
tup_inserted | bigint | | |
tup_updated | bigint | | |
tup_deleted | bigint | | |
conflicts | bigint | | |
temp_files | bigint | | |
temp_bytes | bigint | | |
deadlocks | bigint | | |
checksum_failures | bigint | | |
checksum_last_failure | timestamp with time zone | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
session_time | double precision | | |
active_time | double precision | | |
idle_in_transaction_time | double precision | | |
sessions | bigint | | |
sessions_abandoned | bigint | | |
sessions_fatal | bigint | | |
sessions_killed | bigint | | |
stats_reset | timestamp with time zone | | |
|
其中temp_files和temp_bytes记录了临时数据的使用情况，这两列的信息非常重要，因为其反映了数据库是否不得不在磁盘上写入临时文件，这必然大大拖慢操作的效率。而临时文件的产生原因可能是多种的，比如过小的work_mem设置、低效的查询操作或是索引和其他的DDL操作等。
pg_stat_user_table和pg_statio_user_table
在了解了整体的数据库信息后，有时不得不深入具体的表中去查看相关的信息，这就要用上这两张表了。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
| ubuntu=> \d pg_stat_user_tables
View "pg_catalog.pg_stat_user_tables"
Column | Type | Collation | Nullable | Default
relid | oid | | |
schemaname | name | | |
relname | name | | |
seq_scan | bigint | | |
last_seq_scan | timestamp with time zone | | |
seq_tup_read | bigint | | |
idx_scan | bigint | | |
last_idx_scan | timestamp with time zone | | |
idx_tup_fetch | bigint | | |
n_tup_ins | bigint | | |
n_tup_upd | bigint | | |
n_tup_del | bigint | | |
n_tup_hot_upd | bigint | | |
n_tup_newpage_upd | bigint | | |
n_live_tup | bigint | | |
n_dead_tup | bigint | | |
n_mod_since_analyze | bigint | | |
n_ins_since_vacuum | bigint | | |
last_vacuum | timestamp with time zone | | |
last_autovacuum | timestamp with time zone | | |
last_analyze | timestamp with time zone | | |
last_autoanalyze | timestamp with time zone | | |
vacuum_count | bigint | | |
autovacuum_count | bigint | | |
analyze_count | bigint | | |
autoanalyze_count | bigint | | |
ubuntu=> \d pg_statio_user_tables
View "pg_catalog.pg_statio_user_tables"
Column | Type | Collation | Nullable | Default
relid | oid | | |
schemaname | name | | |
relname | name | | |
heap_blks_read | bigint | | |
heap_blks_hit | bigint | | |
idx_blks_read | bigint | | |
idx_blks_hit | bigint | | |
toast_blks_read | bigint | | |
toast_blks_hit | bigint | | |
tidx_blks_read | bigint | | |
tidx_blks_hit | bigint | | |
|
pg_stat_user_tables记录了表中数据的重要统计数据，pg_statio_user_tables记录了表的缓冲使用情况，包括表(heap_)，索引(idx_)和toast技术的缓冲行为。
仔细分析和比较pg_stat_user_tables中记录的统计信息，对性能优化会有很大的帮助。
pg_stat_bgwriter
很多情况下，数据库连接并不会直接将数据写入到磁盘中，而是交给后台写入者去执行，如果要查看数据是如何被写入的，就可以通过pg_stat_bgwriter来获取相关统计信息。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| ubuntu=> \d pg_stat_bgwriter
View "pg_catalog.pg_stat_bgwriter"
Column | Type | Collation | Nullable | Default
checkpoints_timed | bigint | | |
checkpoints_req | bigint | | |
checkpoint_write_time | double precision | | |
checkpoint_sync_time | double precision | | |
buffers_checkpoint | bigint | | |
buffers_clean | bigint | | |
maxwritten_clean | bigint | | |
buffers_backend | bigint | | |
buffers_backend_fsync | bigint | | |
buffers_alloc | bigint | | |
stats_reset | timestamp with time zone | | |
|
其他系统表
pg_stat_archiver：有关归档进程的重要信息，它会报告有关已经被归档的事务日志文件的数量，以及最后一个归档文件和归档时间；pg_stat_replication：提供从主机到从机的流进程信息；pg_stat_wal_receiver：提供的是复制端的信息；pg_stat_ssl：记录用户通过ssl加密连接的信息；pg_stat_xact_user_tables：只记录当前事务的相关信息；pg_stat_progess_vacuum：提供清理进程的相关信息；
最后要介绍的是pg_stat_statements，它对于性能优化非常重要，因为其记录了系统上查询的重要信息，有助于找出哪种类型的查询执行很慢以及多久执行一次。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
| ubuntu=> \d pg_stat_statements
View "public.pg_stat_statements"
Column | Type | Collation | Nullable | Default
userid | oid | | |
dbid | oid | | |
toplevel | boolean | | |
queryid | bigint | | |
query | text | | |
plans | bigint | | |
total_plan_time | double precision | | |
min_plan_time | double precision | | |
max_plan_time | double precision | | |
mean_plan_time | double precision | | |
stddev_plan_time | double precision | | |
calls | bigint | | |
total_exec_time | double precision | | |
min_exec_time | double precision | | |
max_exec_time | double precision | | |
mean_exec_time | double precision | | |
stddev_exec_time | double precision | | |
rows | bigint | | |
shared_blks_hit | bigint | | |
shared_blks_read | bigint | | |
shared_blks_dirtied | bigint | | |
shared_blks_written | bigint | | |
local_blks_hit | bigint | | |
local_blks_read | bigint | | |
local_blks_dirtied | bigint | | |
local_blks_written | bigint | | |
local_blks_hit | bigint | | |
local_blks_read | bigint | | |
local_blks_dirtied | bigint | | |
local_blks_written | bigint | | |
temp_blks_read | bigint | | |
temp_blks_written | bigint | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
temp_blk_read_time | double precision | | |
temp_blk_write_time | double precision | | |
wal_records | bigint | | |
wal_fpi | bigint | | |
wal_bytes | numeric | | |
jit_functions | bigint | | |
jit_generation_time | double precision | | |
jit_inlining_count | bigint | | |
jit_inlining_time | double precision | | |
jit_optimization_count | bigint | | |
jit_optimization_time | double precision | | |
jit_emission_count | bigint | | |
jit_emission_time | double precision | | |
|
pg的默认配置中没有添加这个模块，要启用的话需要在配置文件postgresql.conf中的shared_preload_libraries内加上pg_stat_statements，然后重启服务器，在要添加的数据库中执行CREATE EXTENSION pg_stat_statements。
设置日志
pg的日志设置都可以在postgresql.conf配置文件中找到，包括log日志输出到哪里，或者日志文件的大小和旋转周期等等，都能找到详细的配置项。
