From b45606c6e401789d65beb391ca947ae65ff08218 Mon Sep 17 00:00:00 2001 From: Greg Smith Date: Sun, 16 Jan 2011 06:02:45 -0500 Subject: [PATCH] Add support for 9.1 backend fsync stat, better reporting, and OS information saving. Also switch around documentation to follow current github standards. --- .gitignore | 1 + HISTORY | 5 ++++ README => README.rst | 26 +++++++++++++++++++++ benchwarmer | 54 +++++++++++++++++++++++++++++++++++--------- bufstats.sql | 36 +++++++++++++++++++++++++++++ init/resultdb.sql | 3 ++- 6 files changed, 113 insertions(+), 12 deletions(-) create mode 100644 .gitignore rename README => README.rst (89%) create mode 100644 bufstats.sql diff --git a/.gitignore b/.gitignore new file mode 100644 index 0000000..b25c15b --- /dev/null +++ b/.gitignore @@ -0,0 +1 @@ +*~ diff --git a/HISTORY b/HISTORY index de77dcc..95ecbe2 100644 --- a/HISTORY +++ b/HISTORY @@ -12,3 +12,8 @@ reST. 0.6 Future Use TESTPORT properly, bug report from Jignesh. + Add support for pg_stat_bgwriter.buffers_backend_fsync + Add new bufsummary.sql with derived buffer cache stats + Rename README to .rst for better GitHub display + Add contact info and ReST notes + Save some operating system information into the report diff --git a/README b/README.rst similarity index 89% rename from README rename to README.rst index 7017a73..d6a834f 100644 --- a/README +++ b/README.rst @@ -145,3 +145,29 @@ Planned features * The client+scale data table used to generate the 3D report would be useful to generate in tabular text format as well. + +Documentation +============= + +The documentation ``README.rst`` for the program is in ReST markup. Tools +that operate on ReST can be used to make versions of it formatted +for other purposes, such as rst2html to make a HTML version. + +Contact +======= + +The project is hosted at https://github.com/gregs1104/pgbench-tools +and is also a PostgreSQL project at http://git.postgresql.org/git/pgbench-tools.git +or http://git.postgresql.org/gitweb + +If you have any hints, changes or improvements, please contact: + + * Greg Smith gsmith@gregsmith.com + +Credits +======= + +Copyright (c) 2007-2011, Gregory Smith +All rights reserved. +See COPYRIGHT file for full license details + diff --git a/benchwarmer b/benchwarmer index 0b7355f..99cbadc 100755 --- a/benchwarmer +++ b/benchwarmer @@ -21,9 +21,12 @@ fi # Query the test database and put its background writer statistics into # a set of environment variables, probably for use inserting stats into -# the results database +# the results database. +# +# This presumes that you have already set $BACKEND_FSYNC before +# calling. function get_bgwriter { - BGW=`$TESTPSQL -A -t -F" " -c "select checkpoints_timed,checkpoints_req,buffers_checkpoint,buffers_clean,maxwritten_clean,buffers_backend,buffers_alloc from pg_stat_bgwriter"` + BGW=`$TESTPSQL -A -t -F" " -c "select checkpoints_timed,checkpoints_req,buffers_checkpoint,buffers_clean,maxwritten_clean,buffers_backend,buffers_alloc,$BACKEND_FSYNC from pg_stat_bgwriter"` set -- $BGW checkpoints_timed=$1 checkpoints_req=$2 @@ -32,6 +35,7 @@ function get_bgwriter { maxwritten_clean=$5 buffers_backend=$6 buffers_alloc=$7 + buffers_backend_fsync=$8 } # Figure out how many transactions per client, then recompute @@ -133,9 +137,17 @@ if [ -z "$TEST" ]; then exit fi +# Figure out if this version of PostgreSQL includes buffers_backend_fsync +$TESTPSQL -c "SELECT buffers_backend_fsync FROM pg_stat_bgwriter" >> /dev/null +if [ "$?" -ne "0" ]; then + BACKEND_FSYNC="0" +else + BACKEND_FSYNC="buffers_backend_fsync" +fi + # Grab starting values for statistics get_bgwriter -$RESULTPSQL -c "insert into test_bgwriter(test,checkpoints_timed,checkpoints_req,buffers_checkpoint,buffers_clean,maxwritten_clean,buffers_backend,buffers_alloc) values('$TEST','$checkpoints_timed','$checkpoints_req','$buffers_checkpoint','$buffers_clean','$maxwritten_clean','$buffers_backend','$buffers_alloc')" +$RESULTPSQL -c "insert into test_bgwriter(test,checkpoints_timed,checkpoints_req,buffers_checkpoint,buffers_clean,maxwritten_clean,buffers_backend,buffers_alloc,buffers_backend_fsync) values('$TEST','$checkpoints_timed','$checkpoints_req','$buffers_checkpoint','$buffers_clean','$maxwritten_clean','$buffers_backend','$buffers_alloc','$buffers_backend_fsync')" echo This is test $TEST @@ -157,7 +169,8 @@ $RESULTPSQL -c "update test_bgwriter set \ buffers_clean=$buffers_clean - buffers_clean,\ maxwritten_clean=$maxwritten_clean - maxwritten_clean,\ buffers_backend=$buffers_backend - buffers_backend,\ - buffers_alloc=$buffers_alloc - buffers_alloc \ + buffers_alloc=$buffers_alloc - buffers_alloc,\ + buffers_backend_fsync=$buffers_backend_fsync - buffers_backend_fsync \ where test='$TEST'" # Save pgbench log and results @@ -178,7 +191,7 @@ $RESULTPSQL -q -c "update tests set tps='$tps',trans='$trans' where test=$TEST" TESTFORTS=`cat pgbench.log | head -n 1 | cut -d" " -f 6` if [ -z "$TESTFORTS" ]; then echo - echo ERROR: the pgbench used for this test is missing transaction + echo "ERROR: the pgbench used for this test is missing transaction" echo timestamps. No latency information will be imported into echo the database, and no plots will be generated. echo @@ -211,13 +224,32 @@ SERVERHOST="$TESTHOST" if [ "$SERVERHOST" = "localhost" ]; then SERVERHOST="$CLIENTHOST" fi +SETTINGS="pg_settings.txt" + +# PostgreSQL and results +echo Test results: > $SETTINGS +$RESULTPSQL -c "select script,clients,round(tps) as tps,1000*round(avg_latency)/1000 as avg_latency,1000*round(max_latency)/1000 as max_latency from tests where test=$TEST" | grep -v " row)" >> $SETTINGS +echo Server $SERVERHOST, client $CLIENTHOST >> $SETTINGS +echo >> $SETTINGS +echo Server settings in postgresql.conf: >> $SETTINGS +$RESULTPSQL -c "select name,current_setting(name) from pg_settings where source='configuration file' and not name in ('DateStyle','lc_messages','lc_monetary','lc_numeric','lc_time','listen_addresses','log_directory','log_rotation_age','log_rotation_size','log_truncate_on_rotation');" | grep -v " rows)" >> $SETTINGS + +# Operating system information +echo >> $SETTINGS +echo "benchmark client OS Configuration (may not be the same as the server)" >> $SETTINGS +uname -a >> $SETTINGS +for f in `ls /proc/sys/vm/dirty_*` ; do + S=`cat $f` + echo $f=$S >> $SETTINGS +done +echo >> $SETTINGS + +for f in `ls /etc/lsb-release /etc/debian_version /etc/redhat-release 2>/dev/null` ; do + echo $f: >> $SETTINGS + cat $f >> $SETTINGS + echo >> $SETTINGS +done -echo Test results: > pg_settings.txt -$RESULTPSQL -c "select script,clients,round(tps) as tps,1000*round(avg_latency)/1000 as avg_latency,1000*round(max_latency)/1000 as max_latency from tests where test=$TEST" | grep -v " row)" >> pg_settings.txt -echo Server $SERVERHOST, client $CLIENTHOST >> pg_settings.txt -echo >> pg_settings.txt -echo Server settings in postgresql.conf: >> pg_settings.txt -$RESULTPSQL -c "select name,current_setting(name) from pg_settings where source='configuration file' and not name in ('DateStyle','lc_messages','lc_monetary','lc_numeric','lc_time','listen_addresses','log_directory','log_rotation_age','log_rotation_size','log_truncate_on_rotation');" | grep -v " rows)" >> pg_settings.txt # Remove temporary files rm pgbench_log.${P}* diff --git a/bufstats.sql b/bufstats.sql new file mode 100644 index 0000000..a3ea70c --- /dev/null +++ b/bufstats.sql @@ -0,0 +1,36 @@ +-- Advanced report of background write effectiveness + +SELECT + set,scale,clients,tps, +-- cast(date_trunc('minute',start_time) AS timestamp) AS start, + date_trunc('second',elapsed) AS elapsed, + (checkpoints_timed + checkpoints_req) as ckpt, + date_trunc('second',elapsed / (checkpoints_timed + checkpoints_req)) AS ckpt_interval, + (100 * checkpoints_req) / (checkpoints_timed + checkpoints_req) + AS ckpt_req_pct, + pg_size_pretty(buffers_checkpoint * block_size / (checkpoints_timed + checkpoints_req)) + AS avg_ckpt_write, + 100 * buffers_checkpoint / (buffers_checkpoint + buffers_clean + buffers_backend) AS ckpt_write_pct, + 100 * buffers_backend / (buffers_checkpoint + buffers_clean + buffers_backend) AS backend_write_pct, + 100 * buffers_clean / (buffers_checkpoint + buffers_clean + buffers_backend) AS clean_write_pct, + pg_size_pretty(cast(block_size::int8 * (buffers_checkpoint + buffers_clean + buffers_backend) / extract(epoch FROM elapsed) AS int8)) AS writes_per_sec, + pg_size_pretty(cast(block_size * (buffers_alloc) / extract(epoch FROM elapsed) AS int8)) AS alloc_per_sec, + buffers_backend_fsync as backend_sync +FROM + ( + select + set,scale,tests.test,clients,round(tps) as tps, + start_time, + end_time - start_time as elapsed, + checkpoints_timed, + checkpoints_req, + buffers_checkpoint, + buffers_clean, + buffers_backend, + buffers_alloc, + buffers_backend_fsync, + (SELECT cast(current_setting('block_size') AS integer)) AS block_size +from test_bgwriter right join tests on tests.test=test_bgwriter.test WHERE NOT end_time is NULL +) raw +order by scale,set,clients,test; + diff --git a/init/resultdb.sql b/init/resultdb.sql index 60e2ef8..44fd028 100644 --- a/init/resultdb.sql +++ b/init/resultdb.sql @@ -47,5 +47,6 @@ CREATE TABLE test_bgwriter( buffers_clean int, maxwritten_clean int, buffers_backend int, - buffers_alloc int + buffers_alloc int, + buffers_backend_fsync int ); -- 2.39.5