一、数据库参数设置
- 修改数据库时区
ALTER DATABASE mydb SET timezone TO 'Asia/Shanghai'二、数据库统计分析
1. pg_stat_database
通过pg_stat_database可以大概了解数据库的历史情况。
select
datname as 数据库名,
numbackends as 连接数,
xact_commit as 事务提交总量,
xact_rollback as 事务回滚总量,
tup_returned as 全表扫描行数,
tup_fetched as 索引返回行数,
deadlocks as 死锁数量
from pg_stat_database
order by datname字段 | 说明 |
datid | 数据库的oid |
datname | 数据库名 |
numbackends | 访问当前数据库的连接数量 |
xact_commit | 该数据库事务提交总量：和下面的rollback和作为TPS统计 |
xact_rollback | 该数据库事务rollback总量，如果特别多，需要看业务是否有问题了 |
blks_read | 总磁盘物理读的块数，这里的read可能是从 cache中读取，如果很高需要结合blk_read_time看是否真的存在从磁盘读取的情况 |
blks_hit | 从shared buffer命中块数 |
tup_returned | 对于表来说，是全表扫描的行数；对于索引是通过索引返回的索引行数，如果这个值明显大于tup_fetched，说明当前数据库存在大量的全表扫描。可结合pg_stat_statments查找慢SQL，也可结合pg_stat_user_table找全表扫描次数和行数最多的表 |
tup_fetched | 通过索引返回的行数 |
tup_inserted | 插入的行数 |
tup_updated | 更新的行数 |
tup_deleted | 删除的行数 |
conflicts | 与恢复冲突取消的查询次数，只会在备机上发生 |
temp_files | 产生临时文件的数量，如果这个值很高，说明存在很多排序，hash,或者聚合这种操作，需要调大work_mem |
temp_bytes | 临时文件的大小 |
deadlocks | 死锁的数量，如果这个值很大说明业务逻辑有问题 |
blk_read_time | 数据库中花费在读取文件的时间，这个值很高说明内存较小，需要频繁从磁盘读入数据文件 |
blk_write_time | 数据库中花费在写数据文件的时间，pg中脏页一般写入page cache，如果这个值较高，则说明cache较小，操作系统的cache需要更积极的写入 |
stats_reset | 统计信息重置的时间 |
2. pg_stat_user_tables
通过查询pg_stat_user_tables，可以基本清除哪些表的全表扫描次数较多，表中DML哪种操作多，也可以了解垃圾数据的数量。
select
schemaname as 模式名,
relname as 表名,
seq_scan as 全表扫描次数,
seq_tup_read as 全表返回行数,
idx_scan as 索引扫描次数,
idx_tup_fetch as 索引返回行数
from pg_stat_user_tables
order by schemaname, relname字段 | 说明 |
relid | 表oid |
schemaname | 模式名 |
relname | 表名 |
seq_scan | 这个表进行全表扫描的次数 |
seq_tup_read | 全表扫描的数据行数，如果这个值很大说明操作这个表的SQL语句很可能是全表扫描，需要结合执行计划分析 |
idx_scan | 索引扫描的次数 |
idx_tup_fetch | 通过索引扫描返回的行数 |
n_tup_ins | 插入的数据行数 |
n_tup_upd | 更新的数据行数 |
n_tup_del | 删除的数据行数 |
n_tup_hot_upd | hot update的数据行数，这个值与n_tup_upd接近说明更新性能较好，不需要更新索引 |
n_live_tup | 活的行数量 |
n_dead_tup | 死记录个数 |
n_mod_since_analyze | 上次analyze的实际 |
last_vacuum | 上次手动vacuum的实际 |
last_autovacuum | 上次autovacuum的实际 |
last_analyze | 上次analyze时间 |
last_autoanalyze | 上次自动analyze时间 |
vacuum_count | acuum次数 |
autovacuum_count | 自动vacuum次数 |
analyze_count | analyze次数 |
autoanalyze_count | 自动analyze次数 |
3. pg_stat_user_indexes
可以知道当前哪些索引频繁使用，哪些是无效索引。无效索引可以删除掉，减少磁盘空间的使用和提升insert、delete、update的性能
select
schemaname as 模式名,
relname as 表名,
indexrelname as 索引名,
idx_scan as 索引扫描次数,
idx_tup_read as 索引读取行数,
idx_tup_fetch as 索引返回行数
from pg_stat_user_indexes
order by schemaname, relname, indexrelname字段 | 说明 |
relid | 相关表的oid |
indexrelid | 索引的oid |
schemaname | 模式名 |
relname | 表名 |
indexrelname | 索引名 |
idx_scan | 通过索引扫描的次数，如果该值很小，说明该索引很少被用到，可以考虑删除 |
idx_tup_read | 通过任意索引方法返回的索引行数 |
idx_tup_fetch | 通过索引方法返回的数据行数 |
三、数据库运行状态
1. pg_stat_activity
查看各客户端的连接数
select
client_addr as 客户端IP,
count(*) as 连接数
from pg_stat_activity
group by client_addr查看当前运行的SQL执行时长
select
pid as 进程ID,
CURRENT_TIMESTAMP - least(query_start,xact_start) AS 运行时长,
query AS 执行语句
from pg_stat_activity
where not pid = pg_backend_pid()
and state = 'active';取消后台操作，回滚未提交事物
select pg_terminate_backend(进程ID);字段 | 说明 |
datid | 这个后端连接到的数据库的OID |
datname | 这个后端连接到的数据库的名称 |
pid | 这个后端的进程 ID |
usesysid | 登录到这个后端的用户的 OID |
usename | 登录到这个后端的用户的名称 |
application_name | 连接到这个后端的应用的名称 |
client_addr | 连接到这个后端的客户端的 IP 地址。如果这个域为空，它表示客户端通过服务器机器上的一个 Unix 套接字连接或者这是一个内部进程（如自动清理）。 |
client_hostname | 已连接的客户端的主机名，由client_addr的反向 DNS 查找报告。这个域将只对 IP 连接非空，并且只有log_hostname被启用时才会非空。 |
client_port | 客户端用以和这个后端通信的 TCP 端口号，如果使用 Unix 套接字则为-1 |
backend_start | 这个进程被启动的时间，即客户端是什么时候连接到服务器的 |
xact_start | 这个进程的当前事务被启动的时间，如果没有活动事务则为空。如果当前查询是它的第一个事务，这一列等于query_start。 |
query_start | 当前活动查询被开始的时间，如果state不是active，这个域为上一个查询被开始的时间 |
state_change | state上一次被改变的时间 |
wait_event_type | 后端正在等待的事件类型，如果不存在则为 NULL。可能的值有： LWLockNamed：后端正在等待一个特定命名的轻量级锁。每一个这样的锁保护共享内存中的一个特定数据结构。wait_event将包含该轻量级锁的名称 LWLockTranche：后端正在等待一组相关轻量级锁中的一个。该组中的所有锁都执行一种相似的功能。wait_event将标识这个组中锁的大体目的。 Lock：后端正在等待一个重量级锁。重量级锁，也称为锁管理器锁或者简单锁，主要保护 SQL 可见的对象，例如表。不过，它们也被用于确保特定内部操作的互斥，例如关系扩展。wait_event将标识等待的锁的类型。 BufferPin：服务器进程正在等待访问一个数据缓冲区，而此时没有其他进程正在检查该缓冲区。如果另一个进程持有一个最终从要访问的缓冲区中读取数据的打开的游标，缓冲区 pin 等待可能会被拖延。 |
wait_event | 如果后端当前正在等待，则是等待事件的名称，否则为 NULL。 |
state | 这个后端的当前总体状态。可能的值是： active：后端正在执行一个查询。 idle：后端正在等待一个新的客户端命令。 idle in transaction：后端在一个事务中，但是当前没有正在执行一个查询。 idle in transaction (aborted)：这个状态与idle in transaction相似，不过在该事务中的一个语句导致了一个错误。 fastpath function call：后端正在执行一个 fast-path 函数。 disabled：如果在这个后端中track_activities被禁用，则报告这个状态。 |
backend_xid | 这个后端的顶层事务标识符（如果存在）。 |
backend_xmin | 当前后端的xmin范围。 |
query | 这个后端最近查询的文本。如果state为active，这个域显示当前正在执行的查询。在所有其他状态下，它显示上一个被执行的查询。 |
