Calculating how much redo log space has been used

Hello PostgreSQL community,
I am helping with a benchmarking exercise using PGSQL (I chair the TPC subcommittee<http://www.tpc.org/tpcx-v/default.asp> that has released a benchmark using PGSQL). A requirement of the benchmark is having enough log space allocated for 8 hours of running without needing to archive, back up, etc. I am trying to a) figure out how I can establish the exact space usage for the auditor; and b) how I can reduce the log space usage. Looking at iostat and pgstatspack, it looks like we will need to allocate something like 1.5TB of log space for a 5TB database, which is a huge ratio. (Yes, in the real world, we’d probably archive or ship the logs; but for benchmarking, that doesn’t work)

pgstatspack gives me something like below:

background writer stats

checkpoints_timed | checkpoints_req | buffers_checkpoint | buffers_clean | maxwritten_clean | buffers_backend | buffers_alloc

-------------------+-----------------+--------------------+---------------+------------------+-----------------+---------------

22 | 0 | 6416768 | 2252636 | 0 | 280211 | 9786558

(1 row)

background writer relative stats

checkpoints_timed | minutes_between_checkpoint | buffers_checkpoint | buffers_clean | buffers_backend | total_writes | avg_checkpoint_write

-------------------+----------------------------+--------------------+---------------+-----------------+--------------+----------------------

100% | 6 | 71% | 25% | 3% | 8.659 MB/s | 2278.000 MB

I can calculate how many checkpoint segments I have used from the MB/s. But is there a more direct way of seeing how/when a checkpoint segment is filled up and we move on to the next one?

Also, it looks like the full_page_writes parameter is the only thing that can help reduce the log usage size, but that I have to set it to 1 to avoid corruption after a system crash, which is a requirement. Another requirement is a very short, 6-minute checkpoint time, which means we will likely write the full page very often. Yes, my hands are tied!

Here are the relevant non-default settings:

shared_buffers = 18000MB # min 128kB

temp_buffers = 2MB # min 800kB

maintenance_work_mem = 5MB # min 1MB

bgwriter_delay = 10ms # 10-10000ms between rounds

bgwriter_lru_maxpages = 200 # 0-1000 max buffers written/round

effective_io_concurrency = 10 # 1-1000; 0 disables prefetching

wal_sync_method = open_datasync # the default is the first option

wal_buffers = 16MB # min 32kB, -1 sets based on shared_buffers

wal_writer_delay = 10ms # 1-10000 milliseconds

checkpoint_segments = 750 # in logfile segments, min 1, 16MB each

checkpoint_timeout = 6min # range 30s-1h

checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0

effective_cache_size = 512MB

default_statistics_target = 10000 # range 1-10000

log_destination = 'stderr' # Valid values are combinations of

logging_collector = on # Enable capturing of stderr and csvlog

log_directory = 'pg_log' # directory where log files are written,

log_filename = 'postgresql-%a.log' # log file name pattern,

log_truncate_on_rotation = on # If on, an existing log file with the

log_rotation_age = 1d # Automatic rotation of logfiles will

log_rotation_size = 0 # Automatic rotation of logfiles will

log_checkpoints = on