postgresql调优实践

1、性能优化

1.1 目标

10Gbps流量，按每秒10w条/s日志量估算，每个日志按500B算。需要50MB/s磁盘写入，一天4T日志量。

1.2  磁盘性能测试

在10.67.5.69上测试，宿主机215，做了raid。

 iops(每秒执行的IO次数)、bw（带宽，每秒的吞吐量）、lat（每次IO操作的延迟）

当每次IO操作的block较小时，如512bytes/4k/8k等，测试的主要是iops。

当每次IO操作的block较大时，如256k/512k/1M等，测试的主要是bw。

1.2.1 dd测试

（i/oflag，不用缓存，直接写盘，测实际速度）

iops——写测试 dd if=/dev/zero of=./a.dat bs=512 count=1000000 oflag=direct                   8000左右         

iops——读测试 dd if=./a.dat of=/dev/null bs=512 count=1000000 iflag=direct                      10278左右 

bw——写测试 dd if=/dev/zero of=./a.dat bs=1M count=8000 oflag=direct                            354MB/s

bw——读测试 dd if=./a.dat of=/dev/null bs=1M count=8000 iflag=direct                                1.1GB/s            

1.2.2 fio测试

随机顺序及混合读写。更准确、精细。

测试脚本参考：run_fio.sh

1.3 优化方向

1、事务

2、配置

synchronous_commit：off            写到缓存中就会向客户端返回提交成功，但也不是一直不刷到磁盘，延迟写入磁盘  参考：https://blog.csdn.net/DB_su/article/details/78224137

shared_buffers：            通过 shared_buffers 和内核和磁盘打交道，因此应该尽量大，让更多的数据缓存在 shared_buffers 中。通常设置为实际 RAM 的 10% 是合理的。要在系统中设置 kernel.shamax 的值，该值决定了进程可调用最 大共享内存数量（ipcs -l）。kernel.shmmax = postgres shared_buffers + 32MB（不按此法算）

work_mem：增加 work_mem 有助于提高排序的速度。通常设置为实际 RAM 的 2% – 4%

effective_cache_size：设置稍大，优化器更倾向使用索引扫描而不是顺序扫描，建议的设置为可用空闲内存的 25%，这里的可用空闲内存指的是主机物理内存在运行 pg 时的空闲值。

maintenance_work_mem：这里定义的内存只是在 CREATE INDEX, VACUUM 等时用到，因此用到的频率不高，但是往往这些指令消耗比较多的资源，因此应该尽快让这些指令快速执行完毕：给 maintence_work_mem 大的内存，比如 512M(524288)

max_connections：max_connections 的目的是防止 max_connections * work_mem 超出了实际内存大小。

fsync：设定 sync=no 关闭 fsync 的话， PostgreSQL 不会等待 WAL 写回硬盘，就直接返回 query 成功。通常这个会带来 15-25% 的性能提升.但是缺点就是，如果系统崩溃（断电， PostgreSQL 挂掉）的时候，你将有可能丢失最后那个 transcation. 不过这个并不会造成你系统的数据结构问题。如果说在系统出问题的时候丢失1-2笔数据是可以接受的，那么 25% 的性能提升是很可观的。

wal：依赖于fsync

commit_delay：延迟提交

3、copy插入 延后写

4、prepare预规划

5、多行插入（和4选一种）

6、多连接/多线程插入：速度上有优势，但是把握不好容易造成资源占用率过高，连接数太大也容易影响其他应用；

7、写入更新：写入更新是postgresql新特性，使用会造成一定的性能消耗（相对直接插入）；

8、触发器和存储过程能不用则不用

9、索引在插入时慢，查询时快，重建索引：减少索引使用

10、大页内存使用

11、表结构，分库分表

12、unlogged table：:Create unlogged table。。unlogged table 在遇到Postgre服务器异常重启后会丢失全部数据，所以如果你的数据不允许丢失，请不要使用。

13、autovacuum：off              手动定期vacuum

14、多个字段联合为一个字段，json化

1.4 优化验证

以字段最多的storage_sample为例，进行全字段插入优化。

分析手段：explain analyze

1.4.1 具体手段

无优化：插入10000条数据，26s，平均384条/s，调试版程序和非调试版程序无差别，磁盘tps在700-800左右

加了事务：10000条/s（每轮事务提交多少条要测试给出）

改了一段当前配置，效果提升不大，可能在大的异构数据量的时候有效

默认采用prepared预规划

unlogged table：有效，大数据量稳定的10000/s

copy导入：命令行操作，1000000/12s=83333，单线程。实现起来有两种方案，都比较耗时：

          1、结合文件copy，需要先弄到文件再执行copy命令；

          2、结合snprintf，用数组缓存。预分配一个大的内存做缓存，然后用snprintf、strncat操作，实测，50000条日志就花了57s。

多线程：已实施，在写入能力上应该是倍数关系。每张日志表创建一个连接来进行事务写入。

多个字段json：目前没有实施，考虑到两个问题，1、转化为json，需要使用snprintf，会有性能影响；2、目前web前端查询还是用的单字段查询，改了之后会影响当前的web显示。

编码引起的入库失败问题：对于非法字符，建议在应用层进行过滤，在数据库中转换，影响写入速度，同时会使得数据库的开销变大。实在是万不得已的行为。入库失败的根因就是同时使用UTF8和GBK编码。