~drizzle-trunk/drizzle/development : contents of tests/sql-bench/README at revision 2324.2.1

~drizzle-trunk/drizzle/development : (revision 2324.2.1)

The MySQL Benchmarks

These tests require a MySQL version of at least 3.20.28 or 3.21.10, 
or any RDBMS and Perl driver.

This test is known to work with DBD::mysql and DBD::drizzle (MySQL and
Drizzle respectively)

Also, previously, the following servers were supported:
MySQL 3.20 and 3.21, PostgreSQL 6.#, mSQL 2.# and Solid Server 2.2

The benchmark directory contains the query files and raw data files used to
populate the MySQL benchmark tables. In order to run the benchmarks, you
should normally execute a command such as the following:

run-all-tests --server=drizzle 

run-all-tests --server=mysql --cmp=mysql,pg,solid --user=test --password=test --log

This means that you want to run the benchmarks with MySQL. The
limits should be taken from all of MySQL, PostgreSQL, and Solid.
The login name and password for connecting to the server both are
``test''.  The result should be saved as a RUN file in the output
directory.

When run-all-tests has finished, will have the individual results and the
the total RUN- file in the output directory.

If you want to look at some old results, use the compare-results script.
For example:

compare-results --dir=Results --cmp=mysql,pg,solid
compare-results --dir=Results --cmp=mysql,pg,solid --relative

compare-results --dir=Results --cmp=msql,mysql,pg,solid
compare-results --dir=Results --cmp=msql,mysql,pg,solid --relative

compare-results --dir=Results --server=mysql --same-server --cmp=mysql,pg,solid

Some of the files in the benchmark directory are:

File			Description

Data/ATIS		Contains data for 29 related tables used in the ATIS tests.
Data/Wisconsin		Contains data for the Wisconsin benchmark.
Results			Contains old benchmark results.
Makefile.am		Automake Makefile
README			This file.
test-ATIS.sh		Creation of 29 tables and a lot of selects on them.
test-connect.sh		Test how fast a connection to the server is.
test-create.sh		Test how fast a table is created.
test-insert.sh		Test create and fill of a table.
test-wisconsin.sh	A port of the PostgreSQL version of this benchmark.
run-all-tests		Use this to run all tests. When all tests are run,
			use the --log and --use-old options to get a RUN-file.
compare-results		Generates a comparison table from different RUN files.
server-cfg		Contains the limits and functions for all supported
			SQL servers.  If you want to add a new server, this
			should be the only file that neads to be changed.


Most of the tests should use portable SQL to make it possible to
compare different databases. Sometimes SQL extensions can make things
a lot faster. In this case the test may use the extensions if the --fast
option is used.

Useful options to all test-scripts (and run-all-tests):

--host=#	Hostname for MySQL server (default: localhost)
--db=#		Database to use (default: test)
--fast		Allow use of any non-standard SQL extension to
		get things done faster.
--lock-tables	Use table locking to get more speed.

From a text at http://www.mgt.ncu.edu.tw/CSIM/Paper/sixth/11.html:

The Wisconsin Benchmark

The Wisconsin Benchmark described in [Bitton, DeWitt, and Turbyfill
1983] [Boral and DeWitt 1984] [Bitton and Turbyfill 1985] [Bitton and
Turbyfill 1988], and [DeWitt 1993] is the first effort to
systematically measure and compare the performance of relational
database systems with database machines.  The benchmark is a
single-user and single-factor experiment using a synthetic database
and a controlled workload.  It measures the query optimization
performance of database systems with 32 query types to exercise the
components of the proposed systems.  The query suites include
selection, join, projection, aggregate, and simple update queries.

The test database consists of four generic relations.  The tenk
relation is the key table and most used. Two data types of small
integer numbers and character strings are utilized.  Data values are
uniformly distributed. The primary metric is the query elapsed
time. The main criticisms of the benchmark include the nature of
single-user workload, the simplistic database structure, and the
unrealistic query tests.  A number of efforts have been made to extend
the benchmark to incorporate the multi-user test.  However, they do
not receive the same acceptance as the original Wisconsin benchmark
except an extension work called the AS3AP benchmark.

Pre-requisites

You will need:

* Perl
* DBI
* DBD::<driver of choice>
* An RDBMS of choice 


2324.2.1 by patrick crews Initial work for sql-bench mode. Added sql-bench to the tree. Test script for running entire suite added	1	The MySQL Benchmarks
	2
	3	These tests require a MySQL version of at least 3.20.28 or 3.21.10,
	4	or any RDBMS and Perl driver.
	5
	6	This test is known to work with DBD::mysql and DBD::drizzle (MySQL and
	7	Drizzle respectively)
	8
	9	Also, previously, the following servers were supported:
	10	MySQL 3.20 and 3.21, PostgreSQL 6.#, mSQL 2.# and Solid Server 2.2
	11
	12	The benchmark directory contains the query files and raw data files used to
	13	populate the MySQL benchmark tables. In order to run the benchmarks, you
	14	should normally execute a command such as the following:
	15
	16	run-all-tests --server=drizzle
	17
	18	run-all-tests --server=mysql --cmp=mysql,pg,solid --user=test --password=test --log
	19
	20	This means that you want to run the benchmarks with MySQL. The
	21	limits should be taken from all of MySQL, PostgreSQL, and Solid.
	22	The login name and password for connecting to the server both are
	23	``test''. The result should be saved as a RUN file in the output
	24	directory.
	25
	26	When run-all-tests has finished, will have the individual results and the
	27	the total RUN- file in the output directory.
	28
	29	If you want to look at some old results, use the compare-results script.
	30	For example:
	31
	32	compare-results --dir=Results --cmp=mysql,pg,solid
	33	compare-results --dir=Results --cmp=mysql,pg,solid --relative
	34
	35	compare-results --dir=Results --cmp=msql,mysql,pg,solid
	36	compare-results --dir=Results --cmp=msql,mysql,pg,solid --relative
	37
	38	compare-results --dir=Results --server=mysql --same-server --cmp=mysql,pg,solid
	39
	40	Some of the files in the benchmark directory are:
	41
	42	File Description
	43
	44	Data/ATIS Contains data for 29 related tables used in the ATIS tests.
	45	Data/Wisconsin Contains data for the Wisconsin benchmark.
	46	Results Contains old benchmark results.
	47	Makefile.am Automake Makefile
	48	README This file.
	49	test-ATIS.sh Creation of 29 tables and a lot of selects on them.
	50	test-connect.sh Test how fast a connection to the server is.
	51	test-create.sh Test how fast a table is created.
	52	test-insert.sh Test create and fill of a table.
	53	test-wisconsin.sh A port of the PostgreSQL version of this benchmark.
	54	run-all-tests Use this to run all tests. When all tests are run,
	55	use the --log and --use-old options to get a RUN-file.
	56	compare-results Generates a comparison table from different RUN files.
	57	server-cfg Contains the limits and functions for all supported
	58	SQL servers. If you want to add a new server, this
	59	should be the only file that neads to be changed.
	60
	61
	62	Most of the tests should use portable SQL to make it possible to
	63	compare different databases. Sometimes SQL extensions can make things
	64	a lot faster. In this case the test may use the extensions if the --fast
65	option is used.
66
67	Useful options to all test-scripts (and run-all-tests):
68
69	--host=# Hostname for MySQL server (default: localhost)
70	--db=# Database to use (default: test)
71	--fast Allow use of any non-standard SQL extension to
72	get things done faster.
73	--lock-tables Use table locking to get more speed.
74
75	From a text at http://www.mgt.ncu.edu.tw/CSIM/Paper/sixth/11.html:
76
77	The Wisconsin Benchmark
78
79	The Wisconsin Benchmark described in [Bitton, DeWitt, and Turbyfill
80	1983] [Boral and DeWitt 1984] [Bitton and Turbyfill 1985] [Bitton and
81	Turbyfill 1988], and [DeWitt 1993] is the first effort to
82	systematically measure and compare the performance of relational
83	database systems with database machines. The benchmark is a
84	single-user and single-factor experiment using a synthetic database
85	and a controlled workload. It measures the query optimization
86	performance of database systems with 32 query types to exercise the
87	components of the proposed systems. The query suites include
88	selection, join, projection, aggregate, and simple update queries.
89
90	The test database consists of four generic relations. The tenk
91	relation is the key table and most used. Two data types of small
92	integer numbers and character strings are utilized. Data values are
93	uniformly distributed. The primary metric is the query elapsed
94	time. The main criticisms of the benchmark include the nature of
95	single-user workload, the simplistic database structure, and the
96	unrealistic query tests. A number of efforts have been made to extend
97	the benchmark to incorporate the multi-user test. However, they do
98	not receive the same acceptance as the original Wisconsin benchmark
99	except an extension work called the AS3AP benchmark.
100
101	Pre-requisites
102
103	You will need:
104
105	* Perl
106	* DBI
107	* DBD::<driver of choice>
108	* An RDBMS of choice
109