Thread: pgbench transaction timestamps
This patch changes the way pgbench outputs its latency log files so that every transaction gets a timestamp and notes which transaction type was executed. It's a one-line change that just dumps some additional information that was already sitting in that area of code. I also made a couple of documentation corrections and clarifications on some of the more confusing features of pgbench. It's straightforward to parse log files in this format to analyze what happened during the test at a higher level than was possible with the original format. You can find some rough sample code to convert this latency format into CVS files and then into graphs at http://www.westnet.com/~gsmith/content/postgresql/pgbench.htm which I'll be expanding on once I get all my little patches sent in here. If you recall the earlier version of this patch I submitted, it added a cleanup feature that did a vacuum and checkpoint after the test was finished and reported two TPS results. The idea was to quantify how much of a hit the eventual table maintenance required to clean up after the test would take. While those things do influence results and cause some of the run-to-run variation in TPS (checkpoints are particularly visible in the graphs), after further testing I concluded running a VACUUM VERBOSE and CHECKPOINT in a script afterwards and analyzing the results was more useful than integrating something into pgbench itself. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Attachment
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---------------------------------------------------------------------------
Greg Smith wrote:
> This patch changes the way pgbench outputs its latency log files so that
> every transaction gets a timestamp and notes which transaction type was
> executed. It's a one-line change that just dumps some additional
> information that was already sitting in that area of code. I also made a
> couple of documentation corrections and clarifications on some of the more
> confusing features of pgbench.
>
> It's straightforward to parse log files in this format to analyze what
> happened during the test at a higher level than was possible with the
> original format. You can find some rough sample code to convert this
> latency format into CVS files and then into graphs at
> http://www.westnet.com/~gsmith/content/postgresql/pgbench.htm which I'll
> be expanding on once I get all my little patches sent in here.
>
> If you recall the earlier version of this patch I submitted, it added a
> cleanup feature that did a vacuum and checkpoint after the test was
> finished and reported two TPS results. The idea was to quantify how much
> of a hit the eventual table maintenance required to clean up after the
> test would take. While those things do influence results and cause some
> of the run-to-run variation in TPS (checkpoints are particularly visible
> in the graphs), after further testing I concluded running a VACUUM VERBOSE
> and CHECKPOINT in a script afterwards and analyzing the results was more
> useful than integrating something into pgbench itself.
>
> --
> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Content-Description:
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Tatsuo, would you please comment on this patch too? --------------------------------------------------------------------------- Greg Smith wrote: > This patch changes the way pgbench outputs its latency log files so that > every transaction gets a timestamp and notes which transaction type was > executed. It's a one-line change that just dumps some additional > information that was already sitting in that area of code. I also made a > couple of documentation corrections and clarifications on some of the more > confusing features of pgbench. > > It's straightforward to parse log files in this format to analyze what > happened during the test at a higher level than was possible with the > original format. You can find some rough sample code to convert this > latency format into CVS files and then into graphs at > http://www.westnet.com/~gsmith/content/postgresql/pgbench.htm which I'll > be expanding on once I get all my little patches sent in here. > > If you recall the earlier version of this patch I submitted, it added a > cleanup feature that did a vacuum and checkpoint after the test was > finished and reported two TPS results. The idea was to quantify how much > of a hit the eventual table maintenance required to clean up after the > test would take. While those things do influence results and cause some > of the run-to-run variation in TPS (checkpoints are particularly visible > in the graphs), after further testing I concluded running a VACUUM VERBOSE > and CHECKPOINT in a script afterwards and analyzing the results was more > useful than integrating something into pgbench itself. > > -- > * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD Content-Description: [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
> Tatsuo, would you please comment on this patch too? No problem. I will come up with a comment by the end of this week. -- Tatsuo Ishii SRA OSS, Inc. Japan > --------------------------------------------------------------------------- > > Greg Smith wrote: > > This patch changes the way pgbench outputs its latency log files so that > > every transaction gets a timestamp and notes which transaction type was > > executed. It's a one-line change that just dumps some additional > > information that was already sitting in that area of code. I also made a > > couple of documentation corrections and clarifications on some of the more > > confusing features of pgbench. > > > > It's straightforward to parse log files in this format to analyze what > > happened during the test at a higher level than was possible with the > > original format. You can find some rough sample code to convert this > > latency format into CVS files and then into graphs at > > http://www.westnet.com/~gsmith/content/postgresql/pgbench.htm which I'll > > be expanding on once I get all my little patches sent in here. > > > > If you recall the earlier version of this patch I submitted, it added a > > cleanup feature that did a vacuum and checkpoint after the test was > > finished and reported two TPS results. The idea was to quantify how much > > of a hit the eventual table maintenance required to clean up after the > > test would take. While those things do influence results and cause some > > of the run-to-run variation in TPS (checkpoints are particularly visible > > in the graphs), after further testing I concluded running a VACUUM VERBOSE > > and CHECKPOINT in a script afterwards and analyzing the results was more > > useful than integrating something into pgbench itself. > > > > -- > > * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD > Content-Description: > > [ Attachment, skipping... ] > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: explain analyze is your friend > > -- > Bruce Momjian <bruce@momjian.us> http://momjian.us > EnterpriseDB http://www.enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. +
> > Tatsuo, would you please comment on this patch too? > > No problem. I will come up with a comment by the end of this week. Here are comments. 1) latency log file format extention looks usefull (-x option). 2) it seems the "cleanup feature" (-X option) was withdrawed by the author, but the patches still include the feature. So I'm confused. -- Tatsuo Ishii SRA OSS, Inc. Japan > > --------------------------------------------------------------------------- > > > > Greg Smith wrote: > > > This patch changes the way pgbench outputs its latency log files so that > > > every transaction gets a timestamp and notes which transaction type was > > > executed. It's a one-line change that just dumps some additional > > > information that was already sitting in that area of code. I also made a > > > couple of documentation corrections and clarifications on some of the more > > > confusing features of pgbench. > > > > > > It's straightforward to parse log files in this format to analyze what > > > happened during the test at a higher level than was possible with the > > > original format. You can find some rough sample code to convert this > > > latency format into CVS files and then into graphs at > > > http://www.westnet.com/~gsmith/content/postgresql/pgbench.htm which I'll > > > be expanding on once I get all my little patches sent in here. > > > > > > If you recall the earlier version of this patch I submitted, it added a > > > cleanup feature that did a vacuum and checkpoint after the test was > > > finished and reported two TPS results. The idea was to quantify how much > > > of a hit the eventual table maintenance required to clean up after the > > > test would take. While those things do influence results and cause some > > > of the run-to-run variation in TPS (checkpoints are particularly visible > > > in the graphs), after further testing I concluded running a VACUUM VERBOSE > > > and CHECKPOINT in a script afterwards and analyzing the results was more > > > useful than integrating something into pgbench itself. > > > > > > -- > > > * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD > > Content-Description: > > > > [ Attachment, skipping... ] > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 6: explain analyze is your friend > > > > -- > > Bruce Momjian <bruce@momjian.us> http://momjian.us > > EnterpriseDB http://www.enterprisedb.com > > > > + If your life is a hard drive, Christ can be your backup. +
FYI, patch applied by Tatsuo. Thanks. --------------------------------------------------------------------------- Greg Smith wrote: > This patch changes the way pgbench outputs its latency log files so that > every transaction gets a timestamp and notes which transaction type was > executed. It's a one-line change that just dumps some additional > information that was already sitting in that area of code. I also made a > couple of documentation corrections and clarifications on some of the more > confusing features of pgbench. > > It's straightforward to parse log files in this format to analyze what > happened during the test at a higher level than was possible with the > original format. You can find some rough sample code to convert this > latency format into CVS files and then into graphs at > http://www.westnet.com/~gsmith/content/postgresql/pgbench.htm which I'll > be expanding on once I get all my little patches sent in here. > > If you recall the earlier version of this patch I submitted, it added a > cleanup feature that did a vacuum and checkpoint after the test was > finished and reported two TPS results. The idea was to quantify how much > of a hit the eventual table maintenance required to clean up after the > test would take. While those things do influence results and cause some > of the run-to-run variation in TPS (checkpoints are particularly visible > in the graphs), after further testing I concluded running a VACUUM VERBOSE > and CHECKPOINT in a script afterwards and analyzing the results was more > useful than integrating something into pgbench itself. > > -- > * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD Content-Description: [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +