CentOS 6.9 64bit にて確認

準備

yum install -y perl-DBD-MySQL

結果 (InnoDB) small-test

benchmark.sh(InnoDB)
#!/bin/sh
set -eux

export PATH=/opt/mysql/bin:$PATH
cd /opt/mysql/sql-bench/

time sudo ./run-all-tests  \
 --create-options=TYPE=InnoDB \
 --small-test \
 --user=root  \
 --socket=/var/run/mysqld/mysqld.sock2  \
 --log
TOTALS CPU 備考
2m6.377s Pentium G4560 @ 3.50GHz drbd protocol B,NIC:1GBps
2m13.032s Pentium G4560 @ 3.50GHz drbd protocol C,NIC:1GBps
6m0.559s Xeon L5630 @ 2.13GHz drbd 非同期(1ノード)
25m23.866s Core i3-3220T @ 2.80GHz kvm環境
InnoDB(L5630,非同期)
alter-table: Total time: 12 wallclock secs ( 0.02 usr  0.02 sys +  0.00 cusr  0.00 csys =  0.04 CPU)
ATIS: Total time: 64 wallclock secs ( 0.69 usr  0.27 sys +  0.00 cusr  0.00 csys =  0.96 CPU)
big-tables: Total time:  1 wallclock secs ( 0.05 usr  0.01 sys +  0.00 cusr  0.00 csys =  0.06 CPU)
connect: Total time:  4 wallclock secs ( 0.25 usr  0.15 sys +  0.00 cusr  0.00 csys =  0.40 CPU)
create: Total time:  7 wallclock secs ( 0.01 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.01 CPU)
insert: Total time: 53 wallclock secs ( 1.14 usr  0.40 sys +  0.00 cusr  0.00 csys =  1.54 CPU)
select: Total time: 14 wallclock secs ( 2.02 usr  0.48 sys +  0.00 cusr  0.00 csys =  2.50 CPU)
transactions: Total time:  3 wallclock secs ( 0.04 usr  0.06 sys +  0.00 cusr  0.00 csys =  0.10 CPU)
wisconsin: Total time: 196 wallclock secs ( 1.34 usr  0.81 sys +  0.00 cusr  0.00 csys =  2.15 CPU)

All 9 test executed successfully
xeonより高速な方(wisconsinは中断)
alter-table: Total time:  2 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)
ATIS: Total time:  8 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)
big-tables: Total time:  0 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)
connect: Total time:  3 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)
create: Total time:  1 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)
insert: Total time: 10 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)
select: Total time:  8 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)
transactions: Total time:  0 wallclock secs ( 0.00 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.00 CPU)
wisconsin: Failed (output/wisconsin-mysql-Linux_2.6.18_419.el5_i686)

Of 9 tests, 1 tests didn't work

結果 (InnoDB)

benchmark.sh(InnoDB)
#!/bin/sh
set -eux

export PATH=/opt/mysql/bin:$PATH
cd /opt/mysql/sql-bench/

time sudo ./run-all-tests  \
 --create-options=TYPE=InnoDB \
 --user=root  \
 --socket=/var/run/mysqld/mysqld.sock2  \
 --log
TOTALS CPU 備考
28m55.946s Pentium G4560 @ 3.50GHz drbd 非同期(1ノード)
40m43.704s Pentium G4560 @ 3.50GHz drbd protocol A,NIC:1GBps
47m19.510s Pentium G4560 @ 3.50GHz drbd protocol B,NIC:1GBps
51m9.603s Pentium G4560 @ 3.50GHz drbd protocol C,NIC:1GBps
InnoDB(G4560,非同期)
alter-table: Total time: 31 wallclock secs ( 0.06 usr  0.01 sys +  0.00 cusr  0.00 csys =  0.07 CPU)
ATIS: Total time: 14 wallclock secs ( 1.73 usr  0.02 sys +  0.00 cusr  0.00 csys =  1.75 CPU)
big-tables: Total time:  3 wallclock secs ( 0.57 usr  0.03 sys +  0.00 cusr  0.00 csys =  0.60 CPU)
connect: Total time: 21 wallclock secs ( 8.56 usr  2.49 sys +  0.00 cusr  0.00 csys = 11.05 CPU)
create: Total time: 171 wallclock secs ( 1.40 usr  0.22 sys +  0.00 cusr  0.00 csys =  1.62 CPU)
insert: Total time: 1375 wallclock secs (110.94 usr  5.40 sys +  0.00 cusr  0.00 csys = 116.34 CPU)
select: Total time: 32 wallclock secs ( 7.16 usr  0.39 sys +  0.00 cusr  0.00 csys =  7.55 CPU)
transactions: Total time: 43 wallclock secs ( 1.89 usr  0.35 sys +  0.00 cusr  0.00 csys =  2.24 CPU)
wisconsin: Total time: 41 wallclock secs ( 2.22 usr  0.12 sys +  0.00 cusr  0.00 csys =  2.34 CPU)

All 9 test executed successfully

Totals per operation:
Operation             seconds     usr     sys     cpu   tests
()
TOTALS                              1737.00  133.10    9.01  142.11 3286250
InnoDB(G4560,drbd,A)
alter-table: Total time: 48 wallclock secs ( 0.02 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.02 CPU)
ATIS: Total time: 24 wallclock secs ( 1.53 usr  0.05 sys +  0.00 cusr  0.00 csys =  1.58 CPU)
big-tables: Total time:  6 wallclock secs ( 0.54 usr  0.04 sys +  0.00 cusr  0.00 csys =  0.58 CPU)
connect: Total time: 21 wallclock secs ( 7.70 usr  3.36 sys +  0.00 cusr  0.00 csys = 11.06 CPU)
create: Total time: 241 wallclock secs ( 1.57 usr  0.33 sys +  0.00 cusr  0.00 csys =  1.90 CPU)
insert: Total time: 1932 wallclock secs (93.09 usr  6.79 sys +  0.00 cusr  0.00 csys = 99.88 CPU)
select: Total time: 39 wallclock secs ( 6.91 usr  0.39 sys +  0.00 cusr  0.00 csys =  7.30 CPU)
transactions: Total time: 66 wallclock secs ( 1.31 usr  0.34 sys +  0.00 cusr  0.00 csys =  1.65 CPU)
wisconsin: Total time: 67 wallclock secs ( 1.48 usr  0.16 sys +  0.00 cusr  0.00 csys =  1.64 CPU)

All 9 test executed successfully

Totals per operation:
Operation             seconds     usr     sys     cpu   tests
()
TOTALS                              2446.00  112.73   11.44  124.17 3286250
InnoDB(G4560,drbd,B)
alter-table: Total time: 49 wallclock secs ( 0.04 usr  0.00 sys +  0.00 cusr  0.00 csys =  0.04 CPU)
ATIS: Total time: 28 wallclock secs ( 1.53 usr  0.04 sys +  0.00 cusr  0.00 csys =  1.57 CPU)
big-tables: Total time:  9 wallclock secs ( 0.56 usr  0.04 sys +  0.00 cusr  0.00 csys =  0.60 CPU)
connect: Total time: 22 wallclock secs ( 5.86 usr  5.09 sys +  0.00 cusr  0.00 csys = 10.95 CPU)
create: Total time: 255 wallclock secs ( 1.53 usr  0.32 sys +  0.00 cusr  0.00 csys =  1.85 CPU)
insert: Total time: 2272 wallclock secs (95.03 usr  6.77 sys +  0.00 cusr  0.00 csys = 101.80 CPU)
select: Total time: 45 wallclock secs ( 6.96 usr  0.35 sys +  0.00 cusr  0.00 csys =  7.31 CPU)
transactions: Total time: 83 wallclock secs ( 1.07 usr  0.61 sys +  0.00 cusr  0.00 csys =  1.68 CPU)
wisconsin: Total time: 77 wallclock secs ( 1.57 usr  0.15 sys +  0.00 cusr  0.00 csys =  1.72 CPU)

All 9 test executed successfully

Totals per operation:
Operation             seconds     usr     sys     cpu   tests
()
TOTALS                              2842.00  112.71   13.37  126.08 3286250
InnoDB(G4560,drbd,C)
alter-table: Total time: 36 wallclock secs ( 0.04 usr  0.01 sys +  0.00 cusr  0.00 csys =  0.05 CPU)
ATIS: Total time: 31 wallclock secs ( 1.53 usr  0.03 sys +  0.00 cusr  0.00 csys =  1.56 CPU)
big-tables: Total time:  9 wallclock secs ( 0.49 usr  0.05 sys +  0.00 cusr  0.00 csys =  0.54 CPU)
connect: Total time: 21 wallclock secs ( 8.37 usr  2.60 sys +  0.00 cusr  0.00 csys = 10.97 CPU)
create: Total time: 322 wallclock secs ( 1.45 usr  0.36 sys +  0.00 cusr  0.00 csys =  1.81 CPU)
insert: Total time: 2424 wallclock secs (94.31 usr  6.68 sys +  0.00 cusr  0.00 csys = 100.99 CPU)
select: Total time: 53 wallclock secs ( 7.20 usr  0.40 sys +  0.00 cusr  0.00 csys =  7.60 CPU)
transactions: Total time: 88 wallclock secs ( 1.25 usr  0.39 sys +  0.00 cusr  0.00 csys =  1.64 CPU)
wisconsin: Total time: 86 wallclock secs ( 1.49 usr  0.16 sys +  0.00 cusr  0.00 csys =  1.65 CPU)

All 9 test executed successfully

Totals per operation:
Operation             seconds     usr     sys     cpu   tests
()
TOTALS                              3081.00  114.72   10.67  125.39 3286250

結果 (MyISAM)

benchmark.sh(MyISAMの場合)
#!/bin/sh
set -eux

export PATH=/opt/mysql/bin:$PATH
cd /opt/mysql/sql-bench/

time sudo ./run-all-tests  \
 --user=root  \
 --socket=/var/run/mysqld/mysqld.sock2  \
 --log
# --password=hogehoge
TOTALS(秒) CPU 備考
359 Pentium G4560 @ 3.50GHz drbd 非同期(1ノード)
376 Pentium G4560 @ 3.50GHz drbd protocol A,NIC:1GBps
380 Pentium G4560 @ 3.50GHz drbd protocol C,NIC:1GBps
385 Pentium G4560 @ 3.50GHz drbd protocol C,NIC:1GBps
1095 Core i3-3220T @ 2.80GHz kvm環境
1188 Core i3-3220T @ 2.80GHz kvm環境
317 Pentium N4200 @ 1.10GHz kvm環境 2 tests didn't work

drbd protocol C,NIC:1GBps を複数回実行してみた

  • 6m29.237s
  • 6m30.119s
  • 7m12.853s

Pentium G4560 @ 3.50GHz

MyISAM
alter-table: Total time: 11 wallclock secs ( 0.07 usr  0.01 sys +  0.00 cusr  0.00 csys =  0.08 CPU)
ATIS: Total time:  1 wallclock secs ( 1.26 usr  0.04 sys +  0.00 cusr  0.00 csys =  1.30 CPU)
big-tables: Total time:  1 wallclock secs ( 0.49 usr  0.03 sys +  0.00 cusr  0.00 csys =  0.52 CPU)
connect: Total time: 15 wallclock secs ( 5.99 usr  1.51 sys +  0.00 cusr  0.00 csys =  7.50 CPU)
create: Total time: 71 wallclock secs ( 0.86 usr  0.25 sys +  0.00 cusr  0.00 csys =  1.11 CPU)
insert: Total time: 266 wallclock secs (81.94 usr  5.16 sys +  0.00 cusr  0.00 csys = 87.10 CPU)
select: Total time: 12 wallclock secs ( 6.89 usr  0.34 sys +  0.00 cusr  0.00 csys =  7.23 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time:  2 wallclock secs ( 0.92 usr  0.08 sys +  0.00 cusr  0.00 csys =  1.00 CPU)

All 9 test executed successfully

Totals per operation:
Operation             seconds     usr     sys     cpu   tests
(略)
TOTALS                               380.00   97.05    7.41  104.46 2901541 

Core i3-3220T @ 2.80GHz (kvm)

MyISAM
alter-table: Total time:  9 wallclock secs ( 0.03 usr  0.01 sys +  0.00 cusr  0.00 csys =  0.04 CPU)
ATIS: Total time:  3 wallclock secs ( 2.04 usr  0.07 sys +  0.00 cusr  0.00 csys =  2.11 CPU)
big-tables: Total time:  1 wallclock secs ( 0.81 usr  0.07 sys +  0.00 cusr  0.00 csys =  0.88 CPU)
connect: Total time: 41 wallclock secs (14.53 usr  3.48 sys +  0.00 cusr  0.00 csys = 18.01 CPU)
create: Total time: 825 wallclock secs ( 2.69 usr  0.50 sys +  0.00 cusr  0.00 csys =  3.19 CPU)
insert: Total time: 282 wallclock secs (119.49 usr 12.42 sys +  0.00 cusr  0.00 csys = 131.91 CPU)
select: Total time: 22 wallclock secs (11.45 usr  0.92 sys +  0.00 cusr  0.00 csys = 12.37 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time:  3 wallclock secs ( 1.17 usr  0.22 sys +  0.00 cusr  0.00 csys =  1.39 CPU)

All 9 test executed successfully

Totals per operation:
Operation             seconds     usr     sys     cpu   tests
(略)
TOTALS                              1188.00  150.01   17.67  167.68 3225950

Pentium N4200 @ 1.10GHz

MyISAM
alter-table: Total time: 39 wallclock secs ( 0.07 usr  0.01 sys +  0.00 cusr  0.00 csys =  0.08 CPU)
ATIS: Total time: 21 wallclock secs ( 4.19 usr  0.19 sys +  0.00 cusr  0.00 csys =  4.38 CPU)
big-tables: Total time:  5 wallclock secs ( 1.75 usr  0.28 sys +  0.00 cusr  0.00 csys =  2.03 CPU)
connect: Total time: 80 wallclock secs (32.42 usr  8.32 sys +  0.00 cusr  0.00 csys = 40.74 CPU)
create: Warning: Can't execute create.  Check the file 'output/create-mysql-Linux_2.6.32_642.13.1.el6.x86_64_x86_64'
Failed (output/create-mysql-Linux_2.6.32_642.13.1.el6.x86_64_x86_64)
insert: Warning: Can't execute insert.  Check the file 'output/insert-mysql-Linux_2.6.32_642.13.1.el6.x86_64_x86_64'
Failed (output/insert-mysql-Linux_2.6.32_642.13.1.el6.x86_64_x86_64)
select: Total time: 170 wallclock secs (25.92 usr  2.25 sys +  0.00 cusr  0.00 csys = 28.17 CPU)
transactions: Test skipped because the database doesn't support transactions
wisconsin: Total time:  4 wallclock secs ( 1.73 usr  0.41 sys +  0.00 cusr  0.00 csys =  2.14 CPU)

Of 9 tests, 2 tests didn't work

Totals per operation:
Operation             seconds     usr     sys     cpu   tests
()
TOTALS                               317.00   66.04   11.45   77.49  847029 

ubuntu 17.10では動かなかった

  • libdbd-mysql-perlがmysql4.0に対応していないため。
wget https://github.com/tukiyo/mysql4.0php5.2/releases/download/ubuntu1704/opt-mysql4_4.0.30-1_amd64.17.04.deb

sudo dpkg -i opt-mysql4_4.0.30-1_amd64.17.04.deb

cd /opt/mysql/bin
$ sudo ./mysql_install_db
Preparing db table
Preparing host table
Preparing user table
Preparing func table
Preparing tables_priv table
Preparing columns_priv table
Installing all prepared tables
180113 16:33:20 /opt/mysql/libexec/mysqld: Shutdown Complete


To start mysqld at boot time you have to copy support-files/mysql.server
to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/opt/mysql/bin/mysqladmin -u root password 'new-password'
/opt/mysql/bin/mysqladmin -u root -h nx1 password 'new-password'
See the manual for more instructions.

You can start the MySQL daemon with:
cd /opt/mysql ; /opt/mysql/bin/mysqld_safe &

You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:
cd sql-bench ; perl run-all-tests

Please report any problems with the /opt/mysql/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
mysqld起動
chown -R mysql:mysql /opt/mysql/var/

cd /opt/mysql
sudo /opt/mysql/bin/mysqld_safe &
perlでmysqlに接続できるように
sudo apt install -y libdbd-mysql-perl
ベンチマーク測定
cd /opt/mysql/sql-bench
エラー
sudo ./run-all-tests \
 --user=root \
 --socket=/tmp/mysql.sock

Got error: 'Malformed packet' when connecting to DBI:mysql:database=test;host=localhost;mysql_socket=/tmp/mysql.sock with user: 'root' password: ''

libdbd-mysql-perlがmysql 4.0に対応していない様子。
自前でmysql4.0のライブラリを指定してリビルドすれば動くと思う。

Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account log in.