Add pgbench option: CHECKPOINT before starting benchmark

Started by KONDO Mitsumasaover 12 years ago4 messages
#1KONDO Mitsumasa
kondo.mitsumasa@lab.ntt.co.jp
1 attachment(s)

Hi,

I add checkpoint option to pgbench.

pgbench is simple and useful benchmark for every user. However, result of
benchmark greatly changes by some situations which are in executing checkpoint,
number of dirty buffers in share_buffers, and so on. For such a problem, it is
custom to carry out a checkpoint before starting benchmark. But it is a fact that
the making of the script takes time, like under following script.

psql -U postgres -d pgbench -p5432 -c "CHECKPOINT"
pgbench -T 600 -c 12 -j4 -U postgres -d pgbench -p 5432

However, this script have a problem.
This script execute "CHECKPOINT" -> "VACUUM" -> "starting benchmark".
If relpages have lot of dirty pages, VACUUM generate dirty buffers on
shared_buffers, and it will cause bad heavily checkpoint.

I think pgbench would be more easy and accuracy benchmark tools for everyone. So
I set checkpoint before starting benchmark.

This patch's output is here.
-----------------------------------------------------
[mitsu-ko@localhost pgbench]$ ./pgbench
starting vacuum...end.
starting checkpoint...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
tps = 312.851958 (including connections establishing)
tps = 364.524478 (excluding connections establishing)
-----------------------------------------------------
It execute "VACUUM" -> "CHECKPOINT" -> "starting benchmark".
I think it is ideal setting for more accuracy benchmark.

My patches option difinition is here.
[mitsu-ko@localhost pgbench]$ ./pgbench --help
~
-N, --no-checkpoint do not run CHECKPOINT after initialization
~
In latest commited pgbench, -N is "--skip-some-updates skip updates of
pgbench_tellers and pgbench_branches". But I cannot understand why -N is this
option, so I set this option -u, and -N is "do not run CHECKPOINT option".

What do you think?
--
Mitsumasa KONDO
NTT Open Source Software Center

Attachments:

checkpoint_before_starting_benchmark_v0.patchtext/x-diff; name=checkpoint_before_starting_benchmark_v0.patchDownload
diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c
index ad8e272..523c278 100644
--- a/contrib/pgbench/pgbench.c
+++ b/contrib/pgbench/pgbench.c
@@ -347,6 +347,7 @@ usage(void)
 		   "  -i, --initialize         invokes initialization mode\n"
 		   "  -F, --fillfactor=NUM     set fill factor\n"
 		   "  -n, --no-vacuum          do not run VACUUM after initialization\n"
+		   "  -N, --no-checkpoint      do not run CHECKPOINT after initialization\n"
 		   "  -q, --quiet              quiet logging (one message each 5 seconds)\n"
 		   "  -s, --scale=NUM          scaling factor\n"
 		   "  --foreign-keys           create foreign key constraints between tables\n"
@@ -366,7 +367,8 @@ usage(void)
 		   "                           protocol for submitting queries "
 		                                           "(default: simple)\n"
 		   "  -n, --no-vacuum          do not run VACUUM before tests\n"
-		   "  -N, --skip-some-updates  skip updates of pgbench_tellers and pgbench_branches\n"
+		   "  -N, --no-checkpoint      do not run CHECKPOINT before tests\n"
+		   "  -u, --skip-some-updates  skip updates of pgbench_tellers and pgbench_branches\n"
 		   "  -P, --progress=NUM       show thread progress report every NUM seconds\n"
 		   "  -r, --report-latencies   report average latency per command\n"
 		   "  -R, --rate=SPEC          target rate in transactions per second\n"
@@ -1520,7 +1522,7 @@ disconnect_all(CState *state, int length)
 
 /* create tables and setup data */
 static void
-init(bool is_no_vacuum)
+init(bool is_no_vacuum, bool is_no_checkpoint)
 {
 /* The scale factor at/beyond which 32bit integers are incapable of storing
  * 64bit values.
@@ -1775,6 +1777,12 @@ init(bool is_no_vacuum)
 		}
 	}
 
+	/* checkpoint */
+	if (!is_no_checkpoint)
+	{
+		fprintf(stderr, "checkpoint...\n");
+		executeStatement(con, "checkpoint");
+	}
 
 	fprintf(stderr, "done.\n");
 	PQfinish(con);
@@ -2213,6 +2221,7 @@ main(int argc, char **argv)
 		{"jobs", required_argument, NULL, 'j'},
 		{"log", no_argument, NULL, 'l'},
 		{"no-vacuum", no_argument, NULL, 'n'},
+		{"no-checkpoint", no_argument, NULL, 'N'},
 		{"port", required_argument, NULL, 'p'},
 		{"progress", required_argument, NULL, 'P'},
 		{"protocol", required_argument, NULL, 'M'},
@@ -2220,7 +2229,7 @@ main(int argc, char **argv)
 		{"report-latencies", no_argument, NULL, 'r'},
 		{"scale", required_argument, NULL, 's'},
 		{"select-only", no_argument, NULL, 'S'},
-		{"skip-some-updates", no_argument, NULL, 'N'},
+		{"skip-some-updates", no_argument, NULL, 'u'},
 		{"time", required_argument, NULL, 'T'},
 		{"transactions", required_argument, NULL, 't'},
 		{"username", required_argument, NULL, 'U'},
@@ -2240,6 +2249,7 @@ main(int argc, char **argv)
 	int			nclients = 1;	/* default number of simulated clients */
 	int			nthreads = 1;	/* default number of threads */
 	int			is_init_mode = 0;		/* initialize mode? */
+	int			is_no_checkpoint = 0;		/* no checkpoint at all before testing? */
 	int			is_no_vacuum = 0;		/* no vacuum at all before testing? */
 	int			do_vacuum_accounts = 0; /* do vacuum accounts before testing? */
 	int			ttype = 0;		/* transaction type. 0: TPC-B, 1: SELECT only,
@@ -2323,11 +2333,14 @@ main(int argc, char **argv)
 			case 'd':
 				debug++;
 				break;
+			case 'u':
+				ttype = 2;
+				break;
 			case 'S':
 				ttype = 1;
 				break;
 			case 'N':
-				ttype = 2;
+				is_no_checkpoint++;
 				break;
 			case 'c':
 				nclients = atoi(optarg);
@@ -2533,7 +2546,7 @@ main(int argc, char **argv)
 
 	if (is_init_mode)
 	{
-		init(is_no_vacuum);
+		init(is_no_vacuum, is_no_checkpoint);
 		exit(0);
 	}
 
@@ -2720,6 +2733,12 @@ main(int argc, char **argv)
 			fprintf(stderr, "end.\n");
 		}
 	}
+	if (!is_no_checkpoint)
+	{
+		fprintf(stderr, "starting checkpoint...");
+		executeStatement(con, "checkpoint");
+		fprintf(stderr, "end.\n");
+	}
 	PQfinish(con);
 
 	/* set random seed */
#2Mark Kirkwood
mark.kirkwood@catalyst.net.nz
In reply to: KONDO Mitsumasa (#1)
Re: Add pgbench option: CHECKPOINT before starting benchmark

On 30/08/13 19:54, KONDO Mitsumasa wrote:

Hi,

I add checkpoint option to pgbench.

pgbench is simple and useful benchmark for every user. However, result of
benchmark greatly changes by some situations which are in executing checkpoint,
number of dirty buffers in share_buffers, and so on. For such a problem, it is
custom to carry out a checkpoint before starting benchmark. But it is a fact that
the making of the script takes time, like under following script.

psql -U postgres -d pgbench -p5432 -c "CHECKPOINT"
pgbench -T 600 -c 12 -j4 -U postgres -d pgbench -p 5432

However, this script have a problem.
This script execute "CHECKPOINT" -> "VACUUM" -> "starting benchmark".
If relpages have lot of dirty pages, VACUUM generate dirty buffers on
shared_buffers, and it will cause bad heavily checkpoint.

I think pgbench would be more easy and accuracy benchmark tools for everyone. So
I set checkpoint before starting benchmark.

This patch's output is here.
-----------------------------------------------------
[mitsu-ko@localhost pgbench]$ ./pgbench
starting vacuum...end.
starting checkpoint...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
tps = 312.851958 (including connections establishing)
tps = 364.524478 (excluding connections establishing)
-----------------------------------------------------
It execute "VACUUM" -> "CHECKPOINT" -> "starting benchmark".
I think it is ideal setting for more accuracy benchmark.

My patches option difinition is here.
[mitsu-ko@localhost pgbench]$ ./pgbench --help
~
-N, --no-checkpoint do not run CHECKPOINT after initialization
~
In latest commited pgbench, -N is "--skip-some-updates skip updates of
pgbench_tellers and pgbench_branches". But I cannot understand why -N is this
option, so I set this option -u, and -N is "do not run CHECKPOINT option".

What do you think?

+1

I have been using a script to add CHECKPOINT before pgbench runs for
ages...adding the option to pgbench is a great idea (wish I had thought
of it)!

Regards

Mark

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#3Fabien COELHO
coelho@cri.ensmp.fr
In reply to: KONDO Mitsumasa (#1)
Re: Add pgbench option: CHECKPOINT before starting benchmark

My patches option difinition is here.
[mitsu-ko@localhost pgbench]$ ./pgbench --help
~
-N, --no-checkpoint do not run CHECKPOINT after initialization
~
In latest commited pgbench, -N is "--skip-some-updates skip updates of
pgbench_tellers and pgbench_branches". But I cannot understand why -N is this
option, so I set this option -u, and -N is "do not run CHECKPOINT option".

What do you think?

Although I agree that the -N is not really meaningful, ISTM that changing
option names is to be avoided if people are used to it. In this instance,
I'm not sure that many people use this option, so maybe this is not an
issue.

The patch should also update the sgml documentation.

--
Fabien.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#4Tom Lane
tgl@sss.pgh.pa.us
In reply to: KONDO Mitsumasa (#1)
Re: Add pgbench option: CHECKPOINT before starting benchmark

KONDO Mitsumasa <kondo.mitsumasa@lab.ntt.co.jp> writes:

My patches option difinition is here.
[mitsu-ko@localhost pgbench]$ ./pgbench --help
~
-N, --no-checkpoint do not run CHECKPOINT after initialization
~
In latest commited pgbench, -N is "--skip-some-updates skip updates of
pgbench_tellers and pgbench_branches". But I cannot understand why -N is this
option, so I set this option -u, and -N is "do not run CHECKPOINT option".

What do you think?

I think it's a bad idea to change the meaning of a pre-existing option
letter, and a worse idea to change pgbench's default behavior. Instead,
have the option be "--checkpoint do a CHECKPOINT after initialization".

It doesn't look like there's any free single-letter option that goes
nicely with "checkpoint" either, but that's life.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers