Attached is an updated version of the patch which turns the sort option into a boolean, and also include the sort time in the checkpoint log.There is still an open question about whether the sorting buffer allocation is lost on some signals and should be reallocated in such event.
In such case, probably the allocation should be managed from CheckpointerMain, and the lazy allocation could remain for other callers (I guess just "initdb").
More open questions:
- best name for the flush option (checkpoint_flush_to_disk,
checkpoint_flush_on_write, checkpoint_flush, ...)
- best name for the sort option (checkpoint_sort,
checkpoint_sort_buffers, checkpoint_sort_ios, ...)
Other nice-to-have inputs:
- tests on a non-linux system with posix_fadvise
(FreeBSD? others?)
- tests on a large dedicated box
Attached are some scripts to help with testing, if someone's feels like
that:
- cp_test.sh: run some tests, to adapt to one's setup... - cp_test_count.pl: show percent of late transactions - avg.py: show stats about stuff sh> grep 'progress: ' OUTPUT_FILE | cut -d' ' -f4 | avg.py *BEWARE* that if pgbench got stuck some "0" data are missing, look for the actual tps in the output file and for the line count to check whether it is the case... some currently submitted patch on pgbench helps, see https://commitfest.postgresql.org/5/199/ -- Fabien.
#! /usr/bin/env python
# -*- coding: utf-8 -*-
#
# $Id: avg.py 1214 2015-06-16 05:50:07Z coelho $
#
import argparse
ap = argparse.ArgumentParser(description='show stats about data')
ap.add_argument('--median', default=True, action='store_true',
help='compute median and quartile values')
ap.add_argument('--no-median', dest='median', default=True,
action='store_false',
help='do not compute median and quartile values')
ap.add_argument('file', nargs='*', help='list of files to process')
opt = ap.parse_args()
# reset arguments for fileinput
import sys
sys.argv[1:] = opt.file
import fileinput
n, skipped, vals = 0, 0, []
k, vmin, vmax = None, None, None
sum1, sum2 = 0.0, 0.0
for line in fileinput.input():
try:
v = float(line)
if opt.median: # keep track only if needed
vals.append(v)
if k is None: # first time
k, vmin, vmax = v, v, v
else: # next time
vmin = min(vmin, v)
vmax = max(vmax, v)
n += 1
vmk = v - k
sum1 += vmk
sum2 += vmk * vmk
except ValueError: # float conversion failed
skipped += 1
# five numbers...
# numpy.percentile requires numpy at least 1.9 to use 'midpoint'
# statistics.median requires python 3.4 (?)
def median(vals, start, length):
m, odd = divmod(length, 2)
#return 0.5 * (vals[start + m + odd - 1] + vals[start + m])
return vals[start + m] if odd else \
0.5 * (vals[start + m-1] + vals[start + m])
if n > 0:
# show result (hmmm, precision is truncated...)
from math import sqrt
avg, stddev = k + sum1 / n, sqrt((sum2 - (sum1 * sum1) / n) / n)
if opt.median:
vals.sort()
med = median(vals, 0, len(vals))
# not sure about odd/even issues here...
q1 = median(vals, 0, len(vals) // 2)
q3 = median(vals, (len(vals)+1) // 2, len(vals) // 2)
print("avg over %d: %f ± %f [%f, %f, %f, %f, %f]" %
(n, avg, stddev, vmin, q1, med, q3, vmax))
else:
print("avg over %d: %f ± %f [%f, %f]" %
(n, avg, stddev, vmin, vmax))
else:
print("no data seen.")
if skipped:
print("warning: %d lines skipped" % skipped)
cp_test.sh
Description: Bourne shell script
#! /usr/bin/perl -w
#
# $Id: cp_test_counts.pl 316 2015-05-31 20:29:44Z fabien $
#
# show the % of skipped and over-the-limit transactions from pgbench output.
#
use strict;
my ($processed, $skipped, $limit);
while (<>) {
if (/^number of transactions /) {
$processed = $1 if /processed: (\d+)/;
$skipped = $1 if /skipped: (\d+)/;
$limit = $1 if /limit: (\d+)/;
if (defined $processed and defined $skipped and defined $limit) {
print 100.0 * ($skipped + $limit) / ($processed + $skipped), "\n";
($processed, $skipped, $limit) = (undef, undef, undef);
}
}
}
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
