You are here

MySQL Benchmarking

General things about benchmarking

Database Benchmark

General Benchmarking Tools

  • Apache JMeter: To test performance on static and dynamic resources (files, Servlets, Perl scripts, Java Objects, Data Bases and Queries, FTP Servers and more).
  • Gatling: Gatling is a highly capable load testing tool. It is designed for ease of use, maintainability and high performance.
  • Tsung: Open-source multi-protocol distributed load testing tool.
  • Slamd Java-based Distributed Load Generation Engine application designed for stress testing and performance analysis of network-based applications.
  • Grinder A Java load testing framework that makes it easy to run a distributed test using many load injector machines.
  • mysqlslap mysqlslap is a diagnostic program designed to emulate client load for a MySQL server and to report the timing of each stage. It works as if multiple clients are accessing the server.
  • bm
  • httperf
  • apachebench (ab)

CPU Benchmarking

Disk benchmarking

# bonnie -s 2047 -S 10000

Bonnie 1.4: File './Bonnie.12330', size: 2146435072, volumes: 1
Writing with putc()...         done:  20440 kB/s  89.7 %CPU
Rewriting...                   done:  18940 kB/s  18.3 %CPU
Writing intelligently...       done:  46256 kB/s  28.8 %CPU
Reading with getc()...         done:  18771 kB/s  82.1 %CPU
Reading intelligently...       done:  32549 kB/s  17.3 %CPU

              ---Sequential Output (nosync)--- ---Sequential Input-- --Rnd Seek-
              -Per Char- --Block--- -Rewrite-- -Per Char- --Block--- --10k (03)-
Machine    MB K/sec %CPU K/sec %CPU K/sec %CPU K/sec %CPU K/sec %CPU   /sec %CPU
master 1*2047 20440 89.7 46256 28.8 18940 18.3 18771 82.1 32549 17.3  183.6  1.5

with fsync

# bonnie -s 2047 -S 10000 -y

Bonnie 1.4: File './Bonnie.12437', size: 2146435072, volumes: 1
Writing with putc()...         done:  20189 kB/s  89.1 %CPU
Rewriting...                   done:  17157 kB/s  16.8 %CPU
Writing intelligently...       done:  40116 kB/s  25.2 %CPU
Reading with getc()...         done:  19356 kB/s  84.9 %CPU
Reading intelligently...       done:  34367 kB/s  18.1 %CPU

              ---Sequential Output (sync)----- --- Sequential Input-- --Rnd Seek-
              -Per Char- --Block--- -Rewrite-- -Per Char- --Block--- --10k (03)-
Machine    MB K/sec %CPU K/sec %CPU K/sec %CPU K/sec %CPU K/sec %CPU   /sec %CPU
master 1*2047 20189 89.1 40116 25.2 17157 16.8 19356 84.9 34367 18.1  182.2  1.8

with o_direct

# bonnie -s 2047 -S 10000 -o_direct

Bonnie 1.4: File './Bonnie.12507', size: 2146435072, volumes: 1
Using O_DIRECT for block based I/O
Writing with putc()...         done:  19610 kB/s  86.1 %CPU
Rewriting...                   done:  15500 kB/s   4.2 %CPU
Writing intelligently...       done:  31387 kB/s  13.5 %CPU
Reading with getc()...         done:  13470 kB/s  61.1 %CPU
Reading intelligently...       done:  35937 kB/s   4.8 %CPU

              ---Sequential Output (nosync)--- ---Sequential Input-- --Rnd Seek-
              -Per Char- -DIOBlock- -DRewrite- -Per Char- -DIOBlock- --10k (03)-
Machine    MB K/sec %CPU K/sec %CPU K/sec %CPU K/sec %CPU K/sec %CPU   /sec %CPU
master 1*2047 19610 86.1 31387 13.5 15500  4.2 13470 61.1 35937  4.8  109.0  1.2

Bonnie++:

# bonnie++ -s 4096 -r 1024

Version 1.01d       ------Sequential Output------ -- Sequential Input- --Random-
                    -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine        Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP  /sec %CP
master           4G 35081  66 37958  28 16755   9 30270  47 36248  14 113.5   0
                    ------Sequential Create------ --------Random Create--------
                    -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
              files  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP
                 16 13694  97 +++++ +++ 10274  88 13566  95 +++++ +++ 10679  95

Dell (Dual Xeons 5130 @ 2.00GHz, 8Gb RAM and 4 x 15k rpm SCSI disks in RAID 0):

# bonnie++ -d /tmp/ -s 16000 -u root

Version  1.03       ------Sequential Output-------- --Sequential Input-- --Random-
                    -Per Chr- --Block--- -Rewrite-- -Per Chr- --Block--- --Seeks--
Machine        Size K/sec %CP K/sec  %CP K/sec  %CP K/sec %CP K/sec  %CP  /sec %CP
server       16000M 58912  98 223914  56 100107  18 54365  79 230161  16 732.5   1
                    ------Sequential Create------ --------Random Create--------
                    -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
              files  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP
                 16 +++++ +++ +++++ +++ +++++ +++ +++++ +++ +++++ +++ +++++ +++

1 Gbyte RAM, 7200 rpm laptop disk:

# bonnie++ -s 4096 -u root

Version  1.03       ------Sequential Output------ --Sequential Input- --Random-
                    -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine        Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP
laptop           4G 20580  60 22596  10 10202   5 21170  63 23001   6 75.3   0
                    ------Sequential Create------ --------Random Create--------
                    -Create-- --Read--- -Delete-- -Create-- --Read--- --Delete-
              files  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP
                 16 27004  90 +++++ +++ 23060  84 26172  94 +++++ +++ 25648  99

RAID-0: 2 disks, 2 x 2 Intel(R) Xeon(TM) CPU 3.00GHz, Cache size: 2048 kbyte, 4 Gbyte RAM:

# bonnie++ -s 8000 -u root
Version  1.03       ------Sequential Output------ --Sequential Input-- --Random-
                    -Per Chr- --Block-- -Rewrite- -Per Chr- --Block--- --Seeks--
Machine        Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec  %CP /sec  %CP
mysql14       8000M 45832  92 91442  32 46449  12 33945  61 101364  11 685.0   1
                    ------Sequential Create------ --------Random Create--------
                    -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
              files  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP /sec  %CP
                 16 21528  69 +++++ +++ +++++ +++ 31245  99 +++++ +++ +++++ +++

2 x 2 Intel(R) Xeon(TM) CPU 2.80GHz, 2 Gbyte RAM
On EMC mounted by NFS (15k rpm SCSI disks):

# bonnie++ -d /var/lib/mysql/test/ -s 8000 -u root

Version  1.03       ------Sequential Output------ --Sequential Input- --Random-
                    -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine        Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec  %CP
ws6.int.euroc 8000M 38366  92 42475   8  5066  87 37715  90 55064   7 267.3   1
                    ------Sequential Create------ --------Random Create--------
                    -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
              files  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP /sec %CP
                 16   415   4   426  88   375   3   444   4  1883   6  401   3

Intel(R) Core(TM) i7 CPU 870, 2.93GHz, Cache size: 8192 KB, 4 core (x 2 threads), 16 GB RAM
Disk: WD5002AALX-0, 500GB, 7200 RPM, 32MB Cache, SATA 6.0Gb/s

# bonnie++ -d /tmp -u mysql

Version  1.96       ------Sequential Output------ --Sequential Input- --Random-
Concurrency   1     -Per Chr- ---Block-- -Rewrite- -Per Chr- --Block--- --Seeks--
Machine        Size K/sec %CP  K/sec %CP K/sec %CP K/sec %CP  K/sec %CP  /sec %CP
master       32088M   369  98 116667  11 51319   6  3382  75 141798   6 254.0   4
Latency             22466us      846ms    6101ms     120ms      385ms    2023ms
Version  1.96       ------Sequential Create------ --------Random Create--------
master              -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
              files  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP
                 16 22596  28 +++++ +++ +++++ +++ +++++ +++ +++++ +++ +++++ +++
Latency             46649us     394us     425us     426us      21us      37us

Intel(R) Core(TM) i7 CPU 870, 2.93GHz, Cache size: 8192 KB, 4 core (x 2 threads), 16 GB RAM
Disk: KINGSTON SNV425S 64 Gbyte SSD-Drive (NAND Flash ), Sequential Speed: up to 200MB/s read; 110MB/s write, Life expectancy: 1 mio hours MTBF

# bonnie++ -d /data -u mysql

Version  1.96       -------Sequential Output------- --Sequential Input- --Random-
Concurrency   1     -Per Chr- ---Block--- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine        Size K/sec %CP  K/sec  %CP K/sec %CP K/sec %CP K/sec %CP  /sec %CP
master       32088M  1539  97 152173    9 31653   3  4098  98 291278  15  2466  25
Latency              6576us     1247ms     1681ms    2637us    5306us    1048ms
Version  1.96       ------Sequential Create------ --------Random Create--------
master              -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
              files  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP
                 16   465   2 +++++ +++   451   2   498   2 +++++ +++   543   2
Latency               715ms     128us    1048ms     625ms       3us     653ms

Intel(R) Core(TM) i7 CPU M 620, 2.67GHz, Cache size: 4096 KB, 2 core (x 2 threads), 8 GB RAM
Disk: Seagate ST9500420AS, 500 Gbyte, SATA 3Gb/s, Cache: 16MB, 7200 RPM

# sudo bonnie++ -u mysql

Version  1.96       ------Sequential Output------ --Sequential Input- --Random-
Concurrency   1     -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine        Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP  /sec %CP
laptop3      15728M   732  92 80287   8 29355   4  1664  39 88391   6 211.1   3
Latency             25187us    1165ms    1784ms     206ms     280ms     868ms
Version  1.96       ------Sequential Create------ --------Random Create--------
laptop3             -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
              files  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP
                 16 10488  14 +++++ +++ 14688  15 27165  38 +++++ +++ +++++ +++
Latency               799us     947us   44528us     437us      42us      55us

Network Benchmarking

Minimal packet size is 84 byte/packet. This happens with the a payload (frame size) of 46 bytes. Max payload is 1500 byte which results in a packet size of 1538 byte.

Speedbits/sbyte/smaximum PPS (f/s)minimum PPS (f/s)
10 Mbps10'000'0001'250'00014'881813
100 Mbps100'000'00012'500'000148'8108'127
1 Gbps1'000'000'000125'000'0001'488'09581'274
10 Gbps10'000'000'0001'250'000'00014'880'952812'744
100 Gbps100'000'000'00012'500'000'000148'809'5248'127'438

Literature:

  1. How many Packets per Second per port are needed to achieve Wire-Speed?
  2. Bandwidth, Packets Per Second, and Other Network Performance Metrics

Tools