postgresql 检查点优化

概述检查点，通俗的理解就是数据库处于数据一致性，完整性的点。 因此在这个点之前提交的事务确保数据已经写入数据文件，事务状态已经写入pg_clog文件。 通常创建检查点会需要一个漫长的过程，那么怎么保证数据的一致性和完整性呢？ 从数据恢复（XLOG）的角度来看，检查点在XLOG文件中分为两个位置，一个是逻辑位置，一个是物理位置。 逻辑位置即开始位置，也是一致性位置，在这个位置之前已提交的事务，确保它们的 检查点，通俗的理解就是数据库处于数据一致性，完整性的点。 因此在这个点之前提交的事务确保数据已经写入数据文件，事务状态已经写入pg_clog文件。 通常创建检查点会需要一个漫长的过程，那么怎么保证数据的一致性和完整性呢？ 从数据恢复（XLOG）的角度来看，检查点在XLOG文件中分为两个位置，一个是逻辑位置，一个是物理位置。 逻辑位置即开始位置，也是一致性位置，在这个位置之前已提交的事务，确保它们的事务状态和脏数据都已经写入持久化存储。 物理位置即结束位置，因为做检查点时，需要将逻辑位置之前已提交事务的事务状态和脏数据都写入持久化存储，这个需要一个过程，这些刷脏页面和CLOG的动作同样会产生XLOG，所以这一系列动作完成后，就是检查点结束的位置，即物理位置。 从逻辑角度来看，这两个XLOG位置实际是同一个位置，所以在做数据恢复时，先找到检查点的XLOG物理位置，然后根据这里的结束检查点时写入的XLOG信息找到逻辑位置，从逻辑位置开始，读取XLOG并实施xlog replay恢复，至少要恢复到XLOG物理位置才能确保数据库的一致性和完整性。 如图： 创建检查点示意图：
数据恢复示意图：

检查点调度的小结： 如果我们开启了检查点调度，默认是开启的，调度系数设置为0.5。 这个调度值到底是什么用意呢？ 检查点的任务之一是刷脏块，例如有1000个脏块需要刷新，那么当刷到100个脏块时，progress=(100/1000)*0.5=0.05 如果这个时候，XLOG经历了10个文件，checkpoint_segments为100，也就是0.1 0.05<0.1,返回false,不休息。什么情况能休息？ 当xlog经历个数比值小于等于0.05时才能休息，也就是发生在XLOG 5个或以内时。 如果调大调度系数到1，那么progress=(100/1000)*1=0.1，当xlog经历个数比值小于等于0.1时才能休息，也就是发生在XLOG10个或以内时。 现在可以理解为，调度系数就是休息区间系数，休息区间为checkpoint_segments和checkpoint_timeout。 调度系数越大，checkpointer休息区间越大，checkpointer可以经常休息，慢悠悠的fsync； 调度系数越小，checkpointer休息区间越小，checkpointer只能在最初的小范围内休息，超过后就要快马加鞭了。 checkpointer刷缓存主要分几个步骤， 1. 遍历shared buffer区，将当前SHARED BUFFER中脏块新增FLAG need checkpoint， 2. 遍历shared buffer区，将上一步标记为need checkpoint的块write到磁盘，WRITE前需要确保该buffer lsn前的XLOG已经fsync到磁盘， 3. 将前面的write sync到持久化存储。 具体耗时可以参考期间的探针，或者检查点日志输出。

本文要说的是full page write的影响，这个影响实际上是非常大的。 1. 写xlog的本质是这样的，数据库在刷shared buffer中的脏块前，必须确保脏块相关的REDO *** 作先写XLOG成功，才能刷脏块。 2. Postgresql的检查点有什么作用呢？ 当数据库crash后需要恢复时，或者因为其他原因需要恢复时，从最后一个检查点开始，读取XLOG并实施恢复。 根据以上特征，为了保证数据的一致性，除了保证XLOG写成功，还要保证数据块是一致的（即刷数据块必须是原子 *** 作，磁盘中这个数据块不能有新老数据共同存在）。 full_page_writes是在文件系统不能保证这种原子 *** 作的前提下设计的，做法就是当刷脏数据块前，如果这个数据块是在检查点后第一次变脏的，那么需要在XLOG中记录下整个数据块的内容。那么在数据恢复时，即使刷脏块不是原子 *** 作也没关系，因为WAL中记录了整个数据块的内容，恢复时会使用xlog中记录的full page覆盖数据文件中的块。 正因为这样，数据块越大，写full page带来的影响也愈大，而且检查点越频繁，WAL内容也会越多（因为FulL PAGE WRITE较多）。 那么怎么样才能安全关闭full_page_writes呢？ 1. 文件系统能帮我们避免partial write。数据文件所在的文件系统可以确保不出现partial write,例如写一个32K的数据块，那么这个写 *** 作必须是原子性的。例如zfs文件系统提供了这样的参数，允许用户打开full page write，如果文件系统打开了这种写，数据库就可以关闭full_page_writes。 2. 硬件支持full page write接口，例如FusionIO,以及宝存的PCI-E SSD硬盘产品，提供了相应的原子写API，(或者它们的原子 *** 作本身就大于数据库的block size)，只要Postgresql 的block_size小于等于硬件能提供的原子写SIZE，使用对应的API后，就可以关闭数据库的full page writes。
如果关闭full_page_writes后，因为硬件问题，或者其他问题导致 *** 作系统Crash,并且在检查点后第一次成为脏块时出现了partial write(data corruption)。(例如32KB的数据块，其中有一些是未改写(old)，一些已改写(new)) 那么怎么办？ 如果真的这样了的话，查询数据时，遇到这种数据块可能报错，可以设置zero_damaged_pages来跳过这种数据块。另一方面，XLOG必须是顺序写入的，所以有一个锁保护，因此在write wal to wal buffer时，需要加这个锁。 也就是说，写WAL越慢，TPS会越低，即使是异步（因为wal异步虽然不需要等flush wal to disk，但是也要保证写wal buffer完成。） 我们来做一个测试，重新启动数据库，刚启动时，数据库会做一个启动检查点，所以第一次的脏块需要写full page： 为了规避shared buffer的影响，我们先使用prewarm把数据加载到shared buffer中

          $ pg_ctl start                             $ psql                   digoal=> select *from pg_prewarm('tbl');               pg_prewarm               ------------                 122933               (1 row)               'tbl_pkey'   34023               )        

开始测试 $ cat test.sql \setrandom ID 50000000 update tbl set crt_time=Now()where ID :ID ;

可以看到，测试数据和做检查点时是一样的，性能逐渐上升，原因就是一开始的WAL要写full page，影响了性能。 $ pgbench -M prepared n r f ./testsql P c 28j T 1000000 progress1.0 s,14744.4 tps lat 1.865 ms stddev 4.009 2.016811.31.6684.304 3.019606.21.4153.358 4.023229.71.2142.922 5.027691.01.0012.356 6.034756.00.8101.685 7.046214.10.6040.839 8.054729.70.5100.358 9.056774.40.4910.324 10.057470.00.4850.330 11.057342.10.4870.349 12.058505.20.4770.323 13.058999.80.4730.315 14.059607.80.4680.310 15.059006.10.328 16.059519.817.059787.60.46618.059188.90.47119.059626.90.321 20.061206.30.4560.297 21.059606.70.318 22.060024.80.4650.316

热数据越多，这种“热身”效应越明显。
现在我把full page writes关闭，再次测试： full_page_writes off pg_ctl restart m fast $psql ) 可以看到，“热身”效应没有了，因为WAL不再需要写full page了。 73337.20.3750.350 68862.40.40564543.70.43262050.60.4490.325 61312.00.4550.316 60668.80.46059014.30.331 60419.30.4620.307 60216.90.46359478.20.46960376.40.301 59792.659747.60.46760387.00.304 59698.859928.50.313 60510.50.302

当full page writes关闭时：

              checkpoint start                    checkpoint done                    pID7976 tID min17660 max avg sum count1                    pID87223902179506087972339179954265896                    87373804239791589212373901808266084                    87263812154405188762364795050266418                    87363932158587788332354577217266553                    8706224236987762340338511266651                    87393913153355488902371167014266720                    86983872214182487592337391283266851                    87323834142614788962375078067266953                    87353876425365589082378517468266998                    8695225684888532364436879267057                    87113883284095587922349657964267224                    86943947268414788192357223023267266                    87183846166626789242385454634267279                    87343835266375687982352532736267382                    86933830191564587642345468816267619                    8738218752188952380585848267620                    87053906257939389642399871667267717                    8728240575588392366833087267749                    87293853161330389472396649611267854                    8730259046189032385215913267903                    87193819273746186962329969230267918                    8708390916140572398100004268029                    8717385721581332359353315268151                    87333831169471488892386096329268426                    8709150195289302402379420268997                    8704159399688732389259952269254                    87143850142807988692388105216269263                    87013860163739888942396702871269470      

当full page writes开启时，wal写的平均时间比关闭full page writes时长很多：

8887203881844018050361002                    900411571226607938435630636                    88053545184792088231280653201145139                    89143478195537124663310678936832228995                    896533328682375679228772302245661                    8986380532983019373699208748829246421                    8969382433472947372429185048728246626                    899032383832372709202617869246912                    895432199797371519175503504246976                    8991379733125798371569179089256247040                    8952378233192867374469251197245247049                    8958380133094778373379232970224247287                    8979384233224595370799171570876247351                    8984378633242997370909176002582247396                    894532980512370639170007762247413                    8978383833609199374529270596686247530                    8981375933190956369159140303488247598                    8993386433163898371789209639310247715                    898033197079370719184138362247739                    8973380832933431370149174981958247876                    896233110329371889218350426247880                    8982382033107999369699173436495248136                    897432363107371289213153086248140                    894932520297372669250689544248229                    8983382732591534370859208350649248303                    8992385433391344371979240135638248407                    8988377533189136368889166448605248489                    8985382332310200367149124542692248528                    8987379332861463370679214883458248599                    32963347369849196461264248654                    8989379033236923368829171642674248674                    895532400840369019210718295249601                    3880458044388283783136090428524                    353977288883821238551429931                    438744587773774082384429974                    27752683849426661430127                    277565588513809751808430398                    271214787863781992446430441                    3868424414187613774796287430843                    22969433829627946430900                    332676388033794778852431045                    3944333177088373809602504431094                    234016387453770891213431177                    -1310289386088813829692548431180                    3781271000589293851335374431306                    330366087663781832008431400                    86993754114431431508                    3856731667088993841025315431579                    21560623867736316431763                    3847521523431890                    88253812586458431990                    3821431064432596                    305162188793841527531432620                    44604233767820516432669                    350048688853844851608432716                    294225188613838738375433191                    3845272785287903808255298433241                    1436150135063853784088433605                    320657688463838045173433846                    22632212816189163869261657433926      

最后，即使我们关闭了full page writes,在某些情况下也会写full page,那就是打开在线备份时做的那个检查点。 因此，我最后做一个测试来验证一下，开始备份后，性能应该会受到wal write full page影响而下降： off wal_level hot_standby archive_mode on archive_command '/bin/date' 准备热备: =# select pg_start_backup('Now()');

影响又回来了 progress38.060170.00.339 39.048121.90.5801.518 40.015061.91.8394.240 41.017799.11.5853.630 42.020799.11.3373.151 43.024623.81.1432.766 44.028381.10.9762.944 45.038196.20.7371.874 46.045302.80.6081.484 47.064550.10.4380.653

最后提供一些优化建议： 1. 配置合理的shared buffer，1/4内存，但是不建议超过热数据大小。 2. 如果开启了异步提交，修改一下on schedule checkpoint算法，参考我前面的文章。 3. 配置合理的checkpoint_segments,checkpoint_timeout,checkpoint_completion_target。 checkpoint_segments建议和shared buffer一样大，例如shared buffer=8G,wal segment=16MB,checkpoint_segments=8G/16MB=512 checkpoint_timeout设置为大于生成checkpoint_segments即512个xlog的时间周期。 4.checkpoint_completion_target根据IO能力进行调整，调整到checkpoint不影响业务为宜。 checkpoint_completion_target越大，对IO影响越小，但是checkpoint周期越长，需用户自己权衡。 5. 如果你不能容忍检查点给wal 带来的full page write。建议采购可以实现原子写的硬件设备，或者使用支持full page write的文件系统。 如果你的数据库对数据一致性要求不是那么高，也可以冒险直接关闭full page writes，仅仅当检查点后第一次变脏的数据块出现partial write时才会导致这个数据块的数据不一致。 6. 对于内存较大场景，建议使用32KB的block size。 7. initdb -k这个参数只是打开数据块的校验，不是来防止partial write的，而是用来检查数据块是否出现了partial write或其他异常的。还可以用来做检测块级别的纂改等。一般不建议打开，因为对性能影响较大。 8. full page writes带来的性能影响如何计算呢？实际是和连续写几个wal block size大小的能力有关，如果block_size=32K,wal_block_size=8K,那么一个脏块的full page write需要写4个wal_block_size，假设wal fsync能力是每秒写10000个8K的块，那么检查点后的写 *** 作如果全部都发生在不同的数据块上面(就比如我前面的update测试用例)，写WAL能力下降可能导致tps降到2500以下。如果开启了异步wal的话，这个时候就是检验内存的写4个wal_block_size能力。 转载自： http://blog.163.com/digoal@126/blog/static/163877040201542103933969/ http://blog.163.com/digoal@126/blog/static/1638770402015463252387/ http://blog.163.com/digoal@126/blog/static/16387704020154651655783/ http://blog.163.com/digoal@126/blog/static/16387704020154653422892/ http://blog.163.com/digoal@126/blog/static/16387704020154811421484/ http://blog.163.com/digoal@126/blog/static/16387704020154129958753/ 总结

以上是内存溢出为你收集整理的postgresql 检查点优化全部内容，希望文章能够帮你解决postgresql 检查点优化所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。