postgres优化记录

postgresql11数据库，批量插入数据巨慢，查询的时候也巨慢，记录一下内存参数调优及性能优化过程：

1.背景

pg11数据库出来以后，在单表环境下（整个库就建立了1个测试表）测试1千万条数据的like查询等速度极快，比oracle和mysql都要快很多，遂决定后续项目更换为pg11数据库，等开发快结束要上线导入生产环境等同的数据量时，发现有慢查询的情况。

 

2.处理过程

2.1 慢查询

通过yum install pg_top11 安装pg_top工具，切换到postgres用户，运行pg_top后发现有执行时间超过半小时的SQL若干，pg_top里面看不了具体执行的SQL内容是什么，执行以下sql查询SQL内容：

select datname, pid, usename, application_name, client_addr, client_port, 
         xact_start, query_start, 
         state_change,wait_event_type,wait_event, state, backend_xid, backend_xmin, query,
         xact_start,(now()-xact_start) as take_times,
         query_start,now()-query_start as run_times
  from pg_stat_activity 
 where 1=1  
   and state<>'idle'
   and (backend_xid is not null or backend_xmin is not null) 
 order by take_times;  

发现确实有一些SQL是开发人员写的不合理的，但是PG一直在慢慢执行处理，有的甚至执行了快1个小时了也没有结束。

2.2 优化

开发环境的机器配置比较低，既然有慢查询，就先找出瓶颈在哪里，安装磁盘IO工具查看磁盘读写情况

yum install iotop

然后iotop -oP 

差不多就是上面这个样子，每秒106M的读取，37M的写入，这基本就已经把磁盘性能跑满了，和慢SQL查询里面看到的情况基本能对应，wait_event都是DataFileRead，等待事件类型都是IO。

 

接着往下找，看查询SQL语句，有一个挺有代表性的，查的是一个单表数据量一千六百万的，做了时间过滤和分组聚合统计，耐心等着3分钟左右能出结果，时间字段已经加了索引，而且pg11查询的时候没有走索引。

PG11数据库在开发前的测试阶段性能超级好，建好一千多张表，灌入能模拟线上规模的数据量以后性能就下降了这么多，令人费解，优化方法如下：

方法一：表分区

既然查询的时候有大量的磁盘IO，需要从单表里面去过滤需要的数据，那就利用PG11的表分区功能对大表进行拆分，1年1个子表，建完以后查询速度直接提高到了2s。

方法二：修改pg内存配置参数

修改postgresql.conf文件,

shared_buffers改为物理内存的四分之一大小

synchronous_commit =off

wal_buffers = 1024MB

checkpoint_timeout = 30min

这样整完了以后，插入数据的速度是这样的：

性能很不错，要是不这么调整，每秒的写入速度才5M左右。