Calculating how much redo log space has been used - Mailing list pgsql-performance
From | Reza Taheri |
---|---|
Subject | Calculating how much redo log space has been used |
Date | |
Msg-id | 32CF54B1-DEAA-46D9-9B65-05D75B591D60@vmware.com Whole thread Raw |
Responses |
Re: Calculating how much redo log space has been used
|
List | pgsql-performance |
Hello PostgreSQL community,
I am helping with a benchmarking exercise using PGSQL (I chair the TPC subcommittee 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
pgsql-performance by date: