减少postgresql wal日志配置

最新推荐文章于 2025-04-27 11:21:11 发布

wangdeng1314

最新推荐文章于 2025-04-27 11:21:11 发布

阅读量1.5k

点赞数

分类专栏： PostgreSql

PostgreSql 专栏收录该内容

37 篇文章

订阅专栏

转载于德哥：

http://blog.163.com/digoal@126/blog/static/163877040201301522423261/?COLLCC=3918643875&suggestedreading&wumii

reduce PostgreSQL pg_xlog full page writes

2013-01-15 14:31:56| 分类： PgSQL PerfTuning |字号订阅

pg_xlog 是 PostgreSQL 可靠性的保证.

今天群里的兄弟聊到归档文件颇多, 如何减少的问题.

简单的方法是通过调整shared_buffer, checkpoint间隔.

如下 :

【场景1】 :

vi postgresql.conf

    
    
     
     shared_buffers = 32MB
    
    
    
    
     
     checkpoint_segments = 3 
    
    
    
    
     
     checkpoint_timeout = 1min

重启数据库.

创建测试表 :

    
    
     
     digoal=> create table test (id int primary key, info text);
    
    
    
    
     
     NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
    
    
    
    
     
     CREATE TABLE
    
    
    
    
     
     digoal=> insert into test select generate_series(1,1000000),'DIGOAL';
    
    
    
    
     
     INSERT 0 1000000

创建检查点 :

    
    
     
     postgres=# checkpoint;
    
    
    
    
     
     CHECKPOINT

记下当前的xlog位置.

    
    
     
     postgres=# select pg_current_xlog_location();
    
    
    
    
     
      pg_current_xlog_location 
    
    
    
    
     
     --------------------------
    
    
    
    
     
      96E8/6ABEB740
    
    
    
    
     
     (1 row)

pgbench脚本 :

    
    
     
     ocz@db-172-16-3-150-> cat test.sql
    
    
    
    
     
     \setrandom id 1 1000000
    
    
    
    
     
     update test set info=clock_timestamp()::text where id=:id;

pgbench测试, 处理80万更新事务 :

    
    
     
     ocz@db-172-16-3-150-> pgbench -M prepared -n -r -f ./test.sql -c 8 -j 4 -t 100000 -U digoal digoal
    
    
    
    
     
     transaction type: Custom query
    
    
    
    
     
     scaling factor: 1
    
    
    
    
     
     query mode: prepared
    
    
    
    
     
     number of clients: 8
    
    
    
    
     
     number of threads: 4
    
    
    
    
     
     number of transactions per client: 100000
    
    
    
    
     
     number of transactions actually processed: 800000/800000
    
    
    
    
     
     tps = 4833.566032 (including connections establishing)
    
    
    
    
     
     tps = 4833.864802 (excluding connections establishing)
    
    
    
    
     
     statement latencies in milliseconds:
    
    
    
    
     
             0.002287        \setrandom id 1 1000000
    
    
    
    
     
             1.640042        update test set info=clock_timestamp()::text where id=:id;

测试完后创建检查点, 并查看当前的xlog位置 :

     
     
      
      
       
       postgres=# checkpoint;
      
      
      
      
       
       CHECKPOINT
      
      
     
     
     
     
      
      digoal=# select pg_current_xlog_location();
     
     
     
     
      
       pg_current_xlog_location 
     
     
     
     
      
      --------------------------
     
     
     
     
      
       96E9/1E7DAF50
     
     
     
     
      
      (1 row)

计算历经了多少的XLOG数据, 如下 2860MB. :

    
    
     
     digoal=# select pg_xlog_location_diff('96E9/1E7DAF50', '96E8/6ABEB740')/1024/1024;
    
    
    
    
     
            ?column?        
    
    
    
    
     
     -----------------------
    
    
    
    
     
      2859.9355621337890625
    
    
    
    
     
     (1 row)

【场景2】

vi $PGDATA/postgresql.conf

修改以下三个参数的值 :

    
    
     
     shared_buffers = 4096MB
    
    
    
    
     
     checkpoint_segments = 256
    
    
    
    
     
     checkpoint_timeout = 5min

xlog文件大小16MB, shared_buffers=4096MB, checkpoint_segments=shared_buffers/xlog文件大小=256;

这几个参数需要配合调节.

重启数据库 :

    
    
     
     ocz@db-172-16-3-150-> pg_ctl stop -m fast
    
    
    
    
     
     waiting for server to shut down.... done
    
    
    
    
     
     server stopped
    
    
    
    
     
     ocz@db-172-16-3-150-> pg_ctl start
    
    
    
    
     
     server starting
    
    
    
    
     
     ocz@db-172-16-3-150-> LOG:  00000: loaded library "pg_stat_statements"
    
    
    
    
     
     LOCATION:  load_libraries, miscinit.c:1249

创建检查点, 记录当前的xlog位置:

    
    
     
     ocz@db-172-16-3-150-> psql
    
    
    
    
     
     psql (9.2.1)
    
    
    
    
     
     Type "help" for help.
    
    
    
    
     
     postgres=# checkpoint;
    
    
    
    
     
     CHECKPOINT
    
    
    
    
     
     postgres=# select pg_current_xlog_location();
    
    
    
    
     
      pg_current_xlog_location 
    
    
    
    
     
     --------------------------
    
    
    
    
     
      96E9/25FE5558
    
    
    
    
     
     (1 row)

pgbench测试, 处理80万更新事务 :

    
    
     
     ocz@db-172-16-3-150-> pgbench -M prepared -n -r -f ./test.sql -c 8 -j 4 -t 100000 -U digoal digoal
    
    
    
    
     
     transaction type: Custom query
    
    
    
    
     
     scaling factor: 1
    
    
    
    
     
     query mode: prepared
    
    
    
    
     
     number of clients: 8
    
    
    
    
     
     number of threads: 4
    
    
    
    
     
     number of transactions per client: 100000
    
    
    
    
     
     number of transactions actually processed: 800000/800000
    
    
    
    
     
     tps = 35387.346131 (including connections establishing)
    
    
    
    
     
     tps = 35405.115679 (excluding connections establishing)
    
    
    
    
     
     statement latencies in milliseconds:
    
    
    
    
     
             0.002269        \setrandom id 1 1000000
    
    
    
    
     
             0.202819        update test set info=clock_timestamp()::text where id=:id;

测完后, 创建检查点并记录当前的xlog位置 :

    
    
     
     postgres=# checkpoint;
    
    
    
    
     
     CHECKPOINT
    
    
    
    
     
     postgres=# select pg_current_xlog_location();
    
    
    
    
     
      pg_current_xlog_location 
    
    
    
    
     
     --------------------------
    
    
    
    
     
      96E9/35B5FB80
    
    
    
    
     
     (1 row)

计算历经了多少的xlog, 如下251MB.

    
    
     
     postgres=# select pg_xlog_location_diff('96E9/35B5FB80', '96E9/25FE5558')/1024/1024;
    
    
    
    
     
            ?column?       
    
    
    
    
     
     ----------------------
    
    
    
    
     
      251.4780654907226563
    
    
    
    
     
     (1 row)

执行同样的事务量, xlog从2860MB下降到251MB.

【小结】

1. 通过减少block_size的大小也是方法之一, 当然效果没有以上的方法明显. 并且程序编译完后就没有办法修改了.

2. 检查点的间隔太长虽然减少了xlog的产生, 但是如果数据库异常关闭(-m immediate)或者服务器异常导致服务器异常关闭后, 在数据库启动时需要recovery, 这个过程也会拉长. 因为要应用更多的变更.

【参考】

1. http://www.postgresql.org/docs/9.2/static/wal-configuration.html

为什么调了这几个参数能减少xlog的量, 如下 :

    
    
     
     If full_page_writes is set (as is the default), there is another factor to consider. To ensure data page consistency, the first modification of a data page after each checkpoint results in logging the entire page content. 
    
    
    
    
     
     In that case, a smaller checkpoint interval increases the volume of output to the WAL log, partially negating the goal of using a smaller interval, and in any case causing more disk I/O.