postgresql 9.6 的 wal_compression 设置为 on/off 的对比

wal_compression = off

–查看当前wal_compression设置

select *
from pg_settings ps
where 1=1
and ps.name like '%compress%'
;

name : wal_compression
setting : off

–查看最近的wal

select t1.file,
       t1.file_ls,
       (pg_stat_file(t1.file)).modification as last_update_time,
       round( ((pg_stat_file(t1.file)).size)/1024/1024*1.0,1) as log_size_mb
  from (select dir||'/'||pg_ls_dir(t0.dir) as file,
               pg_ls_dir(t0.dir) as file_ls
          from ( select '/var/lib/pgsql/9.6/data/pg_xlog'::text as dir
                 --需要修改这个物理路径
                    ) t0
            ) t1 
  where 1=1
  order by (pg_stat_file(file)).modification desc
;

file : /var/lib/pgsql/9.6/data/pg_xlog/0000000100000005000000DA
file_ls : 0000000100000005000000DA
last_update_time: 2017-12-06 13:58:43
log_size_mb : 16

–创建测试表

create table tmp_wal_compress (
 id int8,
 random_char  varchar(50),
 random_int   int8
)
;

–切换pg_xlog

select pg_switch_xlog(); 

–插入100w条数据

insert into tmp_wal_compress
(id,
 random_char,
 random_int
)
SELECT generate_series(1,1000000) as key,
       md5( (random()*1000000)::text) as random_char,
       (random()*1000000000.)::int8 as random_int

–查看最近的wal

select t1.file,
       t1.file_ls,
       (pg_stat_file(t1.file)).modification as last_update_time,
       round( ((pg_stat_file(t1.file)).size)/1024/1024*1.0,1) as log_size_mb
  from (select dir||'/'||pg_ls_dir(t0.dir) as file,
               pg_ls_dir(t0.dir) as file_ls
          from ( select '/var/lib/pgsql/9.6/data/pg_xlog'::text as dir
                 --需要修改这个物理路径
                    ) t0
            ) t1 
  where 1=1
  order by (pg_stat_file(file)).modification desc
;

file : /var/lib/pgsql/9.6/data/pg_xlog/0000000100000005000000E1
file_ls : 0000000100000005000000E1
last_update_time: 2017-12-06 14:03:23
log_size_mb : 16

生成了7个wal

/var/lib/pgsql/9.6/data/pg_xlog/0000000100000005000000E1
/var/lib/pgsql/9.6/data/pg_xlog/0000000100000005000000E0
/var/lib/pgsql/9.6/data/pg_xlog/0000000100000005000000DF
/var/lib/pgsql/9.6/data/pg_xlog/0000000100000005000000DE
/var/lib/pgsql/9.6/data/pg_xlog/0000000100000005000000DD
/var/lib/pgsql/9.6/data/pg_xlog/0000000100000005000000DC
/var/lib/pgsql/9.6/data/pg_xlog/0000000100000005000000DB

wal_compression = on

–开启wal_compression

alter system set wal_compression = ‘on’;

/etc/init.d/postgresql-9.6 stop
/etc/init.d/postgresql-9.6 start

–查看当前wal_compression设置

select *
from pg_settings ps
where 1=1
and ps.name like '%compress%'
;

name : wal_compression
setting : on

–清空表

truncate table tmp_wal_compress;

–切换pg_xlog

select pg_switch_xlog(); 

–查看最近的wal

select t1.file,
       t1.file_ls,
       (pg_stat_file(t1.file)).modification as last_update_time,
       round( ((pg_stat_file(t1.file)).size)/1024/1024*1.0,1) as log_size_mb
  from (select dir||'/'||pg_ls_dir(t0.dir) as file,
               pg_ls_dir(t0.dir) as file_ls
          from ( select '/var/lib/pgsql/9.6/data/pg_xlog'::text as dir
                 --需要修改这个物理路径
                    ) t0
            ) t1 
  where 1=1
  order by (pg_stat_file(file)).modification desc
;

file : /var/lib/pgsql/9.6/data/pg_xlog/0000000100000005000000E9
file_ls : 0000000100000005000000E9
last_update_time: 2017-12-06 14:16:07
log_size_mb : 16

–插入100w条数据

insert into tmp_wal_compress
(id,
 random_char,
 random_int
)
SELECT generate_series(1,1000000) as key,
       md5( (random()*1000000)::text) as random_char,
       (random()*1000000000.)::int8 as random_int

–查看最近的wal

select t1.file,
       t1.file_ls,
       (pg_stat_file(t1.file)).modification as last_update_time,
       round( ((pg_stat_file(t1.file)).size)/1024/1024*1.0,1) as log_size_mb
  from (select dir||'/'||pg_ls_dir(t0.dir) as file,
               pg_ls_dir(t0.dir) as file_ls
          from ( select '/var/lib/pgsql/9.6/data/pg_xlog'::text as dir
                 --需要修改这个物理路径
                    ) t0
            ) t1 
  where 1=1
  order by (pg_stat_file(file)).modification desc
;

file : /var/lib/pgsql/9.6/data/pg_xlog/0000000100000005000000EF
file_ls : 0000000100000005000000EF
last_update_time: 2017-12-06 14:16:17
log_size_mb : 16

生成了6个wal

/var/lib/pgsql/9.6/data/pg_xlog/0000000100000005000000EF
/var/lib/pgsql/9.6/data/pg_xlog/0000000100000005000000EE
/var/lib/pgsql/9.6/data/pg_xlog/0000000100000005000000ED
/var/lib/pgsql/9.6/data/pg_xlog/0000000100000005000000EC
/var/lib/pgsql/9.6/data/pg_xlog/0000000100000005000000EB
/var/lib/pgsql/9.6/data/pg_xlog/0000000100000005000000EA

看上去开启wal_compression的效果并不是太好 ，再仔细看下官方文档了解。