Add support for 9.1 backend fsync stat, better

reporting, and OS information saving.  Also switch around
documentation to follow current github standards.

6 files changed, 113 insertions, 12 deletions

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