前言
记录一下服务器问题排查常用的一些命令。
前言
记录一下服务器问题排查常用的一些命令。
常用篇
Linux
1 | 只列出含 XXX 的文件 |
正则表达式
常用正则:i Hate Regex
1 | // 匹配 hello 之前的字符 |
工具
- crontab:设置定时任务工具;
- Socat:网络工具(透明代理,端口转发,文件传输等),新版瑞士军刀:socat
服务器之间文件传输
参考资料:Linux下的SCP指令详解
1 | 本地主机传输文件到远程主机 |
PostgreSQL
配置文件设置
1 | // 设置空闲长事务超时时间 |
psql
1 | nohup psql postgresql://user:password@host:port/dbname -f update.sql > update.sql 2>&1 & # 刷库命令,update.sql 文件以 begin; 开始,commit; 结束 |
sql
查看锁等待状态
pg中关于AccessShareLock和ExclusiveLock的问题:
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 -- 1. 先用一个函数来将锁转换为数字,
create function f_lock_level(i_mode text) returns int as
$$
declare
begin
case i_mode
when 'INVALID' then return 0;
when 'AccessShareLock' then return 1;
when 'RowShareLock' then return 2;
when 'RowExclusiveLock' then return 3;
when 'ShareUpdateExclusiveLock' then return 4;
when 'ShareLock' then return 5;
when 'ShareRowExclusiveLock' then return 6;
when 'ExclusiveLock' then return 7;
when 'AccessExclusiveLock' then return 8;
else return 0;
end case;
end;
$$
language plpgsql strict;
-- 2. 修改查询语句,按锁级别排序:
with t_wait as
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,
a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,
b.usename,b.datname from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted),
t_run as
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,
a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,
b.usename,b.datname from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted)
select r.locktype,r.mode r_mode,r.usename r_user,r.datname r_db,r.relation::regclass,r.pid r_pid,
r.page r_page,r.tuple r_tuple,r.xact_start r_xact_start,r.query_start r_query_start,
now()-r.query_start r_locktime,r.query r_query,w.mode w_mode,w.pid w_pid,w.page w_page,
w.tuple w_tuple,w.xact_start w_xact_start,w.query_start w_query_start,
now()-w.query_start w_locktime,w.query w_query
from t_wait w,t_run r where
r.locktype is not distinct from w.locktype and
r.database is not distinct from w.database and
r.relation is not distinct from w.relation and
r.page is not distinct from w.page and
r.tuple is not distinct from w.tuple and
r.classid is not distinct from w.classid and
r.objid is not distinct from w.objid and
r.objsubid is not distinct from w.objsubid and
r.transactionid is not distinct from w.transactionid and
r.pid <> w.pid
order by f_lock_level(w.mode)+f_lock_level(r.mode) desc,r.xact_start;现在可以排在前面的就是锁级别高的等待,优先干掉这个。
-[ RECORD 1 ]-+----------------------------------------------------------
locktype | relation -- 冲突类型
r_mode | ShareUpdateExclusiveLock -- 持锁模式
r_user | postgres -- 持锁用户
r_db | postgres -- 持锁数据库
relation | tbl -- 持锁对象
r_pid | 25656 -- 持锁进程
r_xact_start | 2015-05-10 14:11:16.08318+08 -- 持锁事务开始时间
r_query_start | 2015-05-10 14:11:16.08318+08 -- 持锁SQL开始时间
r_locktime | 00:01:49.460779 -- 持锁时长
r_query | vacuum freeze tbl; -- 持锁SQL,注意不一定是这个SQL带来的锁,也有可能是这个事务在之前执行的SQL加的锁
w_mode | AccessExclusiveLock -- 等待锁模式
w_pid | 26731 -- 等待锁进程
w_xact_start | 2015-05-10 14:11:17.987362+08 -- 等待锁事务开始时间
w_query_start | 2015-05-10 14:11:17.987362+08 -- 等待锁SQL开始时间
w_locktime | 00:01:47.556597 -- 等待锁时长
w_query | truncate tbl; -- 等待锁SQL
-[ RECORD 2 ]-+----------------------------------------------------------
locktype | relation
r_mode | ShareUpdateExclusiveLock
r_user | postgres
r_db | postgres
relation | tbl
r_pid | 25656
r_xact_start | 2015-05-10 14:11:16.08318+08
r_query_start | 2015-05-10 14:11:16.08318+08
r_locktime | 00:01:49.460779
r_query | vacuum freeze tbl;
w_mode | RowExclusiveLock
w_pid | 25582
w_xact_start | 2015-05-10 14:11:22.845+08
w_query_start | 2015-05-10 14:11:22.845+08
w_locktime | 00:01:42.698959
w_query | insert into tbl(crt_time) select now() from generate_series(1,1000); -- 这个SQL其实等待的是truncate tbl的锁;
......
统计数据库表以及索引存储空间
1 | -- 按从大到小排序输出数据库每个索引大小 |
常用sql语句
1 | -- 查找超过1小时的长事务 |
权限配置,PostgreSQL权限管理详解:
1 | -- 创建只读组 |
主从&备份
参考资料:postgresql流式复制(Streaming Replication)、【PostgreSQL】PostgreSQL复制的监控、【PostgreSQL】导出数据库表(或序列)的结构和数据、pg_ctl、pg_basebackup
1 | -- 创建流复制备份用户(主从) |
相关命令:
1 | # 冷备数据库(结合刷库命令可恢复数据库),加 -s 参数只导出数据库表结构 |
设置:postgresql.conf
1 | wal_level = hot_standby |
设置:pg_hba.conf
1 | # Allow replication connections from localhost, by a user with the |
设置 recovery.conf(自 Postgresql 12 起,recovery.conf 并入 postgresql.conf):
1 | standby_mode = 'on' |
常见问题:
当自增主键报
duplicate key value violates unique constraint
主键冲突时,一般是因为存在手动分配 id 的数据(复制表或着手动插入分配了 id),自增主键 seqence TABLE_COLUMN_seq 没有更新,新插入一个值自增 id 和数据库已插入的分配 id 冲突,此时需要执行SELECT setval('TABLE_COLUMN_seq', (SELECT max(COLUMN) FROM "TABLE"))
更新自增主键;分析 sql 性能时,可在 sql 语句前增加
EXPLAIN
关键字,查看执行计划,EXPLAIN 一般不会实际执行 sql,但 sql 中带有子语句时,子语句可能会执行,所以为保险起见,最好是在事务中使用 EXPLAIN;eg:1
2
3begin;
EXPLAIN select * from table1 where id=1;
rollback;若要分析实际执行时间,可以使用 EXPLAIN ANALYZE,该选项会实际执行 SQL,也可以组合参数一起分析执行命令
EXPLAIN (ANALYZE, BUFFERS) select * from table1 where id=1;
。如果业务数据无法直接使用批量写入数据库,就最好在一个事务中写入(当然也得看数据量),在同一个事务中写入,不仅能利用事务本身的 ACID 特性,而且比单独分次执行 sql 效率更高;
PG 数据库中,如果要使用 order 排序查询时,一般带主键的复合索引比单个字段索引更有效,以为 PG 数据在数据更新后,一般会乱序存储,导致单字段索引在查询时需要访问的页面会更多;
PG 刚创建/删除索引后,不一定会及时生效,需要数据库运行一段时间后才会开始生效,如需要立即生效,可执行
ANALYZE VERBOSE table_name;
命令。
后记
后面持续更新。。。