PostgreSQL经验小结

本篇基于9.5版本。

>> 解决psql登入后不能输入中文

psql登入后提示No entry for terminal type “xterm”; using dumb terminal settings,查看/opt/PostgreSQL/9.5/bin/psql发现需要依赖某些文件。在系统中找到相关文件,然后创建软连接,再次登录即可输入中文。

find / -name 'libreadline.so.6'
find / -name 'libreadline.so.5'
ln -s /lib/x86_64-linux-gnu/libreadline.so.6 /lib64/libreadline.so.6

>> 对象命名习惯

PostgreSQL中的对象名(表名、字段名、视图名、索引名、函数名、序列名等)是区分大小写字母的,但在SQL语句中直接使用大写字母的话会被自动转换成小写,解决办法是用双引号修饰对象名,阻止自动转换。为了降低复杂度和避免由此引发的bug,建议对象命名时不要用大写字母,单词之间用下划线分隔。
例如:t_user_info,i_user_info_name,f_get_user_friends,s_user_id

>> 添加备注与注释

代码是给人看的,所以应该养成添加备注和注释的习惯,包括表、列、函数等。

COMMENT ON TABLE user_info IS  '用户信息表'; 
COMMENT ON COLUMN user_info.name IS '用户名称';

>> 推敲字段的类型设定

在满足业务需求的前提下,选择合适的数据类型,尽量减少占用的存储空间。

>> 系统表与系统视图

参考:https://www.postgresql.org/docs/current/static/catalogs.html

【名称】 【类型】 【描述】
pg_authid 全局表 PostgreSQL实例中的角色信息
pg_auth_members 全局表 PostgreSQL实例中的角色关系信息
pg_user 全局视图 PostgreSQL实例中的用户信息,具备登陆权限的角色
pg_roles 全局视图 PostgreSQL实例中的角色信息
pg_tablespace 全局表 PostgreSQL实例中的表空间信息
pg_database 全局表 PostgreSQL实例中的数据库信息
pg_namespace 当前database中声明的模式(schema)
pg_class 当前database中声明的对象,包括表、索引、序列、视图、复合类型
pg_attribute 当前database中表的字段信息
pg_attrdef 当前database中表的字段的缺省值信息
pg_constraint 当前database中表的约束,包括检查约束、主键、唯一约束和外键约束
pg_index 当前database中的索引信息
pg_tables 视图 当前database中的表信息
pg_indexes 视图 当前database中的索引信息
pg_views 视图 当前database中的视图信息
pg_rules 视图 当前database中的表的规则信息
pg_settings 视图 配置项定义

>> autovaccum在pgxl中的问题

在Postgres-XL中,有时会因为临时表使用不当残留大量的垃圾临时表(可能是bug)。当发生autovacuum时,日志中会看到,

pgxl-exp-005

这些表需要手动清理,

# 从日志中抓取信息,拼成SQL,然后在一个coordinator节点执行。清理coordinator的时候需要从另一个coordinator执行这些SQL。
cat /log/pgxl_log/dn/dn.2017-11-10.log |grep 'found orphan temp table' | awk '{print "EXECUTE DIRECT ON (datanode1) '\''drop table "$11"'\'';"}' | sort |uniq > /tmp/autovacuum.sql
tail /log/pgxl_log/coord/coord.2017-10-31.log -n10000 |grep 'found orphan temp table' | awk '{print "EXECUTE DIRECT ON (coord1) '\''drop table "$11"'\'';"}' | sort |uniq > /tmp/autovacuum.sql
psql -h 172.28.2.41 -p 21001 -d monsters -f /tmp/autovacuum.sql

>> SQL诊断

查看一条SQL语句的执行计划用explain即可,

EXPLAIN ANALYZE SELECT * FROM user_info s1 INNER JOIN (SELECT * FROM unnest(ARRAY[1,2]) AS NAMES (uid)) s2 ON s1.id = s2.uid;
EXPLAIN VERBOSE SELECT * FROM user_info WHERE id IN (SELECT * FROM unnest(ARRAY[1]));

如果需要查看函数内的SQL的执行计划,可以配置shared_preload_libraries = ‘auto_explain’来自动把内嵌SQL的执行计划输出到日志。但是这个很耗性能,生产环境一般不用。注:启用auto_explain时,使用insert/update … returning语法会报错ERROR: bogus varno: 65001。

生产环境可以通过配置shared_preload_libraries = ‘pg_stat_statements’并create extension pg_stat_statements来开启统计SQL执行情况的组件,用于监测和找到需要优化的SQL或调用层逻辑。然后再结合explain进一步分析SQL的可优化点。

-- 重置统计数据
SELECT pg_stat_statements_reset();
-- 调用此时最多的SQL
SELECT * FROM pg_stat_statements ORDER BY calls DESC LIMIT 5;
-- 平均用时最多的SQL
SELECT * FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 5;

>> 查看shared_buffer使用情况

需要创建扩展插件pg_buffercache,这个扩展是全局性质的,无论在哪个database创建,查询的结果都一样。但是需要注意,在inner join其他database级的表时,查询的范围缩小到当前库。

\c mydb
CREATE EXTENSION pg_buffercache;
-- 查询当前数据库的user_info表在shared buffer中的page
SELECT b.*, c.relname FROM pg_buffercache b 
    INNER JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid) 
    INNER JOIN pg_database d ON b.reldatabase = d.oid
    WHERE datname = current_database() AND relname = 'user_info'
    LIMIT 10;
-- 按表分组统计shared buffer中的page个数
SELECT c.relname, count(*) AS buffers
    FROM pg_buffercache b INNER JOIN pg_class c 
        ON b.relfilenode = pg_relation_filenode(c.oid) 
        AND b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
    GROUP BY c.relname ORDER BY 2 DESC LIMIT 10;

>> 检查点checkpoint调度参数

checkpoint_timeout:可以理解为最长多久如果没执行checkpoint,则强制执行一次checkpoint。通常情况下(full_page_write = on),checkpoint后,在page第一次写的时候会将整个page写入WAL。这会造成更大的IO消耗和磁盘空间占用。所以checkpoint不宜太频繁。但也不宜间隔太久,否则在崩溃重启后事务redo时耗时较长。

checkpoint_segments:可以理解为产生多少个WAL文件后执行一次checkpoint。如果经过了checkpoint_timeout设置的时间,新增WAL没有达到checkpoint_segments设置的值,仍然会执行一次checkpoint。注:这个参数在9.5版本后弃用了。

checkpoint_completion_target:可以理解为完成本次checkpoint计划使用的时间系数。例如checkpoint_timeout=30min,checkpoint_completion_target=0.2,则完成checkpoint的计划用时为6分钟。checkpoint每write完一个page,就计算下新增WAL相对checkpoint_segments的比重,判定是否需要sleep。这个参数的意义在于,可以使checkpoint通过有规律的sleep缓慢完成,避免短时间内消耗大量磁盘IO,影响系统稳定。

>> 结束活动中的会话

SELECT pg_cancel_backend('proc_pid');
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND datname = 'mydb';

>> 锁

PostgreSQL支持多种模式的锁来控制对数据的并发访问。

表级别锁:加在表上的锁,冲突关系如下,

pgxl-exp-015

【SQL命令】 【该命令需要在目标表上持有的锁】 【当其他事务在目标表上持有以下锁时,将阻塞该命令】
SELECT ACCESS SHARE ACCESS EXCLUSIVE
SELECT FOR UPDATE
SELECT FOR SHARE
ROW SHARE ACCESS EXCLUSIVE
EXCLUSIVE
UPDATE
DELETE
INSERT
ROW EXCLUSIVE ACCESS EXCLUSIVE
EXCLUSIVE
SHARE ROW EXCLUSIVE
SHARE
VACUUM
ANALYZE
CREATE INDEX CONCURRENTLY
CREATE STATISTICS
ALTER TABLE VALIDATE
ALTER TABLE SET STATISTICS
ALTER TABLE SET (attr_option = value)
ALTER TABLE RESET (attr_option)
ALTER TABLE CLUSTER ON
ALTER TABLE SET WITHOUT CLUSTER
ALTER TABLE SET (storage_param = value)
SHARE UPDATE EXCLUSIVE ACCESS EXCLUSIVE
EXCLUSIVE
SHARE ROW EXCLUSIVE
SHARE
SHARE UPDATE EXCLUSIVE
CREATE INDEX SHARE ACCESS EXCLUSIVE
EXCLUSIVE
SHARE ROW EXCLUSIVE
SHARE UPDATE EXCLUSIVE
ROW EXCLUSIVE
CREATE COLLATION
CREATE TRIGGER
ALTER TABLE DISABLE TRIGGER
ALTER TABLE ENABLE TRIGGER
SHARE ROW EXCLUSIVE ACCESS EXCLUSIVE
EXCLUSIVE
SHARE ROW EXCLUSIVE
SHARE
SHARE UPDATE EXCLUSIVE
ROW EXCLUSIVE
REFRESH MATERIALIZED VIEW CONCURRENTLY EXCLUSIVE all except ACCESS SHARE
DROP TABLE
TRUNCATE
REINDEX
CLUSTER
VACUUM FULL
REFRESH MATERIALIZED VIEW
ALTER TABLE
ACCESS EXCLUSIVE all

行级别锁:事务A在更新或删除某一行数据时,需要对该行数据持有锁。当事务B已经持有该行数据的锁并尚未释放时,事务A将被阻塞。行级别锁不会影响查询操作。

页级别锁:加在page上的锁,不需要关注。

一个复杂事务可能包含多行的更新或删除操作。在行级别锁中,如果事务A和事务B分别进行了一半,相互持有对方想要的行级别锁,则造成死锁现象,都进入等待状态。在PostgreSQL中有死锁自检机制,受deadlock_timeout参数控制,在发生死锁时解锁双方事务。在当前版本的Postgres-XL中,跨节点的死锁还不能依赖这种机制解决,但是可以通过其他监控程序定时查询所有datanode的pg_stat_activity表来发现死锁的进程,通过结束一方来打破僵局。

>> 使用truncate table需要注意的问题

truncate table语义上是保留表结构,清空表数据。实质上是直接删除磁盘上的旧数据文件,重新创建数据文件(文件名发生变化),并更新pg_class的relfilenode。

在Postgres XL使用中,遇到过以下报错,尚不知道起因,但与truncate table也有一定关系:

2017-12-25 14:00:01.579 UTC 20112 510020507ERROR:  58P01: could not access status of transaction 3081115576
2017-12-25 14:00:01.579 UTC 20112 510020507DETAIL:  Could not open file "pg_clog/0B7A": No such file or directory.
2017-12-25 14:00:01.579 UTC 20112 510020507LOCATION:  SlruReportIOError, slru.c:897
2017-12-25 14:00:01.579 UTC 20112 510020507STATEMENT:  truncate table user_info_copy_table;
2017-12-25 14:00:01.605 UTC 20112 510020509ERROR:  58P01: could not open file "base/16397/380335": No such file or directory
2017-12-25 14:00:01.605 UTC 20112 510020509CONTEXT:  COPY user_info_copy_table, line 666
2017-12-25 14:00:01.605 UTC 20112 510020509LOCATION:  mdopen, md.c:602
2017-12-25 14:00:01.605 UTC 20112 510020509STATEMENT:  COPY public.user_info_copy_table (id, name, icon, "socialType", "socialId", expire_date, rewards_acceptor_phase, total_acceptor_num, rewards_acceptor_num, new_acceptor_num, sign_in_days, last_login_date, "createTime") FROM STDIN CSV
......
2017-12-25 14:23:25.329 UTC 7738 0LOG:  00000: checkpoint starting: time
2017-12-25 14:23:25.329 UTC 7738 0LOCATION:  LogCheckpointStart, xlog.c:8068
2017-12-25 14:23:29.762 UTC 7738 0ERROR:  58P01: could not open file "base/16397/380335": No such file or directory
2017-12-25 14:23:29.762 UTC 7738 0CONTEXT:  writing block 40 of relation base/16397/380335
2017-12-25 14:23:29.762 UTC 7738 0LOCATION:  mdopen, md.c:602
2017-12-25 14:23:29.762 UTC 7738 0WARNING:  58030: could not write block 40 of base/16397/380335
2017-12-25 14:23:29.762 UTC 7738 0DETAIL:  Multiple failures --- write error might be permanent.
2017-12-25 14:23:29.762 UTC 7738 0LOCATION:  AbortBufferIO, bufmgr.c:3593

其中找不到的文件380335就是表user_info_copy_table的数据文件。这个异常情况直接导致checkpoint不能完成,xlog因此不能被清理,而越积越多。此外在定时备份中,由于pg_basebackup运行时也需要执行checkpoint,导致备份也失败。

另一个问题是,truncate table需要持有ACCESS EXCLUSIVE锁。如果在执行truncate操作时,有其他任务在pg_dump数据,truncate会打断pg_dump进程,造成导出失败,备份文件损坏。命令和报错如下:

/usr/local/pgxl/bin/pg_dump -h 172.16.0.206 -p 21001 -U postgres -ab -j 4 -F d -f monsters monsters  >/data/log/monsters.log 2>&1
pg_dump: [parallel archiver] could not obtain lock on relation "public.user_info_copy_table"
This usually means that someone requested an ACCESS EXCLUSIVE lock on the table after the pg_dump parent process had gotten the initial ACCESS SHARE lock on the table.
pg_dump: [parallel archiver] a worker process died unexpectedly

>> 导入数据

批量导入数据时,优先考虑用COPY命令。如果是多条INSERT语句,需要显式执行开始事务(BEGIN TRANSACTION)和提交(COMMIT)命令,以加快导入速度。遵循先数据后索引和约束的原则,避免导入过程中维护索引和约束带来的额外开销。最后创建索引时可以适当增加maintenance_work_mem的值,以加快创建索引的速度。新建或重建索引后需要执行Analyze命令。

>> 压力测试

一般使用PostgreSQL自带的pgbench工具,得到被压测SQL或一定压力下数据库的每秒完成事务数(TPS)和平均耗时情况。也可以编写特定语言环境的压测程序,参考源码。pgbench用法如下:

# 产生测试数据,(-h 127.0.0.1 -p 21001 -U postgres -d postgres)是连接信息
pgbench -h 127.0.0.1 -p 21001 -U postgres -d postgres -i -s 100
# 直接压测pgbench初始化的数据,32个并发客户端(-c 32),32个线程(-j 32)
pgbench -p 21001 -U postgres -n -r -P 2 -c 32 -j 32 -T 120
nohup pgbench -p 21001 -U postgres -n -r -P 2 -c 8 -j 8 -T 60 -l > pgbench.log 2>&1
# 压测来自script.sql文件的SQL(-f script.sql),使用预编译方式(-M prepared),压测前不执行vacuum(-n),每1秒打印一次进度(-r -P 1),压测持续时间1000秒(-T 1000)
echo "update user_info set name = 'test' where id in (178,179,160,161);" > script.sql
pgbench -h 172.30.8.221 -p 21001 -U postgres -M prepared -n -r -P 1 -T 1000 -f script.sql

pgbench还支持在脚本中使用变量,参考https://www.postgresql.org/docs/9.5/static/pgbench.html

\setrandom vuid 10000 35684092
\setrandom vkey 1000000 9999999
\setrandom vtype 1 8
\setrandom vdelta 100 200
\setrandom vsrc 0 1
\setrandom vuid2 100 150
\setrandom vuid3 100 150
--select * from user_info where id = :uid;
select * from func_a(:vuid2, :vuid3);
--select * from "BLUpdCookieFish"(:vuid, '2015-01-11 16:00:00', '1.8.0', 1, :vtype, :vdelta, :vsrc, 1, to_char(:vkey, '9999999'));

>> 用gdb调试postgres进程,追踪源码

首先准备支持调试的二进制postgres文件,需要在编译时启用调试(./configure –enable-debug –enable-cassert),并关闭优化编译选项(可以在src/Makefile.global文件中找到CFLAGS = …,用-g替换-O2,也可以在configure时设置)。

./configure --enable-debug --enable-cassert CFLAGS="-ggdb -Og"

生产环境不要做以上修改。

接下来需要一个被调试的目标进程。可以先在客户端用psql连接到服务器,然后在服务器上ps aux | grep postgres找到子进程,用gdb命令attach到这个进程的id,进入gdb命令行。再然后就可以在客户端执行SQL,在服务端用gdb相关命令调试。

# 从其他主机发起连接
psql -h 172.16.0.205 -p 21001
# 在172.16.0.205上attach到由上面的连接产生的进程3344
gdb /usr/local/pgxl/bin/postgres 3344

常用的gdb命令:

  • directory:设置源码目录。默认情况下是编译时的源码路径。
  • list:在当前位置查看源码。
  • b:在某个函数开始处或当前文件某一行处设置断点。
  • info b:查看所有断点。
  • c:使程序继续执行,直到下一个断点。
  • bt:从当前位置向上查看完整调用路径。
  • n:向下执行一行。
  • 回车:重复执行上一个输入命令。
  • p:打印变量或函数返回值。
  • up:移动到上一个函数。
  • down:移动到下一个函数。
  • quit:退出调试。

pgxl-exp-017

Creative Commons License

本文基于署名-非商业性使用-相同方式共享 4.0许可协议发布,欢迎转载、使用、重新发布,但请保留文章署名wanghengbin(包含链接:https://wanghengbin.com),不得用于商业目的,基于本文修改后的作品请以相同的许可发布。

发表评论