|
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 |
