如何排查PostgreSQL 12中CREATE DATABASE语句执行过慢的问题

当前环境为Ubuntu 22.04 LXC容器（多台主机均存在该问题，且环境已稳定运行数年），PostgreSQL仅CREATE DATABASE和DROP DATABASE操作异常缓慢（耗时从3秒到近30秒不等），其他所有数据库操作均正常。已排除以下因素：

• 磁盘IO、CPU使用率极低，排除资源瓶颈
• 关闭fsync无任何改善
• 开启debug level 3日志未发现异常
• Linux进程日志仅显示进程处于等待状态，但无法定位具体等待对象
• template1数据库几乎为空

原始操作日志：

2022-10-31 14:10:02.743 UTC [1086558] exodus@exodus LOG:  duration: 17249.532 ms  statement: CREATE DATABASE xo_dict WITH ENCODING='UTF8' 
2022-10-31 14:10:11.569 UTC [1090734] exodus@exodus LOG:  duration: 8010.033 ms  statement: DROP DATABASE exodus2b
2022-10-31 14:10:13.359 UTC [1086558] exodus@exodus LOG:  duration: 9596.890 ms  statement: DROP DATABASE xo_dict
2022-10-31 14:10:15.076 UTC [1090734] exodus@exodus LOG:  duration: 3491.147 ms  statement: DROP DATABASE exodus3b
2022-10-31 14:10:32.291 UTC [1093962] exodus@exodus LOG:  duration: 15510.507 ms  statement: CREATE DATABASE exodus2b WITH ENCODING='UTF8' 
2022-10-31 14:10:52.174 UTC [1093962] exodus@exodus LOG:  duration: 19864.597 ms  statement: CREATE DATABASE exodus3b WITH ENCODING='UTF8'  TEMPLATE exodus2b
2022-10-31 14:10:52.932 UTC [1093962] exodus@exodus LOG:  duration: 740.990 ms  statement: DROP DATABASE exodus2b
2022-10-31 14:10:55.849 UTC [1093962] exodus@exodus LOG:  duration: 2129.943 ms  statement: DROP DATABASE exodus3b
2022-10-31 14:11:13.755 UTC [1102944] exodus@exodus LOG:  duration: 17885.511 ms  statement: CREATE DATABASE exodus2b WITH ENCODING='UTF8' 
2022-10-31 14:11:43.537 UTC [1102944] exodus@exodus LOG:  duration: 29769.648 ms  statement: CREATE DATABASE exodus3b WITH ENCODING='UTF8' 
2022-10-31 14:21:33.410 UTC [1247048] exodus@exodus LOG:  duration: 15115.960 ms  statement: CREATE DATABASE xo_dict WITH ENCODING='UTF8' 

排查方向 #

1. 检查数据库目录权限与挂载属性 #

• 确认PostgreSQL数据目录（默认/var/lib/postgresql/<version>/main）权限为postgres:postgres，权限值为700
• 检查数据目录所在挂载点是否有noexec、sync等特殊属性，或是否使用NFS等远程存储（LXC容器挂载远程存储易引发延迟）
• 验证LXC容器存储后端状态，排查ZFS快照延迟、Btrfs的COW特性导致的元数据阻塞问题

2. 排查锁与事务等待 #

• 执行SELECT * FROM pg_locks WHERE NOT granted;查看未授予的锁，重点关注template1或系统目录相关锁
• 执行SELECT pid, query, state, wait_event_type, wait_event FROM pg_stat_activity WHERE state = 'waiting';定位具体等待事件类型（如LWLock、Lock）
• 检查长时事务：SELECT * FROM pg_stat_activity WHERE state = 'idle in transaction';，确认是否有事务持有系统级锁

3. 验证DNS/主机名解析延迟 #

• CREATE/DROP操作可能涉及主机名解析，测试容器内DNS解析速度：nslookup <你的数据库主机名>或getent hosts <你的数据库主机名>
• 检查/etc/hosts是否配置正确的本地主机名映射，避免依赖外部DNS引发延迟

4. 检查系统目录状态 #

• 执行ANALYZE pg_database;和ANALYZE pg_tablespace;更新系统统计信息
• 检查系统目录完整性：若开启校验和，执行SELECT * FROM pg_checksums;；或用pg_dumpall -s导出schema，查看是否有报错

5. LXC容器安全规则与系统调用限制 #

• 检查AppArmor配置：aa-status查看postgres相关profile，排查不必要的权限限制
• 确认LXC容器未禁止PostgreSQL所需系统调用（如fsync、mkdir、rmdir）
• 验证容器capabilities设置，确保postgres进程有足够权限执行文件系统操作

6. 宿主机对比测试 #

• 在宿主机部署同版本PostgreSQL，执行相同的CREATE/DROP操作，对比耗时，排除LXC容器层面问题

内容的提问来源于stack exchange，提问作者Abazoo