All pages
Powered by GitBook
2 of 12

Benchmark Results

This section is for the posting of benchmark results

sysbench v0.5 - Single Five Minute Runs on T500 Laptop

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

sysbench v0.5 - Three Times Five Minutes Runs on work with 5.1.42

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

sysbench v0.5 - 3x Five Minute Runs on work with 5.2-wl86

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

sysbench v0.5 - 3x Five Minute Runs on work with 5.1 vs. 5.2-wl86

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 a

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

sysbench v0.5 - 3x 15 Minute Runs on perro with 5.2-wl86 b

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 and Select Random Point

  • select_random_ranges (select 10 ranges with a delta as parameter)

  • select_random_points (select 100 random points)

Findings:

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

Sysbench Results

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
work

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.

sysbench v0.5 results

  • 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

sysbench v0.5 - Single Five Minute Runs on perro

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

sysbench v0.5 - Single Five Minute Runs on work

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

Threadpool Benchmarks

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:

  1. 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.

  2. 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.

  3. 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.

  4. The results are in queries-per-second (QPS).

OLTP_RO

OLTP_RO facebook-maria1

concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
threadpool

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

oltp-ro-facebook-maria1

OLTP_RO pitbull

concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
threadpool

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

oltp_ro-pitbull

OLTP_RO Windows

concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
threadpool

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

oltp_ro-windows

OLTP_RW

OLTP_RW facebook-maria1

concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
threadpool

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

oltp_rw-facebook-maria1

OLTP_RW pitbull

concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
threadpool

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

oltp_rw-pitbull

OLTP_RW Windows

concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
threadpool

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

oltp_rw-windows

POINT_SELECT

POINT_SELECT facebook-maria1

concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
threadpool

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

point_select-facebook-maria1

POINT_SELECT pitbull

concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
threadpool

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

point_select-pitbull

POINT_SELECT Windows

concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
threadpool

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

point_select-windows

UPDATE_NOKEY

UPDATE_NOKEY facebook-maria1

concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
threadpool

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

update_nokey-facebook-maria1

UPDATE_NOKEY pitbull

concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
threadpool

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

update_nokey-pitbull

UPDATE_NOKEY Windows

concurrent clients
16
32
64
128
256
512
1024
2048
4096
thread per connection
threadpool

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

update_nokey-windows

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