MySQLで論理削除と一意性制約の両立

追記@2015-03-25

素直にMySQLのバックアップを取っていれば(さらに消すときにログを残しておけば)、特に論理削除せず普通に削除すればいい気もしてきました。いろいろと見通しがよくなりますし。とは言えTwitterみたいにアカウント削除しても30日は復活できる、みたいなことをしたい場合必要だと思います。要件次第ですね。


論理削除にはいろいろなメリットがあります。行削除のように関連する他テーブルへ削除が波及しないこと、エントリ復活ができること、障害時にデータ変更の経緯を追いやすくなることなどなど。

ところが論理削除の方針でDBを組んでいて困ったことはありませんか?
「 メールアドレスは一意性(UNIQUE)制約をかけたいのに、それだと削除済みのユーザと同じメールアドレスが使えないことになる 」

論理削除と一意性制約、両立はできないのか?
できないと思っている方、多いと思います。実はちゃんとできます。DB製品によって実現方法がちょっと違ってくるだけで。

論理削除と一意性制約を両立させる方法・DB製品別


↑を読んでなるほどと思ったのですが、MySQLでの実現方法が載っていなかったので、書いてみました。MySQLではCHECK制約が無視されますのでどうしようかなと思ったのですが、要はstatusの値も含めて一意かどうか判別すれば良さそうです。

今回行ったことは、

– 二つのカラム(usernameとemailを想定します)に対する一意性制約と論理削除の両立@MySQL

です。

ともかくやってみます。MySQLのコンソールに入って作業することを想定しています。「#」はコメントです。

# 以下の様なテーブルを作成します
> CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `username` varchar(19) NOT NULL,
  `state` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_state` (`email`,`state`),
  UNIQUE KEY `username_state` (`username`,`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 3つほど値をいれてみます
 > insert into users (email, username) values ("foo@example.com", "foo"),("bar@example.com", "bar"),("fizz@example.com", "fizz");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

# 状態を確認してみます
> select * from users;
+----+------------------+----------+-------+
| id | email            | username | state |
+----+------------------+----------+-------+
|  1 | foo@example.com  | foo      |     0 |
|  2 | bar@example.com  | bar      |     0 |
|  3 | fizz@example.com | fizz     |     0 |
+----+------------------+----------+-------+
3 rows in set (0.00 sec)

# emailとusernameの一意性が保たれているか確かめてみます
# emailが同じだったりusernameが同じだったりするものを入れてみます

# 同じものをいれてみる→一意性制約によりエラー
> insert into users (email, username) values ("foo@example.com", "foo");
ERROR 1062 (23000): Duplicate entry 'foo@example.com-0' for key 'email_state'

# emailが異なり、usernameが同じものを入れてみる→一意性制約によりエラー 
> insert into users (email, username) values ("foo1@example.com", "foo");
ERROR 1062 (23000): Duplicate entry 'foo-0' for key 'username_state'

# emailは同じで、usernameが異なるものを入れてみる→一意性制約によりエラー
> insert into users (email, username) values ("foo@example.com", "foo1");
ERROR 1062 (23000): Duplicate entry 'foo@example.com-0' for key 'email_state'

# emailは同じで、usernameを大文字にしてみる→一意性制約によりエラー
# (これはいわゆるケースセンシティブでない例です。Collation: utf8_general_ciと
# 設定しておく必要があると思います)
> insert into users (email, username) values ("foo@example.com", "FOO");
ERROR 1062 (23000): Duplicate entry 'foo@example.com-0' for key 'email_state'

# ↑上記のように、一意性制約がきちんと働いていることが分かりました。

# 次に、論理削除をしてみます。id:3を論理削除します。
> update users set state = -1 where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# ↓のようにstateが-1となっています
> select * from users;
+----+------------------+----------+-------+
| id | email            | username | state |
+----+------------------+----------+-------+
|  1 | foo@example.com  | foo      |     0 |
|  2 | bar@example.com  | bar      |     0 |
|  3 | fizz@example.com | fizz     |    -1 |
+----+------------------+----------+-------+
3 rows in set (0.00 sec)

# さて、この状態において、emailのfizz@example.comや、usernameのfizzは、
# 論理削除されているので、同じものが追加されても受け付けるはずです。

# まったく同じものを挿入してみました。無事、挿入できました。
> insert into users (email, username) values ("fizz@example.com", "fizz");
Query OK, 1 row affected (0.00 sec)

# 状態を確認すると↓のようになっています。
# 一件、fizz@example.comとfizzが二つあっておかしな感じですが、
# あくまで一意性制約はemail+state, username+stateに掛かっているので、
# 正常です。
# 蛇足ですが、新しいレコードのidが8となっているのは、
# MySQLのトランザクションとロールバックの関係のようです。
# 参考: http://stackoverflow.com/questions/2787910/why-does-mysql-autoincrement-increase-on-failed-inserts
 > select * from users;
+----+------------------+----------+-------+
| id | email            | username | state |
+----+------------------+----------+-------+
|  1 | foo@example.com  | foo      |     0 |
|  2 | bar@example.com  | bar      |     0 |
|  3 | fizz@example.com | fizz     |    -1 |
|  8 | fizz@example.com | fizz     |     0 |
+----+------------------+----------+-------+
4 rows in set (0.00 sec)

# 検索するときは、stateの条件も付与する必要があります

# OKな例↓
select * from users where username like "fizz" and state = 0;
+----+------------------+----------+-------+
| id | email            | username | state |
+----+------------------+----------+-------+
|  8 | fizz@example.com | fizz     |     0 |
+----+------------------+----------+-------+
1 row in set (0.02 sec)

# だめな例↓(2つのレコードがでてくる)
> select * from users where username like "fizz";
+----+------------------+----------+-------+
| id | email            | username | state |
+----+------------------+----------+-------+
|  3 | fizz@example.com | fizz     |    -1 |
|  8 | fizz@example.com | fizz     |     0 |
+----+------------------+----------+-------+
2 rows in set (0.00 sec)

というわけで無事、MySQLでもできました。

MySQLを特定のディレクトリ以下で使う

あるディレクトリ以下にすべてのデータを書いて欲しい、そんな感じのMySQLの使い方を書いてみました。Redisぽくカジュアルに使うようことを想定しています。また、こうすることで様々なバージョンのMySQLを一つのマシンで共存させることができるようになります。MariaDBでも以下の設定でおそらく動くと思います。

想定環境はUbuntuですがLinuxでもMacでも似たような感じです。

まずhttp://dev.mysql.com/downloads/mysql/←ここからMySQLのバイナリをダウンロードします。

以下、作業手順です(↑でmysql-5.6.23-linux-glibc2.5-x86_64.tar.gzをダウンロードしてきたとします)。操作ユーザーは、vncuserという名前とします(これはこのマシンのユーザーであり、MySQLのユーザーではありません)。

# libaioが必要です。Debian系ならapt、Cent系ならyumでお願いします。
$ aptitude install libaio-dev -y 
$ tar zxvf mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
$ cd mysql-5.6.23-linux-glibc2.5-x86_64/
$ ./scripts/mysql_install_db --defaults-file=./support-files/my-default.cnf --datadir=./data/

# my.cnfを作成します (文字コードはひとまずutf-8にしておきました。show variables like "char%";として、filesytemがbinary、character_sets_dirがどこかのディレクトリ、それ以外がutf-8となっていたらOKです)
# 注意点として、絶対パスで指定してやる必要があります。
# 相対パスだと、おそらくデフォルトの設定が適用されてよく分からないことになります。
$ vi my.cnf
$ cat my.cnf
[client]
default-character-set=utf8

[mysqld_safe]
ledir = /home/vncuser/Downloads/mysql-5.6.23-linux-glibc2.5-x86_64/bin
 
[mysqld]
port = 13306
socket = /tmp/mysql3306.sock
basedir = /home/vncuser/Downloads/mysql-5.6.23-linux-glibc2.5-x86_64
datadir = /home/vncuser/Downloads/mysql-5.6.23-linux-glibc2.5-x86_64/data
tmpdir = /tmp
pid-file = /tmp/mysql3306.pid

skip-character-set-client-handshake
character-set-server = utf8
collation-server = utf8_general_ci
init-connect = SET NAMES utf8

[mysqldump]
default-character-set=utf8

[mysql]
default-character-set=utf8

# 起動・シャットダウン・ログインのスクリプトを書きます。
$ vi run.sh
$ cat run.sh
echo '*** How to stop MySQL ***'
echo ''
echo 'Ctrl + C is not valid here!!!'
echo ''
echo 'You should do:'
echo '$ ./stop.sh'
echo ''
echo '*****************************'
 
./bin/mysqld_safe --defaults-file=./my.cnf

$ echo './bin/mysql -h127.0.0.1 -P13306 -uroot' > login.sh
$ echo './bin/mysqladmin -h127.0.0.1 -P13306 -uroot shutdown' > stop.sh
$ chmod +x run.sh login.sh stop.sh 

さて、これでOKのはずです。動かしてみると以下のようになります。

– 起動
$ ./run.sh 
*** How to stop MySQL ***

Ctrl + C is not valid here!!!

You should do:
$ ./stop.sh

*****************************
150305 04:07:03 mysqld_safe Logging to '/home/vncuser/Downloads/mysql-5.6.23-linux-glibc2.5-x86_64/data/atom.err'.
150305 04:07:03 mysqld_safe Starting mysqld daemon with databases from /home/vncuser/Downloads/mysql-5.6.23-linux-glibc2.5-x86_64/data

– ログイン
$ ./login.sh 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 


– 停止
$ ./stop

これで、特定のディレクトリ以下にすべてのデータが書かれるようになり、他の領域を汚さずに済みます。

参考: http://mysql-nordic.blogspot.jp/2015/02/creating-minimal-mysql-installation-for.html

MySQLでテストデータを流し込む

.sqlファイルを作ってそれをMySQLに流し込んでみます。想定環境はdebianです。

# example.sql は後述
$ sudo mysql -u root -p < example.sql

以上です。以下、example.sqlと確認方法について述べています。

example.sql
# please do like this: $ sudo mysql -u root -p < example.sql

# it's just misc
SET sql_log_bin=0;
SET NAMES 'utf8';

CREATE DATABASE IF NOT EXISTS testdb CHARACTER SET utf8;

USE testdb;

CREATE TABLE characters (
	id INTEGER NOT NULL AUTO_INCREMENT,
	name VARCHAR(128) NOT NULL,
	primary key(id)
) ENGINE=InnoDB default character set utf8;

INSERT INTO characters (name) VALUES
('PAC-MAN'),
('IKE'),
('PALUTENA'),
('CHARIZARD'),
('Mii FIGHTERS'),
('YOSHI'),
('GRENINJA'),
('SHEIK'),
('ZERO SUIT SAMUS'),
('DIDDY KONG'),
('LUCARIO'),
('KING DEDEDE'),
('LITTLE MAC'),
('ZELDA'),
('ROSALINA & LUMA'),
('MARTH'),
('SONIC'),
('TOON LINK'),
('PEACH'),
('LUIGI'),
('PIKMIN & OLIMAR'),
('VILLAGER'),
('MARIO'),
('DONKEY KONG'),
('LINK'),
('SAMUS'),
('MEGA MAN'),
('KIRBY'),
('Wii Fit TRAINER'),
('FOX'),
('PIKACHU'),
('BOWSER'),
('PIT');

確認するには以下のようにします。
$ sudo mysql -uroot -p

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
+--------------------+
4 rows in set (0.00 sec)

mysql> use testdb
Database changed

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| characters       |
+------------------+
1 row in set (0.00 sec)

mysql> desc characters;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(128) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from characters;
+----+-----------------+
| id | name            |
+----+-----------------+
|  1 | PAC-MAN         |
|  2 | IKE             |
|  3 | PALUTENA        |
|  4 | CHARIZARD       |
|  5 | Mii FIGHTERS    |
|  6 | YOSHI           |
|  7 | GRENINJA        |
|  8 | SHEIK           |
|  9 | ZERO SUIT SAMUS |
| 10 | DIDDY KONG      |
| 11 | LUCARIO         |
| 12 | KING DEDEDE     |
| 13 | LITTLE MAC      |
| 14 | ZELDA           |
| 15 | ROSALINA & LUMA |
| 16 | MARTH           |
| 17 | SONIC           |
| 18 | TOON LINK       |
| 19 | PEACH           |
| 20 | LUIGI           |
| 21 | PIKMIN & OLIMAR |
| 22 | VILLAGER        |
| 23 | MARIO           |
| 24 | DONKEY KONG     |
| 25 | LINK            |
| 26 | SAMUS           |
| 27 | MEGA MAN        |
| 28 | KIRBY           |
| 29 | Wii Fit TRAINER |
| 30 | FOX             |
| 31 | PIKACHU         |
| 32 | BOWSER          |
| 33 | PIT             |
+----+-----------------+
33 rows in set (0.00 sec)

MySQLのベンチマーク

追記@2014-07-16
mysqlslapの他にもベンチマークソフトは様々あります。mybench: http://jeremy.zawodny.com/mysql/mybench/ もいいと思います。具体的には、自分が作ったスキーマに対してベンチをしたりでき柔軟です。mysqlslapと合わせて使うと良いかもです。
使い方は検索して下さい。


=== まとめここから ===

mysqlslapの結果まとめ

OS MySQL Version MySQL Engine CPU Storage Read (req/sec) Write (req/sec)
Debian 7.2 5.5.31 InnoDB Pentium DualCore E5200 Crucial m4 CT064M4SSD2 (64GB SSD) 27800.9 18892.8
Ubuntu 12.04.3 LTS 5.5.34 InnoDB Opteron 3280 Hitachi HDT721010SLA360 14806.0 9145.7
Ubuntu 12.04.3 LTS 5.5.34 InnoDB Celeron G540 ST500NM0011 39370.0 14359.5

やはりSSDは高速ですね。DBでは信頼性を確保するためにはどうしても不揮発性の媒体に書いておく必要があり、そのストレージで性能が決まるようです。不揮発性メモリが楽しみですね!

今回↑で使ったinsertのクエリの具体例は↓です。
INSERT INTO t1 VALUES 
(uuid(),1976594918,'xHogEXydBNC1CRXW0HkRAR8A2T2R04JxDzTKWRxai2buLyHMF1hQKicN3Wq3Q2sg1L0OhxyNrZADYRRnS0Doii3mCuhfoaJpnBDS0Bksdu59LYohYuL7cPlgBQJz0W')

ところで、Celeron G540 + ST500NM0011の組み合わせが健闘しています。読み込みはE5200 + SSDより高速ですし(メモリに全部載っていて、DRAMの速度の違いなのかもです、E5200の方はDDR2-800なので)、書き込みもHDDを使っているにも関わらずSSDの半分の速度が出ています。HDD自体の速度向上と、キャッシュによるものなのでしょうかね。

ふと思ったのですが、HDDやSSDにはDRAMのキャッシュが載っています(WD Blackですと64MBほど)。それに書き込んだ時点でOKを返すのでしょうか?だとしたらあまり信頼性が高くないような、、、という気がしてきました。その辺の議論ってどうなっているのでしょうね?確か某SSDですと、電源断が起こったときはコンデンサに貯めこんでおいた電気を使うはずです(なのでコンデンサが充電されていない状態で落ちるとデータは揮発します)。HDDだとどうなんでしょうね。まあトラブルは電源断だけではないので(OOM Killerに落とされるとか)、難しいところですが。。。

また、↑のベンチマークでは、Celeron G540でReadが3.9万req/sec、Writeが1.4万req/sec程度出ていて、早すぎないかと疑問に思いました。しかし、Amazon Relational Database Serviceによると、

データベースインスタンスごとに最大 3 TB のストレージ、30,000 IOPS をプロビジョニングできます。m2.4xlarge インスタンスで実行される 50% の書き込み、50% の読み取り作業負荷に対し、Oracle は最大 25,000 IOPS まで実現できます。cr1.8xlarge で実行している同様の作業負荷では、MySQL または PostgreSQL は最大 20,000 IOPS まで実現


とありますので、ほぼ一致と言えるでしょう。というのも、
>50% の書き込み、50% の読み取り作業負荷
を今回の私のベンチで言うと、3.9 * 0.5 + 1.4 * 0.5 = 2.65 万 [req/sec]となり、この計算結果は「最大 20,000 IOPS」と近い値だからです。

=== まとめここまで ===

MySQLのベンチマークをどのように取るかは悩ましいところだと思います。

– mysqlslapを使う
– アプリケーション側で記述してみる

などのやり方があると思います。アプリケーション側だとコンカレンシーを増やすなどがやりにくいので、基本的にはベンチマークツールを使いたいところですね。mysqlslapはデフォルトで付属しているツールなのですが、オプションが多すぎていまいち使い勝手がよくないです。。。

が、調べてみると丁寧に解説がなされたものをいくつか見つけました。

MySQL5.5と、mysqlslapについて

↑かなり参考にさせていただきました。

という訳でベンチマークを取ってみました。MySQLのコンフィグファイルの設定は山ほどあります。今回は速度と信頼性の中間ぐらいの、標準的なものにしてみました。

マシンA(ベンチマーク対象)
– DMI: System manufacturer System Product Name/P5KPL-CM, BIOS 0514 08/14/2008
– Intel Corporation 82G33/G31/P35/P31 Express DRAM Controller (rev 10)
– Intel Pentium(R) Dual-Core CPU E5200 @ 2.50GHz stepping 06
– M4-CT064M4SSD2
– Ethernet controller: Atheros Communications Inc. AR8121/AR8113/AR8114 Gigabit or Fast Ethernet (rev b0)
– Linux version 3.2.0-4-amd64 (debian-kernel@lists.debian.org) (gcc version 4.6.3 (Debian 4.6.3-14) ) #1 SMP Debian 3.2.51-1

マシンAの準備

# mysqlをインストール
$ sudo aptitude install mysql-server

# version確認
$ mysql --version
mysql  Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (x86_64) using readline 6.2

# テスト用ユーザーの作成 (username: piyo, password: piyo)
$ mysql -u root -p

mysql> GRANT ALL PRIVILEGES ON *.* TO piyo
    -> IDENTIFIED BY 'piyo' WITH GRANT OPTION;

# my.confの編集 (後述)
$ sudo vi /etc/mysql/my.cnf

# restart
$ sudo service mysql restart

注意点として↑で作ったユーザーは、ローカルホストからはアクセスできません。なので他のマシンからアクセスしてください。localhostからアクセス出来るようにするには、↓を打ち込みます。
mysql> GRANT ALL PRIVILEGES ON *.* TO piyo@'localhost' IDENTIFIED BY 'piyo' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

さて、マシンBを用意します(mysqlをインストールしておいてください)。マシンAとはGbEスイッチングハブなどでつなぎます。

マシンBからベンチマーク(mysqlslap)を実行 (同時実行数は10,実行回数は1万回で合計10万リクエスト)
# 読み込み
$ mysqlslap --no-defaults --create-schema=SLAP --auto-generate-sql --auto-generate-sql-guid-primary --engine=InnoDB --number-int-cols=1 --number-char-cols=1 --concurrency=10 --auto-generate-sql-write-number=10000 --auto-generate-sql-execute-number=10000 --auto-generate-sql-load-type=key --iterations=3 --user=piyo --password=piyo --host=192.168.1.118 --port=3306
Benchmark
	Running for engine InnoDB
	Average number of seconds to run all queries: 3.563 seconds
	Minimum number of seconds to run all queries: 3.440 seconds
	Maximum number of seconds to run all queries: 3.649 seconds
	Number of clients running queries: 10
	Average number of queries per client: 10000

# 書き込み
$ mysqlslap --no-defaults --create-schema=SLAP --auto-generate-sql --auto-generate-sql-guid-primary --engine=InnoDB --number-int-cols=1 --number-char-cols=1 --concurrency=10 --auto-generate-sql-write-number=10000 --auto-generate-sql-execute-number=10000 --auto-generate-sql-load-type=write --iterations=3 --user=piyo --password=piyo --host=192.168.1.118 --port=3306
Benchmark
	Running for engine InnoDB
	Average number of seconds to run all queries: 5.071 seconds
	Minimum number of seconds to run all queries: 4.766 seconds
	Maximum number of seconds to run all queries: 5.344 seconds
	Number of clients running queries: 10
	Average number of queries per client: 10000

ちなみに↑を実行中のマシンAの状態です。
# 読み込み時
$ dstat
You did not select any stats, using -cdngy by default.
----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq| read  writ| recv  send|  in   out | int   csw 
  9   3  87   2   0   0|  18k 2652k|   0     0 |   0     0 |1786  8163 
  0   0 100   0   0   0|   0     0 | 984B 1664B|   0     0 |  40    86 
 16   6  77   1   0   0|   0    88k| 596k  191k|   0     0 |5192  6927 
 20   9  71   1   0   0|   0  1764k| 809k  258k|   0     0 |6573  8770 
 40  12  46   1   0   2|   0  1844k|1694k 2289k|   0     0 |  17k   30k
 54  27  12   1   0   7|   0   588k|2988k 6586k|   0     0 |  31k   58k
 41  36  18   0   0   6|   0   168k|5698k   12M|   0     0 |  32k   60k
 38  37  21   0   0   5|   0   120k|3991k 8797k|   0     0 |  31k   58k
 20   5  73   2   0   1|   0   316k|1878k 2761k|   0     0 |6553  8910 
 19   4  77   1   0   0|   0  1852k| 630k  201k|   0     0 |5415  7226 
 27  10  60   2   0   2|   0  2148k| 790k  252k|   0     0 |  11k   18k
 54  28  12   0   0   6|   0  1196k|3888k 8531k|   0     0 |  32k   62k
 39  41  12   1   0   8|   0  1072k|3511k 7740k|   0     0 |  29k   53k
 38  43  13   0   0   6|   0   360k|5575k   12M|   0     0 |  30k   55k
 22  17  58   1   0   2|   0   452k|2005k 4030k|   0     0 |  13k   21k
 19   6  71   4   0   0|   0  7496k|1126k  360k|   0     0 |5898  7940 
 18   7  71   4   0   0|   0  5724k| 706k  226k|   0     0 |6081  8174 
 54  25  15   0   0   6|   0  1196k|3411k 7270k|   0     0 |  30k   58k
 45  34  15   1   0   5|   0   360k|3673k 8096k|   0     0 |  32k   58k
 36  42  18   0   0   4|   0   328k|3910k 8618k|   0     0 |  33k   61k
 20  15  64   0   0   1|   0   384k|3892k 8576k|   0     0 |  15k   28k
  0   0  99   1   0   0|   0   384k|  66B  354B|   0     0 |  67   127 
  0   1  99   1   0   0|   0   992k| 132B  468B|   0     0 | 103   208 
  1   0  95   4   0   0|   0  5192k| 132B  468B|   0     0 | 157   329 

# 書き込み時
$ dstat
You did not select any stats, using -cdngy by default.
----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq| read  writ| recv  send|  in   out | int   csw 
  9   3  86   2   0   0|  18k 2643k|   0     0 |   0     0 |1806  8176 
  0   0 100   0   0   0|   0     0 | 444B 1322B|   0     0 |  38    87 
 17  10  72   1   0   1|   0    84k| 773k  247k|   0     0 |6330  8465 
 19   9  71   1   0   0|   0  1528k| 803k  256k|   0     0 |6519  8707 
 40  16  38   2   0   4|   0  5924k|3490k 1493k|   0     0 |  11k   19k
 54  19  15   8   0   5|   0    20M|3751k 1201k|   0     0 |  13k   25k
 52  16  18  10   0   5|   0    21M|5419k 1735k|   0     0 |  12k   23k
 55  22  14   5   0   5|   0    18M|4257k 1362k|   0     0 |  14k   27k
 52  18  19   7   0   5|   0    17M|3452k 1106k|   0     0 |  13k   24k
 33   9  50   6   0   2|   0    14M|4277k 1369k|   0     0 |8310    14k
 19   9  71   2   0   0|   0  2232k| 784k  251k|   0     0 |6441  8622 
 23   9  65   2   0   1|   0  3668k|1194k  381k|   0     0 |6888  9461 
 46  13  22  15   0   4|   0    13M|3164k 1391k|   0     0 |  11k   20k
 52  19  18   7   0   4|   0    18M|5428k 1739k|   0     0 |  13k   24k
 50  17  20   9   0   4|   0    22M|3943k 1262k|   0     0 |  12k   23k
 55  19  15   6   0   5|   0    11M|3515k 1126k|   0     0 |  14k   26k
 47  14  23  12   0   4|   0    23M|4953k 1588k|   0     0 |  11k   21k
 34  14  47   4   0   2|   0  7588k|2844k  910k|   0     0 |9035    15k
 20   8  68   4   0   1|   0  6384k|1075k  344k|   0     0 |6312  8416 
 20   9  69   3   0   0|   0  5024k| 817k  261k|   0     0 |6422  8589 
 49  20  20   7   0   3|   0    17M|4472k 1811k|   0     0 |  13k   23k
 53  21  16   6   0   6|   0    18M|4299k 1376k|   0     0 |  14k   25k
 55  18  16   7   0   5|   0    18M|3877k 1241k|   0     0 |  14k   25k
 52  16  20   8   0   5|   0    19M|5826k 1865k|   0     0 |  13k   23k
 53  20  15   7   0   5|   0    14M|3736k 1196k|   0     0 |  13k   25k
  3   1  94   3   0   0|   0  5668k|1576k  504k|   0     0 | 459   800 
  1   0  98   1   0   0|   0  2684k|  66B  354B|   0     0 | 109   198 
  0   0  99   1   0   0|   0  1432k|  66B  354B|   0     0 |  63   117 
  1   0  99   1   0   0|   0   824k|  66B  354B|   0     0 | 110   197

↑読み込み時にもけっこうなdisk writeがありますが、WAL(Write Ahead Log)なのでしょうか。selectでは意味がなさそうですが。。。それと、↑を見ると、MySQLプロトコルが古いのかなという気がします、というのもRedisあたりのベンチマーク中にdstatを見ますと、読み込みと書き込み時では、net/totalの桁が異なるからです。具体的には読み込み時はrecvがわずかで、sendが大きくなります。

ちなみに、いろんなマシンで計測した結果を一番上の表にまとめておきます。

my.cnfの内容を以下に置いておきます。速度と信頼性の中間を取った感じです。デフォルトから書き換えた・追加した項目は以下の4項目です。

– bind-address = 0.0.0.0
– innodb_buffer_pool_size = 1G
– innodb_flush_log_at_trx_commit = 2
– sync_binlog = 1
– innodb_flush_method = O_DIRECT

bind-addressについてですが、127.0.0.1だとローカルホストからのみアクセス可能です。ですので0.0.0.0としてグローバルで取れるようにしておきます。その他の↑のオプションについては検索してみてください。

my.conf
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address		= 127.0.0.1
bind-address = 0.0.0.0

#
# * Fine Tuning
#
key_buffer		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit	= 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
#log_slow_queries	= /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 10
max_binlog_size         = 100M
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

innodb_buffer_pool_size = 1G
innodb_flush_log_at_trx_commit = 2
sync_binlog = 1
innodb_flush_method = O_DIRECT

[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

[isamchk]
key_buffer		= 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/