PostgreSQL 运用技巧之二：各时间段WAL产生量查询

背景描述

通常可以通过统计wal在各个时间段内产生的数量确认数据库繁忙程度。

技巧方法

• pg10及以后的通用版本

select to_char(date_trunc('day',wal.modification),'yyyymmdd') as day,
        sum(case when date_part('hour',wal.modification) >=0 and date_part('hour',wal.modification) <24 then 1 else 0 end) as all,
        sum(case when date_part('hour',wal.modification) >=0 and date_part('hour',wal.modification) <1 then 1 else 0 end) as w0_1,
        sum(case when date_part('hour',wal.modification) >=1 and date_part('hour',wal.modification) <2 then 1 else 0 end) as w1_2,
        sum(case when date_part('hour',wal.modification) >=2 and date_part('hour',wal.modification) <3 then 1 else 0 end) as w2_3,
        sum(case when date_part('hour',wal.modification) >=3 and date_part('hour',wal.modification) <4 then 1 else 0 end) as w3_4,
        sum(case when date_part('hour',wal.modification) >=4 and date_part('hour',wal.modification) <5 then 1 else 0 end) as w4_5,
        sum(case when date_part('hour',wal.modification) >=5 and date_part('hour',wal.modification) <6 then 1 else 0 end) as w5_6,
        sum(case when date_part('hour',wal.modification) >=6 and date_part('hour',wal.modification) <7 then 1 else 0 end) as w6_7,
        sum(case when date_part('hour',wal.modification) >=7 and date_part('hour',wal.modification) <8 then 1 else 0 end) as w7_8,
        sum(case when date_part('hour',wal.modification) >=8 and date_part('hour',wal.modification) <9 then 1 else 0 end) as w8_9,
        sum(case when date_part('hour',wal.modification) >=9 and date_part('hour',wal.modification) <10 then 1 else 0 end) as w9_10,
        sum(case when date_part('hour',wal.modification) >=10 and date_part('hour',wal.modification) <11 then 1 else 0 end) as w10_11,
        sum(case when date_part('hour',wal.modification) >=11 and date_part('hour',wal.modification) <12 then 1 else 0 end) as w11_12,
        sum(case when date_part('hour',wal.modification) >=12 and date_part('hour',wal.modification) <13 then 1 else 0 end) as w12_13,
        sum(case when date_part('hour',wal.modification) >=13 and date_part('hour',wal.modification) <14 then 1 else 0 end) as w13_14,
        sum(case when date_part('hour',wal.modification) >=14 and date_part('hour',wal.modification) <15 then 1 else 0 end) as w14_15,
        sum(case when date_part('hour',wal.modification) >=15 and date_part('hour',wal.modification) <16 then 1 else 0 end) as w15_16,
        sum(case when date_part('hour',wal.modification) >=16 and date_part('hour',wal.modification) <17 then 1 else 0 end) as w16_17,
        sum(case when date_part('hour',wal.modification) >=17 and date_part('hour',wal.modification) <18 then 1 else 0 end) as w17_18,
        sum(case when date_part('hour',wal.modification) >=18 and date_part('hour',wal.modification) <19 then 1 else 0 end) as w18_19,
        sum(case when date_part('hour',wal.modification) >=19 and date_part('hour',wal.modification) <20 then 1 else 0 end) as w19_20,
        sum(case when date_part('hour',wal.modification) >=20 and date_part('hour',wal.modification) <21 then 1 else 0 end) as w20_21,
        sum(case when date_part('hour',wal.modification) >=21 and date_part('hour',wal.modification) <22 then 1 else 0 end) as w21_22,
        sum(case when date_part('hour',wal.modification) >=22 and date_part('hour',wal.modification) <23 then 1 else 0 end) as w22_23,
        sum(case when date_part('hour',wal.modification) >=23 and date_part('hour',wal.modification) <24 then 1 else 0 end) as w23_24
from (select * from  pg_ls_waldir()) wal
where wal.name not in ('archive_status')
  and wal.name not like '%.backup'
group by to_char(date_trunc('day',wal.modification),'yyyymmdd')
order by to_char(date_trunc('day',wal.modification),'yyyymmdd') desc;

• pg9.6及之前的Linux版本

with tmp_file as (
    select t1.file,
           t1.file_ls,
           (pg_stat_file(t1.file)).modification as modification,
           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 'pg_xlog'::text as dir) t0
            ) t1
)
select to_char(date_trunc('day',tf0.modification),'yyyymmdd') as day,
        sum(case when date_part('hour',tf0.modification) >=0 and date_part('hour',tf0.modification) <24 then 1 else 0 end) as all,
        sum(case when date_part('hour',tf0.modification) >=0 and date_part('hour',tf0.modification) <1 then 1 else 0 end) as w0_01,
        sum(case when date_part('hour',tf0.modification) >=1 and date_part('hour',tf0.modification) <2 then 1 else 0 end) as w1_02,
        sum(case when date_part('hour',tf0.modification) >=2 and date_part('hour',tf0.modification) <3 then 1 else 0 end) as w2_03,
        sum(case when date_part('hour',tf0.modification) >=3 and date_part('hour',tf0.modification) <4 then 1 else 0 end) as w3_04,
        sum(case when date_part('hour',tf0.modification) >=4 and date_part('hour',tf0.modification) <5 then 1 else 0 end) as w4_05,
        sum(case when date_part('hour',tf0.modification) >=5 and date_part('hour',tf0.modification) <6 then 1 else 0 end) as w5_06,
        sum(case when date_part('hour',tf0.modification) >=6 and date_part('hour',tf0.modification) <7 then 1 else 0 end) as w6_07,
        sum(case when date_part('hour',tf0.modification) >=7 and date_part('hour',tf0.modification) <8 then 1 else 0 end) as w7_08,
        sum(case when date_part('hour',tf0.modification) >=8 and date_part('hour',tf0.modification) <9 then 1 else 0 end) as w8_09,
        sum(case when date_part('hour',tf0.modification) >=9 and date_part('hour',tf0.modification) <10 then 1 else 0 end) as w9_10,
        sum(case when date_part('hour',tf0.modification) >=10 and date_part('hour',tf0.modification) <11 then 1 else 0 end) as wal_10_11,
        sum(case when date_part('hour',tf0.modification) >=11 and date_part('hour',tf0.modification) <12 then 1 else 0 end) as wal_11_12,
        sum(case when date_part('hour',tf0.modification) >=12 and date_part('hour',tf0.modification) <13 then 1 else 0 end) as wal_12_13,
        sum(case when date_part('hour',tf0.modification) >=13 and date_part('hour',tf0.modification) <14 then 1 else 0 end) as wal_13_14,
        sum(case when date_part('hour',tf0.modification) >=14 and date_part('hour',tf0.modification) <15 then 1 else 0 end) as wal_14_15,
        sum(case when date_part('hour',tf0.modification) >=15 and date_part('hour',tf0.modification) <16 then 1 else 0 end) as wal_15_16,
        sum(case when date_part('hour',tf0.modification) >=16 and date_part('hour',tf0.modification) <17 then 1 else 0 end) as wal_16_17,
        sum(case when date_part('hour',tf0.modification) >=17 and date_part('hour',tf0.modification) <18 then 1 else 0 end) as wal_17_18,
        sum(case when date_part('hour',tf0.modification) >=18 and date_part('hour',tf0.modification) <19 then 1 else 0 end) as wal_18_19,
        sum(case when date_part('hour',tf0.modification) >=19 and date_part('hour',tf0.modification) <20 then 1 else 0 end) as wal_19_20,
        sum(case when date_part('hour',tf0.modification) >=20 and date_part('hour',tf0.modification) <21 then 1 else 0 end) as wal_20_21,
        sum(case when date_part('hour',tf0.modification) >=21 and date_part('hour',tf0.modification) <22 then 1 else 0 end) as wal_21_22,
        sum(case when date_part('hour',tf0.modification) >=22 and date_part('hour',tf0.modification) <23 then 1 else 0 end) as wal_22_23,
        sum(case when date_part('hour',tf0.modification) >=23 and date_part('hour',tf0.modification) <24 then 1 else 0 end) as wal_23_24
from tmp_file tf0
where tf0.file_ls not in ('archive_status')
	and tf0.file_ls not like '%.backup'
group by to_char(date_trunc('day',tf0.modification),'yyyymmdd')
order by to_char(date_trunc('day',tf0.modification),'yyyymmdd') desc;

• pg9.6及之前的Windows版本

with tmp_file as (
    select t1.file,
           t1.file_ls,
           (pg_stat_file(t1.file)).modification as modification,
           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 'pg_xlog'::text as dir) t0
            ) t1
)
select to_char(date_trunc('day',tf0.modification),'yyyymmdd') as day,
        sum(case when date_part('hour',tf0.modification) >=0 and date_part('hour',tf0.modification) <24 then 1 else 0 end) as all,
        sum(case when date_part('hour',tf0.modification) >=0 and date_part('hour',tf0.modification) <1 then 1 else 0 end) as w0_01,
        sum(case when date_part('hour',tf0.modification) >=1 and date_part('hour',tf0.modification) <2 then 1 else 0 end) as w1_02,
        sum(case when date_part('hour',tf0.modification) >=2 and date_part('hour',tf0.modification) <3 then 1 else 0 end) as w2_03,
        sum(case when date_part('hour',tf0.modification) >=3 and date_part('hour',tf0.modification) <4 then 1 else 0 end) as w3_04,
        sum(case when date_part('hour',tf0.modification) >=4 and date_part('hour',tf0.modification) <5 then 1 else 0 end) as w4_05,
        sum(case when date_part('hour',tf0.modification) >=5 and date_part('hour',tf0.modification) <6 then 1 else 0 end) as w5_06,
        sum(case when date_part('hour',tf0.modification) >=6 and date_part('hour',tf0.modification) <7 then 1 else 0 end) as w6_07,
        sum(case when date_part('hour',tf0.modification) >=7 and date_part('hour',tf0.modification) <8 then 1 else 0 end) as w7_08,
        sum(case when date_part('hour',tf0.modification) >=8 and date_part('hour',tf0.modification) <9 then 1 else 0 end) as w8_09,
        sum(case when date_part('hour',tf0.modification) >=9 and date_part('hour',tf0.modification) <10 then 1 else 0 end) as w9_10,
        sum(case when date_part('hour',tf0.modification) >=10 and date_part('hour',tf0.modification) <11 then 1 else 0 end) as wal_10_11,
        sum(case when date_part('hour',tf0.modification) >=11 and date_part('hour',tf0.modification) <12 then 1 else 0 end) as wal_11_12,
        sum(case when date_part('hour',tf0.modification) >=12 and date_part('hour',tf0.modification) <13 then 1 else 0 end) as wal_12_13,
        sum(case when date_part('hour',tf0.modification) >=13 and date_part('hour',tf0.modification) <14 then 1 else 0 end) as wal_13_14,
        sum(case when date_part('hour',tf0.modification) >=14 and date_part('hour',tf0.modification) <15 then 1 else 0 end) as wal_14_15,
        sum(case when date_part('hour',tf0.modification) >=15 and date_part('hour',tf0.modification) <16 then 1 else 0 end) as wal_15_16,
        sum(case when date_part('hour',tf0.modification) >=16 and date_part('hour',tf0.modification) <17 then 1 else 0 end) as wal_16_17,
        sum(case when date_part('hour',tf0.modification) >=17 and date_part('hour',tf0.modification) <18 then 1 else 0 end) as wal_17_18,
        sum(case when date_part('hour',tf0.modification) >=18 and date_part('hour',tf0.modification) <19 then 1 else 0 end) as wal_18_19,
        sum(case when date_part('hour',tf0.modification) >=19 and date_part('hour',tf0.modification) <20 then 1 else 0 end) as wal_19_20,
        sum(case when date_part('hour',tf0.modification) >=20 and date_part('hour',tf0.modification) <21 then 1 else 0 end) as wal_20_21,
        sum(case when date_part('hour',tf0.modification) >=21 and date_part('hour',tf0.modification) <22 then 1 else 0 end) as wal_21_22,
        sum(case when date_part('hour',tf0.modification) >=22 and date_part('hour',tf0.modification) <23 then 1 else 0 end) as wal_22_23,
        sum(case when date_part('hour',tf0.modification) >=23 and date_part('hour',tf0.modification) <24 then 1 else 0 end) as wal_23_24
from tmp_file tf0
where tf0.file_ls not in ('archive_status')
	and tf0.file_ls not like '%.backup'
group by to_char(date_trunc('day',tf0.modification),'yyyymmdd')
order by to_char(date_trunc('day',tf0.modification),'yyyymmdd') desc;