PostgreSQL 如何面对高压力下的写操作的优化

数据库的重要性能指标中有一项对于高并发下的数据库写操作,不少数据库都对此有执念,一秒钟写入的数据量是多少,并为此而自豪. 数据的写入在单位时间中的确是很重要的. POSTGRESQL 怎么能应对高并发下的写操作,并且在不改变目前的硬件的条件的基础上, 怎么进行优化.

我们来捋一捋, PostgreSQL 在写入数据时有那些写操作

简单的捋了捋POSTGRESQL 数据写入的几个方向

1 日志的方向, POSTGRESQL 的日志本身写入的量根据相关的设定越详细越大, 可以将日志与数据磁盘分离,使用不同的磁盘来存储数据,分散数据写入的压力.

2 数据写入是这里面的重要的问题,而写入数据时与数据库系统的一些性能优化参数有关,后面我们详细来说

3 WAL LOG 日志的写入和数据写入是对应的,单位时间写入的数据量越大,则WAL LOG 单位时间写入的数据也会很大.

那么我们来根据以上三点来看看如何优化, 日志的问题已经解决,不能因为减少日志数据的写入,而降低日志的某些级别, 所以划分一块独立的磁盘给日志写入,分散压力.

剩下的就是数据磁盘的问题了, 这里我们探讨的基础是传统机械磁盘,而不是SSD. 那么基于传统磁盘的情况下, 写入数据顺序写比随机写要好的多,所以数据库会设立缓存,有checkpoint 将数据在某些条件下,刷入磁盘系统.

当我们看到如下的日志文件时,说明checkpoint 的次数在单位时间里,有问题了,我们就需要调整相关CHECKPOINT 的参数

LOG: checkpoints are occurring too frequently (9 seconds apart) HINT: Consider increasing the configuration parameter “max_wal_size”. LOG: checkpoints are occurring too frequently (2 seconds apart) HINT: Consider increasing the configuration parameter “max_wal_size”. select name, setting from pg_settings where name like ‘%wal_size%’

or name like ‘%checkpoint%’ order by name;

优化点 1

wal_level[1] : 曾经见过这个位置的设置为logical ,而实际上数据库中并未使用逻辑复制的功能. 如果是单机可以使用minimal 的选项, 如果是纯streaming 的方式复制,选择replica 就好, 没有必要选择logical , logical 中使用的添加了逻辑复制需要的编码信息,无形中造成WAL 日志的增大.

优化点 2

max_wal_size[2] : 这个参数主要的设置起因是在CHECKPOINT 点之间能承受最大的WAL 日志的大小. 这个参数的大小控制着CHECKPOINT 的频率,如果系统写入的WAL 日志多, 则达到MAX_WAL_SIZE 的值就会触发CHECKPOINT. 当然这里也不是设置的越大越好,越大会增加系统CRASH 恢复的时间.

优化点 3

checkpoint_completion_target[3] : 此参数主要的意义有两个 1 控制checkpoint 何时开始将数据刷新到磁盘, 同时还控制数据写入时长, 如checkpointtimeout , checkpoint_completion_target * checkpoint_timeout 是每次CHECKPOINT 最大允许的时间. 将checkpoint_completion target 调整超过默认的0.5 会给每次checkpoint 数据更多的时间.

优化点 4

wal_buffers[4] : 除了数据刷入磁盘,WAL LOG 写入磁盘也是有缓冲的,尤其对于高并发的系统WAL LOG 的量也一定小不了, 所以利用WAL BUFFERS 来进行日志的缓冲也是有必要的,默认 WAL BUFFERS 是-1 占整体的shared buffers 3%, 这里一般来说可以不进行更改, 但如果观察到日志写入频繁也可以将一个固定的值给wal_buffers ,但特别大的值没有必要,主要在于每次commit 就会将wal_buffer 的数据刷入磁盘,这里wal buffer 主要针对大事务产生大量的WAL LOG. 具体看数据库上承载的业务系统的状态.

优化点 5

commit_delay[5]: 这个参数与上面的wal buffer 可以联合使用,主要的作用是提高每次wal log 刷入磁盘的效率. 提高commit_delay 值有利于合并commit 后需要刷新到磁盘的WAL LOG 数据, 通过合并刷新数据达到更高效的利用磁盘写入的目的,多个指令一次下发同时刷新.

优化点 6

archive_command[6] : 归档作为优化磁盘性能最后一个部分,其实首先也需要将ARCHIVE DIR 放置在和数据磁盘相对独立的磁盘环境上. 另外也可以在归档命令中如果使用cp 命令可以使用cp的always 模式, 提高复制的效率,具体cp 命令的 always模式可以自行百度. cp —sparse=always