PostgreSQL 另类性能优化及测试-白红宇

PostgreSQL 另类性能优化及测试

阅读量：2169 次

发布时间：2019-05-01

本文共 2094 字，大约阅读时间需要 6 分钟。

一般查询优化都是针对SQL 进行优化，SQL 改写，添加必要的索引，更有深入的通过理解业务的方式来进行SQL 的优化，今天对PostgreSQL中的SQL进行另类的优化，主要是POSTGRESQL 和其他的都数据库有一点不同的是可以调整Planner cost，而其他的数据库这方面是不能进行调整的。而通过调整Planner Cost 可以导引查询是更愿意走索引还是更原因进行表扫描的方式来查询数据。

下面我们做一个实验我们先按照默认的方式来进行查询，然后我们调整计划成本在做比较。

下面就是要进行测试查询的表

相关的索引已经添加（关键索引例如 rental_id）

查询的时间在 16.324ms 约等于 0.016秒

seq_page_cost = 1.0

random_page_cost = 4.0

在postgresql 中以上两条是可以进行调整的，顺序读取和随机读取的代价默认是 1比4，也就是说postgresql 对于顺序读取比随机读取的性能上要高。

一般为什么会调整这个值，

1 使用的磁盘系统是高速SSD 磁盘系统，在使用SSD 高速磁盘系统中，其实顺序读和随机读的区别不是很大，我们可以调低 random_page_cost的值，以适应更好的磁盘性能

2 降低seq_page_cost 的值可以让系统更愿意去走索引，如果调高这个值，系统认为走索引将可能是昂贵的获取数据的方式。

那我们可以验证一下上面的说法到底是不是正确的

验证，我们调高 seq_page_cost 的值看看是不是有变化标准的值我们已经在上面的图片上贴出

下面我们将seq_page_cost 的值调整到20 下面两个图从图中可以看到原来的执行计划没有改变但cost的值升高了好几倍，而相关的执行时间也变为0.162秒，是原来的10倍

我们继续调整查询，将此参数调整到 100, 然后继续观察，基本上和调整成20的时候，没有太大的差别

调换到10时，也是一样，这三次的调整时间，执行计划都有变化，将原来的seq 方式变为了index_scan 方式

以上的调试，无一例外的改变了执行计划，延长了查询时间

那我们换个思路，我们调低seq_page_cost 的值会怎么样

首先尝试将值调整为 0.8 后，发现查询的时间比原来的1.0时，有所下降

时间0.013秒

默认值是意味着数据库可能掌握的缓存不多，调低意味着更多的数据查询将利用更多的缓存来进行。

后面又尝试极端的0.1这个值，但是结果并没有变得更好。所以调整这个值，和磁盘以及内存的容量都有关系，如果系统不是经常改变，查询的语句也都固化好，其实可以前期对于一些语句进行这方面的测试和优化。

调整random_page_cost 参数能对查询有什么帮助，我们继续来尝试

先将random_page_cost 调整到 10，在还原seq_page_cost 的值，调整random_page_cost 值后，进行查询，发现查询的速度也有所加快，查询的计划并没有改变。

我们继续讲值调整到 100，查询的时间并没有太大的变化与刚才调整到 10进行比较。将值在调整到 7，发现和调整 seq_page_cost有一样的功效，查询也降低到 0.013S 左右。

最后实验的结果是，适当的调低seq_page_cost 或者调高 random_page_cost 对加速查询是有一定帮助的，但需要进行相关的语句测试和对比。

而其他的参数对POSTGRESQL 有什么影响

1 Share buffer

与其他的数据库不同，postgresql 使用了 double 缓冲技术，postgresql本身是不对接I/O的 postgresql的缓冲是对接LINUX的缓冲区，这样的缓冲使用的方法导致 postgresql的缓冲本身并不需要特别大，和其他的数据库都不大一样，但share buffer （version 11)默认只给了128MB ,针对目前的一些应用，可以调整为当前总内存的20-25%，这里借用一个PPT关于share buffer 的测试，可以从图中看出，调整share buffer 对于你的tps的性能增长的关联性。