DBT3 Benchmark Results MyISAM

Introduction

This page shows the results for benchmarking the following configuration:

• MariaDB 5.3.2 + MyISAM

• MariaDB 5.5.18 + MyISAM

• MySQL 5.5.19 + MyISAM

• MySQL 5.6.4 + MyISAM

The test is performed using the automation script /mariadb-tools/dbt3_benchmark/launcher.pl.

Details about this automation script can be found on the DBT3 automation scripts page.

Hardware

The tests were performed on our facebook-maria1 machine. It has the following parameters:

• CPU: 16 Intel® Xeon® CPU L5520 @ 2.27GHz

• Memory: Limited to 16 GB out of 72 by adding 'mem=16G' parameter to /boot/grub/menu.lst

• Logical disk: HDD 2 TB

• Operating system:

Scale factor 30

This test was performed with the following parameters:

• Scale factor: 30

• Query timeout: 2 hours

• Number of tests per query: 1

• Total DB size on disk: about 50GB

• Available memory: 16 GB

NOTE: The available memory is controlled by a parameter mem=16G added to the file /boot/grub/menu.lst

Steps to reproduce

Follow the instructions in DBT3 automation scripts to prepare the environment for the test.

Before you run the test, ensure that the settings in the test configuration files match your prepared environment. For more details on the test configuration, please, refer to the Test configuration parameters.

After the environment is prepared, the following command should be executed in the shell:

perl launcher.pl \
--results-output-dir=/home/mariadb/benchmark/dbt3/results/myisam_test \
--project-home=/home/mariadb/benchmark/dbt3/ \
--datadir=/home/mariadb/benchmark/dbt3/db_data/ \
--test=./tests/myisam_test_mariadb_5_3_mysql_5_5_mysql_5_6.conf \
--queries-home=/home/mariadb/benchmark/dbt3/gen_query/ --scale-factor=30 \
--TIMEOUT=7200

Compared configurations

The following configurations have been compared in this test:

Case 1: MariaDB 5.3.2 + MyISAM

Here are the common options that the mysqld server was started with:

net_read_timeout = 300
net_write_timeout = 600
key_buffer_size = 3G
skip-external-locking
key_buffer = 16M
max_allowed_packet = 16M
table_open_cache = 1024
thread_cache = 512
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
max_connections = 256
query_cache_size = 0
query_cache_type = 0
sql-mode = NO_ENGINE_SUBSTITUTION

#Per-test optimizations
optimizer_switch='index_merge=on'
optimizer_switch='index_merge_union=on'
optimizer_switch='index_merge_sort_union=on'
optimizer_switch='index_merge_intersection=on'
optimizer_switch='index_merge_sort_intersection=off'
optimizer_switch='index_condition_pushdown=on'
optimizer_switch='derived_merge=on'
optimizer_switch='derived_with_keys=on'
optimizer_switch='firstmatch=off'
optimizer_switch='loosescan=off'
optimizer_switch='materialization=on'
optimizer_switch='in_to_exists=on'
optimizer_switch='semijoin=on'
optimizer_switch='partial_match_rowid_merge=on'
optimizer_switch='partial_match_table_scan=on'
optimizer_switch='subquery_cache=off'
optimizer_switch='mrr=on'
optimizer_switch='mrr_cost_based=off'
optimizer_switch='mrr_sort_keys=on'
optimizer_switch='outer_join_with_cache=on'
optimizer_switch='semijoin_with_cache=off'
optimizer_switch='join_cache_incremental=on'
optimizer_switch='join_cache_hashed=on'
optimizer_switch='join_cache_bka=on'
optimizer_switch='optimize_join_buffer_size=on'
optimizer_switch='table_elimination=on'

join_buffer_space_limit = 3072M
join_buffer_size = 1536M
join_cache_level = 6
mrr_buffer_size = 96M
tmp_table_size = 96M
max_heap_table_size = 96M

Case 2: MariaDB 5.5.18 + MyISAM

Uses the same configuration file as MariaDB 5.3.2 in Case 1.

Case 3: MySQL 5.5.19 + MyISAM

Here are the common options that the mysqld server was started with:

net_read_timeout = 300
net_write_timeout = 600
key_buffer_size = 3G
skip-external-locking
key_buffer = 16M
max_allowed_packet = 16M
table_open_cache = 1024
thread_cache = 512
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
myisam_sort_buffer_size = 8M
max_connections = 256
query_cache_size = 0
query_cache_type = 0
sql-mode = NO_ENGINE_SUBSTITUTION

join_buffer_size = 1536M
tmp_table_size = 96M
max_heap_table_size = 96M
read_rnd_buffer_size = 96M

Case 4: MySQL 5.6.4 + MyISAM

Here are the common options that the mysqld server was started with:

net_read_timeout = 300
net_write_timeout = 600
key_buffer_size = 3G
skip-external-locking
key_buffer = 16M
max_allowed_packet = 16M
table_open_cache = 1024
thread_cache = 512
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
myisam_sort_buffer_size = 8M
max_connections = 256
query_cache_size = 0
query_cache_type = 0
sql-mode = NO_ENGINE_SUBSTITUTION

optimizer_switch='mrr=on'
optimizer_switch='mrr_cost_based=off'
optimizer_switch='batched_key_access=on'
optimizer_switch='index_condition_pushdown=on'

join_buffer_size = 1536M
tmp_table_size = 96M
max_heap_table_size = 96M
read_rnd_buffer_size = 96M

The server has been restarted between each query run and the caches have been cleared between each query run.

Results (without q20)

Here is the graphics of the results:

(Smaller bars are better)

dbt3-myisam-s30-hdd

NOTE: Queries that are cut off by the graphics have timed out the period of 2 hours.

Here are the actual results in seconds (smaller is better):

Configuration	1.sql	2.sql	2-opt.sql	3.sql	4.sql	5.sql	6.sql	7.sql	8.sql	9.sql	10.sql	11.sql	12.sql	13.sql	14.sql	15.sql	16.sql	17.sql	17-opt1.sql	17-opt2.sql	18.sql	18-opt.sql	19.sql	19-opt1.sql	19-opt2.sql	20.sql	21.sql	22.sql	Version	Query and explain details
Configuration	MariaDB 5.3.2 + MyISAM	Ratio	MariaDB 5.5.18 + MyISAM	Ratio	MySQL 5.5.19 + MyISAM	Ratio	MySQL 5.6.4 + MyISAM	Ratio
1.sql	261	1.00	308	1.18	259	0.99	277	1.06
2.sql	47	1.00	48	1.02	499	10.62	49	1.04
2-opt.sql	46	1.00	48	1.04	-	-	-	-
3.sql	243	1.00	246	1.01	>7200	-	1360	5.60
4.sql	137	1.00	135	0.99	4117	30.05	137	1.00
5.sql	181	1.00	187	1.03	6164	34.06	1254	6.93
6.sql	198	1.00	205	1.04	>7200	-	194	0.98
7.sql	779	1.00	896	1.15	814	1.04	777	1.00
8.sql	270	1.00	287	1.06	749	2.77	1512	5.60
9.sql	252	1.00	254	1.01	>7200	-	298	1.18
10.sql	782	1.00	854	1.09	>7200	-	1881	2.41
11.sql	45	1.00	36	0.80	357	7.93	49	1.09
12.sql	211	1.00	217	1.03	>7200	-	213	1.01
13.sql	251	1.00	236	0.94	1590	6.33	244	0.97
14.sql	88	1.00	91	1.03	1590	18.07	94	1.07
15.sql	162	1.00	164	1.01	4580	28.27	165	1.02
16.sql	154	1.00	152	0.99	174	1.13	173	1.12
17.sql	1493	1.00	1495	1.00	865	0.58	794	0.53
17-opt1.sql	795	1.00	794	1.00	862	1.08	794	1.00
17-opt2.sql	1482	1.00	1458	0.98	2167	1.46	1937	1.31
18.sql	971	1.00	931	0.96	>7200	-	>7200	-
18-opt.sql	121	1.00	125	1.03	-	-	-	-
19.sql	212	1.00	212	1.00	2004	9.45	61	0.29
19-opt1.sql	59	1.00	59	1.00	1999	33.88	61	1.03
19-opt2.sql	260	1.00	216	0.83	443	1.70	236	0.91
20.sql	-	-	-	-	-	-	-	-
21.sql	173	1.00	179	1.03	>7200	-	183	1.06
22.sql	13	1.00	14	1.08	10	0.77	13	1.00
Version	5.3.2-MariaDB-beta		5.5.18-MariaDB		5.5.19		5.6.4-m7
Query and explain details	Explain details		Explain details		Explain details		Explain details

NOTE: The columns named "Ratio" are calculated values of the ratio between the current value compared to the value in the first test configuration. The formula for it is (current_value/value_in_first_row). For example if MariaDB 5.3.2 (the first column) handles a query for 100 seconds and MySQL 5.6.4 (the last configuration) handles the same query for 120 seconds, the ratio will be120/100 = 1.20. This means that it takes MySQL 5.6.4 20% more time to handle the same query.

The archived folder with all the results and details for that benchmark can be downloaded from here:MyISAM s30 on facebook-maria1

Notes

Queries 2-opt.sql and 18-opt.sql are tested only for MariaDB 5.3.2 and MariaDB 5.5.18

• Additional startup parameters for 2_opt:

--optimizer_switch='mrr_sort_keys=off'

• Additional startup parameters for 18_opt:

--optimizer_switch='semijoin=off' --optimizer_switch='index_condition_pushdown=on'

• Additional modifications for 17-opt1:

SELECT
SUM(l_extendedprice) / 7.0 AS avg_yearly
FROM
 part STRAIGHT_JOIN lineitem
WHERE
p_partkey = l_partkey
...

• Additional modifications for 17-opt2:

SELECT
 	SUM(l_extendedprice) / 7.0 AS avg_yearly
FROM
 	lineitem STRAIGHT_JOIN part
WHERE
 	p_partkey = l_partkey
...

• Additional modifications for 19-opt1:

SELECT
 	SUM(l_extendedprice* (1 - l_discount)) AS revenue
FROM
 	part STRAIGHT_JOIN lineitem
WHERE
 	(
 		p_partkey = l_partkey
...

• Additional modifications for 19-opt2:

SELECT
 	SUM(l_extendedprice* (1 - l_discount)) AS revenue
FROM
 	lineitem STRAIGHT_JOIN part
WHERE
 	(
 		p_partkey = l_partkey
...

Benchmark for q20

This benchmarked only q20 with the same settings as described above for the other queries. The only difference is the timeout that was used: 30000 seconds (8 hours and 20 min).

Compared cases

The benchmark for q20 compares the following cases:

• q20.sql - the original query is run with the IN-TO-EXISTS strategy for all servers. The following optimizer switches were used for MariaDB:

--optimizer_switch='in_to_exists=on,materialization=off,semijoin=off';

• q20-opt0.sql - the original query is changed so that the same join order is chosen as for the two subsequent variants that test materialization where this order is optimal. The join order is:

SELECT s_name, s_address
 FROM supplier, nation
 WHERE s_suppkey IN (SELECT DISTINCT (ps_suppkey)
 FROM '''part straight_join partsupp'''
 WHERE ps_partkey = p_partkey ...

• Since the IN-TO-EXISTS strategy is essentially the same for both MariaDB and MySQL, this query was tested for MySQL only.

• q20-opt1.sql - modifies the original query in two ways:

  • enforces the MATERIALIZATION strategy, and

  • enforces an optimal JOIN order via straight_join as follows:

SELECT s_name, s_address
 FROM supplier, nation
 WHERE s_suppkey IN (SELECT DISTINCT (ps_suppkey)
 FROM '''part straight_join partsupp'''
 WHERE ps_partkey = p_partkey ...

q20-opt1.sql uses the following optimizer switches for MariaDB:

--optimizer_switch='in_to_exists=off,materialization=on,semijoin=off';

• q20-opt2.sql - the same as q20-opt1.sql but allows the optimizer to choose the subquery strategy via the following switch:

--optimizer_switch='in_to_exists=on,materialization=on,semijoin=on';

• This switch results in the choice of SJ-MATERIALIZATION.

NOTE: For MySQL there are no such optimizer-switch parameters, and the tests were started without any additional startup parameters. The default algorithm in MySQL is in_to_exists.

Results for q20

Here is the graphics of the results of the benchmarked q20: (Smaller bars are better)

dbt3-q20-myisam-s30-hdd

NOTE: Queries that are cut off by the graphics have timed out the period of 30000 seconds.

Here are the actual results in seconds (smaller is better):

Configuration	20.sql	20-opt0.sql	20-opt1.sql	20-opt2.sql	Version	Query and explain details
MariaDB 5.3.2 + MyISAM	20070	-	5560	5615	5.3.2-MariaDB-beta	Explain details
MariaDB 5.5.18 + MyISAM	19922	-	5529	5572	5.5.18-MariaDB	Explain details
MySQL 5.5.19 + MyISAM	17832	>30000	-	-	5.5.19	Explain details
MYSQL 5.6.4 + MyISAM	19845	>30000	-	-	5.6.4-m7	Explain details

_{This page is licensed: CC BY-SA / Gnu FDL}