pgbench - allow to specify scale as a size

Started by Fabien COELHOalmost 8 years ago17 messages

coelho@cri.ensmp.fr

almost 8 years ago

1 attachment(s)

After Karel Moppel piece on pgbench scale/size conversion, it occured to
me that having this as an option would be nice.

https://www.cybertec-postgresql.com/en/a-formula-to-calculate-pgbench-scaling-factor-for-target-db-size/

Here is a attempt at extending --scale so that it can be given a size.

pgbench -i --scale=124G ...

The approximated database size is also shown in the end-of-run summary.

--
Fabien.

Attachments:

pgbench-scale-size-1.patchtext/x-diff; name=pgbench-scale-size-1.patchDownload

diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 3dd492c..093e1d4 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -49,7 +49,7 @@
 
 <screen>
 transaction type: &lt;builtin: TPC-B (sort of)&gt;
-scaling factor: 10
+scaling factor: 10 (about 149 MiB)
 query mode: simple
 number of clients: 10
 number of threads: 1
@@ -282,6 +282,16 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
         in order to be big enough to hold the range of account
         identifiers.
        </para>
+
+       <para>
+        The scale can also be specified as an expected database size by
+        specifying a unit, assuming around 15 MiB size increments per scale unit.
+        For instance, <literal>-s 5G</literal> will approximate the scale required
+        for a 5 GiB database.
+        Allowed units are IEC 1024 powers (<literal>KiB MiB GiB TiB PiB</literal>),
+        SI 1000 powers (<literal>kB MB GB TB PB</literal>) and for convenience
+        simple size prefixes <literal>K M G T P</literal> are aliases for the IEC binary sizes.
+       </para>
       </listitem>
      </varlistentry>
 
@@ -1600,7 +1610,7 @@ END;
 <screen>
 starting vacuum...end.
 transaction type: &lt;builtin: TPC-B (sort of)&gt;
-scaling factor: 1
+scaling factor: 1 (about 15 MiB)
 query mode: simple
 number of clients: 10
 number of threads: 1
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index d420942..38eb13d 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -524,7 +524,7 @@ usage(void)
 		   "  -F, --fillfactor=NUM     set fill factor\n"
 		   "  -n, --no-vacuum          do not run VACUUM during initialization\n"
 		   "  -q, --quiet              quiet logging (one message each 5 seconds)\n"
-		   "  -s, --scale=NUM          scaling factor\n"
+		   "  -s, --scale=NUM|SIZE     scaling factor or expected database size\n"
 		   "  --foreign-keys           create foreign key constraints between tables\n"
 		   "  --index-tablespace=TABLESPACE\n"
 		   "                           create indexes in the specified tablespace\n"
@@ -552,7 +552,7 @@ usage(void)
 		   "  -P, --progress=NUM       show thread progress report every NUM seconds\n"
 		   "  -r, --report-latencies   report average latency per command\n"
 		   "  -R, --rate=NUM           target rate in transactions per second\n"
-		   "  -s, --scale=NUM          report this scale factor in output\n"
+		   "  -s, --scale=NUM|SIZE     report this scale factor in output\n"
 		   "  -t, --transactions=NUM   number of transactions each client runs (default: 10)\n"
 		   "  -T, --time=NUM           duration of benchmark test in seconds\n"
 		   "  -v, --vacuum-all         vacuum all four standard tables before tests\n"
@@ -668,6 +668,87 @@ gotdigits:
 	return ((sign < 0) ? -result : result);
 }
 
+/* return a size in bytes, or exit with an error message
+ */
+static int64
+parse_size(char * s, const char * error_message)
+{
+	static struct { char *name; int64 multiplier; }
+		UNITS[17] = {
+			/* IEC units */
+			{ "KiB", 1024 },
+			{ "MiB", 1024 * 1024 },
+			{ "GiB", 1024 * 1024 * 1024 },
+			{ "TiB", (int64) 1024 * 1024 * 1024 * 1024 },
+			{ "PiB", (int64) 1024 * 1024 * 1024 * 1024 * 1024 },
+			/* SI units */
+			{ "kB", 1000 },
+			{ "MB", 1000 * 1000 },
+			{ "GB", 1000 * 1000 * 1000 },
+			{ "TB", (int64) 1000 * 1000 * 1000 * 1000 },
+			{ "PB", (int64) 1000 * 1000 * 1000 * 1000 * 1000 },
+			/* common/convenient JEDEC usage */
+			{ "KB", 1024 },
+			{ "K", 1024 },
+			{ "M", 1024 * 1024 },
+			{ "G", 1024 * 1024 * 1024 },
+			{ "T", (int64) 1024 * 1024 * 1024 * 1024 },
+			{ "P", (int64) 1024 * 1024 * 1024 * 1024 * 1024 },
+			/* unit */
+			{ "B", 1 },
+	};
+
+	int		len = strlen(s), last = -1, i;
+	int64	size;
+	char	clast;
+
+	/* look for the unit */
+	for (i = 0; i < lengthof(UNITS); i++)
+		if (strcmp(s + len - strlen(UNITS[i].name), UNITS[i].name) == 0)
+			break;
+
+	/* found, or not */
+	if (i < lengthof(UNITS))
+	{
+		last = len - strlen(UNITS[i].name);
+		clast = s[last];
+		s[last] = '\0';
+	}
+	else /* assume bytes */
+		i = lengthof(UNITS) - 1;
+
+	if (!is_an_int(s))
+	{
+		fprintf(stderr, "invalid %s: \"%s\"\n", error_message, s);
+		exit(1);
+	}
+
+	size = strtoint64(s) * UNITS[i].multiplier;
+
+	if (last != -1)
+		s[last] = clast;
+
+	return size;
+}
+
+/* parse scale, returning at least 1 */
+static int
+parse_scale(char * s)
+{
+	int64 size = parse_size(s, "scaling factor");
+	/*
+	 * formula from Kaarel Moppel linear regression on pg 10.1,
+	 * which gives about 15 MiB per pgbench scale unit
+	 */
+	int scale = (int) ceil(0.066888816 * size / (1024 * 1024) - 0.511799076);
+	if (scale <= 0)
+	{
+		fprintf(stderr, "scale %s too small, rounded to 1\n", s);
+		scale = 1;
+	}
+	return scale;
+}
+
 /* random number generator: uniform distribution from min to max inclusive */
 static int64
 getrand(TState *thread, int64 min, int64 max)
@@ -4244,7 +4325,8 @@ printResults(TState *threads, StatsData *total, instr_time total_time,
 	/* Report test parameters. */
 	printf("transaction type: %s\n",
 		   num_scripts == 1 ? sql_script[0].desc : "multiple scripts");
-	printf("scaling factor: %d\n", scale);
+	/* scale to MiB evaluation must be consistent with parse_scale */
+	printf("scaling factor: %d (about %.0f MiB)\n", scale, 14.95 * scale);
 	printf("query mode: %s\n", QUERYMODE[querymode]);
 	printf("number of clients: %d\n", nclients);
 	printf("number of threads: %d\n", nthreads);
@@ -4560,12 +4642,7 @@ main(int argc, char **argv)
 				break;
 			case 's':
 				scale_given = true;
-				scale = atoi(optarg);
-				if (scale <= 0)
-				{
-					fprintf(stderr, "invalid scaling factor: \"%s\"\n", optarg);
-					exit(1);
-				}
+				scale = parse_scale(optarg);
 				break;
 			case 't':
 				benchmarking_option_set = true;
diff --git a/src/bin/pgbench/t/002_pgbench_no_server.pl b/src/bin/pgbench/t/002_pgbench_no_server.pl
index 6ea55f8..52d135c 100644
--- a/src/bin/pgbench/t/002_pgbench_no_server.pl
+++ b/src/bin/pgbench/t/002_pgbench_no_server.pl
@@ -44,6 +44,7 @@ my @options = (
 	[   'bad #threads', '-j eleven', [qr{invalid number of threads: "eleven"}]
 	],
 	[ 'bad scale', '-i -s two', [qr{invalid scaling factor: "two"}] ],
+	[ 'bad scale size', '-i -s 2stuff', [qr{invalid scaling factor: "2stuff"}] ],
 	[   'invalid #transactions',
 		'-t zil',
 		[qr{invalid number of transactions: "zil"}] ],

Erik Rijkers

er@xs4all.nl

almost 8 years ago

In reply to: Fabien COELHO (#1)

Re: pgbench - allow to specify scale as a size

On 2018-02-17 10:20, Fabien COELHO wrote:

After Karel Moppel piece on pgbench scale/size conversion, it occured
to me that having this as an option would be nice.

https://www.cybertec-postgresql.com/en/a-formula-to-calculate-pgbench-scaling-factor-for-target-db-size/

Here is a attempt at extending --scale so that it can be given a size.

pgbench -i --scale=124G ...

The approximated database size is also shown in the end-of-run summary.

[pgbench-scale-size-1.patch]

Seem a nice addition but something isn't quite right; with '-s 50' (no
unit) I get: 'scale 50 too small':

$ pgbench -is 50
scale 50 too small, rounded to 1
dropping old tables...
creating tables...
generating data...
100000 of 100000 tuples (100%) done (elapsed 0.13 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.

echo '\dti+ pgbench_accounts*' | psql -qX

thanks,

Erik Rijkers

Fabien COELHO

coelho@cri.ensmp.fr

almost 8 years ago

In reply to: Erik Rijkers (#2)

1 attachment(s)

Re: pgbench - allow to specify scale as a size

Seem a nice addition but something isn't quite right; with '-s 50' (no unit)
I get: 'scale 50 too small':

Sigh. Indeed, it seems that I forgot to test some cases... Thanks
for the debug. Here is an hopefully better attempt.

I also upgraded the regression test to test more that "-s 1". I also tried
to improve the documentation to point out that the result is an
approximation.

--
Fabien.

Attachments:

pgbench-scale-size-2.patchtext/x-diff; name=pgbench-scale-size-2.patchDownload

diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 3dd492c..82343ed 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -49,7 +49,7 @@
 
 <screen>
 transaction type: &lt;builtin: TPC-B (sort of)&gt;
-scaling factor: 10
+scaling factor: 10 (about 150 MiB)
 query mode: simple
 number of clients: 10
 number of threads: 1
@@ -282,6 +282,21 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
         in order to be big enough to hold the range of account
         identifiers.
        </para>
+
+       <para>
+        The scale can also be specified as an expected database size by
+        specifying a unit, assuming around 15 MiB size increments per scale
+        unit.
+        For instance, <literal>-s 5G</literal> will approximate the scale
+        required for a 5 GiB database.
+        Note that the size to scale conversion is based on a regression which
+        entails some errors, especially for small scales.
+        Moreover, the actual database size may vary depending on options.
+        Allowed units are IEC 1024 powers (<literal>KiB MiB GiB TiB PiB</literal>),
+        SI 1000 powers (<literal>kB MB GB TB PB</literal>) and for convenience
+        simple size prefixes <literal>K M G T P</literal> are aliases for the IEC
+        binary sizes.
+       </para>
       </listitem>
      </varlistentry>
 
@@ -1600,7 +1615,7 @@ END;
 <screen>
 starting vacuum...end.
 transaction type: &lt;builtin: TPC-B (sort of)&gt;
-scaling factor: 1
+scaling factor: 1 (about 15 MiB)
 query mode: simple
 number of clients: 10
 number of threads: 1
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index d420942..e3f950a 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -524,7 +524,7 @@ usage(void)
 		   "  -F, --fillfactor=NUM     set fill factor\n"
 		   "  -n, --no-vacuum          do not run VACUUM during initialization\n"
 		   "  -q, --quiet              quiet logging (one message each 5 seconds)\n"
-		   "  -s, --scale=NUM          scaling factor\n"
+		   "  -s, --scale=NUM|SIZE     scaling factor or expected database size\n"
 		   "  --foreign-keys           create foreign key constraints between tables\n"
 		   "  --index-tablespace=TABLESPACE\n"
 		   "                           create indexes in the specified tablespace\n"
@@ -552,7 +552,7 @@ usage(void)
 		   "  -P, --progress=NUM       show thread progress report every NUM seconds\n"
 		   "  -r, --report-latencies   report average latency per command\n"
 		   "  -R, --rate=NUM           target rate in transactions per second\n"
-		   "  -s, --scale=NUM          report this scale factor in output\n"
+		   "  -s, --scale=NUM|SIZE     report this scale factor in output\n"
 		   "  -t, --transactions=NUM   number of transactions each client runs (default: 10)\n"
 		   "  -T, --time=NUM           duration of benchmark test in seconds\n"
 		   "  -v, --vacuum-all         vacuum all four standard tables before tests\n"
@@ -668,6 +668,100 @@ gotdigits:
 	return ((sign < 0) ? -result : result);
 }
 
+/* return a size in bytes, or exit with an error message
+ */
+static int64
+parse_size(char * s, const char * error_message)
+{
+	static struct { char *name; int64 multiplier; }
+		UNITS[17] = {
+			/* IEC units */
+			{ "KiB", 1024 },
+			{ "MiB", 1024 * 1024 },
+			{ "GiB", 1024 * 1024 * 1024 },
+			{ "TiB", (int64) 1024 * 1024 * 1024 * 1024 },
+			{ "PiB", (int64) 1024 * 1024 * 1024 * 1024 * 1024 },
+			/* SI units */
+			{ "kB", 1000 },
+			{ "MB", 1000 * 1000 },
+			{ "GB", 1000 * 1000 * 1000 },
+			{ "TB", (int64) 1000 * 1000 * 1000 * 1000 },
+			{ "PB", (int64) 1000 * 1000 * 1000 * 1000 * 1000 },
+			/* common/convenient JEDEC usage */
+			{ "KB", 1024 },
+			{ "K", 1024 },
+			{ "M", 1024 * 1024 },
+			{ "G", 1024 * 1024 * 1024 },
+			{ "T", (int64) 1024 * 1024 * 1024 * 1024 },
+			{ "P", (int64) 1024 * 1024 * 1024 * 1024 * 1024 },
+			/* unit */
+			{ "B", 1 },
+	};
+
+	int		len = strlen(s), last = -1, i;
+	int64	size;
+	char	clast;
+
+	/* look for the unit */
+	for (i = 0; i < lengthof(UNITS); i++)
+		if (strcmp(s + len - strlen(UNITS[i].name), UNITS[i].name) == 0)
+			break;
+
+	/* found, or not */
+	if (i < lengthof(UNITS))
+	{
+		last = len - strlen(UNITS[i].name);
+		clast = s[last];
+		s[last] = '\0';
+	}
+	else /* assume bytes */
+		i = lengthof(UNITS) - 1;
+
+	if (!is_an_int(s))
+	{
+		fprintf(stderr, "invalid %s: \"%s\"\n", error_message, s);
+		exit(1);
+	}
+
+	size = strtoint64(s) * UNITS[i].multiplier;
+
+	if (last != -1)
+		s[last] = clast;
+
+	return size;
+}
+
+/* parse scale, returning at least 1 */
+static int
+parse_scale(char * s)
+{
+	int scale;
+
+	if (is_an_int(s))
+	{
+		/* standard scaling */
+		scale = atoi(s);
+	}
+	else
+	{
+		/* try size scaling */
+		int64 size = parse_size(s, "scaling factor");
+		/*
+		 * formula from Kaarel Moppel linear regression on pg 10.1,
+		 * which gives about 15 MiB per pgbench scale unit
+		 */
+		scale = (int) ceil(0.066888816 * size / (1024 * 1024) - 0.511799076);
+	}
+
+	if (scale <= 0)
+	{
+		fprintf(stderr, "scale %s too small, rounded to 1\n", s);
+		scale = 1;
+	}
+
+	return scale;
+}
+
 /* random number generator: uniform distribution from min to max inclusive */
 static int64
 getrand(TState *thread, int64 min, int64 max)
@@ -4244,7 +4338,8 @@ printResults(TState *threads, StatsData *total, instr_time total_time,
 	/* Report test parameters. */
 	printf("transaction type: %s\n",
 		   num_scripts == 1 ? sql_script[0].desc : "multiple scripts");
-	printf("scaling factor: %d\n", scale);
+	/* scale to MiB evaluation must be consistent with parse_scale */
+	printf("scaling factor: %d (about %.0f MiB)\n", scale, 14.95 * scale);
 	printf("query mode: %s\n", QUERYMODE[querymode]);
 	printf("number of clients: %d\n", nclients);
 	printf("number of threads: %d\n", nthreads);
@@ -4560,12 +4655,7 @@ main(int argc, char **argv)
 				break;
 			case 's':
 				scale_given = true;
-				scale = atoi(optarg);
-				if (scale <= 0)
-				{
-					fprintf(stderr, "invalid scaling factor: \"%s\"\n", optarg);
-					exit(1);
-				}
+				scale = parse_scale(optarg);
 				break;
 			case 't':
 				benchmarking_option_set = true;
diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl
index 99286f6..49e0ce3 100644
--- a/src/bin/pgbench/t/001_pgbench_with_server.pl
+++ b/src/bin/pgbench/t/001_pgbench_with_server.pl
@@ -81,7 +81,7 @@ pgbench(
 
 # Again, with all possible options
 pgbench(
-'--initialize --init-steps=dtpvg --scale=1 --unlogged-tables --fillfactor=98 --foreign-keys --quiet --tablespace=pg_default --index-tablespace=pg_default',
+'--initialize --init-steps=dtpvg --scale=30M --unlogged-tables --fillfactor=98 --foreign-keys --quiet --tablespace=pg_default --index-tablespace=pg_default',
 	0,
 	[qr{^$}i],
 	[   qr{dropping old tables},
@@ -89,6 +89,7 @@ pgbench(
 		qr{vacuuming},
 		qr{creating primary keys},
 		qr{creating foreign keys},
+		qr{200000 of 200000 tuples}, # scale 2
 		qr{done\.} ],
 	'pgbench scale 1 initialization');
 
diff --git a/src/bin/pgbench/t/002_pgbench_no_server.pl b/src/bin/pgbench/t/002_pgbench_no_server.pl
index 6ea55f8..52d135c 100644
--- a/src/bin/pgbench/t/002_pgbench_no_server.pl
+++ b/src/bin/pgbench/t/002_pgbench_no_server.pl
@@ -44,6 +44,7 @@ my @options = (
 	[   'bad #threads', '-j eleven', [qr{invalid number of threads: "eleven"}]
 	],
 	[ 'bad scale', '-i -s two', [qr{invalid scaling factor: "two"}] ],
+	[ 'bad scale size', '-i -s 2stuff', [qr{invalid scaling factor: "2stuff"}] ],
 	[   'invalid #transactions',
 		'-t zil',
 		[qr{invalid number of transactions: "zil"}] ],

Tom Lane

tgl@sss.pgh.pa.us

almost 8 years ago

In reply to: Fabien COELHO (#1)

Re: pgbench - allow to specify scale as a size

Fabien COELHO <coelho@cri.ensmp.fr> writes:

Here is a attempt at extending --scale so that it can be given a size.

I do not actually find this to be a good idea. It's going to be
platform-dependent, or not very accurate, or both, and thereby
contribute to confusion by making results less reproducible.

Plus, what do we do if the backend changes table representation in
some way that invalidates Kaarel's formula altogether? More confusion
would be inevitable.

regards, tom lane

Alvaro Hernandez

aht@ongres.com

almost 8 years ago

In reply to: Tom Lane (#4)

Re: pgbench - allow to specify scale as a size

On 17/02/18 11:26, Tom Lane wrote:

Fabien COELHO <coelho@cri.ensmp.fr> writes:

Here is a attempt at extending --scale so that it can be given a size.

I do not actually find this to be a good idea. It's going to be
platform-dependent, or not very accurate, or both, and thereby
contribute to confusion by making results less reproducible.

Plus, what do we do if the backend changes table representation in
some way that invalidates Kaarel's formula altogether? More confusion
would be inevitable.

Why not then insert a "few" rows, measure size, truncate the table,
compute the formula and then insert to the desired user requested size?
(or insert what should be the minimum, scale 1, measure, and extrapolate
what's missing). It doesn't sound too complicated to me, and targeting a
size is something that I believe it's quite good for user.

Álvaro

Alvaro Hernandez

-----------
OnGres

Tom Lane

tgl@sss.pgh.pa.us

almost 8 years ago

In reply to: Alvaro Hernandez (#5)

Re: pgbench - allow to specify scale as a size

Alvaro Hernandez <aht@ongres.com> writes:

On 17/02/18 11:26, Tom Lane wrote:

Fabien COELHO <coelho@cri.ensmp.fr> writes:

Here is a attempt at extending --scale so that it can be given a size.

I do not actually find this to be a good idea. It's going to be
platform-dependent, or not very accurate, or both, and thereby
contribute to confusion by making results less reproducible.

Plus, what do we do if the backend changes table representation in
some way that invalidates Kaarel's formula altogether? More confusion
would be inevitable.

Why not then insert a "few" rows, measure size, truncate the table,
compute the formula and then insert to the desired user requested size?
(or insert what should be the minimum, scale 1, measure, and extrapolate
what's missing). It doesn't sound too complicated to me, and targeting a
size is something that I believe it's quite good for user.

Then you'd *really* have irreproducible results.

regards, tom lane

Alvaro Hernandez

aht@ongres.com

almost 8 years ago

In reply to: Tom Lane (#6)

Re: pgbench - allow to specify scale as a size

On 17/02/18 12:17, Tom Lane wrote:

Alvaro Hernandez <aht@ongres.com> writes:

On 17/02/18 11:26, Tom Lane wrote:

Fabien COELHO <coelho@cri.ensmp.fr> writes:

Here is a attempt at extending --scale so that it can be given a size.

I do not actually find this to be a good idea. It's going to be
platform-dependent, or not very accurate, or both, and thereby
contribute to confusion by making results less reproducible.

Plus, what do we do if the backend changes table representation in
some way that invalidates Kaarel's formula altogether? More confusion
would be inevitable.

Why not then insert a "few" rows, measure size, truncate the table,
compute the formula and then insert to the desired user requested size?
(or insert what should be the minimum, scale 1, measure, and extrapolate
what's missing). It doesn't sound too complicated to me, and targeting a
size is something that I believe it's quite good for user.

Then you'd *really* have irreproducible results.

regards, tom lane

You also have irreproducible results today, according to your
criteria. Either you agree on the number of rows but may not agree on
the size (today), or you agree on the size but may not agree on the
number of rows. Right now you can only pick the former, while I think
people would significantly appreciate the latter. If neither is correct,
let's at least provide the choice.

Regards,

Álvaro

Alvaro Hernandez

-----------
OnGres

Fabien COELHO

coelho@cri.ensmp.fr

almost 8 years ago

In reply to: Tom Lane (#4)

Re: pgbench - allow to specify scale as a size

Hello Tom,

Here is a attempt at extending --scale so that it can be given a size.

I do not actually find this to be a good idea. It's going to be
platform-dependent, or not very accurate, or both, and thereby
contribute to confusion by making results less reproducible.

I have often wanted to have such an option for testing, with criterion
like "within shared_buffers", "within memory", "twice the available
memory", to look for behavioral changes in some performance tests.

I you want reproducible (for some definition of reproducible) and
accurate, you can always use scale with a number. The report provides the
actual scale used anyway, so providing the size is just a convenience for
the initialization phase. I agree that it cannot be really exact.

Would it be more acceptable with some clear(er)/explicit caveat?

Plus, what do we do if the backend changes table representation in
some way that invalidates Kaarel's formula altogether?

Then the formula (a simple linear regression, really) should have to be
updated?

More confusion would be inevitable.

There is no much confusion when the "scale" is reported. As for confusion,
a performance tests is influenced by dozen of parameters anyway.

Now if you do not want such a feature, you can mark it as rejected, and we
will keep on trying to guess or look for the formula till the end of
time:-)

--
Fabien.

Fabien COELHO

coelho@cri.ensmp.fr

almost 8 years ago

In reply to: Alvaro Hernandez (#5)

Re: pgbench - allow to specify scale as a size

Why not then insert a "few" rows, measure size, truncate the table,
compute the formula and then insert to the desired user requested size? (or
insert what should be the minimum, scale 1, measure, and extrapolate what's
missing). It doesn't sound too complicated to me, and targeting a size is
something that I believe it's quite good for user.

The formula I used approximates the whole database, not just one table.
There was one for the table, but this is only part of the issue. In
particular, ISTM that index sizes should be included when caching is
considered.

Also, index sizes are probably in n ln(n), so some level of approximation
is inevitable.

Moreover, the intrinsic granularity of TPC-B as multiple of 100,000 rows
makes it not very precise wrt size anyway.

--
Fabien.

#10

Alvaro Hernandez

aht@ongres.com

almost 8 years ago

In reply to: Fabien COELHO (#9)

Re: pgbench - allow to specify scale as a size

On 17/02/18 12:37, Fabien COELHO wrote:

Why not then insert a "few" rows, measure size, truncate the
table, compute the formula and then insert to the desired user
requested size? (or insert what should be the minimum, scale 1,
measure, and extrapolate what's missing). It doesn't sound too
complicated to me, and targeting a size is
something that I believe it's quite good for user.

The formula I used approximates the whole database, not just one
table. There was one for the table, but this is only part of the
issue. In particular, ISTM that index sizes should be included when
caching is considered.

Also, index sizes are probably in n ln(n), so some level of
approximation is inevitable.

Moreover, the intrinsic granularity of TPC-B as multiple of 100,000
rows makes it not very precise wrt size anyway.

Sure, makes sense, so my second suggestion seems more reasonable:
insert with scale 1, measure there (ok, you might need to crete indexes
only to later drop them), and if computed scale > 1 then insert whatever
is left to insert. Shouldn't be a big deal to me.

I like the feature :)

Álvaro

Alvaro Hernandez

-----------
OnGres

#11

Fabien COELHO

coelho@cri.ensmp.fr

almost 8 years ago

In reply to: Alvaro Hernandez (#10)

Re: pgbench - allow to specify scale as a size

Hello Alvaro & Tom,

Why not then insert a "few" rows, measure size, truncate the table,
compute the formula and then insert to the desired user requested
size? (or insert what should be the minimum, scale 1, measure, and
extrapolate what's missing). It doesn't sound too complicated to me,
and targeting a size is something that I believe it's quite good for
user.

The formula I used approximates the whole database, not just one table.
There was one for the table, but this is only part of the issue. In
particular, ISTM that index sizes should be included when caching is
considered.

Also, index sizes are probably in n ln(n), so some level of
approximation is inevitable.

Moreover, the intrinsic granularity of TPC-B as multiple of 100,000
rows makes it not very precise wrt size anyway.

Sure, makes sense, so my second suggestion seems more reasonable: insert
with scale 1, measure there (ok, you might need to crete indexes only to
later drop them), and if computed scale > 1 then insert whatever is left
to insert. Shouldn't be a big deal to me.

I could implement that, even if it would lead to some approximation
nevertheless: ISTM that the very large scale regression performed by
Kaarel is significantly more precise than testing with scale 1 (typically
a few MiB) and extrapolation that to hundreds of GiB.

Maybe it could be done with kind of an open ended dichotomy, but creating
and recreating index looks like an ugly solution, and what should be
significant is the whole database size, including tellers & branches
tables and all indexes, so I'm not convinced. Now as tellers & branches
tables have basically the same structure as accounts, it could be just
scaled by assuming that it would incur the same storage per row.

Anyway, even if I do not like it, it could be better than nothing. The key
point for me is that if Tom is dead set against the feature the patch is
dead anyway.

Tom, would Alvaro approach be more admissible to you that a fixed formula
that would need updating, keeping in mind that such a feature implies
some level approximation?

--
Fabien.

#12

Mark Wong

mark@2ndQuadrant.com

almost 8 years ago

In reply to: Alvaro Hernandez (#7)

Re: pgbench - allow to specify scale as a size

On Sat, Feb 17, 2018 at 12:22:37PM -0500, Alvaro Hernandez wrote:

On 17/02/18 12:17, Tom Lane wrote:

Alvaro Hernandez <aht@ongres.com> writes:

On 17/02/18 11:26, Tom Lane wrote:

Fabien COELHO <coelho@cri.ensmp.fr> writes:

Here is a attempt at extending --scale so that it can be given a size.

I do not actually find this to be a good idea. It's going to be
platform-dependent, or not very accurate, or both, and thereby
contribute to confusion by making results less reproducible.

Plus, what do we do if the backend changes table representation in
some way that invalidates Kaarel's formula altogether? More confusion
would be inevitable.

ï¿½ï¿½ï¿½ Why not then insert a "few" rows, measure size, truncate the table,
compute the formula and then insert to the desired user requested size?
(or insert what should be the minimum, scale 1, measure, and extrapolate
what's missing). It doesn't sound too complicated to me, and targeting a
size is something that I believe it's quite good for user.

Then you'd *really* have irreproducible results.

regards, tom lane

ï¿½ï¿½ï¿½ You also have irreproducible results today, according to your
criteria. Either you agree on the number of rows but may not agree on
the size (today), or you agree on the size but may not agree on the
number of rows. Right now you can only pick the former, while I think
people would significantly appreciate the latter. If neither is correct,
let's at least provide the choice.

What if we consider using ascii (utf8?) text file sizes as a reference
point, something independent from the database?

I realize even if a flat file size can be used as a more consistent
reference across platforms, it doesn't help with accurately determining
the final data file sizes due to any architecture specific nuances or
changes in the backend. But perhaps it might still offer a little more
meaning to be able to say "50 gigabytes of bank account data" rather
than "10 million rows of bank accounts" when thinking about size over
cardinality.

Regards,
Mark

--
Mark Wong http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services

#13

Fabien COELHO

coelho@cri.ensmp.fr

almost 8 years ago

In reply to: Mark Wong (#12)

Re: pgbench - allow to specify scale as a size

Hello Mark,

What if we consider using ascii (utf8?) text file sizes as a reference
point, something independent from the database?

Why not.

TPC-B basically specifies that rows (accounts, tellers, branches) are all
padded to 100 bytes, thus we could consider (i.e. document) that
--scale=SIZE refers to the amount of useful data hold, and warn that
actual storage would add various overheads for page and row headers, free
space at the end of pages, indexes...

Then one scale step is 100,000 accounts, 10 tellers and 1 branch, i.e.
100,011 * 100 ~ 9.5 MiB of useful data per scale step.

I realize even if a flat file size can be used as a more consistent
reference across platforms, it doesn't help with accurately determining
the final data file sizes due to any architecture specific nuances or
changes in the backend. But perhaps it might still offer a little more
meaning to be able to say "50 gigabytes of bank account data" rather
than "10 million rows of bank accounts" when thinking about size over
cardinality.

Yep.

Now the overhead is really 60-65%. Although the specification is
unambiguous, but we still need some maths to know whether it fits in
buffers or memory... The point of Karel regression is to take this into
account.

Also, whether this option would be more admissible to Tom is still an open
question. Tom?

--
Fabien.

#14

Fabien COELHO

coelho@cri.ensmp.fr

almost 8 years ago

In reply to: Fabien COELHO (#13)

1 attachment(s)

Re: pgbench - allow to specify scale as a size

Now the overhead is really 60-65%. Although the specification is unambiguous,
but we still need some maths to know whether it fits in buffers or memory...
The point of Karel regression is to take this into account.

Also, whether this option would be more admissible to Tom is still an open
question. Tom?

Here is a version with this approach: the documentation talks about
"actual data size, without overheads", and points out that storage
overheads are typically an additional 65%.

--
Fabien.

Attachments:

pgbench-scale-size-3.patchtext/x-diff; name=pgbench-scale-size-3.patchDownload

diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index 3dd492c..ba6174f 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -49,7 +49,7 @@
 
 <screen>
 transaction type: &lt;builtin: TPC-B (sort of)&gt;
-scaling factor: 10
+scaling factor: 10 (95.4 MiB of actual data)
 query mode: simple
 number of clients: 10
 number of threads: 1
@@ -282,6 +282,20 @@ pgbench <optional> <replaceable>options</replaceable> </optional> <replaceable>d
         in order to be big enough to hold the range of account
         identifiers.
        </para>
+
+       <para>
+        The scale can also be specified as an expected actual data size in
+        the accounts, tellers and branches tables, without overheads such
+        as headers and indexes, by specifying a unit.
+        For instance, <literal>-s 5G</literal> will approximate the scale
+        required for 5 GiB of data.
+        Note that storage overheads typically represent about 65\% of the actual data,
+        and may vary depending on options.
+        Allowed units are IEC 1024 powers (<literal>KiB MiB GiB TiB PiB</literal>),
+        SI 1000 powers (<literal>kB MB GB TB PB</literal>) and for convenience
+        simple size prefixes <literal>K M G T P</literal> are aliases for the IEC
+        binary sizes.
+       </para>
       </listitem>
      </varlistentry>
 
@@ -1600,7 +1614,7 @@ END;
 <screen>
 starting vacuum...end.
 transaction type: &lt;builtin: TPC-B (sort of)&gt;
-scaling factor: 1
+scaling factor: 1 (9.5 MiB of actual data)
 query mode: simple
 number of clients: 10
 number of threads: 1
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index d420942..a8745d3 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -524,7 +524,7 @@ usage(void)
 		   "  -F, --fillfactor=NUM     set fill factor\n"
 		   "  -n, --no-vacuum          do not run VACUUM during initialization\n"
 		   "  -q, --quiet              quiet logging (one message each 5 seconds)\n"
-		   "  -s, --scale=NUM          scaling factor\n"
+		   "  -s, --scale=NUM|SIZE     scaling factor or expected actual data (without overheads) size\n"
 		   "  --foreign-keys           create foreign key constraints between tables\n"
 		   "  --index-tablespace=TABLESPACE\n"
 		   "                           create indexes in the specified tablespace\n"
@@ -552,7 +552,7 @@ usage(void)
 		   "  -P, --progress=NUM       show thread progress report every NUM seconds\n"
 		   "  -r, --report-latencies   report average latency per command\n"
 		   "  -R, --rate=NUM           target rate in transactions per second\n"
-		   "  -s, --scale=NUM          report this scale factor in output\n"
+		   "  -s, --scale=NUM|SIZE     report this scale factor in output\n"
 		   "  -t, --transactions=NUM   number of transactions each client runs (default: 10)\n"
 		   "  -T, --time=NUM           duration of benchmark test in seconds\n"
 		   "  -v, --vacuum-all         vacuum all four standard tables before tests\n"
@@ -668,6 +668,97 @@ gotdigits:
 	return ((sign < 0) ? -result : result);
 }
 
+/* return a size in bytes, or exit with an error message
+ */
+static int64
+parse_size(char * s, const char * error_message)
+{
+	static struct { char *name; int64 multiplier; }
+		UNITS[17] = {
+			/* IEC units */
+			{ "KiB", 1024 },
+			{ "MiB", 1024 * 1024 },
+			{ "GiB", 1024 * 1024 * 1024 },
+			{ "TiB", (int64) 1024 * 1024 * 1024 * 1024 },
+			{ "PiB", (int64) 1024 * 1024 * 1024 * 1024 * 1024 },
+			/* SI units */
+			{ "kB", 1000 },
+			{ "MB", 1000 * 1000 },
+			{ "GB", 1000 * 1000 * 1000 },
+			{ "TB", (int64) 1000 * 1000 * 1000 * 1000 },
+			{ "PB", (int64) 1000 * 1000 * 1000 * 1000 * 1000 },
+			/* common/convenient JEDEC usage */
+			{ "KB", 1024 },
+			{ "K", 1024 },
+			{ "M", 1024 * 1024 },
+			{ "G", 1024 * 1024 * 1024 },
+			{ "T", (int64) 1024 * 1024 * 1024 * 1024 },
+			{ "P", (int64) 1024 * 1024 * 1024 * 1024 * 1024 },
+			/* unit */
+			{ "B", 1 },
+	};
+
+	int		len = strlen(s), last = -1, i;
+	int64	size;
+	char	clast;
+
+	/* look for the unit */
+	for (i = 0; i < lengthof(UNITS); i++)
+		if (strcmp(s + len - strlen(UNITS[i].name), UNITS[i].name) == 0)
+			break;
+
+	/* found, or not */
+	if (i < lengthof(UNITS))
+	{
+		last = len - strlen(UNITS[i].name);
+		clast = s[last];
+		s[last] = '\0';
+	}
+	else /* assume bytes */
+		i = lengthof(UNITS) - 1;
+
+	if (!is_an_int(s))
+	{
+		fprintf(stderr, "invalid %s: \"%s\"\n", error_message, s);
+		exit(1);
+	}
+
+	size = strtoint64(s) * UNITS[i].multiplier;
+
+	if (last != -1)
+		s[last] = clast;
+
+	return size;
+}
+
+/* parse scale, returning at least 1 */
+static int
+parse_scale(char * s)
+{
+	int scale;
+
+	if (is_an_int(s))
+	{
+		/* standard scaling */
+		scale = atoi(s);
+	}
+	else
+	{
+		/* try data size scaling */
+		int64 size = parse_size(s, "scaling factor");
+		/* size refers to actual data, without overheads */
+		scale = (int) ceil(size / ((naccounts + ntellers + nbranches) * 100.0));
+	}
+
+	if (scale <= 0)
+	{
+		fprintf(stderr, "scale %s too small, rounded to 1\n", s);
+		scale = 1;
+	}
+
+	return scale;
+}
+
 /* random number generator: uniform distribution from min to max inclusive */
 static int64
 getrand(TState *thread, int64 min, int64 max)
@@ -4244,7 +4335,9 @@ printResults(TState *threads, StatsData *total, instr_time total_time,
 	/* Report test parameters. */
 	printf("transaction type: %s\n",
 		   num_scripts == 1 ? sql_script[0].desc : "multiple scripts");
-	printf("scaling factor: %d\n", scale);
+	/* scale to MiB evaluation must be consistent with parse_scale */
+	printf("scaling factor: %d (%.1f MiB of actual data)\n",
+		   scale, (naccounts + ntellers + nbranches) * 100.0 * scale / (1024 * 1024));
 	printf("query mode: %s\n", QUERYMODE[querymode]);
 	printf("number of clients: %d\n", nclients);
 	printf("number of threads: %d\n", nthreads);
@@ -4560,12 +4653,7 @@ main(int argc, char **argv)
 				break;
 			case 's':
 				scale_given = true;
-				scale = atoi(optarg);
-				if (scale <= 0)
-				{
-					fprintf(stderr, "invalid scaling factor: \"%s\"\n", optarg);
-					exit(1);
-				}
+				scale = parse_scale(optarg);
 				break;
 			case 't':
 				benchmarking_option_set = true;
diff --git a/src/bin/pgbench/t/001_pgbench_with_server.pl b/src/bin/pgbench/t/001_pgbench_with_server.pl
index 99286f6..d64f55d 100644
--- a/src/bin/pgbench/t/001_pgbench_with_server.pl
+++ b/src/bin/pgbench/t/001_pgbench_with_server.pl
@@ -81,7 +81,7 @@ pgbench(
 
 # Again, with all possible options
 pgbench(
-'--initialize --init-steps=dtpvg --scale=1 --unlogged-tables --fillfactor=98 --foreign-keys --quiet --tablespace=pg_default --index-tablespace=pg_default',
+'--initialize --init-steps=dtpvg --scale=18M --unlogged-tables --fillfactor=98 --foreign-keys --quiet --tablespace=pg_default --index-tablespace=pg_default',
 	0,
 	[qr{^$}i],
 	[   qr{dropping old tables},
@@ -89,6 +89,7 @@ pgbench(
 		qr{vacuuming},
 		qr{creating primary keys},
 		qr{creating foreign keys},
+		qr{200000 of 200000 tuples}, # scale 2
 		qr{done\.} ],
 	'pgbench scale 1 initialization');
 
diff --git a/src/bin/pgbench/t/002_pgbench_no_server.pl b/src/bin/pgbench/t/002_pgbench_no_server.pl
index 6ea55f8..52d135c 100644
--- a/src/bin/pgbench/t/002_pgbench_no_server.pl
+++ b/src/bin/pgbench/t/002_pgbench_no_server.pl
@@ -44,6 +44,7 @@ my @options = (
 	[   'bad #threads', '-j eleven', [qr{invalid number of threads: "eleven"}]
 	],
 	[ 'bad scale', '-i -s two', [qr{invalid scaling factor: "two"}] ],
+	[ 'bad scale size', '-i -s 2stuff', [qr{invalid scaling factor: "2stuff"}] ],
 	[   'invalid #transactions',
 		'-t zil',
 		[qr{invalid number of transactions: "zil"}] ],

#15

Peter Eisentraut

peter.eisentraut@2ndquadrant.com

almost 8 years ago

In reply to: Fabien COELHO (#14)

Re: pgbench - allow to specify scale as a size

On 2/20/18 05:06, Fabien COELHO wrote:

Now the overhead is really 60-65%. Although the specification is unambiguous,
but we still need some maths to know whether it fits in buffers or memory...
The point of Karel regression is to take this into account.

Also, whether this option would be more admissible to Tom is still an open
question. Tom?

Here is a version with this approach: the documentation talks about
"actual data size, without overheads", and points out that storage
overheads are typically an additional 65%.

I think when deciding on a size for a test database for benchmarking,
you want to size it relative to RAM or other storage layers. So a
feature that allows you to create a database of size N but it's actually
not going to be anywhere near N seems pretty useless for that.

(Also, we have, for better or worse, settled on a convention for byte
unit prefixes in guc.c. Let's not introduce another one.)

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

#16

Fabien COELHO

coelho@cri.ensmp.fr

almost 8 years ago

In reply to: Peter Eisentraut (#15)

Re: pgbench - allow to specify scale as a size

Now the overhead is really 60-65%. Although the specification is unambiguous,
but we still need some maths to know whether it fits in buffers or memory...
The point of Karel regression is to take this into account.

Also, whether this option would be more admissible to Tom is still an open
question. Tom?

Here is a version with this approach: the documentation talks about
"actual data size, without overheads", and points out that storage
overheads are typically an additional 65%.

I think when deciding on a size for a test database for benchmarking,
you want to size it relative to RAM or other storage layers. So a
feature that allows you to create a database of size N but it's actually
not going to be anywhere near N seems pretty useless for that.

Hmmm.

At least the option say the size of the useful data, which should be what
the user be really interested in:-) You have a developer point of view
about the issue. From a performance point of view, ISTM that useful data
storage size is an interesting measure, which allows to compare between
(future) storage engines and show the impact of smaller overheads, for
instance.

The other option can only be some kind of approximation, and would require
some kind of maintenance (eg a future zheap overhead would be different
that the heap overhead, the overhead depends on the size itself, and it
could also depend on other options). This has been rejected, and I agree
with the rejection (incredible:-).

So ISTM that the patch is dead because it is somehow necessarily
imprecise. People will continue to do some wild guessing on how to
translate scale to anything related to size.

(Also, we have, for better or worse, settled on a convention for byte
unit prefixes in guc.c. Let's not introduce another one.)

Hmmm. Indeed for worse, as it is soooo much better to invent our own units
than to reuse existing ones which were not confusing enough:-)

- SI units: 1kB = 1000 bytes (*small* k)
- IEC units: 1KiB = 1024 bytes
- JEDEC units: 1KB = 1024 bytes (*capital* k)

But postgres documentation uses "kB" for 1024 bytes, too bad:-(

The gucs are about memory, which is measured in 1024, but the storage is
usually measured in 1000, and this option was about storage, hence I felt
it better to avoid confusion.

Conclusion: mark the patch as rejected?

--
Fabien.

#17

Peter Eisentraut

peter.eisentraut@2ndquadrant.com

almost 8 years ago

In reply to: Fabien COELHO (#16)

Re: pgbench - allow to specify scale as a size

On 3/4/18 04:09, Fabien COELHO wrote:

So ISTM that the patch is dead because it is somehow necessarily
imprecise. People will continue to do some wild guessing on how to
translate scale to anything related to size.

I think so.

Conclusion: mark the patch as rejected?

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services