PostgreSQL数据库调优

发表于： 2023-08-17 17:27:04
分类：网络资源
浏览：浏览(72)

概述

在硬件层面，影响数据库性能的主要因素有CPU、 I/O、内存和网络；在软件层面则要复杂得多，操作系统配置、中间件配置、数据库参数配置、运行在数据库之上的查询和命令等，随着业务增长、数据量的变化，应用复杂度变更等种种因素的影响，数据库系统遇到瓶颈，运行在不健康状态的情况也很多。硬件不同，应用类型不同，优化方法也不同。

硬件与操作系统

存储

生产环境通常使用固态磁盘，如目前使用广泛的SATASSD和PCieSSD。建议使用企业级SSD；或使用外部存储设备，例如SAN（存储区域网络）和NAS（网络接入存储）

CPU

服务器在BIOS中如可设置CPU的性能模式，可能的模式有高性能模式、普通模式和节能模式，建议使用高性能模式

内存

较大的内存明显降低服务器的I/O压力，缓解CPU的I/O等待时间，对数据库性能起着关键作用。需要密切监控容量和指标趋势，在适当的时候进行扩容和升级。

I/O调度算法

磁盘I/O通常是数据库服务器主要瓶颈，调整调度算法提高数据库服务器性能。对于数据库的读写操作，Linux系统在收到数据库的请求时，内核并不立即执行请求，而是通过I/O调度算法，先尝试合并请求，再发送到块设备中。

预读参数

在内存中读取数据比从磁盘读取要快，增加Linux内核预读，对于大量顺序读取的操作，可有效减少I/O等待时间。

Swap

当内存不足时，操作系统会将虚拟内存写入磁盘进行内存交换，而数据库并不知道数据在磁盘中，这种情况下就会导致性能急剧下降，甚至造成生产故障。有些系统管理员会彻底禁用Swap，但如果这样，一旦内存消耗完就会导致OOM（内存溢出），数据库就会随之崩溃。

透明大页

透明大页（TransparentHugePages）在运行时动态分配内存，而运行时的内存分配会有延误，对于数据库来说并不友好，建议关闭透明大页。

NUMA架构

NUMA（Non-Uniform Memory Access）不一致内存访问。这意味着NUMA架构内存比特定CPU访问的本地内存更复杂。

——————

数据库

全局参数

vim postgresql.conf 修改相应参数，重新启动数据库服务

统计信息查询计划

数据库在运行期间会收集大量的数据库、表、索引的统计信息，查询优化器通过这些统计信息估计查询运行时间，然后选择最快的查询路径。

索引

索引对数据库应用性能至关重要。针对不同的使用场景数据库有许多种索引类型来应对，例如B-tree、 Hash、 GiST、 SP-GiST、GIN、 BRIN和Bloom等等

其它

除此之外，定时进行VACUUM操作和ANALYZE操作，在业务低谷时段定时进行VACUUMFREEZE操作，及时处理慢查询，硬件的监控维护也很重要，性能调优不是一次性，也不能在性能表现已经很差的时候才去做，好的监控系统，历史数据的分析，慢查询的优化都需要不断完善，才能保障业务系统稳定高效运转。

实测

在本地建两个虚拟机local1,local2，分别安装了postgres，local1做了优化，local2不动；通过300s数据压测，参考batchinsert.md，对比观察调参前后性能变化。

local2：未调参

progress: 298.0 s, 23571.1 tps, lat 2.123 ms stddev 0.731
progress: 299.0 s, 23861.0 tps, lat 2.095 ms stddev 0.879
progress: 300.0 s, 22843.2 tps, lat 2.190 ms stddev 1.679
select count(*) from test;

  count  

 6838045
(1 row)

local1：调参

progress: 298.0 s, 52168.1 tps, lat 0.958 ms stddev 0.850
progress: 299.0 s, 53477.8 tps, lat 0.935 ms stddev 0.618
progress: 300.0 s, 53453.2 tps, lat 0.936 ms stddev 0.613
postgres=# select count(*) from test;
count 
15483178
(1 row)

300s数据压入测试：每秒钟处理事务量与实际数据量，提升126%

以下是调参、监控、优化的一些方法：

1、top监控

Linux操作系统提供了非常多的性能监控工具，可以全方位监控CPU、内存、虚拟内存、磁盘I/O、网络等各项指标，为问题排查提供了便利。常用的性能检测i具，例如top、 free、 vmstat、 iostat、 mpstat、 sar, pidstat等
top命令是最常用的性能分析工具，它可以实时监控系统状态，输出系统整体资源占用状况以及各个进程的资源占用状况。

top - 08:58:35 up 4 min, 1 user, load average: 0.07, 0.26, 0.13
Tasks: 176 total, 1 running, 175 sleeping, 0 stopped, 0 zombie
%Cpu(s): 0.0 us, 0.0 sy, 0.0 ni,100.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 3865308 total, 2802976 free, 307860 used, 754472 buff/cache
KiB Swap: 2097148 total, 2097148 free, 0 used. 3174236 avail Mem

看晕了吧，翻译一下：

top - 08:58:35 up 4 min ——开机时间为08:58:35，系统已开机运行4分钟了
1 user ——有1个用户登录了操作系统
load average: 0.07, 0.26, 0.13 ——1分钟、5分钟、15分钟的系统负载分别是0.07, 0.26, 0.13
Tasks: 176 total ——当前一共有176 个进程，

1 running 1个正在运行
175 sleeping 175个在睡眠，
0 stopped 0个进程停止，
0 zombie 0个僵尸进程
%Cpu(s):
0.0 us 用户CPU占用0%
0.0 sy 内核CPU占用 0.0%
0.0 ni 特定优先级的进程CPU占用0.0%0.0%
100.0 id 空闲CPU为100%
0.0 wa IO等待的CPU占用0.0%
0.0 hi 硬中断CPU占用0.0%
0.0 si 软中段CPU占用0.0%
0.0 st 虚拟机盗取占用0.0%
KiB Mem :

3865308 total 共有内存3865308k 约为3.69GB
2802976 free 空闲内存约为2.67GB
307860 used 在用内存约为0.29GB
754472 buff/cache 缓存约为0.72GB

注：total=free+used+buff/cache
KiB Swap:

2097148 total 交换区总量约为2GB
2097148 free 空闲交换区总量约为2GB
0 used. 在使用的交换区总量0k
3174236 可用交换取总量约为3G

在top里我们要时刻监控第五行swap交换分区的used，如果这个数值不断变化，说明内核在不断进行内存和swap的数据交换，这是真的是内存不够用了。

2、释放缓存

free -g

显示当前系统的内存使用情况

free命令的输出结果中，可用内存(used)如果越来越少，通常都是因为缓存；

是否使用到Swap可以作为判断内存是否够用的一个简单标准；

只要没有使用到Swap如果想把缓存释放出来，可以使用命令：

sync
echo '1'>/proc/sys/vm/drop_caches
* 如遇Permission denied，切换到超级用户（root）下执行

生产环境释放缓存的命令要慎用

3、优化内存

Swap

内存方面，对数据库性能影响最恶劣的就是Swap了。

因此，要么我们一方面要求有足够大的内存给数据库，另一方面，系统上线前，监控一段时间，尽可能保障不出现OOM情况，使用free -g查看内存的使用情况，Swap的used列为0，正常；

free -g
              total        used        free      shared  buff/cache   available
Mem:              3           0           2           0           0           3
Swap:             1           0           1

那么应该在有可用内存时释放己使用的Swap，释放Swap的过程实际上是先禁用Swap，再启用Swap的过程。

使用root帐号禁用Swap

swapoff -a

启用Swap的命令是

swapon -a

重点：追求性能：关闭swap，追求安全：开启swap，两者兼顾的做法：关闭swap，提供较大的内存，监控以确保不出现内存溢出

透明大页

透明大页（TransparentHugePages）在运行时动态分配内存，而运行时的内存分配会有延误，对于数据库来说并不友好，建议关闭透明大页。

查看透明大页的系统配置：

cat /sys/kernel/mm/transparent_hugepage/enabled

[always] madvise never 输出中方括号包围的值就是当前值

关闭透明大页

echo never>/sys/kernel/mm/transparent_hugepage/enabled

永久禁用透明大页

编辑/etc/rc.local，追写入

if test -f /sys/kernel/mm/transparent_hugepage/enabled; then 
echo never>/sys/kernel/mm/transparent_hugepage/enabled 
fi 
if test - f /sys/kernel/mm/transparent_hugepage/defrag; then 
echo never > /sys/kernel/mm/transparent_hugepage/defrag 
fi

4、修改磁盘调度算法

查看当前系统支持的调度算法：

dmesg|grep -i scheduler

1 noop（实现简单的FIFO，基本的直接合并与排序，适合SSD）
2 anticipatory（延迟I/O请求，可进行临界区的优化排序）
3 deadline（对anticipatory缺点进行改善，降低延迟时间，适合虚拟机所在宿主机器或I/O压力较重的场景，比如数据库服务器）
4 cfq（均匀分配I/O带宽，公平机制，系统默认）

查看磁盘sda的I/O调度算法：

cat /sys/block/sda/queue/scheduler 
noop anticipatory deadline [cfq]

方括号括起来的值就是当前sda磁盘所使用的调度算法。

临时修改l/O调度算法

echo deadline>/sys/block/sda/queue/scheduler

长期修改l/O调度算法

shell命令修改的调度算法，在服务器重启后就会恢复到系统默认值，永久修改调度算法需要修改/etc/grub.conf文件。

如找不到，可使用下面命令修改，如：

grubby --update-kernel=ALL --args="elevator=deadline"
或
grubby --update-kernel=ALL --args="elevator=cfq"

reboot
cat /sys/block/sda/queue/scheduler

5、修改预读磁盘扇区参数

在内存中读取数据比从磁盘读取要快，增加Linux内核预读，对于大量顺序读取的操作，可有效减少I/O等待时间。

/sbin/blockdev --getra /dev/sda
8192

默认为256，在较新的服务器上，可以设置到16384或更大。

查看

临时修改

/sbin/blockdev --setra 16384 /dev/sda
或
echo 16384 /sys/block/sda/queue/read_ahead_kb

长期有效

把配置追写入/etc/rc.local 文件，还可以对多块磁盘设置该值：

echo "/sbin/blockdev --setra 16384 /dev/sda /dev/sda1 /dev/sda2" >>/etc/rc.local
reboot
/sbin/blockdev --getra /dev/sda

6、开关NUMA架构

NUMA（Non-Uniform Memory Access）不一致内存访问。这意味着NUMA架构内存比特定CPU访问的本地内存更复杂。

查看是否为NUMA架构

numactl --hardware
available: 2 nodes (0-1)
.....

如有多个nodes，即为NUMA架构

调优

1 优化方案：postgres在node0中执行：

0：内核将检查是否有足够的可用内存供应用进程使用；如果有足够的可用内存，内存申请允许；否则，内存申请失败，并把错误返回给应用进程。

1：内核允许分配所有的物理内存，不管当前内存状态如何

2：内核允许分配超过所有物理内存和交换空间总和的内存

查看

cat /proc/sys/vm/overcommit_memory

修改

echo '1'>/proc/sys/vm/overcommit_memory

永久生效

echo "overcommit_memory = 0" >> /etc/sysctl.conf

3 zone_reclaim_mode

zone_reclaim_mode当区域内存不足时，允许设置强制方法回收内存。如果设置为0，则不会发生区域回收。系统中的其他区域/节点将满足分配。

1 = Zone reclaim on 未使用的页面缓存页，性能降低
2 = Zone reclaim writes dirty pages out 写入脏页，限制了进程降低单个进程的性能，不影响其他节点上其他进程性能
4 = Zone reclaim swaps pages 交换页限制对本地节点的分配，除非内存策略或CPU使用配置显式重写

查看

cat /proc/sys/vm/zone_reclaim_mode

修改

postgres在很大程度上依赖于文件系统缓存，在这种情况下，需要禁用区域回收模式。

echo '0'>/proc/sys/vm/zone_reclaim_mode

永久生效

echo "vm.zone_reclaim_mode = 0" >> /etc/sysctl.conf

如未关闭本架构，可以临时修改numa内存分配策略

如 interleave=all （在所有node节点进行交织分配的策略）

numactl --interleave=all /usr/pgsql-14/bin/pg_ctl start -D ../data

关闭NUMA

方法一：通过bios关闭

BIOS:interleave = Disable / Enable

方法二：通过OS关闭

1、编辑 /etc/default/grub 文件，加上：numa=off

GRUB_CMDLINE_LINUX="crashkernel=auto numa=off rd.lvm.lv=centos/root rd.lvm.lv=centos/swap rhgb quiet"

2、重新生成 /etc/grub2.cfg 配置文件：

#grub2-mkconfig -o /etc/grub2.cfg

7、调整全局参数

vim postgresql.conf 修改相应参数，重新启动数据库服务

shared_buffers：共享缓存

数据缓存尽量大，通常为实际内存的10％是合理的，如50000(400M)

work_mem：工作内存

增加work_mem提高排序操作速度。通常为实际内存的2% -4%，如81920(80M)

effective_cache_size：有效缓存

比如有4G内存，可以设置为3.5G(437500)

maintence_work_mem：维护工作内存

在CREATE INDEX, VACUUM等时用到，频率不高，消耗大，给较大内存，如512M(524288)

max_connections：最大数据连接数

不能大于实际内存 /工作内存，如50

random_page_cost：随机访问磁盘块的代价估计。

参数的默认值是4，如果使用固态磁盘，建议将它设置为比seq_page＿cost稍大即可

8、JIT编译

JIT实现支持对表达式计算以及元组拆解的加速。增强长时运行CPU密集查询。

使用前

EXPLAIN ANALYZE	SELECT SUM(relpages) from pg_class;

使用后

Set jit_above_cost =10;
EXPLAIN ANALYZE	SELECT SUM(relpages) from pg_class;

对4000万条记录库操作测试Planning time提升了千倍，Execution time 数百倍，未来更多操作有计划采用这种技术加速

赞(0)
踩(0)

免责声明：

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源，请您支持作者，购买正版内容。

6、资源失效，请下方留言，欢迎分享资源链接

文章评论

共0条评论