This section is for the posting of benchmark results
MariDB/MySQL sysbench benchmark comparison in %
Number of threads
1 4 8 16 32 64 128
sysbench test
delete 92.42 82.60 88.05 89.85 94.68 98.75 97.77
insert 100.70 99.90 103.21 89.51 85.16 105.39 108.51
oltp_complex_ro 101.09 101.83 100.32 103.78 102.10 101.29 102.92
oltp_complex_rw 95.04 90.22 91.84 88.78 100.98 101.96 101.60
oltp_simple 100.29 99.90 101.69 102.22 102.61 102.18 101.49
select 101.57 101.73 100.26 102.15 101.99 102.39 102.09
update_index 96.01 103.06 105.89 108.35 108.13 104.36 101.61
update_non_index 99.85 102.05 110.76 119.51 119.69 118.25 122.77
(MariaDB q/s / MySQL q/s * 100)
Run on Lenovo ThinkPad T500 with dual core 2.80GHz and 4GB RAM
MariaDB and MySQL were compiled with
BUILD/compile-amd64-max
MariaDB revision was:
revno: 2818 timestamp: Wed 2010-02-17 21:10:02 +0100
MySQL revision was:
revno: 3360 [merge] timestamp: Wed 2010-02-17 18:48:40 +0100
sysbench was run with these parameters:
--oltp-table-size=2000000 \
--max-time=300 \
--max-requests=0 \
--mysql-table-engine=InnoDB \
--mysql-user=root \
--mysql-engine-trx=yes
and this variable part of parameters
--num-threads=$THREADS --test=${TEST_DIR}/${SYSBENCH_TEST}
Configuration used for MariDB and MySQL:
--no-defaults \
--skip-grant-tables \
--language=./sql/share/english \
--datadir=$DATA_DIR \
--tmpdir=$TEMP_DIR \
--socket=$MY_SOCKET \
--table_open_cache=512 \
--thread_cache=512 \
--query_cache_size=0 \
--query_cache_type=0 \
--innodb_data_home_dir=$DATA_DIR \
--innodb_data_file_path=ibdata1:128M:autoextend \
--innodb_log_group_home_dir=$DATA_DIR \
--innodb_buffer_pool_size=1024M \
--innodb_additional_mem_pool_size=32M \
--innodb_log_file_size=256M \
--innodb_log_buffer_size=16M \
--innodb_flush_log_at_trx_commit=1 \
--innodb_lock_wait_timeout=50 \
--innodb_doublewrite=0 \
--innodb_flush_method=O_DIRECT \
--innodb_thread_concurrency=0 \
--innodb_max_dirty_pages_pct=80"
This page is licensed: CC BY-SA / Gnu FDL
MariDB/MySQL sysbench benchmark comparison in %
Each test was run for 5 minutes 3 times
Number of threads
1 4 8 16 32 64 128
sysbench test
delete 98.99 86.56 97.42 102.60 101.25 98.91 99.99
insert 99.20 97.52 98.18 99.01 99.32 99.76 99.36
oltp_complex_ro 100.34 99.60 98.97 100.34 99.37 99.98 100.25
oltp_complex_rw 115.90 101.87 101.93 100.78 100.45 95.67 105.08
oltp_simple 100.09 99.82 99.73 99.57 99.57 101.48 100.59
select 99.72 99.83 98.85 99.92 101.29 99.34 100.11
update_index 112.62 101.40 99.31 100.21 98.15 99.12 99.98
update_non_index 99.36 99.28 100.20 87.68 97.09 102.04 99.91
(MariaDB q/s / MySQL q/s * 100)
Benchmark was run on work: Linux openSUSE 11.1 (x86_64), daul socket quad-core Intel 3.0GHz. with 6MB L2 cache, 8 GB RAM, data_dir on single disk.
MariaDB and MySQL were compiled with
BUILD/compile-amd64-max
MariaDB revision was:
-rtag:5.1.42
MySQL revision was:
-rtag:5.1.42
sysbench was run with these parameters:
--oltp-table-size=2000000 \
--max-time=300 \
--max-requests=0 \
--mysql-table-engine=InnoDB \
--mysql-user=root \
--mysql-engine-trx=yes
and this variable part of parameters
--num-threads=$THREADS --test=${TEST_DIR}/${SYSBENCH_TEST}
Configuration used for MariaDB and MySQL:
--no-defaults \
--skip-grant-tables \
--language=./sql/share/english \
--datadir=$DATA_DIR \
--tmpdir=$TEMP_DIR \
--socket=$MY_SOCKET \
--table_open_cache=512 \
--thread_cache=512 \
--query_cache_size=0 \
--query_cache_type=0 \
--innodb_data_home_dir=$DATA_DIR \
--innodb_data_file_path=ibdata1:128M:autoextend \
--innodb_log_group_home_dir=$DATA_DIR \
--innodb_buffer_pool_size=1024M \
--innodb_additional_mem_pool_size=32M \
--innodb_log_file_size=256M \
--innodb_log_buffer_size=16M \
--innodb_flush_log_at_trx_commit=1 \
--innodb_lock_wait_timeout=50 \
--innodb_doublewrite=0 \
--innodb_flush_method=O_DIRECT \
--innodb_thread_concurrency=0 \
--innodb_max_dirty_pages_pct=80"
This page is licensed: CC BY-SA / Gnu FDL
3x Five Minute Runs on work with 5.2-wl86 key cache partitions on and off
MariDB 5.2-wl86 sysbench benchmark comparison with key_cache_partitions off and 7 in %
Each test was run three times for 5 minutes.
Number of threads
1 4 8 16 32 64 128
sysbench test
delete -18.36 -20.66 -11.32 5.42 -2.91 -14.62 -3.47
insert -2.38 -30.11 -1.64 -0.98 -1.19 0.12 -2.37
oltp_complex_ro 0.16 2.61 4.03 2.99 3.10 5.73 20.95
oltp_complex_rw Dup key errors (due to sysbench)
oltp_simple -1.24 1.86 11.14 10.69 16.11 17.16 14.31
select -0.22 2.00 11.42 10.31 15.58 17.10 14.31
update_index -9.34 15.75 -0.36 -10.33 1.94 2.44 41.44
update_non_index 0.73 1.04 11.12 17.32 5.30 -0.24 -9.55
(MariaDB 5.2-wl86 key_cache_partitions off q/s /
MariaDB 5.2-wl86 key_cache_partitions=7 q/s * 100)
key_buffer_size = 32M
Benchmark was run on work: Linux openSUSE 11.1 (x86_64), daul socket quad-core Intel 3.0GHz. with 6MB L2 cache, 8 GB RAM, data_dir on single disk.
MariaDB and MySQL were compiled with
BUILD/compile-amd64-max
MariaDB revision was:
lp:~maria-captains/maria/maria-5.2-wl86
revno: 2742
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-5.2-keycache
timestamp: Tue 2010-02-16 08:41:11 -0800
message:
WL#86: Partitioned key cache for MyISAM.
This is the base patch for the task.
sysbench was run with the following parameters:
--oltp-table-size=20000000 \ # 20 mio rows
--max-time=300 \
--max-requests=0 \
--mysql-table-engine=MyISAM \
--mysql-user=root \
--mysql-engine-trx=no \
--myisam-max-rows=50000000"
and this variable part of parameters
--num-threads=$THREADS --test=${TEST_DIR}/${SYSBENCH_TEST}
Configuration used for MariDB:
--no-defaults \
--datadir=$DATA_DIR \
--language=./sql/share/english \
--key_buffer_size=32M \
--max_connections=256 \
--query_cache_size=0 \
--query_cache_type=0 \
--skip-grant-tables \
--socket=$MY_SOCKET \
--table_open_cache=512 \
--thread_cache=512 \
--tmpdir=$TEMP_DIR"
# --key_cache_partitions=7 \
This page is licensed: CC BY-SA / Gnu FDL
3x Five Minute Runs on work with 5.1 vs. 5.2-wl86 key cache partitions off
MariaDB 5.1 vs. 5.2-wl86 sysbench benchmark comparison in %
Each test was run three times for 5 minutes.
Number of threads
1 4 8 16 32 64 128
sysbench test
delete 107.28 94.70 98.10 107.12 93.59 89.24 86.89
insert 103.15 105.13 101.75 102.78 101.52 100.29 100.89
oltp_complex_ro 101.31 101.77 100.41 98.72 98.53 101.59 100.44
oltp_complex_rw Dup key errors (due to sysbench)
oltp_simple 102.28 100.76 102.70 100.94 101.05 101.81 102.06
select 100.88 101.05 100.48 101.61 101.48 101.87 101.44
update_index 97.57 96.81 93.58 102.43 89.19 107.63 88.29
update_non_index 101.58 83.24 110.46 94.52 106.33 103.87 115.22
(MariaDB 5.1 key_cache_partitions off q/s /
MariaDB 5.2-wl86 key_cache_partitions off q/s * 100)
key_buffer_size = 512M
Benchmark was run on work: Linux openSUSE 11.1 (x86_64), daul socket quad-core Intel 3.0GHz. with 6MB L2 cache, 8 GB RAM, data_dir on single disk.
MariaDB and MySQL were compiled with
BUILD/compile-amd64-max
MariaDB 5.1 revision was:
revno: 2821
committer: Sergei Golubchik <sergii@pisem.net>
branch nick: maria-5.1
timestamp: Tue 2010-02-23 13:04:58 +0100
message:
fix for a possible DoS in the my_net_skip_rest()
MariaDB 5.2-wl86 revision was:
lp:~maria-captains/maria/maria-5.2-wl86
revno: 2742
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-5.2-keycache
timestamp: Tue 2010-02-16 08:41:11 -0800
message:
WL#86: Partitioned key cache for MyISAM.
This is the base patch for the task.
sysbench was run with the following parameters:
--oltp-table-size=20000000 \ # 20 mio rows
--max-time=300 \
--max-requests=0 \
--mysql-table-engine=MyISAM \
--mysql-user=root \
--mysql-engine-trx=no \
--myisam-max-rows=50000000"
and this variable part of the parameters
--num-threads=$THREADS --test=${TEST_DIR}/${SYSBENCH_TEST}
Configuration used for MariaDB:
--no-defaults \
--datadir=$DATA_DIR \
--language=./sql/share/english \
--key_buffer_size=512M \
--max_connections=256 \
--query_cache_size=0 \
--query_cache_type=0 \
--skip-grant-tables \
--socket=$MY_SOCKET \
--table_open_cache=512 \
--thread_cache=512 \
--tmpdir=$TEMP_DIR"
# --key_cache_partitions=7 \
This page is licensed: CC BY-SA / Gnu FDL
sysbench v0.5 - 3x 15 Minute Runs on perro with 5.2-wl86 key cache partitions off, 8, and 32 and key buffer size 400
MariDB sysbench benchmark comparison for key_cache_partitions in % with key_buffer_size = 400MB
Each test was run 3 times for 15 minutes with 3 minutes warmup.
Number of threads
1 4 8 16 32 64 128
sysbench test
oltp_complex_ro
8 / off -0.78 -0.42 -0.18 -0.49 -1.03 -0.64 1.08
32 / off -0.38 -0.30 0.55 -0.39 -0.75 -0.05 2.49
oltp_simple
8 / off -1.19 -2.20 -0.74 -2.74 -1.54 0.28 -1.46
32 / off -1.24 -1.22 0.33 -0.13 0.11 2.09 -1.34
select
8 / off -0.71 -1.68 -1.48 -2.05 0.94 -2.93 -0.18
32 / off -0.71 -1.33 -2.11 -0.63 -0.40 -19.68* -11.45*
update_index
8 / off -1.30 4.37 -14.69* -2.56 17.69* -1.14 2.82
32 / off -1.47 7.03* 0.71 -0.72 15.61* 1.61 0.33
( 8/off*100)-100
(32/off*100)-100
* means due to unusual high STDEV (see OO.org spreadsheet for details)
off means key_cache_partitions off
8 means key_cache_partitions = 8
32 means key_cache_partitions = 32
Benchmark was run on perro: Linux openSUSE 11.1 (x86_64), single socket dual-core Intel 3.2GHz. with 1MB L2 cache, 2GB RAM, data_dir on 2 disk software RAID 0
MariaDB and MySQL were compiled with
BUILD/compile-amd64-max
MariaDB revision was:
revno: 2742
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-5.2-keycache
timestamp: Tue 2010-02-16 08:41:11 -0800
message:
WL#86: Partitioned key cache for MyISAM.
This is the base patch for the task.
sysbench was run with the following parameters:
--oltp-table-size=20000000 \ # 20 million rows.
--max-requests=0 \
--mysql-table-engine=MyISAM \
--mysql-user=root \
--mysql-engine-trx=no \
--myisam-max-rows=50000000 \
--rand-seed=303
and these variable parameters:
--num-threads=$THREADS --test=${TEST_DIR}/${SYSBENCH_TEST}
Configuration used for MariaDB:
--no-defaults \
--datadir=/mnt/data/sysbench/data \
--language=./sql/share/english \
--key_buffer_size=400M \
--key_cache_partitions=32 \ # Off | 8 | 32
--max_connections=256 \
--query_cache_size=0 \
--query_cache_type=0 \
--skip-grant-tables \
--socket=/tmp/mysql.sock \
--table_open_cache=512 \
--thread_cache=512 \
--tmpdir=/mnt/data/sysbench
This page is licensed: CC BY-SA / Gnu FDL
3x 15 Minute Runs on perro with 5.2-wl86 key cache partitions off, 8, and 32 and key buffer size 75
MariDB sysbench benchmark comparison for key_cache_partitions in % with key_buffer_size = 75MB
Each test was run 3 times for 15 minutes with 3 minutes warmup.
Number of threads
1 4 8 16 32 64 128
sysbench test
oltp_complex_ro
8 / off 3.05 -0.10 0.57 1.00 -0.05 0.15 2.50
32 / off 3.04 -26.61* 0.89 -0.02 1.03 0.44 2.94
oltp_simple
8 / off -0.95 -1.10 -1.17 1.36 -2.65 -0.59 -1.01
32 / off 0.76 -0.02 0.66 2.75 1.96 3.05 -0.86
select
8 / off -1.45 -0.68 -2.31 -27.61* -0.52 -3.97 -0.24
32 / off -0.87 -1.63 -1.15 -2.10 0.44 1.12 1.39
update_index
8 / off -2.55 8.29* 3.14 4.16 1.13 1.95 1.29
32 / off -2.27 9.46* -0.12 2.64 0.69 -7.26* -0.24
( 8/off*100)-100
(32/off*100)-100
* means unusually high STDEV
off means key_cache_partitions off
8 means key_cache_partitions = 8
32 means key_cache_partitions = 32
Benchmark was run on perro: Linux openSUSE 11.1 (x86_64), single socket dual-core Intel 3.2GHz. with 1MB L2 cache, 2GB RAM, data_dir on 2 disk software RAID 0
MariaDB and MySQL were compiled with
BUILD/compile-amd64-max
MariaDB revision was:
revno: 2742
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-5.2-keycache
timestamp: Tue 2010-02-16 08:41:11 -0800
message:
WL#86: Partitioned key cache for MyISAM.
This is the base patch for the task.
sysbench was run with the following parameters:
--oltp-table-size=20000000 \ # 20 million rows.
--max-requests=0 \
--mysql-table-engine=MyISAM \
--mysql-user=root \
--mysql-engine-trx=no \
--myisam-max-rows=50000000 \
--rand-seed=303
and the following variable parameters
--num-threads=$THREADS --test=${TEST_DIR}/${SYSBENCH_TEST}
Configuration used for MariDB:
--no-defaults \
--datadir=/mnt/data/sysbench/data \
--language=./sql/share/english \
--key_buffer_size=75M \
--key_cache_partitions=32 \ # Off | 8 | 32
--max_connections=256 \
--query_cache_size=0 \
--query_cache_type=0 \
--skip-grant-tables \
--socket=/tmp/mysql.sock \
--table_open_cache=512 \
--thread_cache=512 \
--tmpdir=/mnt/data/sysbench
This page is licensed: CC BY-SA / Gnu FDL
select_random_ranges
(select 10 ranges with a delta as parameter)
select_random_points
(select 100 random points)
select_random_ranges
A delta of 100 for the ranges gives 3 - 6% performance gain
A delta of 50 for the ranges gives 3 - 15% performance gain
A delta of 5 for the ranges gives up to 70% performance gain
A delta of 1 million shows no difference at all.
select_random_points
We see up to 150% performance gain fetching index only
We see up to 50% performance gain fetching index and data
The absolute numbers are highly RAM depended
We see an up to 250% performance difference on a 2GB system compared to a 4GB system.
MariaDB and MySQL were compiled with
BUILD/compile-amd64-max
MariaDB revision was:
revno: 2742
committer: Igor Babaev <igor@askmonty.org>
branch nick: maria-5.2-keycache
timestamp: Tue 2010-02-16 08:41:11 -0800
message:
WL#86: Partitioned key cache for MyISAM.
This is the base patch for the task.
sysbench was run with the following parameters:
--oltp-table-size=20000000 \ # 20 million rows.
--max-requests=0 \
--mysql-table-engine=MyISAM \
--mysql-user=root \
--mysql-engine-trx=no \
--myisam-max-rows=50000000 \
--rand-seed=303
and the following variable parameters
--num-threads=$THREADS --test=${TEST_DIR}/${SYSBENCH_TEST}
Configuration used for MariDB:
--no-defaults \
--datadir=/mnt/data/sysbench/data \
--language=./sql/share/english \
--key_buffer_size=512M \
--key_cache_partitions=32 \ # Off | 32 | 64
--max_connections=256 \
--query_cache_size=0 \
--query_cache_type=0 \
--skip-grant-tables \
--socket=/tmp/mysql.sock \
--table_open_cache=512 \
--thread_cache=512 \
--tmpdir=/mnt/data/sysbench
This page is licensed: CC BY-SA / Gnu FDL
Results from various Sysbench runs. The data is in OpenDocument Spreadsheet format (.ods).
For reference, the "perro" and "work" systems were configured as follows:
perro
Linux openSUSE 11.1 (x86_64), single socket dual-core Intel 3.2GHz. with 1MB L2 cache, 2GB RAM, data_dir on 2 disk software RAID 0
work
Linux openSUSE 11.1 (x86_64), dual socket quad-core Intel 3.0GHz. with 6MB L2 cache, 8 GB RAM, data_dir on single disk.
Single Five Minutes Runs on T500 Laptop, OO.org spreadsheet: Sysbench_five_minutes_mariadb_mysql_t500.ods
Single Five Minutes Runs on perro, OO.org spreadsheet: Sysbench_five_minutes_mariadb_mysql_perro.ods
Single Five Minutes Runs on work, OO.org spreadsheet: Sysbench_five_minutes_mariadb_mysql_work.ods
Three Times Five Minutes Runs on work with 5.1.42, OO.org spreadsheet: Sysbench_five_minutes_mariadb_mysql_work_5.1.42.ods
Three Times Five Minutes Runs on work with 5.2-wl86 key_cache_partitions on and off, OO.org spreadsheet: Sysbench_five_minutes_mariadb-5.2-wl86_key_cache_partitions_on_off_work.ods
Three Times Five Minutes Runs on work with 5.1 vs. 5.2-wl86 key_cache_partitions off, OO.org spreadsheet: Sysbench_five_minutes_mariadb-5.2-wl86_key_cache_partitions_on_off_work.ods
Three Times Fifteen Minutes Runs on perro with 5.2-wl86 key_cache_partitions off, 8, and 32 and key_buffer_size 400, OO.org spreadsheet: Sysbench_fifteen_minutes_mariadb-5.2-wl86_key_cache_partitions_off_8_32_kbs_400.ods
Three Times Fifteen Minutes Runs on perro with 5.2-wl86 key_cache_partitions off, 8, and 32 and key_buffer_size 75, OO.org spreadsheet: Sysbench_fifteen_minutes_mariadb-5.2-wl86_key_cache_partitions_off_8_32_kbs_75.ods
select_random_ranges and select_random_points, OO.org spreadsheet: Sysbench_select_random_ranges_points.ods
select_100_random_points.lua result on perro with key_cache_partitions off and 32, OO.org spreadsheet: Sysbench_v0.5_select_100_random_points.ods
select_random_points.lua --random-points=50
result on perro with key_cache_partitions off and 32, OO.org spreadsheet: Sysbench_v0.5_select_50_random_points.ods
select_random_points.lua --random-points=10
result on perro with key_cache_partitions off and 32, OO.org spreadsheet: Sysbench_v0.5_select_10_random_points.ods
select_random_points.lua --random-points=10, 50, and 100
results on perro with key_cache_segments off, 32, and 64 OO.org spreadsheet: Sysbench_v0.5_select_random_points_10_50_100_perro.ods
select_random_points.lua --random-points=10, 50, and 100
results on pitbull with key_cache_segments off, 32, and 64 OO.org spreadsheet: Sysbench_v0.5_select_random_points_10_50_100_pitbull.ods
This page is licensed: CC BY-SA / Gnu FDL
MariDB/MySQL sysbench benchmark comparison in % Each test was run for 5 minutes.
Number of threads
1 4 8 16 32 64 128
sysbench test
delete 103.72 101.84 106.56 102.80 94.19 86.23 65.13
insert 102.01 95.04 97.44 89.00 82.42 81.82 85.63
oltp_complex_ro 104.21 104.98 105.30 102.67 102.69 102.95 101.10
oltp_complex_rw 105.08 104.34 103.60 102.90 100.76 98.41 89.94
oltp_simple 100.66 100.44 102.82 104.23 103.08 100.55 95.90
select 102.93 101.56 103.70 104.18 102.25 100.65 97.33
update_index 101.74 92.33 101.69 93.09 76.45 73.67 72.88
update_non_index 101.58 98.13 98.91 92.32 84.00 76.75 74.19
(MariaDB q/s / MySQL q/s * 100)
Benchmark was run on perro: Linux openSUSE 11.1 (x86_64), single socket dual-core Intel 3.2GHz. with 1MB L2 cache, 2GB RAM, data_dir on 2 disk software RAID 0
MariaDB and MySQL were compiled with
BUILD/compile-amd64-max
MariaDB revision was:
revno: 2821
committer: Sergei Golubchik <sergii@pisem.net>
branch nick: maria-5.1
timestamp: Tue 2010-02-23 13:04:58 +0100
message:
fix for a possible DoS in the my_net_skip_rest()
MySQL revision was:
revno: 3360 [merge]
author: hery.ramilison@sun.com
committer: MySQL Build Team <build@mysql.com>
branch nick: mysql-5.1
timestamp: Wed 2010-02-17 18:48:40 +0100
message:
Merge from mysql-5.1.44-release
sysbench was run with these parameters:
--oltp-table-size=2000000 \
--max-time=300 \
--max-requests=0 \
--mysql-table-engine=InnoDB \
--mysql-user=root \
--mysql-engine-trx=yes
and this variable part of parameters
--num-threads=$THREADS --test=${TEST_DIR}/${SYSBENCH_TEST}
Configuration used for MariDB and MySQL:
--no-defaults \
--skip-grant-tables \
--language=./sql/share/english \
--datadir=$DATA_DIR \
--tmpdir=$TEMP_DIR \
--socket=$MY_SOCKET \
--table_open_cache=512 \
--thread_cache=512 \
--query_cache_size=0 \
--query_cache_type=0 \
--innodb_data_home_dir=$DATA_DIR \
--innodb_data_file_path=ibdata1:128M:autoextend \
--innodb_log_group_home_dir=$DATA_DIR \
--innodb_buffer_pool_size=1024M \
--innodb_additional_mem_pool_size=32M \
--innodb_log_file_size=256M \
--innodb_log_buffer_size=16M \
--innodb_flush_log_at_trx_commit=1 \
--innodb_lock_wait_timeout=50 \
--innodb_doublewrite=0 \
--innodb_flush_method=O_DIRECT \
--innodb_thread_concurrency=0 \
--innodb_max_dirty_pages_pct=80"
This page is licensed: CC BY-SA / Gnu FDL
MariDB/MySQL sysbench benchmark comparison in %
Each test was run for 5 minutes.
Number of threads
1 4 8 16 32 64 128
sysbench test
delete 121.52 144.77 117.70 115.15 100.48 75.39 66.56
insert 114.89 181.50 118.06 136.00 125.53 141.83 113.88
oltp_complex_ro 103.13 100.99 94.65 104.14 97.87 90.18 79.93
oltp_complex_rw 131.65 149.90 120.88 128.58 116.71 89.92 80.63
oltp_simple 102.32 102.57 97.33 96.34 93.99 78.81 59.71
select 102.12 102.05 96.64 97.28 93.55 81.53 59.83
update_index 114.08 103.98 115.59 124.90 123.51 104.38 99.11
update_non_index 134.04 147.94 150.91 150.04 152.12 108.34 89.24
insert/4 is a glitch
(MariaDB q/s / MySQL q/s * 100)
Benchmark was run on work: Linux openSUSE 11.1 (x86_64), daul socket quad-core Intel 3.0GHz. with 6MB L2 cache, 8 GB RAM, data_dir on single disk.
MariaDB and MySQL were compiled with
BUILD/compile-amd64-max
MariaDB revision was:
revno: 2821
committer: Sergei Golubchik <sergii@pisem.net>
branch nick: maria-5.1
timestamp: Tue 2010-02-23 13:04:58 +0100
message:
fix for a possible DoS in the my_net_skip_rest()
MySQL revision was:
revno: 2929
committer: Alexander Nozdrin <alik@sun.com>
branch nick: mysql-trunk
timestamp: Sat 2010-02-20 12:26:22 +0300
message:
Fix default.conf.
sysbench was run with these parameters:
--oltp-table-size=2000000 \
--max-time=300 \
--max-requests=0 \
--mysql-table-engine=InnoDB \
--mysql-user=root \
--mysql-engine-trx=yes
and this variable part of parameters
--num-threads=$THREADS --test=${TEST_DIR}/${SYSBENCH_TEST}
Configuration used for MariaDB and MySQL:
--no-defaults \
--skip-grant-tables \
--language=./sql/share/english \
--datadir=$DATA_DIR \
--tmpdir=$TEMP_DIR \
--socket=$MY_SOCKET \
--table_open_cache=512 \
--thread_cache=512 \
--query_cache_size=0 \
--query_cache_type=0 \
--innodb_data_home_dir=$DATA_DIR \
--innodb_data_file_path=ibdata1:128M:autoextend \
--innodb_log_group_home_dir=$DATA_DIR \
--innodb_buffer_pool_size=1024M \
--innodb_additional_mem_pool_size=32M \
--innodb_log_file_size=256M \
--innodb_log_buffer_size=16M \
--innodb_flush_log_at_trx_commit=1 \
--innodb_lock_wait_timeout=50 \
--innodb_doublewrite=0 \
--innodb_flush_method=O_DIRECT \
--innodb_thread_concurrency=0 \
--innodb_max_dirty_pages_pct=80"
This page is licensed: CC BY-SA / Gnu FDL
Here are some benchmarks of some development threadpool code (the 5.5 threadpool).
The benchmarks were run on three machines:
facebook-maria1 (Linux, 16 cores)
pitbull (Linux, 24 cores)
windows (Windows, 8 cores)
Sysbench 0.4 was used to run some "unit" OLTP tests (point-select and update-nokey), as well as the "classic" OLTP-readonly and OLTP-readwrite. All tests were run with the number of concurrent clients ranging from 16 to 4096, with warm cache, with the sysbench table having 1M records.
The results are quite different on all of the machines tested (the machines are very different, in terms of cores, IO etc), yet threadpool has a positive effect on all 3 machines, and the positive effect seems to grow with the number of cores.
Some notes on how the benchmarks were run:
The benchmark client and server used different CPUs - ('taskset'
(Linux), or 'start /affinity' (Windows) were used to run the benchmark client
on #CPUs/4
, the rest of CPUs were used by the server). On
the Linux boxes, --thread_pool_size=<N>
(where N is number
of cores dedicated to the server) was used.
innodb_flush_log_at_trx_commit=0
and innodb_flush_method=ALL_O_DIRECT
was used to avoid extensive fsyncing, which is ok for the purposes of the
testing for this.
Every "write" benchmark (oltp_rw
and update_nokey
) started with a new
server (i.e. kill mysqld, remove innodb files, and restart mysqld for each
test). Every "read" benchmark, on the other hand, reused the same running
server instance. Starting afresh with a new server on write benchmarks is
done mainly to eliminate the effects of the purge lag.
The results are in queries-per-second (QPS).
concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
3944
4725
4878
4863
4732
4554
4345
4103
1670
threadpool
3822
4955
4991
5017
4908
4716
4610
4307
2962
concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
6754
7905
8152
7948
7924
7587
5313
3827
208
threadpool
6566
7725
8108
8079
7976
7793
7429
6523
4456
concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
1822
1831
1825
1829
1816
1879
1866
1783
987
threadpool
2019
2049
2024
1992
1924
1897
1855
1825
1403
concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
2833
3510
3545
3420
3259
2818
1788
820
113
threadpool
3163
3590
3498
3459
3354
3117
2190
1064
506
concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
4561
5316
5332
3512
2874
2476
1380
265
53
threadpool
4504
5382
5694
5567
5302
4514
2548
1186
484
concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
1480
1498
1472
1477
1456
1371
731
328
82
threadpool
1449
1523
1527
1492
1443
1409
1365
1240
862
concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
91322
113116
115418
114484
111169
104612
26902
12843
5038
threadpool
100359
115618
118115
120136
119165
113931
110787
109970
104985
concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
148673
161547
169747
172083
69036
42041
21775
4368
282
threadpool
143222
167069
167270
165977
164983
158410
148690
147107
143934
concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
39734
42885
44448
44478
41720
38196
36844
35404
23306
threadpool
42143
45679
47066
47753
46720
44215
43677
43093
44364
concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
60165
65761
67727
57232
47612
26341
8981
3265
389
threadpool
65092
68683
67053
64141
64815
63047
63346
63638
62843
concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
65213
71680
19418
13008
11155
8742
5645
635
332
threadpool
64902
70236
70037
68926
69930
69929
67099
62376
17766
concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
24790
25634
25639
25309
24754
19420
5249
2361
824
threadpool
25251
25259
25406
25327
24850
23818
23137
23003
22047
This page is licensed: CC BY-SA / Gnu FDL