DBT-3 (OSDL Database Test 3) is a workload tool for the Linux kernel that OSDL (Open Source Development Labs, inc) developed based on TPC-H which is provided by the Transaction Performance Processing Council (TPC).
DBT-3, like TPC-H, simulates an actual decision-making support system and models complex business analysis applications that perform data processing jobs for making better business decisions. By running the workload that DBT-3 simulates, it is possible to verify and measure the performances of the Linux kernel in an actual decision-making support system.
DBT-3 uses the "scale factor (SF)" as a stress indicator of the system. By varying the SF, it becomes possible to make the size of a database the SF times its size.
The tests performed by DBT-3 comprise the three tests listed below. DBT-3 obtains the execution times of these three tests as well as the system status information and database statistics information.
For the purpose of this task, only the Power test is performed over preliminary prepared database with various Scale factors. The time for each query execution will be measured and stored into a database. Later the results of one whole test with all 22 queries will be rendered into a histogram graphics comparing it to different configurations.
The user that will run the benchmark must have sudo rights on the machine.
For clearing the system caches between query runs, the automation script uses the following command:
sudo /sbin/sysctl vm.drop_caches=3
This command must be run with superuser rights. Even if a user supplies a password to sudo, this password expires after some timeout. In order for this command to be run without requiring password, the following line should be added to the sudoers file (edit it with the "sudo visudo" command):
'your_username' ALL=NOPASSWD:/sbin/sysctl
...where 'your_username' is the user that will run the benchmark.
The automated DBT3 benchmark requires the following software:
dbt3_benchmark" and is under mariadb-tools. sudo cpan Config::Auto
sudo cpan DBD::mysql
NOTE: You may receive an error saying that CPAN could not find mysql_config. In this case you have to install the mysql client development library. In OpenSuse the command is:
sudo zypper install libmysqlclient-devel
Alternatively this module can be installed manually by following these steps:
perl Makefile.PL --mysql_config=/path/to/some/mysql_binary_distribution/bin/mysql_config
make to compile DBD::mysql:make
DBD::mysql:export PERL5LIB="/path/to/unzipped_DBD_mysql/DBD-mysql-4.020/lib" export LD_LIBRARY_PATH="/path/to/unzipped_DBD_mysql/DBD-mysql-4.020/blib/arch/auto/DBD/mysql/:/path/to/some/mysql_binary_distribution/lib/"
bzr branch lp:maria/5.3
bzr branch lp:maria/5.5
NOTE: The DBT3 benchmark requires a lot of disk space (for example MySQL 5.5.x + MyISAM database with scale factor 30 takes about 50 GB). Also some queries require the utilization of temp tables under the directory set by the --tmpdir startup parameter passed to mysqld. In the prepared configuration files the temp directory is pointed to the mysql system directory of the binary distribution, but one should reassure that there is enough free space available for the temp directory.
NOTE: The directory where all the files will be downloaded or installed will be referred as $PROJECT_HOME. This could be for example ~/benchmark/dbt3.
cd $PROJECT_HOME
bzr branch lp:mariadb-tools
Now the project for the dbt3 benchmark test will be in the following dir:
$PROJECT_HOME/mariadb-tools/dbt3_benchmark/
The project dbt3_benchmark has the following directories and files:
sXX', where XX is the scale factor. For the purpose of the benchmark from DBT3-1.9 we will only need DBGEN and QGEN. DBGEN is a tool that generates a workload for the test and QGEN is a tool that generates the queries used for the test.
cd $PROJECT_HOME tar -zxf dbt3-1.9.tar.gz
cp $PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/tpcd.h $PROJECT_HOME/dbt3-1.9/src/dbgen/
$PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/ into the dbt3 folder cp $PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/Makefile $PROJECT_HOME/dbt3-1.9/src/dbgen/
cd $PROJECT_HOME/dbt3-1.9/src/dbgen make
export DSS_QUERY=$PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/mysql_queries/
export DSS_QUERY=$PROJECT_HOME/dbt3-1.9/queries/pgsql/
mkdir $PROJECT_HOME/gen_query
NOTE: The examples use scale factor 30. If you want different scale, change the value of -s parameter
cd $PROJECT_HOME/dbt3-1.9/src/dbgen ./qgen -s 30 1 > $PROJECT_HOME/gen_query/s30-m/1.sql ./qgen -s 30 2 > $PROJECT_HOME/gen_query/s30-m/2.sql ./qgen -s 30 3 > $PROJECT_HOME/gen_query/s30-m/3.sql ./qgen -s 30 4 > $PROJECT_HOME/gen_query/s30-m/4.sql ./qgen -s 30 5 > $PROJECT_HOME/gen_query/s30-m/5.sql ./qgen -s 30 6 > $PROJECT_HOME/gen_query/s30-m/6.sql ./qgen -s 30 7 > $PROJECT_HOME/gen_query/s30-m/7.sql ./qgen -s 30 8 > $PROJECT_HOME/gen_query/s30-m/8.sql ./qgen -s 30 9 > $PROJECT_HOME/gen_query/s30-m/9.sql ./qgen -s 30 10 > $PROJECT_HOME/gen_query/s30-m/10.sql ./qgen -s 30 11 > $PROJECT_HOME/gen_query/s30-m/11.sql ./qgen -s 30 12 > $PROJECT_HOME/gen_query/s30-m/12.sql ./qgen -s 30 13 > $PROJECT_HOME/gen_query/s30-m/13.sql ./qgen -s 30 14 > $PROJECT_HOME/gen_query/s30-m/14.sql ./qgen -s 30 15 > $PROJECT_HOME/gen_query/s30-m/15.sql ./qgen -s 30 16 > $PROJECT_HOME/gen_query/s30-m/16.sql ./qgen -s 30 17 > $PROJECT_HOME/gen_query/s30-m/17.sql ./qgen -s 30 18 > $PROJECT_HOME/gen_query/s30-m/18.sql ./qgen -s 30 19 > $PROJECT_HOME/gen_query/s30-m/19.sql ./qgen -s 30 20 > $PROJECT_HOME/gen_query/s30-m/20.sql ./qgen -s 30 21 > $PROJECT_HOME/gen_query/s30-m/21.sql ./qgen -s 30 22 > $PROJECT_HOME/gen_query/s30-m/22.sql
./qgen -s 30 -x 1 > $PROJECT_HOME/gen_query/s30-m/1_explain.sql ./qgen -s 30 -x 2 > $PROJECT_HOME/gen_query/s30-m/2_explain.sql ./qgen -s 30 -x 3 > $PROJECT_HOME/gen_query/s30-m/3_explain.sql ./qgen -s 30 -x 4 > $PROJECT_HOME/gen_query/s30-m/4_explain.sql ./qgen -s 30 -x 5 > $PROJECT_HOME/gen_query/s30-m/5_explain.sql ./qgen -s 30 -x 6 > $PROJECT_HOME/gen_query/s30-m/6_explain.sql ./qgen -s 30 -x 7 > $PROJECT_HOME/gen_query/s30-m/7_explain.sql ./qgen -s 30 -x 8 > $PROJECT_HOME/gen_query/s30-m/8_explain.sql ./qgen -s 30 -x 9 > $PROJECT_HOME/gen_query/s30-m/9_explain.sql ./qgen -s 30 -x 10 > $PROJECT_HOME/gen_query/s30-m/10_explain.sql ./qgen -s 30 -x 11 > $PROJECT_HOME/gen_query/s30-m/11_explain.sql ./qgen -s 30 -x 12 > $PROJECT_HOME/gen_query/s30-m/12_explain.sql ./qgen -s 30 -x 13 > $PROJECT_HOME/gen_query/s30-m/13_explain.sql ./qgen -s 30 -x 14 > $PROJECT_HOME/gen_query/s30-m/14_explain.sql ./qgen -s 30 -x 15 > $PROJECT_HOME/gen_query/s30-m/15_explain.sql ./qgen -s 30 -x 16 > $PROJECT_HOME/gen_query/s30-m/16_explain.sql ./qgen -s 30 -x 17 > $PROJECT_HOME/gen_query/s30-m/17_explain.sql ./qgen -s 30 -x 18 > $PROJECT_HOME/gen_query/s30-m/18_explain.sql ./qgen -s 30 -x 19 > $PROJECT_HOME/gen_query/s30-m/19_explain.sql ./qgen -s 30 -x 20 > $PROJECT_HOME/gen_query/s30-m/20_explain.sql ./qgen -s 30 -x 21 > $PROJECT_HOME/gen_query/s30-m/21_explain.sql ./qgen -s 30 -x 22 > $PROJECT_HOME/gen_query/s30-m/22_explain.sql
Now the generated queries for MariaDB/MySQL test are ready and are stored into the folder $PROJECT_HOME/gen_query/s30-m/ (-m is for MariaDB/MySQL).
Additional reorganization of directories is up to the user.
mkdir $PROJECT_HOME/gen_data/s30
export DSS_PATH=$PROJECT_HOME/gen_data/s30/
30. If you want to change it, you should change the parameter -s../dbgen -vfF -s 30
$DSS_PATH = $PROJECT_HOME/gen_data/ For the purpose of this benchmark these steps have been performed for scale factor 30 and are stored on facebook-maria1 in the following locations:
/benchmark/dbt3/gen_data/s30 — the data load for scale factor 30 /benchmark/dbt3/gen_query/s30-m — generated queries for MariaDB/MySQL with scale factor 30 /benchmark/dbt3/gen_query/s30-p — generated queries for PostgreSQL with scale factor 30 See DBT3 example preparation time to see how long it would take you to prepare the databases for the test.
$PROJECT_HOME/bin/ for example gunzip < mysql-5.5.x-linux2.6-x86_64.tar.gz |tar xf -
Now the server could be started with the following command:
$PROJECT_HOME/bin/mysql-5.5.x-linux2.6-x86_64/bin/mysqld_safe --datadir=some/data/dir &
$PROJECT_HOME/bin/ for example gunzip < mysql-5.6.x-m5-linux2.6-x86_64.tar.gz |tar xf -
Now the server could be started with the following command:
$PROJECT_HOME/bin/mysql-5.6.x-m5-linux2.6-x86_64/bin/mysqld_safe --datadir=some/data/dir &
NOTE: These steps are the same for MariaDB 5.5.x with properly replaced version numbers
bzr branch lp:maria/5.3 mv 5.3/ mariadb-5.3
cd mariadb-5.3/ ./BUILD/compile-amd64-max
./scripts/make_binary_distribution
mv mariadb-5.3.x-beta-linux-x86_64.tar.gz $PROJECT_HOME/bin/ cd $PROJECT_HOME/bin/ tar -xf mariadb-5.3.x-beta-linux-x86_64.tar.gz
Now the server could be started with the following command:
$PROJECT_HOME/bin/mariadb-5.3.x-beta-linux-x86_64/bin/mysqld_safe --datadir=some/data/dir &
NOTE: These instructions are the same for MariaDB, MySQL 5.5.x and MySQL 5.6.x with changing only the database home folders, noted here as $DB_HOME (for example for MySQL 5.5.x $DB_HOME is $PROJECT_HOME/bin/mysql-5.5.x-linux2.6-x86_64). Also you can prepare InnoDB storage engine test databases. Instructions for preparing PostgreSQL could be found in the section for downloading, building and preparing PostgreSQL later on this page.
$PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/make-dbt3-db_innodb.sql and edit the values for the call of the sql commands that look like this one:LOAD DATA INFILE '/some/path/to/gen_data/nation.tbl' into table nation fields terminated by '|';
LOAD DATA INFILE '~/benchmark/dbt3/gen_data/s30/nation.tbl' into table nation fields terminated by '|';
cd $DB_HOME ./scripts/mysql_install_db --defaults-file=$PROJECT_HOME/mariadb-tools/dbt3_benchmark/config/s30/load_mysql_myisam_my.cnf --basedir=$DB_HOME --datadir=$PROJECT_HOME/db_data/myisam-s30/
load_mysql_innodb_my.cnf. ./bin/mysqld_safe --defaults-file=$PROJECT_HOME/mariadb-tools/dbt3_benchmark/config/s30/load_mysql_myisam_my.cnf --tmpdir=$PROJECT_HOME/temp/ --socket=$PROJECT_HOME/temp/mysql.sock --datadir=$PROJECT_HOME/db_data/myisam-s30/ & load_mysql_innodb_my.cnf. Also make sure that you have enough space in the directory set by the parameter --tmpdir, since loading the database could take a lot of temporary space. make-dbt3-db_pre_create_PK.sql (for InnoDB) or make-dbt3-db_post_create_PK.sql (for MyISAM)./bin/mysql -u root -S $PROJECT_HOME/temp/mysql.sock < $PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/make-dbt3-db_post_create_PK.sql make-dbt3-db_pre_create_PK.sql for loading InnoDB and make-dbt3-db_post_create_PK.sql for loading MyISAM databases. ./bin/mysqladmin --user=root --socket=$PROJECT_HOME/temp/mysql.sock shutdown 0
Now you have a database loaded with scale 30. Its datadir is $PROJECT_HOME/db_data/myisam-s30/
The same steps can be reproduced for different scale factors and for different storage engines.
gunzip < postgresql-9.1rc1.tar.gz |tar xf -
mkdir $PROJECT_HOME/PostgreSQL_bin cd $PROJECT_HOME/postgresql-9.1rc1 ./configure --prefix=$PROJECT_HOME/bin/PostgreSQL_bin make make install
--without-readline --without-zlib mkdir $PROJECT_HOME/db_data/postgre_s30 cd $PROJECT_HOME/bin/PostgreSQL_bin ./bin/initdb -D $PROJECT_HOME/db_data/postgre_s30
./bin/postgres -D $PROJECT_HOME/db_data/postgre_s30 -p 54322 &
./bin/createdb -O {YOUR_USERNAME} dbt3 -p 54322
./bin/psql -p 54322 -d dbt3 -f $PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/make-dbt3-db_pg.sql
{YOUR_USERNAME} you should put the database owner. ./bin/pg_ctl -D $PROJECT_HOME/db_data/postgre_s30/ -p 54322 stop
The steps for preparing the workload for the benchmark on facebook-maria1 are already made for MariaDB, MySQL and PostgreSQL. Here are the directories for the different DBMS, storage engines and scale factors that are prepared on facebook-maria1:
~/benchmark/dbt3/db_data/myisam_s30 — datadir for MariaDB/MySQL + MyISAM with scale factor 30 ~/benchmark/dbt3/db_data/innodb_mariadb_s30 — datadir for MariaDB + InnoDB with scale factor 30 (TODO) ~/benchmark/dbt3/db_data/innodb_mysql_s30 — datadir for MySQL + InnoDB with scale factor 30 (TODO) ~/benchmark/dbt3/db_data/postgre_s30 — datadir for PostgreSQL with scale factor 30 (TODO) The results of the benchmark will be stored in a separate database that will be run by MariaDB 5.3.x.
NOTE: The results database will be a subject to change in future versions of the DBT3 benchmarking project.
The database is created by the file $PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/make-results-db.sql. In that file you can find details about every table and column in the database.
To prepare the database for work follow these steps:
cd $PROJECT_HOME/bin/mariadb-5.3.x-beta-linux-x86_64
$PROJECT_HOME/db_data/dbt3_results_db)./scripts/mysql_install_db --datadir=$PROJECT_HOME/db_data/dbt3_results_db
./bin/mysqld_safe --defaults-file=$PROJECT_HOME/mariadb-tools/dbt3_benchmark/config/results_mariadb_my.cnf --port=12340 --socket=$PROJECT_HOME/temp/mysql_results.sock --datadir=$PROJECT_HOME/db_data/dbt3_results_db/ &
./bin/mysql -u root -P 12340 -S $PROJECT_HOME/temp/mysql_results.sock < $PROJECT_HOME/mariadb-tools/dbt3_benchmark/dbt3_mysql/make-results-db.sql
./bin/mysqladmin --user=root --port=12340 --socket=$PROJECT_HOME/temp/mysql_results.sock shutdown 0
In order to run a benchmark, one should have:
launcher.pl that could be found under mariadb-tools/dbt3_benchmark/ Details about each of these is given in the following sections.
Each benchmark is configured by a set of configuration files. One can find example (default) configuration files under the directory 'mariadb-tools/dbt3_benchmark/tests'. Each configuration file has an 'ini' configuration syntax and is parsed by the perl automation script with the CPAN module Config::Auto
Every configuration file could contain keywords that will be replaced by the script with particular values. They are used for convenience when you want to make your configuration files more common to the environment that you have prepared for the benchmark. These keywords are:
$PROJECT_HOME — used as the directory where the project 'mariadb-tools' is located or as a base path for the whole project (e.g. "DBMS_HOME = $PROJECT_HOME/bin/mariadb-5.3.x-beta-linux-x86_64"). It is replaced by the value set with the startup parameter 'project-home' passed to launcher.pl, $DATADIR_HOME — used as the directory where the datadir folders are located for the benchmark (e.g. "$DATADIR_HOME/myisam-s30"). It is replaced by the value set with the startup parameter 'datadir-home' passed to launcher.pl. $QUERIES_HOME — used as the directory where the queries are located (e.g. "$QUERIES_HOME/s30-m" — queries for MariaDB/MySQL for scale factor 30). It is replaced by the value set with the startup parameter 'queries-home' passed to launcher.pl. $SCALE_FACTOR — the scale factor that will be used. It is usually a part of the name of the datadir directory (e.g. "$DATADIR_HOME/myisam-s$SCALE_FACTOR"), the queries directory (e.g. "$QUERIES_HOME/s$SCALE_FACTOR-m") or the database configuration directory (e.g. $PROJECT_HOME/mariadb-tools/dbt3_benchmark/config/s$SCALE_FACTOR). It is replaced by the value set with the startup parameter 'scale-factor' passed to launcher.pl. Note that if any of the configuration files contains such keyword, the corresponding startup parameter passed to launcher.pl will become required.
A top-level configuration file provides paths to the Test, DBMS, Queries and Results database configurations files
There are default configuration files in the directory mariadb-tools/dbt3_benchmark/tests/ and contain the following settings:
| Parameter | Description |
|---|---|
RESULTS_DB_CONFIG |
The configuration file for results DB settings |
TEST_CONFIG |
The configuration file for the test settings |
QUERIES_CONFIG |
The configuration file for the queries settings |
DB_CONFIG |
The configuration file for the DBMS server settigns |
This file has the following format:
[common] RESULTS_DB_CONFIG = $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/results_db_conf/results_db.conf TEST_CONFIG = $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/test_conf/test_myisam.conf [mariadb_5_3] QUERIES_CONFIG = $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/queries_conf/queries.conf DB_CONFIG = $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/db_conf/db_mariadb_5_3_myisam.conf [mysql_5_5] QUERIES_CONFIG = $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/queries_conf/queries_mysql.conf DB_CONFIG = $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/db_conf/db_mysql_5_5_myisam.conf ...
NOTE: The settings RESULTS_DB_CONFIG and TEST_CONFIG should be set under the [common] section. They are common for the whole test (although some settings from TEST_CONFIG could be overridden in the QUERIES_CONFIG file). All settings that combine QUERIES_CONFIG and DB_CONFIG should be in a separate section (e.g. [mariadb_5_3]).
A test configuration is passed as an input parameter to the automation script with the parameter --test=/path/to/some_test_configuration.conf (see #script-startup-parameters)
These configuration files contain settings that describe the benchmarked DBMS. They are usually contained into the folder mariadb-tools/dbt3_benchmark/tests/db_conf.
Here is the list of parameters that could be set into this configuration file:
| Parameter | Description |
|---|---|
DBMS_HOME |
Where the instalation folder of MariaDB / MySQL / PostgreSQL is located. NOTE: The automation script uses " ./bin/mysqld_safe" to start the mysqld process. So the versions of MariaDB and MySQL should be a "binary distribution" ones. |
DBMS_USER |
The database user that will be used. |
CONFIG_FILE |
The config file that mysqld or postgres will use when starting |
SOCKET |
The socket that will be used to start the server |
PORT |
The port that the server will be started on |
HOST |
The host where the server is located |
DATADIR |
Where the datadir for mysqld or postgres is located |
TMPDIR |
Where the temp tables will be created while sorting and grouping. |
DBNAME |
The database (schema) name where the benchmark tables are located. |
KEYWORD |
This text will be stored into the results database as a keyword. Also will be used as a name for a subfolder with results and statistics. |
DBMS |
Database Management System that will be used. Possible values: "MySQL", "MariaDB" and "PostgreSQL" |
STORAGE_ENGINE |
The storage engine that was used (MyISAM, InnoDB, etc.) |
STARTUP_PARAMS |
Any startup parameters that will be used while starting the mysqld process or postgres process. Same format as given on the command line. |
GRAPH_HEADING |
The heading of the graphic for that particular test. |
MYSQL_SYSTEM_DIR |
See "MYSQL_SYSTEM_DIR note", below. |
READ_ONLY |
If set to 1, mysqld process will be started with '--read-only' startup parameter |
PRE_RUN_SQL |
SQL commands that are run prior each query run |
POST_RUN_SQL |
SQL commands that are run after each query run |
PRE_TEST_SQL |
SQL commands that are run prior the whole test with that database settings |
POST_TEST_SQL |
SQL commands that are run after the whole test with that database settings |
MYSQL_SYSTEM_DIR note:
This option is added for convenience when you want to save time and disk space for generating databases for different DBMS (and different versions) and use a single data directory for all of them. When running different versions of MariaDB/MySQL over a single datadir, one should run mysql-upgrade in order to fix the system tables. So in one data directory, you could prepare the following directories for different MariaDB/MySQL system directories:
mysql_mysql_5_5 — a copy of the system directory 'mysql' upgraded by MySQL 5.5.x mysql_mariadb_5_3 — a copy of the system directory 'mysql' upgraded by MariaDB 5.3.x mysql_mariadb_5_5 — a copy of the system directory 'mysql' upgraded by MariaDB 5.5.xIf MYSQL_SYSTEM_DIR is set to one of these directories, the automation script will unlink the current system directory 'mysql' and make a new symbolic link with that name to the one in the setting.
Here is an example command that will be executed:
unlink /path/to/datadir/mysql ln -s /path/to/value/in/MYSQL_SYSTEM_DIR/mysql_mariadb_5_3 /path/to/datadir/mysql
NOTE: This approach is suitable for MyISAM tests.
The configuration file looks like this:
[db_settings] DBMS_HOME = $PROJECT_HOME/bin/mariadb-5.3.2-beta-linux-x86_64 DBMS_USER = root ...
Note that the section [db_settings] is required for the file to be properly parsed by the automation script.
These configuration files contain settings describing the test. They are usually contained into the folder mariadb-tools/dbt3_benchmark/tests/test_conf.
Here is the list of parameters that could be set into this configuration file:
| Parameter | Description |
|---|---|
QUERIES_AT_ONCE |
If set to 1, then all the queries are executed sequentially without restarting the server or clearing the caches between queries. |
CLEAR_CACHES |
If set to 1, the disk caches will be cleared before each query test. |
WARMUP |
Perform a warm-up runs before running the query. |
EXPLAIN |
Run an Explain command prior the run of the query. The explain results will be stored in a file under the results output directory. |
RUN |
Perform the actual test |
ANALYZE_EXPLAIN |
A result extraction mechanism where only the best execution plan (results from EXPLAIN select) will be measured. It is designed to be used when benchmarking InnoDB storage engine where execution plan is changing between server restarts (see #results-extraction-mechanisms). |
MIN_MAX_OUT_OF_N |
A result extraction mechanism where the minimal and maximal values out of N (set by the parameter NUM_TESTS) tests are taken as a result. This could be used when InnoDB storage engine is tested (see #results-extraction-mechanisms). |
SIMPLE_AVERAGE |
A result extraction mechanism where the final result is the average time taken for the tests. The number of tests is per query is set by the NUM_TESTS parameter. Note that if even one test has timed out, the result is 'time-out'. This is used when testing MyISAM storage engine since there the execution plan is constant (see #results-extraction-mechanisms). |
NUM_TESTS |
How many tests should be performed for each query. When ANALYZE_EXPLAIN is set, this value could be set to 0, meaning that the tests will continue until enough results are extracted (see setting CLUSTER_SIZE). This parameter is very important when MIN_MAX_OUT_OF_N or SIMPLE_AVERAGE is selected. |
MAX_SKIPPED_TESTS |
When ANALYZE_EXPLAIN is set and an execution plan that is slower is selected, the execution of the query is skipped and the server is restarted in order to change the execution plan. If the server is restarted more than MAX_SKIPPED_TESTS, there are obviously no more different execution plans and the script continues to the next query benchmark. |
WARMUPS_COUNT |
How many warmup runs will be performed prior the actual benchmark run. |
CLUSTER_SIZE |
How big a cluster with results for a query should be in order to extract the final result. It is used when ANALYZE_EXPLAIN is selected as a result extraction method. |
MAX_QUERY_TIME |
The maximum time that one query will be tested. Currently it is applicable only when ANALYZE_EXPLAIN is selected. |
TIMEOUT |
The maximum time that one query could run. Currently timeout is applicable only for MySQL and MariaDB. |
OS_STATS_INTERVAL |
What is the time interval between extraction of OS statistics for CPU, memory, etc. |
PRE_RUN_OS |
OS commands that should be executed prior each query run |
POST_RUN_OS |
OS commands that should be executed after each query run |
PRE_TEST_OS |
OS commands that should be executed prior the whole test |
POST_TEST_OS |
OS commands that should be executed after the whole test is complete |
The configuration file looks like this:
QUERIES_AT_ONCE = 0 CLEAR_CACHES = 1 WARMUP = 0 ...
These configuration files contain the list of all the queries that will be benchmarked against each database. Some settings from DBMS server configuration and Test configuration could be overridden into the Queries configuration files. The folder that contains such configurations is mariadb-tools/dbt3_benchmark/tests/queries_conf.
Here is the list of parameters that could be set into this configuration file:
| Parameter | Description |
|---|---|
QUERIES_HOME |
Where the queries are located on disk. This value is concatenated to the QUERY setting and this makes the path to the particular query. NOTE: This setting should be set under the section [queries_settings]. |
CONFIG_FILE |
This overrides the startup setting CONFIG_FILE from DMBS server configuration file and sets the database configuration file that is used. It could be used if some configuration file without any optimizations should be set for this particular queries configuration file.NOTE: This setting should be set under the section [queries_settings]. |
QUERY |
The name of the query located into QUERIES_HOME folder. E.g. "1.sql" |
EXPLAIN_QUERY |
The name of the explain query into QUERIES_HOME folder. E.g. "1_explain.sql" |
TMPDIR |
This overrides the setting TMPDIR from the DMBS server configuration. |
STARTUP_PARAMS |
This overrides the setting STARTUP_PARAMS from the DMBS server configuration. Using this setting one could change the particular startup parameters (like optimizations and buffers) for the DB server. |
PRE_RUN_SQL |
This overrides the setting PRE_RUN_SQL from the DMBS server configuration. |
POST_RUN_SQL |
This overrides the setting POST_RUN_SQL from the DMBS server configuration. |
RUN |
This overrides the setting RUN from the test configuration. |
EXPLAIN |
This overrides the setting EXPLAIN from the test configuration. |
TIMEOUT |
This overrides the setting TIMEOUT from the test configuration. |
NUM_TESTS |
This overrides the setting NUM_TESTS from the test configuration. |
MAX_SKIPPED_TESTS |
This overrides the setting MAX_SKIPPED_TESTS from the test configuration. |
WARMUP |
This overrides the setting WARMUP from the test configuration. |
WARMUPS_COUNT |
This overrides the setting WARMUPS_COUNT from the test configuration. |
MAX_QUERY_TIME |
This overrides the setting MAX_QUERY_TIME from the test configuration. |
CLUSTER_SIZE |
This overrides the setting CLUSTER_SIZE from the test configuration. |
PRE_RUN_OS |
This overrides the setting PRE_RUN_OS from the test configuration. |
POST_RUN_OS |
This overrides the setting POST_RUN_OS from the test configuration. |
OS_STATS_INTERVAL |
This overrides the setting OS_STATS_INTERVAL from the test configuration. |
The queries configuration file could look like this:
[queries_settings] QUERIES_HOME = /path/to/queries [query1] QUERY=1.sql EXPLAIN_QUERY=1_explain.sql STARTUP_PARAMS= [query2] QUERY=2.sql EXPLAIN_QUERY=2_explain.sql STARTUP_PARAMS=--optimizer_switch='mrr=on' --mrr_buffer_size=8M --some_startup_parmas ...
...where "QUERIES_HOME = /path/to/queries" could be replaced with "QUERIES_HOME = $QUERIES_HOME/s$SCALE_FACTOR-m" for example and thus $QUERIES_HOME and $SCALE_FACTOR will be replaced by the script startup parameters passed to launcher.pl (see #script-startup-parameters)
NOTE: The section [queries_settings] is required for the configuration file to be parsed correctly. Also each query settings should be set under an uniquely named configuration section (e.g. [query1] or [1.sql])
These configuration files contain settings about the database where the results will be stored. They are usually contained into the folder mariadb-tools/dbt3_benchmark/tests/results_db_conf.
Here is the list of parameters that could be set into this configuration file:
| Parameter | Description |
|---|---|
DBMS_HOME |
Where the database directory is located. E.g. "$PROJECT_HOME/mariadb-5.3.x-beta-linux-x86_64". This should be a binary distribution of MariaDB or MySQL. |
DBMS_USER |
The user that will be used by the DBMS |
DATADIR |
Where the data directory is located for the results database |
CONFIG_FILE |
What the configuration file used by the database is. |
SOCKET |
The socket that will be used by the results database. This should be different socket than the one provided for the testing databases. |
PORT |
The port that the results database will use. This should be different port than the one provided for the testing databases. |
STARTUP_PARAMS |
Any startup parameters that should be set to start the server. |
DBNAME |
The database name to use. |
HOST |
The host where the results database is. |
The results database configuration could look like this:
DBMS_HOME = $PROJECT_HOME/mariadb-5.3.x-beta-linux-x86_64 DBMS_USER = root ...
launcher.pl could accept startup parameters called in the manner "--some-param". Note that these startup parameters are case-sensitive. The ones that are with upper-case are used when overriding a setting in some of the configuration files.
Here is a list of the startup parameters:
| Parameter | Description |
|---|---|
test |
The top-level benchmark configuration file that will be run. This is a required startup parameter. |
results-output-dir |
Where the results of the benchmark will be stored. A timestamp is automatically attached to the directory name so that it keeps track of time and date of the benchmark. This is a required parameter. |
dry-run |
If set, no benchmark will be performed. Instead only messages will be displayed for the actions that were intended to be done. |
project-home |
Required if any configuration file uses the variable '$PROJECT_HOME'. If all configuration files use absolute paths, not used. |
datadir-home |
The value in this parameter will replace any occurrences of the string '$DATADIR_HOME' into the configuration files. If there are no such occurances, it is not a required parameter. |
queries-home |
The value in this parameter will replace any occurrences of the string '$QUERIES_HOME' into the configuration files. If there are no such occurances, it is not a required parameter. |
scale-factor |
The value in this parameter will replace any occurrences of the string '$SCALE_FACTOR' into the configuration files. If there are no such occurances, it is not a required parameter. |
CLEAR_CACHES |
If set. this overrides the default setting set into the test configuration file. |
QUERIES_AT_ONCE |
If set. this overrides the default setting set into the test configuration file. |
RUN |
If set. this overrides the default setting set into the test configuration file. |
EXPLAIN |
If set. this overrides the default setting set into the test configuration file. |
TIMEOUT |
If set. this overrides the default setting set into the test configuration file. |
NUM_TESTS |
If set. this overrides the default setting set into the test configuration file. |
MAX_SKIPPED_TESTS |
If set. this overrides the default setting set into the test configuration file. |
WARMUP |
If set. this overrides the default setting set into the test configuration file. |
WARMUPS_COUNT |
If set. this overrides the default setting set into the test configuration file. |
MAX_QUERY_TIME |
If set. this overrides the default setting set into the test configuration file. |
CLUSTER_SIZE |
If set. this overrides the default setting set into the test configuration file. |
PRE_RUN_OS |
If set. this overrides the default setting set into the test configuration file. |
POST_RUN_OS |
If set. this overrides the default setting set into the test configuration file. |
OS_STATS_INTERVAL |
If set. this overrides the default setting set into the test configuration file. |
There are three possible result extraction mechanisms. They are set by the parameters set into the test configuration file:
ANALYZE_EXPLAIN MIN_MAX_OUT_OF_N SIMPLE_AVERAGE Only one of these should be set to true (1).
ANALYZE_EXPLAIN is used for benchmarking InnoDB storage engine where the execution plan could change for the same query when the server is restarted. It is designed to run the query only with the fastest execution plan. This means that the server is restarted if the current execution plan is proven slower than the other. As a final result is taken the result for the query plan that turns out to be fastest and there are at least CLUSTER_SIZE tests with it for that query. By setting the configuration parameter NUM_TESTS you can set a maximum test runs that when reached will get the best cluster's average time (even if it is less than CLUSTER_SIZE). Also when a timeout for that query (MAX_QUERY_TIME) is reached, the scoring mechanism will return the best available cluster result.
MIN_MAX_OUT_OF_N is also used for benchmarking InnoDB storage engine. As a result are stored the values for the fastest and the slowest run. It is assumed that when the execution plan has changed it has different execution plan and we are interested only in the min and max time.
SIMPLE_AVERAGE is used for benchmarking storage engines that do not change the execution plan between restarts like MyISAM. The final result is the average execution time from all the test runs for the query.
After each query test run, the result is stored into a file named results.dat located into {RESULTS_OUTPUT_DIR}. This file is designed to be easy to be read by the plotting program Gnuplot 4.4. It is divided into blocks, separated by several new lines. Each block starts with a comment line containing details for the current block of results.
Queries that have timed out have a value of 100000 so that they run out of the graphics and are cut off. Other queries have their real times (in seconds) starting from 0. The graphics is cut off on the y-axis on the longest time for completed test + 20%. For example if the longest time is 100 seconds, the graphics is cut-off to 120 seconds. Thus the timed out queries will be truncated by this limitation and will seem as really timed out.
During the test run, a gnuplot script file is generated with the necessary parameters for the graphics to be generated automatically. After each query test run is complete, the graphic is regenerated, so that the user can see the current results before the whole benchmark is complete. This file is called gnuplot_script.txt and is located into {RESULTS_OUTPUT_DIR}. The user can edit it to fine-tune the parameters or headings after the test is complete so that one could get the look and feel he/she wants for the final result.
In the directory set by the parameter {RESULTS_OUTPUT_DIR} (example: /benchmark/dbt3/results/myisam_test_2011-12-08_191427/) there are the following files/directories:
mariadb-5-3-2) /bin/cat /proc/cpuinfo" OS command uname -a" OS command myisam_test_mariadb_5_3_mysql_5_5_mysql_5_6.conf) copied from mariadb-tools/dbt3_benchmark/tests/ results_db.conf) copied from mariadb-tools/dbt3_benchmark/tests/results_db_conf/ test_myisam.conf) copied from mariadb-tools/dbt3_benchmark/tests/test_conf/ In the subdirectory for the particular test, set by the parameter {KEYWORD} (example: /benchmark/dbt3/results/myisam_test_2011-12-08_191427/mariadb-5-3-2/), there are the following files:
mariadb_myisam_my.cnf) copied from mariadb-tools/dbt3_benchmark/config/s$SCALE_FACTOR/ mariadb-tools/dbt3_benchmark/tests/queries_conf/ mariadb-tools/dbt3_benchmark/tests/db_conf/ For each query execution there are several files that are outputted by the automation script. They are all saved under the subdirectory set by the parameters {KEYWORD}:
The automation script provides hooks that allow the user to add both SQL and OS commands prior and after each test. Here is a list of all possible hooks:
PRE_TEST_SQL. Contains SQL commands that are run once for the whole test configuration before the first run. (Example: "use dbt3; select version(); show variables; show engines; show table status;
") POST_TEST_SQL. Contains SQL commands that are run once for the whole test configuration after the last run. PRE_TEST_OS. Contains OS commands that are run once for the whole test configuration before the first run. POST_TEST_OS. Contains OS commands that are run once for the whole test configuration after the last run. PRE_RUN_SQL. Contains SQL commands that are run prior each query run. (Example: "flush status; set global userstat=on;
") POST_RUN_SQL. Contains SQL commands that are run after each query run. (Example: "show status; select * from information_schema.TABLE_STATISTICS;
") PRE_RUN_OS. Contains OS commands that are run once prior each query run. POST_RUN_OS. Contains OS commands that are run once after each query run. The results of these commands is stored into the {RESULTS_OUTPUT_DIR}/{KEYWORD} folder (see #script-output)
Here are the main activities that this script does:
cpuinfo and uname. Results of these commands are stored into the results output directory set as an input parameter sudo /sbin/sysctl vm.drop_caches=3
sudoers file (edit it with "sudo vusudo" command):{your_username} ALL=NOPASSWD:/sbin/sysctl
results_output_dir/{KEYWORD} folder and are called pre_test_sql_results.txt. {KEYWORD} is a unique keyword for the current database configuration. results_output_dir/{KEYWORD} folder and are called pre_test_os_results.txt. pre_run_os_q_{QUERY}_no_{RUN_NO}_results.txt under results_output_dir/{KEYWORD} where {QUERY} is the query name (ex: 1.sql), {RUN_NO} is the sequential run number and {KEYWORD} is a unique keyword for the particular test configuration. pre_run_sql_q_{QUERY}_no_{RUN_NO}_results.txt under results_output_dir/{KEYWORD} where {QUERY} is the query name (ex: 1.sql), {RUN_NO} is the sequential run number and {KEYWORD} is a unique keyword for the particular test configuration. sar -u 0 2>null
sar -b 0 2>null
sar -r 0 2>null
N is set with the OS_STATS_INTERVAL test configuration parameter. TIMEOUT test parameter. Currently for PostgreSQL there is no such functionality and should be implemented in future versions. EXPLAIN queries with MySQL prior version 5.6.3 could result in long running queries since MySQL has to execute the whole query when there are nested selects in it. For MariaDB and PostgreSQL there is no such problem. The long-running explain queries are for queries #7, 8, 9, 13 and 15. For that reason in MySQL prior version 5.6.3 for these queries no EXPLAIN selects should be executed. results_output_dir/{KEYWORD} folder and are called post_test_sql_results.txt. results_output_dir/{KEYWORD} folder and are called post_test_os_results.txt. perl launcher.pl \ --project-home=/path/to/project/home/ \ --results-output-dir=/path/to/project/home/results/myisam_test \ --datadir=/path/to/project/home/db_data/ \ --test=/path/to/project/home/mariadb-tools/dbt3_benchmark/tests/myisam_test_mariadb_5_3_mysql_5_5_mysql_5_6.conf \ --queries-home=/path/to/project/home/gen_query/ \ --scale-factor=30 \ --TIMEOUT=600
...where /path/to/project/home is where the mariadb-tools project is located. This will replace all occurrences of the string "$PROJECT_HOME" in the configuration files (example: "TMPDIR = $PROJECT_HOME/temp/" will become "TMPDIR = /path/to/project/home/temp/").
--TIMEOUT overrides the timeout setting into the test configuration file to 10 minutes.
perl launcher.pl \ --project-home=/path/to/project/home/ \ --results-output-dir=/path/to/project/home/results/innodb_test \ --datadir=/path/to/project/home/db_data/ \ --test=/path/to/project/home/mariadb-tools/dbt3_benchmark/tests/innodb_test_mariadb_5_3_mysql_5_5_mysql_5_6.conf \ --queries-home=/path/to/project/home/gen_query/ \ --scale-factor=30 \ --TIMEOUT=7200 \ --NUM_TESTS=3
mariadb-tools/dbt3_benchmark/tests/db_conf/db_mariadb_5_5_myisam.conf and change the parameter DBMS_HOME to the new binary distribution. You can also edit KEYWORD and GRAPH_HEADING mariadb-tools/dbt3_benchmark/tests/db_conf/db_mariadb_5_3_myisam.conf and change the parameter CONFIG_FILE to the new my.cnf mariadb-tools/dbt3_benchmark/tests/myisam_test_mariadb_5_3_mysql_5_5_mysql_5_6.conf and add the new configuration settings:[mariadb_5_3_new_configuration] QUERIES_CONFIG = $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/queries_conf/queries-mariadb.conf DB_CONFIG = $PROJECT_HOME/mariadb-tools/dbt3_benchmark/tests/db_conf/db_mariadb_5_3_myisam_new_configuration.conf
mariadb-tools/dbt3_benchmark/tests/queries_conf/queries-mariadb.conf and add a parameter "STARTUP_PARAMS=--optimizer_switch='mrr=on' --mrr_buffer_size=96M" for example for the section for query 6. mariadb-tools/dbt3_benchmark/tests/myisam_test_mariadb_5_3_mysql_5_5_mysql_5_6.conf to include the new queries configuration file DBT3 benchmark for the following configuration:
Results page: DBT3 benchmark results MyISAM
DBT3 benchmark for the following configuration:
Results page: DBT3 benchmark results InnoDB
DBT3 benchmark for the following configuration:
Results page: (TODO)
© 2023 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/dbt3-automation-scripts/