PostgreSQL postgresql 读书会 一期 5 优化数据库的参数们

313页起

关于硬盘的settings这一段写的比较少,并且写的也不是很清晰,这里进行扩展(扩展的字体为粗体)

在文中提到了fsync setting forces each transaction to be written to the hard disk after each commit.

这个设置在POSTGRESQL中哪里存在书中并未有明确的指出,这里个人认为

synchronous_commit, fsync两个参数 牵扯synchronous_commit 和 Asynchronous commit 的问题.这也是书中提到的 turning this off will increase performance的地方.

在postgresql.conf中synchronous_commit 有几个可以选择的选择项 off , local ,remote_write, remote_apply, on 和 fsync = off or on , 在官方文档中写道 关于 fsync 的内容

If this parameter is on, the PostgreSQL server will try to make sure that updates are physically written to disk, by issuing fsync() system calls or various equivalent methods (see wal_sync_method). This ensures that the database cluster can recover to a consistent state after an operating system or hardware crash.

不直译大致的意思, 这个参数确保你的物理写到磁盘,以备在系统崩溃后,数据库可以恢复一致性状态.

While turning off fsync is often a performance benefit, this can result in unrecoverable data corruption in the event of a power failure or system crash. Thus it is only advisable to turn off fsync if you can easily recreate your entire database from external data.

关闭这个参数是可以提高性能的,但结果是如果出现类似power failure或系统崩溃的情况,你的数据可能会无法恢复.

如果有人提出强大的硬件可以弥补这个问题,官方的文字里有这样一句 High quality hardware alone is not a sufficient justification for turning off fsync.

在上面的文字中提到了synchronous_commit 种会有几种选择,但在没有物理复制的情况下,对于单机来说,也只有两种选择项, OFF ON , 默认是ON 如果你选择OFF 则可能会对于日志的写入会有延迟.所以还是按照默认值来设置,虽然设置OFF 也会有少许的性能提升. 那有人会问性能有多少提升,根据文档提到的最大会有3倍与 wal_writer_delay 的 下面默认的 wal_write_delay是200ms 那我也就只能认为每次写入的延迟的时间就是能提高多少性能的一个指标.

在上面提到了fsync参数=on的情况下, 的几种磁盘交互的方式,在文档中提到在系统编译的情况下,会自动选择一个合适的值来进行设置,默认是fsync,但实际上可能不是这个值.具体请看下面的recommendations

另外一个点是是书中提到的checkpoint_segment , 检查点的不正确的配置会影响PG的数据库的I/O性能.

首先要确认checkpoint 的作用是将缓冲区中的数据刷入到磁盘,此时wal 日志和数据是一致的, 为了性能,数据并不是commit后直接就写入到data文件,而是在缓冲中,到了一定的状态后,在刷入到磁盘中,

这里有对此有影响的参数

min_wal_size

max_wal_size

checkpoint_timeout

min_wal_size 和 max_wal_size 之间是运行dirty page 的大小但实际上可能会在负载较重的情况下,超过max_wal_size.

checkpoint_timeout 是默认的触发checkpoint的时间的设置, 实际上大部分系统在默认的状态下,都能满足系统的需求, 尤其在编译的安装的情况下,postgresql .conf中会自动配置这些参数.

实际上关于磁盘I/O性能在POSTGRESQL中可以配置的影响的参数很多,例如

full_page_writes = on 就是一个争论点, 但实际上大多数的系统是要打开这个点,让数据库在 crash 时可以恢复页面后,在通过wal log对数据库进行恢复操作. 具体原理这里就不展开,之前也写过相关的内容可以去查找.

最终文中提到有bluk insert的情况下可以关闭auto vacuum, 但并未提到有多少bluk insert的量是 100万还是1000万还是一个亿,所以这里也觉得这点有点......

ending 314