pg_test_fsync output and commit_delay
I propose that we try and develop better commit_delay advice, to make
it easier to set the parameter in a way that actually helps
performance. I have been researching a way to make commit_delay
adaptive, though have yet to develop any further insight that is worth
sharing. This may change.
The attached simple patch alters the output produced by pg_test_fsync,
so that we also see the average sync time per op. The pg_test_fsync
docs have had minor alterations too. Hopefully, another doc patch will
follow that builds upon this, and actually firmly recommends taking a
particular course of action when setting commit_delay - my previous
observations about what helped throughput, though supported by Greg
Smith, have not been scrutinised enough just yet, I feel. For now, the
equivocated wording of my doc alterations (that raw wal_sync_method
file sync time might *somehow* be useful here) seems appropriate.
--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services
Attachments:
pg_test_fsync.v1.2012_09_08.patchapplication/octet-stream; name=pg_test_fsync.v1.2012_09_08.patchDownload
diff contrib/pg_test_fsync/pg_test_fsync.c
index 9fe2301..7f7368a
*** a/contrib/pg_test_fsync/pg_test_fsync.c
--- b/contrib/pg_test_fsync/pg_test_fsync.c
***************
*** 25,31 ****
#define LABEL_FORMAT " %-32s"
#define NA_FORMAT "%18s"
! #define OPS_FORMAT "%9.3f ops/sec"
/* These are macros to avoid timing the function call overhead. */
#ifndef WIN32
--- 25,32 ----
#define LABEL_FORMAT " %-32s"
#define NA_FORMAT "%18s"
! #define OPS_FORMAT "%9.3f ops/sec (%6.f microsecs/op)"
! #define USECS_SEC 1000000
/* These are macros to avoid timing the function call overhead. */
#ifndef WIN32
*************** print_elapse(struct timeval start_t, str
*** 568,575 ****
double total_time = (stop_t.tv_sec - start_t.tv_sec) +
(stop_t.tv_usec - start_t.tv_usec) * 0.000001;
double per_second = ops / total_time;
! printf(OPS_FORMAT "\n", per_second);
}
#ifndef WIN32
--- 569,577 ----
double total_time = (stop_t.tv_sec - start_t.tv_sec) +
(stop_t.tv_usec - start_t.tv_usec) * 0.000001;
double per_second = ops / total_time;
+ double avg_op_time_us = (total_time / ops) * USECS_SEC;
! printf(OPS_FORMAT "\n", per_second, avg_op_time_us);
}
#ifndef WIN32
diff doc/src/sgml/pgtestfsync.sgml
index 95ba3b8..00ef209
*** a/doc/src/sgml/pgtestfsync.sgml
--- b/doc/src/sgml/pgtestfsync.sgml
***************
*** 30,40 ****
<application>pg_test_fsync</> is intended to give you a reasonable
idea of what the fastest <xref linkend="guc-wal-sync-method"> is on your
specific system,
! as well as supplying diagnostic information in the event of an
! identified I/O problem. However, differences shown by <application>pg_test_fsync</application>
! might not make any difference in real database throughput, especially
! since many database servers are not speed-limited by their transaction
! logs.
</para>
</refsect1>
--- 30,43 ----
<application>pg_test_fsync</> is intended to give you a reasonable
idea of what the fastest <xref linkend="guc-wal-sync-method"> is on your
specific system,
! as well as supplying diagnostic information in the event of an identified I/O
! problem. However, differences shown by
! <application>pg_test_fsync</application> might not make any significant
! difference in real database throughput, especially since many database servers
! are not speed-limited by their transaction logs.
! <application>pg_test_fsync</application> reports average file sync operation
! time in microseconds for each wal_sync_method, which can be used to inform
! efforts to optimize the value of <varname>commit_delay</varname>.
</para>
</refsect1>
The attached simple patch alters the output produced by pg_test_fsync,
so that we also see the average sync time per op.
Good idea. A median would be even better, but harder to calculate, I
imagine. You might consider providing a maximum, too.
The pg_test_fsync
docs have had minor alterations too. Hopefully, another doc patch will
follow that builds upon this, and actually firmly recommends taking a
particular course of action when setting commit_delay - my previous
observations about what helped throughput, though supported by Greg
Smith, have not been scrutinised enough just yet, I feel. For now, the
equivocated wording of my doc alterations (that raw wal_sync_method
file sync time might *somehow* be useful here) seems appropriate.
I'll see what I can do.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com