memorycraft: mysql

ラベル mysql の投稿を表示しています。すべての投稿を表示

2013年6月15日土曜日

Spiderってなんじゃ？（SUBPARTITIONで負荷分散しながらストレージ容量を無限追加）

Spiderシリーズです。Spiderではパーティションを使ってシャーディングしますが、パーティションの定義によって分散の特性が変わります。

1. サロゲートキーなどのHASHパーティション

データは均等に分散
更新負荷は均等に分散
増設はデータ全体の再ハッシュが必要

2. 日付の年月などのHASHパーティション

データは順次格納
更新負荷は常に1データノードにのみかかる
増設は新しい年月を追加

3. サロゲートキーなどのRANGEパーティション

データは順次格納
更新負荷は常に1データノードにのみかかる
増設は新しいキー範囲を追加

理想は均等に負荷分散しながら、データ容量を後から増やせるのがベストですが、１，２，３とも負荷分散と容量追加のどちらかにしか親和性がありません。
そこで、作者の斯波さん(@kentokushiba)に伺ったところ、　SpiderでもSUBPARTITIONが使えるとのことでRANGEとHASHを組み合わせることで実現できると教えて頂きました。

早速やってみます。

CREATE TABLE `spdoc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT NULL,
  `content` text,
  `docname` varchar(255),
  INDEX(created_at) ,
  PRIMARY KEY (`id`)
) ENGINE=SPIDER DEFAULT CHARSET=utf8 CONNECTION=' table "spdoc", user "xxxxxxxx", password "yyyyyyyyy" auto_increment_mode "1" '
PARTITION BY RANGE (id)
SUBPARTITION BY HASH(id) (
  PARTITION p1 VALUES LESS THAN (1000000) (
    SUBPARTITION db1 COMMENT = 'host "10.0.1.141", port "3306"' ENGINE = SPIDER,
    SUBPARTITION db2 COMMENT = 'host "10.0.1.142", port "3306"' ENGINE = SPIDER
  ),
  PARTITION p2 VALUES LESS THAN (2000000) (
    SUBPARTITION db3 COMMENT = 'host "10.0.1.143", port "3306"' ENGINE = SPIDER,
    SUBPARTITION db4 COMMENT = 'host "10.0.1.144", port "3306"' ENGINE = SPIDER
  )
);

まずRANGEパーティションをidの値の範囲で定義します。試しに1000000,2000000に設定します。
次に、SUBPARTITIONでidでHASHパーティションを定義し、ここにデーターノードを指定します。

すると、idが1000000以下のデータについてdb1, db2のデータノードに均等に分散され、
1000000〜2000000の範囲では、db3, db4に均等に分散されます。

idが2000000を超えそうになった場合は、後から３つめのパーティションを追加することができます。

ALTER TABLE spdoc ADD PARTITION
(
  PARTITION p3 VALUES LESS THAN (3000000) (
    SUBPARTITION db5 COMMENT = 'host "10.0.1.145", port "3306"' ENGINE = SPIDER,
    SUBPARTITION db6 COMMENT = 'host "10.0.1.146", port "3306"' ENGINE = SPIDER
  )
);

これでデータ容量はほぼ無限に増やすことができます。

パーティションごとにSUBPARTITIONの数を変えることはできないので、負荷の分散数は同じデータノード数で賄わなければいけませんが、その場合はパーティションごとのデータノードのインスタンスタイプやPIOPSを増やすことで処理能力をアップさせることで対応できるかと思います。

斯波さんありがとうございました！

以上です。

2013年5月25日土曜日

spiderってなんじゃ？（spider自身の冗長化）

いままではSpiderがひとつに対して、データノードが複数ある構成をとってきましたが、実戦ではSpiderが単一障害点になるため好ましくありません。ここではSpider自身の冗長化を行なってみます。

いままでのSpider x 1 + mroonga x 2 の構成に対してデータを入れ込むためのappノードを用意します。

SpoderのSPOF

mroonga

 CREATE TABLE doc (
  id INT PRIMARY KEY AUTO_INCREMENT,
  created_at datetime,
  content text,
  FULLTEXT INDEX (content)
 ) ENGINE = mroonga;

spider

 CREATE TABLE doc (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  created_at datetime,
  content text,
  FULLTEXT INDEX (content)
 ) ENGINE = spider CHARSET utf8
 CONNECTION ' table "doc", user "memorycraft_user", password "memorycraft_pass" '
 PARTITION BY KEY() (
  PARTITION db1 comment 'host "10.0.1.136", port "3306"',
  PARTITION db2 comment 'host "10.0.1.137", port "3306"'
 );

app

# yum install -y php php-mbstring php-pdo php-mysql
$ vim links.php

<?php

//ランダム文字列
function getRandomString($size = 8){
    $chars = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
    mt_srand();
    $char = "";
    for($i = 0; $i < $size; $i++) {
        $char .= $chars{mt_rand(0, strlen($chars) - 1)};
    }
    return $char;
}

//XMLテンプレート
$tmpl =<<< END
<?xml version="1.0" encoding="UTF-8" ?>
<data>
  <info>
    <datetime>%s</datetime>
    <address>%s</address>
    <content>%s</content>
  </info>
</data>
END;

//XMLテンプレートに流しこむcontent要素の内容を読み込む
$contents = array();
for($i=0;$i<10;$i++){
  $contents[] = file_get_contents(dirname(__FILE__)."/dummy".$i.".txt");
}

//spiderホストは引数から取得する
$host = $argv[1];
//タイムゾーン
date_default_timezone_set('Asia/Tokyo');

//無限ループ
while(true){
  //XMLテンプレートに各要素を割り当てる
  $xml = sprintf($tmpl, date('Y/m/d H:i:s'), getRandomString(), $contents[rand(0,9)]);
  echo ".";
  //DB接続
  $pdo = new PDO(
    "mysql:dbname=memorycraft;host=$host;",
    "memorycraft_user",
    "memorycraft_pass",
    array(
     PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET `utf8`"
   ));
  $pdo->query("SET NAMES utf8;");
  //投入
  $st = $pdo->prepare("INSERT INTO doc VALUES(NULL,NOW(),?);");
  $rslt = $st->execute(array($xml));
}
?>

実行します

$ php links.php 10.0.1.20
.......................................

Spiderノードで投入されていることを確認します。

mysql> select id, created_at from doc limit 10;
+------+---------------------+
| id   | created_at          |
+------+---------------------+

|    1 | 2013-05-25 11:27:47 |
|    2 | 2013-05-25 11:27:47 |
|    3 | 2013-05-25 11:27:47 |
|    4 | 2013-05-25 11:27:47 |
|    5 | 2013-05-25 11:27:47 |
|    6 | 2013-05-25 11:27:48 |
|    7 | 2013-05-25 11:27:48 |
|    8 | 2013-05-25 11:27:48 |
|    9 | 2013-05-25 11:27:48 |
|   10 | 2013-05-25 11:27:48 |
+------+---------------------+
10 rows in set (0.06 sec)

これで、spider x 1 + mroonga x 2 + app x 1になりました。

Spiderの冗長化

次は、Spider x 2 + mroonga x 2 + internal ELB + app x 4 にしてみます。

Spiderノードをもう１台(spider2)追加します。

テーブルはspider1と同じ内容にします。
/etc/my.cnfでauto_increment_increment = 100でoffsetを１，２でずらします。
これについては、別の記事で触れています。

mysqlってなんじゃ？（マルチマスタでauto_increment）
http://memocra.blogspot.jp/2013/05/mysqlautoincrement.html

また、spiderを複数にした場合、appからの接続は１つにまとめます。
Proxy系のソフトウェアを入れるのも１つの方法ですが、ここでは内部ELBを利用してみます。

ヘルスチェックは3306ではなく別のポート経由でMySQL監視プロセスに接続スべきですが、ここではとりいそぎhttpdを立ちあげ80番に対してヘルスチェックします。

それではappで投入プログラムを実行します。
対象のホストは内部ELBのDNSNameになります。

$ php links.php internal-spider-2040122974.ap-northeast-1.elb.amazonaws.com
........................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................

spider1でデータを見てみます。

mysql> select id, created_at from doc limit 20;
+------+---------------------+
| id   | created_at          |
+------+---------------------+
|    1 | 2013-05-25 11:27:47 |
|  101 | 2013-05-25 11:27:47 |
|  201 | 2013-05-25 11:27:47 |
|  202 | 2013-05-25 11:27:47 |
|  601 | 2013-05-25 11:27:47 |
|  701 | 2013-05-25 11:27:48 |
| 1101 | 2013-05-25 11:27:48 |
| 1201 | 2013-05-25 11:27:48 |
| 1601 | 2013-05-25 11:27:48 |
| 1701 | 2013-05-25 11:27:48 |
| 2101 | 2013-05-25 11:27:49 |
| 2201 | 2013-05-25 11:27:49 |
| 2301 | 2013-05-25 11:27:49 |
| 2601 | 2013-05-25 11:27:49 |
| 2701 | 2013-05-25 11:27:49 |
| 2802 | 2013-05-25 11:27:49 |
| 3101 | 2013-05-25 11:27:49 |
| 3201 | 2013-05-25 11:27:49 |
| 3301 | 2013-05-25 11:27:49 |
| 3601 | 2013-05-25 11:27:50 |
+------+---------------------+

mysql> select count(*) from doc;
+----------+
| count(*) |
+----------+
|   111433 |
+----------+

順調に入っていってるみたいです。
あとはSpiderノードを必要に応じて追加していき、my.cnfでoffsetをずらして起動するだけです。
offsetずらしの処理はcloud-initなどで行うと、AutoScalingすることもできそうです。

今回は以上です。

mysqlってなんじゃ？（マルチマスタでauto_increment）

MySQLでマルチマスタやシャーディングなどを行った場合、auto_incrementを使いたい場合があります。

たとえばspiderだとすると、複数台のSpiderノードを経由してデータが投入されると、データノードでauto_increment値が競合してしまうことがあるため、auto_incrementのオフセットをspiderノードごとにずらします。

spider1

/etc/my.cnf
------
[mysqld]
auto_increment_increment = 100
auto_increment_offset = 1

spider2

/etc/my.cnf
------
[mysqld]
auto_increment_increment = 100
auto_increment_offset = 2

auto_increment_incrementはカウントアップされる単位です。上の例では100ずつ増えていきます。
auto_increment_offsetはauto_incrementのスタート時点の値です。

これによって、各spiderノードのauto_increment値は以下のように増えていきます。

spider1

spider2

つまり３桁目以上は全体で同じ値をカウントアップしていきますが、下２桁は重複しなくなります。

これを元に投入されたデータはspider上で抽出すると以下のようになります。

随分値を飛ばしているように感じますが、ビットずらしのような感覚でとらえれば違和感はなくなると思います。そして、この場合は、Spiderノードを99台まで増やしても値が重複しません。
将来的に何台まで増えるかわからない場合はこのようにするとよさそうです。

以上です。

2013年5月20日月曜日

mroongaってなんじゃ？（Spiderで分散全文検索）

前回の記事でmroongaを使用しましたが、全文検索のデータは大きくなりがちなので、Spiderを利用して書き込み負荷やストレージ容量を分散してみます。

設定

mroongaデータノード

前回と同じようにｍroongaテーブルを作りますが、Spiderノードからアクセスするためにユーザー権限を登録します。今回はホストは特に絞りません。

mysql> GRANT ALL PRIVILEGES ON *.* TO 'memorycraft_user'@localhost IDENTIFIED BY 'memorycraft_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'memorycraft_user'@'%' IDENTIFIED BY 'memorycraft_pass';

新しくデータベースをつくり、blogテーブルを作ります。

mysql> CREATE DATABASE memorycraft;
mysql> use memorycraft;
mysql> 
mysql> CREATE TABLE blog (
mysql>  id INT PRIMARY KEY AUTO_INCREMENT,
mysql>  content text,
mysql>  FULLTEXT INDEX (content)
mysql> ) ENGINE = mroonga DEFAULT CHARSET utf8;

Spiderノード

Spiderのインストールは以前の記事のとおりです。
同じように権限を登録し、データベースを作成します。

mysql> GRANT ALL PRIVILEGES ON *.* TO 'memorycraft_user'@localhost IDENTIFIED BY 'memorycraft_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'memorycraft_user'@'%' IDENTIFIED BY 'memorycraft_pass';
mysql> 
mysql> CREATE DATABASE memorycraft;
mysql> use memorycraft;

Spiderテーブルを作成します。
ここでは、２つのノードにKEYパーティションで分散してみます。

mysql> CREATE TABLE blog (
mysql>  id INT PRIMARY KEY AUTO_INCREMENT,
mysql>  content text,
mysql>  FULLTEXT INDEX (content)
mysql> ) ENGINE = Spider DEFAULT CHARSET utf8
mysql> CONNECTION ' table "blog", user "memorycraft_user", password "memorycraft_pass" '
mysql> PARTITION BY KEY() (
mysql>  PARTITION db1 comment 'host "10.0.1.169", port "3306"',
mysql>  PARTITION db2 comment 'host "10.0.1.170", port "3306"'
mysql> );

これで設定は完了です。

確認

次にデータを登録してみます。
登録の仕方は前回と同様です。

Spiderノード

mysql> INSERT INTO blog (content) VALUES ("前回はpsqlでRedshiftを利用してみましたが、通常データウェアハウス（DWH）というのはBI（Buisiness Intelligence）ツールを利用することが多いようです。

エンジニアの観点からすると複雑なSQLを書くだけでいいかもしれませんが、経営者などの立場からするとBIツールなどを使って画面上でポチポチやって分析できることが重要なようです。
今回はそのBIツールの中で、Redshiftにいち早く対応しているJaspersoftという製品を使ってRedshiftに接続してみたいと思います。");

mysql> INSERT INTO blog (content) VALUES ("久しぶりにnagiosの話題です。
アプリログ内容の監視の仕方には様々な要件がありますが、特定の間隔でログを監視し「error」などの文言があったらアラートする。などがよくあるケースで、以前の記事にも書きました。
その逆に、例えば、多量のアクセスがあるにも関わらず頻繁に出力されるはずの重要なキーワードがでていない場合は、不測の事態がおこっているかも知れません。
今回は特定の間隔でログを監視し、その中にキーワードが含まれていなかったらアラートする
というものです。
ではやってみます。");

mysql> INSERT INTO blog (content) VALUES ("S3のwebホスティングで、ログ出力の設定をしていた場合、ログファイルが大量に出力されます。
ログの記録時間は標準時で出力されていてわかりづらいです。
今回はEMRのHiveを利用して、日本時間の0時〜翌日の0時までのログを1ファイルにまとめてみたいと思います。");

mysql > INSERT INTO blog (content) VALUES ("久しぶりのSpiderの話題です。
今回はtpcc-mysqlというベンチマークツールを使ってSpiderのベンチマークをとってみました。
mysqlにかぎらずDBのベンチマークツールの多くは、TPCという団体の定めたベンチマーク仕様に基いて実装されていて、トランザクションやアクセスなどのDB用途によっていくつかのベンチマークタイプに分かれていて、OLTP向けのTPC-Eや意思決定システム向けのTPC-HやTPC-DSなど色々あるようです。");


mysql> select id,MATCH(content) AGAINST("ログ アクセス" IN BOOLEAN MODE) as score from blog WHERE MATCH(content) AGAINST("ログ アクセス" IN BOOLEAN MODE)
    -> ;
+----+-------+
| id | score |
+----+-------+
|  3 |     4 |
|  2 |     4 |
|  4 |     1 |
+----+-------+
3 rows in set (0.01 sec)

mysql> select id, MATCH(content) AGAINST("ログ" IN BOOLEAN MODE) from blog WHERE MATCH(content) AGAINST("ログ" IN BOOLEAN MODE) ORDER BY  MATCH(content) AGAINST("ログ" IN BOOLEAN MODE) DESC;
+----+--------------------------------------------------+
| id | MATCH(content) AGAINST("ログ" IN BOOLEAN MODE)   |
+----+--------------------------------------------------+
|  3 |                                                4 |
|  2 |                                                3 |
+----+--------------------------------------------------+
2 rows in set (0.01 sec)

データノード

mysql> select id from blog;
+----+
| id |
+----+
|  1 |
|  3 |
+----+
2 rows in set (0.00 sec)


mysql> select id from blog;
+----+
| id |
+----+
|  2 |
|  4 |
+----+
2 rows in set (0.00 sec)

うまく分散されているようです。
これで沢山データがあっても分散されます。

以上です。

2013年5月17日金曜日

mroongaってなんじゃ？ (mroongaでmysql全文検索)

mysqlで全文検索といえばTritonnが有名でした。現在ではmysqlで全文検索をするときにmroongaというプロダクトが有望なようです。

Tritonnとの違いは以下のサイトが詳しいです。
「全文検索エンジンgroongaを囲む夕べ #1」参加メモ

簡単にいうと、

Tritonn：Sennaという全文検索エンジンをMySQL用に組み込んだものです。MyISAMを前提に組み込まれていることもあり、更新が遅く、また更新時の検索が重くなるなど、ベストなパフォーマンスが得られにくかったようです。
mroonga：groongaという全文検索エンジンをMySQL用にストレージエンジンにしたものです。MyISAMに依存しないため更新時の参照に影響を与えず、更新も速いようです。

では、さっそく導入してみます。
環境はEC2上のCentOS-6.4.1（suz-lab AMI）です。

groongaとmecabのインストール

まずgroongaとmecabをインストールします。
yumリポジトリを登録し、必要なものをインストールします。

# rpm -ivh http://packages.groonga.org/centos/groonga-release-1.1.0-1.noarch.rpm
# yum makecache
# yum install -y mecab
# yum install -y mecab-ipadic
# yum install -y groonga
# yum install -y groonga-tokenizer-mecab
# yum install -y wget

mysqlのインストール

yumを使った場合mroongaはmysql5.1を前提とするようです。ここではmysql5.5を使用したいのでソースからインストールします。

# cd /usr/local/src/
# wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.31.tar.gz/from/http://cdn.mysql.com/
# tar xzvf mysql-5.5.31.tar.gz
# cd mysql-5.5.31/
# yum install cmake
# yum install -y ncurses
# yum install -y ncurses-devel
# yum install -y gcc-c++
# yum install -y bison
# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DENABLED_LOCAL_INFILE=true -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all -DWITH_READLINE=OFF
# make
# make install

mysql の設定

適当に設定ファイルを用意します。

# cd /usr/local/src/
# mv /etc/my.cnf /etc/my.cnf.org
# cp /usr/local/mysql/support-files/my-large.cnf /etc/my.cnf
# vim /etc/my.cnf

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port  = 3306
socket  = /tmp/mysql.sock
default-character-set = utf8
# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port  = 3306
socket  = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed


# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

innodb_file_per_table
innodb_fast_shutdown = 0

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

mysqlの起動

mysqlユーザーの作成と、起動スクリプトのコピー、DBの初期化を行い、mysqlにパスを通して起動します。

# groupadd mysql
# useradd mysql -g mysql -s /sbin/nologin
# cd /usr/local/mysql/
# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
# vim /root/.bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
 . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

export PATH

# source ~/.bash_profile
# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
# /etc/init.d/mysql start

mroongaのインストール

ここでmroongaをソースからビルドします。
configureオプションで、mysqlのソースを指定します。

# yum install -y automake
# cd /usr/local/src
# curl -OL http://packages.groonga.org/source/mroonga/mroonga-3.03.tar.gz
# tar xzvf mroonga-3.03.tar.gz
# cd mroonga-3.03/
# yum install groonga-devel
# ./configure --with-mysql-source=/usr/local/src/mysql-5.5.31 --with-mysql-config=/usr/local/mysql/bin/mysql_config
# make
# make install

mysqlへのmroongaエンジンのインストール

プラグインのインストールを行い、いくつかの関数を登録します。

mysql -u root
mysql> INSTALL PLUGIN mroonga SONAME 'ha_mroonga.so';
mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| MyISAM             | YES     | MyISAM storage engine                                      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                         | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
| mroonga            | YES     | CJK-ready fulltext search, column store                    | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
7 rows in set (0.00 sec)

mysql> CREATE FUNCTION last_insert_grn_id RETURNS INTEGER SONAME 'ha_mroonga.so';
mysql> CREATE FUNCTION mroonga_snippet RETURNS STRING SONAME 'ha_mroonga.so';
mysql> CREATE FUNCTION mroonga_command RETURNS STRING SONAME 'ha_mroonga.so';

これでひと通りのインストールが完了しました。
show enginesにmroongaが含まれていれば成功です。

確認

それでは簡単に触ってみます。
testデータベースにblogテーブルをつくってみます。
ここでは自分のグログ記事の抜粋をtextカラムにいれておきます。

mysql> use test;
mysql> CREATE TABLE blog (
mysql>   id INT PRIMARY KEY AUTO_INCREMENT,
mysql>   content text,
mysql>   FULLTEXT INDEX (content)
mysql> ) ENGINE = mroonga DEFAULT CHARSET utf8;

mysql> INSERT INTO blog (content) VALUES ("前回はpsqlでRedshiftを利用してみましたが、通常データウェアハウス（DWH）というのはBI（Buisiness Intelligence）ツールを利用することが多いようです。
エンジニアの観点からすると複雑なSQLを書くだけでいいかもしれませんが、経営者などの立場からするとBIツールなどを使って画面上でポチポチやって分析できることが重要なようです。
今回はそのBIツールの中で、Redshiftにいち早く対応しているJaspersoftという製品を使ってRedshiftに接続してみたいと思います。");

mysql> INSERT INTO blog (content) VALUES ("久しぶりにnagiosの話題です。
アプリログ内容の監視の仕方には様々な要件がありますが、特定の間隔でログを監視し「error」などの文言があったらアラートする。などがよくあるケースで、以前の記事にも書きました。
その逆に、例えば、多量のアクセスがあるにも関わらず頻繁に出力されるはずの重要なキーワードがでていない場合は、不測の事態がおこっているかも知れません。
今回は特定の間隔でログを監視し、その中にキーワードが含まれていなかったらアラートする
というものです。
ではやってみます。");

mysql> INSERT INTO blog (content) VALUES ("S3のwebホスティングで、ログ出力の設定をしていた場合、ログファイルが大量に出力されます。
ログの記録時間は標準時で出力されていてわかりづらいです。
今回はEMRのHiveを利用して、日本時間の0時〜翌日の0時までのログを1ファイルにまとめてみたいと思います。");

mysql > INSERT INTO blog (content) VALUES ("久しぶりのSpiderの話題です。
今回はtpcc-mysqlというベンチマークツールを使ってSpiderのベンチマークをとってみました。
mysqlにかぎらずDBのベンチマークツールの多くは、TPCという団体の定めたベンチマーク仕様に基いて実装されていて、トランザクションやアクセスなどのDB用途によっていくつかのベンチマークタイプに分かれていて、OLTP向けのTPC-Eや意思決定システム向けのTPC-HやTPC-DSなど色々あるようです。");

そして、「ログ」を含むレコードを探してみます。

mysql> SELECT * FROM blog WHERE MATCH(content) AGAINST("ログ");
mysql> SELECT * FROM blog WHERE MATCH(content) AGAINST("ログ") \G;
*************************** 1. row ***************************
     id: 2
content: 久しぶりにnagiosの話題です。アプリログ内容の監視の仕方には様々な要件がありますが、特定の間隔でログを監視し「error」などの文言があったらアラートする。などがよくあるケースで、以前の記事にも書きました。その逆に、例えば、多量のアクセスがあるにも関わらず頻繁に出力されるはずの重要なキーワードがでていない場合は、不測の事態がおこっているかも知れません。今回は特定の間隔でログを監視し、その中にキーワードが含まれていなかったらアラートする
というものです。

ではやってみます。
*************************** 2. row ***************************
     id: 3
content: S3のwebホスティングで、ログ出力の設定をしていた場合、ログファイルが大量に出力されます。

ログの記録時間は標準時で出力されていてわかりづらいです。

今回はEMRのHiveを利用して、日本時間の0時〜翌日の0時までのログを1ファイルにまとめてみたいと思います。
2 rows in set (0.00 sec)

ERROR:
No query specified

mysql>

検索が成功したようです。
次に検索スコアを出してみます。デフォルトでは自然言語検索によるスコアのようです。

mysql> select id,MATCH(content) AGAINST("ログ") as score from blog;
+----+---------+
| id | score   |
+----+---------+
|  1 |       0 |
|  2 |  786435 |
|  3 | 1048580 |
|  4 |       0 |
+----+---------+
4 rows in set (0.00 sec)

また、「ログ」と「アクセス」が含まれるレコードの検索をしてみます。

両方含まれるもの

mysql> select id,MATCH(content) AGAINST("+ログ +アクセス" IN BOOLEAN MODE) as score from blog WHERE MATCH(content) AGAINST("+ログ +アクセス" IN BOOLEAN MODE);
+----+-------+
| id | score |
+----+-------+
|  2 |     4 |
+----+-------+
1 row in set (0.00 sec)

どちらかが含まれるもの

mysql> select id,MATCH(content) AGAINST("ログ アクセス" IN BOOLEAN MODE) as score from blog WHERE MATCH(content) AGAINST("ログ アクセス" IN BOOLEAN MODE)
+----+-------+
| id | score |
+----+-------+
|  2 |     4 |
|  3 |     4 |
|  4 |     1 |
+----+-------+
3 rows in set (0.00 sec)

マッチ度がスコアで取得されるので、ソートをすることもできます。

mysql> select id, MATCH(content) AGAINST("ログ" IN BOOLEAN MODE) from blog WHERE MATCH(content) AGAINST("ログ" IN BOOLEAN MODE) ORDER BY  MATCH(content) AGAINST("ログ" IN BOOLEAN MODE) DESC;
+----+--------------------------------------------------+
| id | MATCH(content) AGAINST("ログ" IN BOOLEAN MODE)   |
+----+--------------------------------------------------+
|  3 |                                                4 |
|  2 |                                                3 |
+----+--------------------------------------------------+
2 rows in set (0.00 sec)

ざっとさわりでしたが、mroongaを試してみました。
パフォーマンスやクエリの用法などはまたの機会に調べてみます。

またMySQLでは5.6からInnoDB FTSというInnoDBでの全文検索が可能なようですが、こちらは参照は速いものの更新はmroongaの方が速いという結果も出ているようなので、こちらも機会があれば調べてみたいと思います。
http://www.slideshare.net/y-ken/my-sql-56innodb-fts

以上です。

2013年4月1日月曜日

Spiderってなんじゃ？（tpcc-mysqlでベンチマークをとってみた）

久しぶりのSpiderの話題です。
今回はtpcc-mysqlというベンチマークツールを使ってSpiderのベンチマークをとってみました。

mysqlにかぎらずDBのベンチマークツールの多くは、TPCという団体の定めたベンチマーク仕様に基いて実装されていて、トランザクションやアクセスなどのDB用途によっていくつかのベンチマークタイプに分かれていて、OLTP向けのTPC-Eや意思決定システム向けのTPC-HやTPC-DSなど色々あるようです。
http://www.tpc.org/information/benchmarks.asp

その中で、TPC-Cというのが複数ユーザーのトランザクションが発生するベンチマーク仕様で、顧客が商品を注文し在庫チェックと発送などをシュミレートするもののようです。
そして、このTPC-C仕様をmysqlのベンチマークとして実装したものが、tpcc-mysqlです。

このあたりのことは、以下のサイトに詳しく書かれていて非常に勉強になりました。
データベース負荷テストツールまとめ(2)
tpcc-mysqlによるMySQLのベンチマーク

今回はtpcc-mysqlを使ってSpider+RDS*4とRDS単体をテストしてみます。
構成は以下の通りです。

tpcc-mysqlのインストール

まず、ベンチ用のインスタンス（10.0.1.99）にtpcc-mysqlをインストールします。

# yum install bzr mysql-devel -y
$ mkdir ~/test
$ cd ~/test/
$ bzr init
$ bzr branch lp:~percona-dev/perconatools/tpcc-mysql
$ cd tpcc-mysql/
$ cd src
$ make all

これでインストールできました。
~/test/tpcc-mysql配下にtpcc_loadとtpcc_startという実行ファイルができていれば成功です。

$ ls -l ~/test/tpcc-mysql/
合計 248
-rw-rw-r-- 1 appadmin appadmin    851  4月  1 20:03 2013 README
-rw-rw-r-- 1 appadmin appadmin   1621  4月  1 20:03 2013 add_fkey_idx.sql
-rw-rw-r-- 1 appadmin appadmin    317  4月  1 20:03 2013 count.sql
-rw-rw-r-- 1 appadmin appadmin   3105  4月  1 20:03 2013 create_table.sql
-rw-rw-r-- 1 appadmin appadmin    763  4月  1 20:03 2013 drop_cons.sql
-rw-rw-r-- 1 appadmin appadmin    477  4月  1 20:03 2013 load.sh
drwxrwxr-x 2 appadmin appadmin   4096  4月  1 20:03 2013 schema2
drwxrwxr-x 5 appadmin appadmin   4096  4月  1 20:03 2013 scripts
drwxrwxr-x 2 appadmin appadmin   4096  4月  1 20:33 2013 src
-rwxrwxr-x 1 appadmin appadmin  60751  4月  1 20:33 2013 tpcc_load
-rwxrwxr-x 1 appadmin appadmin 154558  4月  1 20:33 2013 tpcc_start

テーブルの準備

予めstressという名前のデータベースを作成しておきます。
（名前はなんでも構いません）

次に、対象のテーブルとインデックスを作成します。
tpcc-mysql配下には、テーブルとインデックスの作成用DDL（create_table.sql、add_fkey_idx.sql）が付属しています。

インデックス用のSQLファイルには外部キーの作成も含まれていますが、
Spiderでは外部キーが使用できないため、外部キーからインデックス部分を除いたものを作ります。

また、Spiderノード用のテーブル作成DDLも作成します。

またSpiderノードからRDSに接続するときのために、Spiderノードのmy.cnfに以下の設定をしておきます。

spider_remote_sql_log_off = 1

次に作成したDDLを各インスタンスに流し込みます。

$ mysql -h 10.0.1.172 -u memorycraft stress -p < ~/test/tpcc-mysql/create_table.sql
$ mysql -h 10.0.1.20 -u memorycraft stress -p < ~/test/tpcc-mysql/create_table-spider.sql
$ mysql -h data1.cwnvl1ncuiwq.ap-northeast-1.rds.amazonaws.com -u memorycraft stress -p < ~/test/tpcc-mysql/create_table.sql
$ mysql -h data2.cwnvl1ncuiwq.ap-northeast-1.rds.amazonaws.com -u memorycraft stress -p < ~/test/tpcc-mysql/create_table.sql
$ mysql -h data3.cwnvl1ncuiwq.ap-northeast-1.rds.amazonaws.com -u memorycraft stress -p < ~/test/tpcc-mysql/create_table.sql
$ mysql -h data4.cwnvl1ncuiwq.ap-northeast-1.rds.amazonaws.com -u memorycraft stress -p < ~/test/tpcc-mysql/create_table.sql

$ mysql -h 10.0.1.172 -u memorycraft stress -p < ~/test/tpcc-mysql/add_fkey_idx.sql
$ mysql -h 10.0.1.20 -u memorycraft stress -p < ~/test/tpcc-mysql/add_fkey_idx.sql
$ mysql -h data1.cwnvl1ncuiwq.ap-northeast-1.rds.amazonaws.com -u memorycraft stress -p < ~/test/tpcc-mysql/add_fkey_idx.sql
$ mysql -h data2.cwnvl1ncuiwq.ap-northeast-1.rds.amazonaws.com -u memorycraft stress -p < ~/test/tpcc-mysql/add_fkey_idx.sql
$ mysql -h data3.cwnvl1ncuiwq.ap-northeast-1.rds.amazonaws.com -u memorycraft stress -p < ~/test/tpcc-mysql/add_fkey_idx.sql
$ mysql -h data4.cwnvl1ncuiwq.ap-northeast-1.rds.amazonaws.com -u memorycraft stress -p < ~/test/tpcc-mysql/add_fkey_idx.sql

ここまででDDLは出来上がりました。

テストデータの投入

次にデータの投入です。データの投入には先ほどのビルドで出来たtpcc_loadコマンドを使用します。
第１〜第４引数まではそれぞれホスト、DB名、DBユーザー、DBパスワードを指定し、第５引数には倉庫(warehouse)の数を指定します。 warehouseは１つにつき一番レコードの多いorder_lineテーブルで30万件程度に増えます。ここではwarehouseを100（order_lineテーブルで3000万件程度）に設定します。
このデータ投入処理はとても時間がかかります。

./tpcc_load 10.0.1.172 stress memorycraft xxxxxxxxx 100
./tpcc_load 10.0.1.20 stress memorycraft xxxxxxxxx 100

テストの実行

データがロードできたらtpcc_startでテストを実行します。
-w以降のオプションは以下の通りです。

-w：warehouseの数、基本的にはloadで設定したのと同じ値
-c：接続数
-r：待機時間
-l：実行時間（秒）

基本的にはwarehouse数と、接続数を調整していろいろなパターンでテストしていきます。

./tpcc_start -h 10.0.1.172 -P 3306 -d stress -u memorycraft -p xxxxxxxxx -w 100 -c 10 -r 300 -l 3600
./tpcc_start -h 10.0.1.20 -P 3306 -d stress -u memorycraft -p xxxxxxxxx -w 100 -c 10 -r 300 -l 3600

結果は以下のようになり、一番最後のTpmC（1分間に処理できたトランザクション数）が指標になります。

***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
option h with value '10.0.1.20'
option P with value '3306'
option d with value 'stress'
option u with value 'memorycraft'
option p with value 'satoru00'
option w with value '100'
option c with value '10'
option r with value '300'
option l with value '3600'

     [server]: 10.0.1.20
     [port]: 3306
     [DBname]: stress
       [user]: memorycraft
       [pass]: satoru00
  [warehouse]: 100
 [connection]: 10
     [rampup]: 300 (sec.)
    [measure]: 3600 (sec.)

RAMP-UP TIME.(300 sec.)

MEASURING START.

  10, 257(0):3.061|3.773, 258(0):0.580|0.773, 25(0):0.297|0.415, 26(0):3.753|4.259, 26(0):9.967|11.042
  20, 265(0):3.003|3.222, 264(0):0.570|0.617, 27(0):0.252|0.329, 26(0):3.448|3.478, 26(0):8.935|9.011
  30, 258(0):3.185|3.452, 256(0):0.626|0.684, 26(0):0.374|0.414, 25(0):3.700|4.063, 26(0):9.790|10.474
  40, 247(0):3.191|3.331, 249(0):0.605|0.630, 24(0):0.279|0.298, 26(0):3.702|4.258, 24(0):10.012|10.016
  50, 251(0):2.930|2.980, 248(0):0.526|0.548, 25(0):0.258|0.275, 24(0):3.111|3.113, 25(0):8.949|8.967
  60, 252(0):2.864|3.176, 254(0):0.544|0.585, 25(0):0.248|0.253, 26(0):3.249|3.315, 26(0):9.449|9.567
  70, 249(0):2.850|2.878, 247(0):0.527|0.540, 25(0):0.247|0.276, 25(0):3.126|3.156, 24(0):9.074|9.135
  80, 246(0):3.337|3.388, 249(0):0.651|0.663, 25(0):0.310|0.338, 24(0):3.897|3.967, 25(0):11.210|11.281
  90, 261(0):2.923|3.133, 262(0):0.548|0.577, 26(0):0.284|0.297, 27(0):3.218|3.283, 27(0):9.433|9.563
 100, 262(0):2.910|3.139, 258(0):0.528|0.561, 27(0):0.254|0.282, 26(0):3.170|3.193, 26(0):8.908|9.046
 110, 254(0):3.013|3.200, 253(0):0.570|0.586, 25(0):0.279|0.289, 25(0):3.328|3.511, 25(0):9.278|9.294
 120, 269(0):2.912|2.975, 272(0):0.546|0.553, 26(0):0.250|0.259, 27(0):3.180|3.212, 26(0):8.939|8.954
 130, 266(0):2.936|2.960, 265(0):0.533|0.567, 27(0):0.257|0.264, 26(0):3.190|3.192, 27(0):9.350|9.428
 140, 263(0):2.936|2.963, 263(0):0.578|0.632, 26(0):0.256|0.259, 27(0):3.348|3.456, 27(0):9.167|9.708
 150, 248(0):2.888|2.948, 247(0):0.545|0.603, 25(0):0.252|0.258, 24(0):3.144|3.165, 24(0):9.086|9.450
 160, 231(0):2.936|3.002, 232(0):0.526|0.539, 23(0):0.245|0.262, 24(0):3.199|3.237, 24(0):8.894|10.282
~(略)~
3480, 265(0):2.957|3.211, 265(0):0.534|0.603, 26(0):0.264|0.267, 26(0):3.267|3.330, 26(0):9.178|9.661
3490, 262(0):2.919|2.949, 266(0):0.527|0.538, 26(0):0.249|0.251, 27(0):3.178|3.183, 27(0):8.766|9.398
3500, 259(0):2.952|3.189, 257(0):0.541|0.652, 27(0):0.263|0.282, 26(0):3.266|3.279, 25(0):9.387|9.501
3510, 259(0):2.901|3.069, 262(0):0.532|0.612, 25(0):0.246|0.252, 25(0):3.205|3.226, 27(0):8.785|9.184
3520, 260(0):2.880|2.899, 259(0):0.519|0.586, 26(0):0.253|0.254, 27(0):3.168|3.189, 25(0):8.908|9.342
3530, 259(0):2.914|3.136, 250(0):0.534|0.604, 25(0):0.269|0.278, 25(0):3.296|3.330, 26(0):8.985|9.735
3540, 253(0):3.034|3.061, 261(0):0.572|0.632, 27(0):0.258|0.262, 26(0):3.234|3.319, 26(0):9.063|9.236
3550, 270(0):2.959|3.097, 269(0):0.522|0.547, 26(0):0.254|0.273, 27(0):3.242|3.244, 27(0):8.790|9.263
3560, 251(0):3.057|3.258, 250(0):0.609|0.653, 26(0):0.276|0.360, 25(0):3.360|3.444, 25(0):9.423|9.474
3570, 244(0):3.031|3.133, 248(0):0.546|0.618, 24(0):0.251|0.259, 24(0):3.286|3.299, 25(0):8.948|9.190
3580, 260(0):2.933|3.057, 260(0):0.527|0.542, 26(0):0.243|0.249, 27(0):3.175|3.190, 26(0):8.928|9.061
3590, 260(0):3.000|3.370, 255(0):0.560|0.667, 26(0):0.257|0.310, 25(0):3.327|3.329, 25(0):9.263|9.397
3600, 262(0):2.933|3.016, 263(0):0.523|0.540, 27(0):0.256|0.257, 27(0):3.180|3.230, 26(0):9.017|9.607

STOPPING THREADS..........


  [0] sc:91448  lt:0  rt:0  fl:0 
  [1] sc:91446  lt:0  rt:0  fl:0 
  [2] sc:9145  lt:0  rt:0  fl:0 
  [3] sc:9145  lt:0  rt:0  fl:0 
  [4] sc:9144  lt:0  rt:0  fl:0 
 in 3600 sec.


  [0] sc:91448  lt:0  rt:0  fl:0 
  [1] sc:91446  lt:0  rt:0  fl:0 
  [2] sc:9145  lt:0  rt:0  fl:0 
  [3] sc:9145  lt:0  rt:0  fl:0 
  [4] sc:9144  lt:0  rt:0  fl:0 

 (all must be [OK])
 [transaction percentage]
        Payment: 43.48% (>=43.0%) [OK]
   Order-Status: 4.35% (>= 4.0%) [OK]
       Delivery: 4.35% (>= 4.0%) [OK]
    Stock-Level: 4.35% (>= 4.0%) [OK]
 [response time (at least 90% passed)]
      New-Order: 100.00%  [OK]
        Payment: 100.00%  [OK]
   Order-Status: 100.00%  [OK]
       Delivery: 100.00%  [OK]
    Stock-Level: 100.00%  [OK]


                 1524.133 TpmC

何通りかテストをした結果、RDS単体とSpider+RDS*4で結果が入れ替わる条件がありました。

タイプ	データ数	接続数	RDS*1	Spider+RDS*4
medium	warehouse=100	10	2287.250 tpm	1524.133 tpm
medium	warehouse=100	20	2368.133 tpm	1564.283 tpm
medium	warehouse=150	20	1418.667 tpm	1540.333 tpm

作者の斯波さんにも伺ったのですが、Spiderの特性としてデータノードへのオーバーヘッドがある分、通常の単体DBに比べると最初はパフォーマンスが落ちますが、並列アクセス（同時アクセス数）が多くなり、データ数も多くなってくるとSpiderの優位性が出てくるとのことで、それが上の結果と一致しました。

細かなチューニングなどで結果も変わってくるかと思いますが、最初からSpider構成にするというよりも、負荷やデータ量の拡大に応じて移行していくのがよいのかなと感じました。
前述のように外部キーが使用できないなど、Spiderはいくつかの特殊な制限があるため、運用後のSpiderによるスケールアウトを視野に入れているのであれば、Spiderの制限などを見越したテーブル、データ設計なども考慮したほうが良いのかもしれません。

以上です。

2011年11月30日水曜日

シェルってなんじゃ？（mysqlに簡単ランダムデータ生成）

DBにランダムな大量データを入れ込みたいけれども、データの内容やテーブル同士の結合などあまり気にしなくて良い場合は、ちゃんとしたジェネレーターなどをインストールするよりもシェルでさくっと書いてしまいたい時があります。

ここではmysqlにシェルからINSERTし続けるシェルを書いてみたいと思います。

例として対象となるテーブルは以下のとおり

CREATE TABLE user (
 id int(11) NOT NULL AUTO_INCREMENT,
 name varchar(50) DEFAULT NULL,
 age int(11) DEFAULT NULL,
 description text,
 created_at datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB

ここで、idはオートインクリメント、created_atはNOW()を使用するのでいいとして、name, age, descriptionにランダムなデータを入れ込むためにランダム生成をします。

文字列のランダム

mkpasswdというパスワード生成用のランダム文字列の生成ツールで、expectパッケージに含まれています。
たとえば３文字以上の数字と５文字以上の大文字が必ず含まれる５０文字のランダム文字列が生成するには以下のようにします

$ mkpasswd -l 50 -d 3 -C 5
kDVoDyxqwd5csu0bfuk8dpoygyhwwersqoazoycvehsovsMjqS

mkpasswdは厳密にランダムさを求めたり使用文字のルールを決めることができるので便利ですが、とても重いので大量に生成するには向かない場合があります。ほかにもuuidgenというUUIDを作成するコマンドがあります。
これは全世界的に一意なIDを発行するためのツールです。

$ uuidgen
a62f9c8d-9d97-4b5d-a572-d83222837fc2

数値のランダム

数値をランダムに出力するにはexprが便利です。
たとえば1〜8までのランダム数値は以下のようにします。

$ expr 1 + $RANDOM % 8
3

これらをつかってランダムなデータをテーブルに入れるスクリプトを書いてみます。
mysqlへの投入はmysql -e "SQL文"で、コマンドから直にSQL実行できるので、それを利用します。

ランダムデータ生成スクリプト

insert_random.sh

#!/bin/sh

while ((1))
do
name=$(uuidgen | tr -d '-' | cut -c 1-10)
desc=$(mkpasswd -l 50 -s 0)
age=`expr 1 + $RANDOM % 1000`
mysql -u hoge_user cloudpack -phoge_pass -e "INSERT INTO user(name, age, description, created_at) VALUES('${name}', ${age}, '${desc}', NOW());"
echo -n "."
done;

exit 1

これを実行します。

$ sh ./insert_random.sh 
...............................................................................................................................................................................................................................................................................................................................................................................................

進捗がすすんでいるようです。
ちょっと別のターミナルセッションからデータを覗いてみます。

mysql> select * from user;
+----+------------+------+----------------------------------------------------+---------------------+
| id | name       | age  | description                                        | created_at          |
+----+------------+------+----------------------------------------------------+---------------------+
|  1 | 598d056309 |  896 | rkggeRoxaqvmhlcwhon2ordjhafdLoqdrs5ecaplouaqkccdfk | 2011-11-30 03:00:14 |
|  2 | 6ab7bf0029 |  487 | 8iOrolcltandneoxtsmwizx6shtreovoodulnmpjePdqgyrxxa | 2011-11-30 03:00:14 |
|  3 | c62df8dab5 |  464 | oxncixqdamctmeppmohmwkdayxzgw0rxRvwx3vbDhlyndqnlcv | 2011-11-30 03:00:14 |
|  4 | 63a9d01e41 |  609 | ilovfkcskrvdui2nHnumcfarlkn5iVaopabyymiafjiystbyvx | 2011-11-30 03:00:14 |
|  5 | e5abb35d2d |  677 | epkdylnwhlx9kophdOxcvcofqqheuljknsiivgon8cyovyAvaf | 2011-11-30 03:00:15 |
|  6 | 2888a4f6fd |  230 | ywqbCazysbakzweqfohli6oqhjuncohtyxplxp2Frcuhfacyqa | 2011-11-30 03:00:15 |

〜

| 2452 | 2b1e84b98d |  388 | kEheuvccvwusygulkfhig6qvnffxmnveahazmnaclcIbllcz5j | 2011-11-30 03:00:52 |
| 2453 | ea1ef37248 |   44 | feoeumwokgrvtypatzxfyfZg3ltvrnxwdEhzfveveykavitzl9 | 2011-11-30 03:00:52 |
| 2454 | 05a5341597 |  309 | chlpvjttq2axlvkrubjgubfdfhwzoatolbfddo6nqpgWgbkfPh | 2011-11-30 03:00:52 |
| 2455 | 8403fbafe9 |  655 | tdwi2egdtAucxroylarikvqdfhpxnwxeopqxtcufvqsix0Wekz | 2011-11-30 03:00:52 |
| 2456 | 471be293bc |  660 | lnKqpbueti3izbg1orceykiomjqocexezpkulqgkdhzFczyfze | 2011-11-30 03:00:52 |
| 2457 | 05628b4a7c |  180 | ueebrCqwn5brvjUqrkwcpg6qpjpkokkrtxvfczxjtcyqmrfpnf | 2011-11-30 03:00:52 |
| 2458 | 3688ea54b3 |   92 | wrpamv9QmycbccpuwqPnlsjdyiwcl5avyfgytdqllgtiljcwsc | 2011-11-30 03:00:52 |
+------+------------+------+----------------------------------------------------+---------------------+
2458 rows in set (0.01 sec)

おお、順調に投入されています。
このように、気軽に大量のデータをつくれ、また常にデータ投入が行われている状態にすることで開発の最初から負荷を気にすることもできます。
以上です。

統計ってなんじゃ？(mysqlで度数分布)

プロジェクトを運用していると、ユーザーのサービス利用傾向を知るためにデータの統計を依頼されることがあります。

たとえば、ゲームを例にすると、ユーザーがどのアイテムを何個持っているか、と聞かれた場合、一人ひとりのユーザーの持ち物をしらべるとなると、結局DBのダンプになってしまいます。
そういった場合、「何個もっているユーザーが何人いる」ということを調べることで、ユーザーの全体的な動向を知ることができます。

ここでは、０〜9個の人は何人、１0〜19個の人は何人、、、というように段階的に範囲を絞って、アイテムごとにMySQLから統計を採ってみたいと思います。このような統計の採り方を度数分布といいます。

対象となるテーブルは

●ユーザー

mysql> select id, name, age, created_at from user limit 5;
+----+------------+------+---------------------+
| id | name       | age  | created_at          |
+----+------------+------+---------------------+
|  1 | 598d056309 |  896 | 2011-11-30 03:00:14 |
|  2 | 6ab7bf0029 |  487 | 2011-11-30 03:00:14 |
|  3 | c62df8dab5 |  464 | 2011-11-30 03:00:14 |
|  4 | 63a9d01e41 |  609 | 2011-11-30 03:00:14 |
|  5 | e5abb35d2d |  677 | 2011-11-30 03:00:15 |
+----+------------+------+---------------------+
5 rows in set (0.00 sec)

●アイテム

mysql> select id,name from item;
+----+--------------------------+
| id | name                     |
+----+--------------------------+
|  1 | やくそう                 |
|  2 | どくけしそう             |
|  3 | ばんそうこう             |
|  4 | １００円ライター         |
|  5 | ビー玉                   |
|  6 | りんご                   |
|  7 | けしごむ                 |
|  8 | わゴム                   |
+----+--------------------------+
8 rows in set (0.00 sec)

●ユーザーの持ち物

mysql> select * from my_item limit 5;
+------------+---------+---------+---------------------+
| my_item_id | item_id | user_id | created_at          |
+------------+---------+---------+---------------------+
|          1 |       7 |     330 | 2011-11-30 01:33:17 |
|          2 |       6 |      68 | 2011-11-30 01:33:17 |
|          3 |       7 |     477 | 2011-11-30 01:33:17 |
|          4 |       5 |     554 | 2011-11-30 01:33:17 |
|          5 |       4 |     153 | 2011-11-30 01:33:17 |
+------------+---------+---------+---------------------+
5 rows in set (0.00 sec)

だとします。

アイテムごとに各ユーザーが持っている個数を取得するには以下のようにとれます。

select item_id, user_id, count(my_item_id) from my_item group by item_id, user_id;

ここで、例えば０〜９個までを持っているユーザーは、更にhaving句を使って以下のように絞られます。

select item_id, user_id, count(my_item_id) from my_item group by item_id, user_id having count(my_item_id) >= 0 AND count(my_item_id) < 10

オンラインゲームなどは保持数の上限などを設けている場合がほとんどです、例えばアイテムは最大100個しか
持てないという制限があるとします。
ここでは、アイテムの所持数を0〜９、10〜19、、、90〜100のように区切って、段階ごとに範囲を決めて抽出するとすると、以下のSQLになります。

mysql< select 
z.item_id,
z.name,
IFNULL(t1.cnt, 0) as "0=<10",
IFNULL(t2.cnt, 0) as "10=<20",
IFNULL(t3.cnt, 0) as "20=<30",
IFNULL(t4.cnt, 0) as "30=<40",
IFNULL(t5.cnt, 0) as "40=<50"
from
(select 
 id as item_id,
 name
 from 
 item
) z
LEFT OUTER JOIN
(
select a.item_id,count(a.user_id) as cnt from (select item_id, user_id, count(my_item_id) from my_item group by item_id, user_id having count(my_item_id) >= 0 AND count(my_item_id) < 10) a group by a.item_id
) t1 ON t1.item_id = z.item_id
LEFT OUTER JOIN(
select a.item_id,count(a.user_id) as cnt from (select item_id, user_id, count(my_item_id) from my_item group by item_id, user_id having count(item_id) >= 10 AND count(item_id) < 20) a group by a.item_id
) t2 ON t2.item_id = z.item_id
LEFT OUTER JOIN(
select a.item_id,count(a.user_id) as cnt from (select item_id, user_id, count(my_item_id) from my_item group by item_id, user_id having count(item_id) >= 20 AND count(item_id) < 30) a group by a.item_id
) t3 ON t3.item_id = z.item_id
LEFT OUTER JOIN(
select a.item_id,count(a.user_id) as cnt from (select item_id, user_id, count(my_item_id) from my_item group by item_id, user_id having count(item_id) >= 30 AND count(item_id) < 40) a group by a.item_id
) t4 ON t4.item_id = z.item_id
LEFT OUTER JOIN(
select a.item_id, count(a.user_id) as cnt from (select item_id, user_id, count(my_item_id) from my_item group by item_id, user_id having count(item_id) >= 40 AND count(item_id) < 50) a group by a.item_id
) t5 ON t5.item_id = z.item_id;
+---------+--------------------------+-------+--------+--------+--------+--------+
| item_id | name                     | 0=<10 | 10=<20 | 20=<30 | 30=<40 | 40=<50 |
+---------+--------------------------+-------+--------+--------+--------+--------+
|       1 | やくそう                 |  1336 |      0 |     16 |     52 |     21 |
|       2 | どくけしそう             |  1346 |      0 |     13 |     60 |     16 |
|       3 | ばんそうこう             |  1327 |      1 |     14 |     47 |     27 |
|       4 | １００円ライター         |  1355 |      0 |     18 |     47 |     24 |
|       5 | ビー玉                   |  1368 |      0 |     14 |     52 |     24 |
|       6 | りんご                   |  1334 |      1 |     21 |     49 |     19 |
|       7 | けしごむ                 |  1354 |      1 |     18 |     54 |     17 |
|       8 | わゴム                   |  1329 |      1 |     23 |     42 |     23 |
+---------+--------------------------+-------+--------+--------+--------+--------+

少し冗長になってしまいましたが、もっと他にスマートなやり方があるのかも知れません。他にいい方法も浮かばなかったのですが、とりあえず抽出することができました。

本日は以上です。

2011年10月14日金曜日

EC2でMySQL(世界編2 Spiderとレプリケーションで高速負荷分散)

前回のつづきです。

前回はSpiderとレプリケーションで、リージョン間での高速なデータ分散を紹介しました。前回で少し触れましたが、この構成ではたとえばEU側のDBにJPのデータの書き込みには対応できません。

それを解決し、どちらのリージョンからもすべての商品を投入できるようにしてみたいと思います。
構成は以下のとおりです。

SpiderノードのDBで、書き込みと読み込みでそれぞれ別々のテーブルを用意し、別々のデータノードにそれぞれシャーディングを行います。

データノードの作成とレプリケーションの設定は前回と同じなので割愛します。

Spiderノード

spider_ja

CREATE TABLE item_r (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(256) DEFAULT NULL,
  shop_id int(11) DEFAULT NULL,
  region_id int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (id, region_id)
) ENGINE=SPIDER DEFAULT CHARSET=utf8
CONNECTION=' table "item", user "remote_user", password "remote_pass" '
PARTITION BY LIST (region_id) (
  PARTITION ja_ja VALUES IN (1) COMMENT = 'host "111.111.0.1", port "3306"',
  PARTITION ja_eu VALUES IN (2) COMMENT = 'host "111.111.0.2", port "3306"'
)
;

CREATE TABLE item_w (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(256) DEFAULT NULL,
  shop_id int(11) DEFAULT NULL,
  region_id int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (id, region_id)
) ENGINE=SPIDER DEFAULT CHARSET=utf8
CONNECTION=' table "item", user "remote_user", password "remote_pass" '
PARTITION BY LIST (region_id) (
  PARTITION ja_ja VALUES IN (1) COMMENT = 'host "111.111.0.1", port "3306"',
  PARTITION eu_eu VALUES IN (2) COMMENT = 'host "222.222.0.2", port "3306"'
)
;

spider_eu

CREATE TABLE item_r (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(256) DEFAULT NULL,
  shop_id int(11) DEFAULT NULL,
  region_id int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (id, region_id)
) ENGINE=SPIDER DEFAULT CHARSET=utf8
CONNECTION=' table "item", user "remote_user", password "remote_pass" '
PARTITION BY LIST (region_id) (
  PARTITION eu_ja VALUES IN (1) COMMENT = 'host "222.222.0.1", port "3306"',
  PARTITION eu_eu VALUES IN (2) COMMENT = 'host "222.222.0.2", port "3306"'
)
;

CREATE TABLE item_w (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(256) DEFAULT NULL,
  shop_id int(11) DEFAULT NULL,
  region_id int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (id, region_id)
) ENGINE=SPIDER DEFAULT CHARSET=utf8
CONNECTION=' table "item", user "remote_user", password "remote_pass" '
PARTITION BY LIST (region_id) (
  PARTITION ja_ja VALUES IN (1) COMMENT = 'host "111.111.0.1", port "3306"',
  PARTITION eu_eu VALUES IN (2) COMMENT = 'host "222.222.0.2", port "3306"'
)
;

item_wに対して書き込んでみます。

mysql> INSERT INTO item_w (name, shop_id, region_id) VALUES('おいしい水', 1, 1), ('おしゃれなバッグ', 2, 1), ('Cool Watch', 3, 2),('Cute Ring', 4, 2);
Query OK, 4 rows affected (1.95 sec)
Records: 4  Duplicates: 0  Warnings: 0

書き込みが成功しました。
item_wで参照すると、

mysql> select * from item_w;
+----+--------------------------+---------+-----------+
| id | name                     | shop_id | region_id |
+----+--------------------------+---------+-----------+
|  1 | おいしい水          |       1 |         1 |
|  2 | おしゃれなバッグ |       2 |         1 |
|  3 | Cool Watch               |       3 |         2 |
|  4 | Cute Ring                |       4 |         2 |
+----+--------------------------+---------+-----------+
4 rows in set (1.96 sec)

正しく投入されていることがわかりますが、レスポンスはやはり悪いです。

そこでitem_rで参照してみます。

mysql> select * from item_r;
+----+--------------------------+---------+-----------+
| id | name                     | shop_id | region_id |
+----+--------------------------+---------+-----------+
|  1 | おいしい水          |       1 |         1 |
|  2 | おしゃれなバッグ |       2 |         1 |
|  3 | Cool Watch               |       3 |         2 |
|  4 | Cute Ring                |       4 |         2 |
+----+--------------------------+---------+-----------+
4 rows in set (0.01 sec)

高速で読み込みできています。

これで、どちらのリージョンからも全リージョン用のデータの書き込みができ、なおかつ読み込みスピードを維持することが出来ました。

書き込み時は他方のリージョンにSpiderでアクセスするので、少し時間がかかります。
前回のように基本はJPのデータはJPリージョンから投入するようにルール化すれば書き込みスピードが損なわれることはありません。
すべてmicroインスタンスでの確認ということもあり、もしかしたらSpiderのサーバーやテーブルパラメータをいじることで他リージョンへのSpiderアクセスも速くなるかもしれません。それは今後の課題にしたいと思います。

以上です。

2011年10月13日木曜日

EC2でMySQL(世界編1 Spiderとレプリケーションで世界進出)

ふたたびSpiderの話題です。

WEBアプリを世界展開する場合、要件の１つとして、世界各国のユーザーは全てのデータを参照する必要があるが、データの登録は各国、地域の担当者が各自のデータを登録したい、というケースが多いと思います。

たとえば、商品のサイトで、EUと日本にそれぞれ管理者がいるとします。
その場合のユースケースとしては、

EUの管理者はEUの商品を登録する
日本の管理者は日本の商品を登録する
ユーザーは全ての商品を閲覧できる

となります。

このとき、DBがどのリージョンにあるかによって、ユーザーや管理者の間で、レスポンススポードに差がでます。
DBを日本に置くとEUのユーザーや管理者には遅いシステムとなってしまい、EUに置くとその逆になります。
そのため、各リージョンに全てのリージョンで登録した商品データを用意し、ユーザーのロケーションに関わらず、DBアクセスをユーザーと同じリージョン内で済ませる必要があります。管理者がアクセスするDBも管理者のいるリージョン内で済ませる必要があります。

これをSpiderとレプリケーションで実現してみます。
上図をみると想像できるかも知れませんが、方法としてはEUとJPそれぞれのリージョンに属する商品マスタに対して書き込み、EUではJPの、JPはEUのスレーブをもち、それぞれをSpiderでシャーディングします。
DBの図で表すと、下記のようになります。

それでは実際に試してみます。

データノード

CREATE TABLE item (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(256) DEFAULT NULL,
  shop_id int(11) DEFAULT NULL,
  region_id int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (id, region_id)
) ENGINE=SPIDER DEFAULT CHARSET=utf8
;

Spiderノード

spider_ja

CREATE TABLE item (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(256) DEFAULT NULL,
  shop_id int(11) DEFAULT NULL,
  region_id int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (id, region_id)
) ENGINE=SPIDER DEFAULT CHARSET=utf8
CONNECTION=' table "item", user "remote_user", password "remote_pass" '
PARTITION BY LIST (region_id) (
  PARTITION ja_ja VALUES IN (1) COMMENT = 'host "111.111.0.1", port "3306"',
  PARTITION ja_eu VALUES IN (2) COMMENT = 'host "111.111.0.2", port "3306"'
)
;

spider_eu

CREATE TABLE item (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(256) DEFAULT NULL,
  shop_id int(11) DEFAULT NULL,
  region_id int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (id, region_id)
) ENGINE=SPIDER DEFAULT CHARSET=utf8
CONNECTION=' table "item", user "remote_user", password "remote_pass" '
PARTITION BY LIST (region_id) (
  PARTITION eu_ja VALUES IN (1) COMMENT = 'host "222.222.0.1", port "3306"',
  PARTITION eu_eu VALUES IN (2) COMMENT = 'host "222.222.0.2", port "3306"'
)
;

レプリケーション

ja-eu

$ mysql -u root
mysql> CHANGE MASTER TO
  MASTER_HOST='222.222.0.2',
  MASTER_USER='remote_user',
  MASTER_PASSWORD='remote_user',
  MASTER_PORT=3306;

eu-ja

$ mysql -u root
mysql> CHANGE MASTER TO
  MASTER_HOST='111.111.0.1',
  MASTER_USER='remote_user',
  MASTER_PASSWORD='remote_user',
  MASTER_PORT=3306;

※各ノード間のアクセス権限やサーバーIDの登録、セキュリティグループの設定などは以前の記事に書いたので割愛します

ポイントはregion_id(1:ja, 2:eu)のLISTパーティションにするため、プライマリキーをidとregion_idの複合キーにするところです。INSERT時には、登録したリージョンの region_idをセットします。
※登録したリージョンではないregion_idをセットするとパーティションに含まれず、シャーディング対象にならないので、注意が必要です。

spider-ja

mysql> INSERT INTO item (name, shop_id, region_id) VALUES('おいしい水', 1, 1), ('おしゃれなバッグ', 2, 1);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

spider-eu

mysql> insert into item(name,shop_id,region_id) values('Cool Watch', 3, 2),('Cute Ring', 4, 2);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

すると、
それぞれのspiderノードですべてのitemがselectできます。

mysql> select * from item;
+----+--------------------------+---------+-----------+
| id | name                     | shop_id | region_id |
+----+--------------------------+---------+-----------+
|  1 | おいしい水          |       1 |         1 |
|  2 | おしゃれなバッグ |       2 |         1 |
|  1 | Cool Watch               |       3 |         2 |
|  2 | Cute Ring                |       4 |         2 |
+----+--------------------------+---------+-----------+
4 rows in set (0.01 sec)

このように、リージョンが分かれていても、アクセス速度を気にすること無くシャーディングができました。
以上です。

2011年9月22日木曜日

EC2でMySQL(運用編 VP＋Spiderで無停止負荷分散)

前回はVPでテーブルのALTERを行いましたが、ほとんど同様に通常のInnoDBテーブルをSpiderシャーディングに無停止で移行することも可能です。さっそくやってみます。初期テーブルは以下のとおりです。

mysql> create table gift(
  id int auto_increment,
  name varchar(255),
  description text,
  created_at datetime not null,
  primary key(id)
)engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

これをSpider化していきますが、今回は定期的にデータを投入しながらSpider化を行ってみます。まず、以下のようなシェルを実行し、常にデータの投入がされている状態をつくります。

$ vi insert2gift.sh

$ sh insert2gift.sh
................................

それではVPをつかって無停止でSpiderに移行を行なってみたいと思います。基本的には、前回と同様です。切り替え用のダミーと２つのデータノードをもつ新規Spiderテーブル、それらを束ねるVPテーブルを用意します。

データノードの設定

mysql> GRANT ALL PRIVILEGES ON *.* TO 'xxxxxxxxxxxxxx'@localhost IDENTIFIED BY 'xxxxxxxxxxxxxx';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'xxxxxxxxxxxxxx'@'%' IDENTIFIED BY 'xxxxxxxxxxxxxx';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'xxxxxxxxxxxxxx'@'123.123.123.123' IDENTIFIED BY 'xxxxxxxxxxxxxx';
Query OK, 0 rows affected (0.00 sec)

mysql> use cloudpack;
Database changed

mysql> create table gift(
  id int auto_increment,
  name varchar(255),
  description text,
  created_at datetime not null,
  primary key(id)
)engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

SpiderノードのSpider、ダミー、VPテーブルの設定

mysql> create table gift_new(
  id int auto_increment,
  name varchar(255),
  description text,
  created_at datetime not null,
  primary key(id)
) engine = Spider DEFAULT CHARSET=utf8
CONNECTION ' table "gift", user "xxxxxxxxxxxxx", password "xxxxxxxxxxxxx" '
PARTITION BY LIST(MOD(id, 2)) (
    PARTITION hostb VALUES IN (0) comment 'host "111.111.111.111", port "3306"',
    PARTITION hostc VALUES IN (1) comment 'host "222.222.222.222", port "3306"'
);
Query OK, 0 rows affected (0.02 sec)

mysql> create table gift_dummy like gift;
Query OK, 0 rows affected (0.01 sec)

mysql> create table gift_vp(
   id int auto_increment,
   name varchar(255),
   description text,
   created_at datetime not null,
   primary key(id)
 )engine=vp
 comment 'table_name_list "gift_dummy gift_new", cit "2", cil "2", ctm "1", ist "1", zru "1"';
Query OK, 0 rows affected (0.03 sec)

この時点では、以下のイメージのような構成になっています。

テーブルのリネーム

ここも前回同様テーブルのリネームを行い、接続先をVPテーブルに向けます。

mysql> rename table 
  gift_dummy to gift_delete,  
  gift to gift_dummy,  
  gift_vp to gift;
Query OK, 0 rows affected (0.03 sec)

データのコピー

ここも前回同様です。

mysql> select vp_copy_tables('gift', 'gift_dummy', 'gift_new');
Query OK, 0 rows affected (0.01 sec)

テーブルの再リネーム

コピーが完了したら、テーブルのリネームを再度行い、移行先のSpiderテーブルをgiftにします。

mysql> rename table 
  gift to gift_vp,  
  gift_new to gift;
Query OK, 0 rows affected (0.01 sec)

不要テーブルの削除

最後に、必要のなくなったテーブルを削除します。

mysql> drop table 
  gift_dummy,
  gift_vp, 
  gift_delete;
Query OK, 0 rows affected (0.00 sec)

これで、移行が完了しました。それでは、実際にテーブルの内容を見てみましょう。

host A

mysql> select * from gift order by id;
+------+----------------------------------+----------------------------------+---------------------+
| id   | name                             | description                      | created_at          |
+------+----------------------------------+----------------------------------+---------------------+
|   1  | d84c7d13d56e48999f6e42396bb0d6b8 | 57b6f87681df4141953b63cd6ee74... | 2011-09-21 22:35:23 |
|   2  | fc52fbdee1904e40a92711bc3ff5b53b | 4fe1116428e142369ab419d603a8b... | 2011-09-21 22:35:23 |
|   3  | 936917c0e7b246b380573654c80863e1 | 08cf01342f1149b5a7fa15f2df5b6... | 2011-09-21 22:35:24 |
|   4  | 5d630544409e4ef98ece5b489cd5aff5 | c3c1848832d04b69bc9f9c1bd7249... | 2011-09-21 22:35:23 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| 5429 | 0462eaf7646b4855a993a49128ba3f4d | 0d07cdbac3c14723b25bc1a1bc74a... | 2011-09-22 01:07:42 |
| 5430 | b67fdb0b633c40829dd812c0358f5ccb | 85a310e34668427ab392b07fe36f8... | 2011-09-22 01:07:49 |
| 5431 | 431a0be6d0d94223bad8405d0011560e | f78b63b0ed5c42d0a947dc3db6bcb... | 2011-09-22 01:07:56 |
| 5432 | 4a0f9c4dcbaa42a99aeb014232091551 | 9ceb2dce039e49268280560631578... | 2011-09-22 01:08:02 |
+------+----------------------------------+----------------------------------+---------------------+
5007 rows in set (0.06 sec)

host B

mysql> select * from gift order by id;
+------+----------------------------------+----------------------------------+---------------------+
| id   | name                             | description                      | created_at          |
+------+----------------------------------+----------------------------------+---------------------+
|   1  | d84c7d13d56e48999f6e42396bb0d6b8 | 57b6f87681df4141953b63cd6ee74... | 2011-09-21 22:35:23 |
|   3  | 936917c0e7b246b380573654c80863e1 | 08cf01342f1149b5a7fa15f2df5b6... | 2011-09-21 22:35:24 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| 5429 | 0462eaf7646b4855a993a49128ba3f4d | 0d07cdbac3c14723b25bc1a1bc74a... | 2011-09-22 01:07:42 |
| 5431 | 431a0be6d0d94223bad8405d0011560e | f78b63b0ed5c42d0a947dc3db6bcb... | 2011-09-22 01:07:56 |
+------+----------------------------------+----------------------------------+---------------------+
2503 rows in set (0.01 sec)

host C

mysql> select * from gift order by id;
+------+----------------------------------+----------------------------------+---------------------+
| id   | name                             | description                      | created_at          |
+------+----------------------------------+----------------------------------+---------------------+
|   2  | fc52fbdee1904e40a92711bc3ff5b53b | 4fe1116428e142369ab419d603a8b... | 2011-09-21 22:35:23 |
|   4  | 5d630544409e4ef98ece5b489cd5aff5 | c3c1848832d04b69bc9f9c1bd7249... | 2011-09-21 22:35:23 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| 5430 | b67fdb0b633c40829dd812c0358f5ccb | 85a310e34668427ab392b07fe36f8... | 2011-09-22 01:07:49 |
| 5432 | 4a0f9c4dcbaa42a99aeb014232091551 | 9ceb2dce039e49268280560631578... | 2011-09-22 01:08:02 |
+------+----------------------------------+----------------------------------+---------------------+
2504 rows in set (0.00 sec)

このように、元々入っていたデータと、移行中に投入されたデータがきれいにシャーディングされていることがわかります。
本日はここまで。

2011年9月15日木曜日

EC2でMySQL(運用編 VPで無停止ALTER)

WEBサイトを運用していると、仕様の追加変更やパフォーマンス対策など、さまざまな理由で、テーブルの構造を途中で変更する必要が出てきます。そのような場合、通常は一時的にWEBサイトを停止して、メンテナンス期間中にDBにALTERをかけます。

ですが、そういった機会が頻繁に得られない場合などは、稼働中に変更しなければならないケースもあるかと思います。そこで、前回紹介したVPを利用して、無停止でテーブルにALTERをかけてみたいと思います。

初期のテーブルが以下の通りだとすると、

create table gift(
  id int auto_increment,
  gift_name varchar(255),
  description text,
  created_at datetime not null,
  primary key(id)
)engine=InnoDB;

ここに削除日(deleted_at)を追加し、下記のように変更したいとします。

create table gift(
  id int auto_increment,
  gift_name varchar(255),
  description text,
  created_at datetime not null,
  deleted_at datetime,
  primary key(id)
)engine=InnoDB;

それでは、VPをつかって元のテーブルと新規テーブルを入れ替えてみます。
まず初期イメージは以下の通りです。

新しいスキーマのテーブルと、入れ替え用のダミーのテーブルを用意し、VPテーブルで新スキーマテーブルとダミーテーブルをつなげておきます。

mysql> create table gift_new(
   id int auto_increment,
   gift_name varchar(255),
   description text,
   created_at datetime not null,
   deleted_at datetime,
   primary key(id)
 )engine=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> create table gift_dummy like gift;
Query OK, 0 rows affected (0.01 sec)

mysql> create table gift_vp(
   id int auto_increment,
   gift_name varchar(255),
   description text,
   created_at datetime not null,
   primary key(id)
 )engine=vp
 comment 'table_name_list "gift_dummy gift_new", cit "2", cil "2", ctm "1", ist "1", zru "1"';
Query OK, 0 rows affected (0.01 sec)

ここで、VPテーブルには、Spiderと同様、基本設定以外にもさまざまなオプションがあり、ここでは、cit, cil, ctm, ist, zruというオプション値を指定しています。これらはそれぞれ以下のような意味があります。(マニュアルから抜粋)

・choose_ignore_table_list(cit)
　検索時に利用するテーブルの選択から指定した番号のテーブルを除外する。
　デフォルト値は指定なし。

・choose_ignore_table_list_for_lock(cil)
　ロック付検索時に利用するテーブルの選択から指定した番号のテーブルを除外する。
　更新は行われる。
　デフォルト値は指定なし。

・choose_table_mode(ctm)
　検索時に利用するテーブルの選択モード。
  サーバパラメータvp_choose_table_modeが設定されている場合は、
　そちらが優先される。
   0：最適化モード。
   1：「table_list」の前からの記載順に利用するテーブルを決定する。
　デフォルト値は 0

・infomation_source_table(ist)
　テーブルステータス取得のモード。
   0 ：全ての子テーブルからテーブルステータス取得を行う。
   1-：指定した子テーブルからのみテーブルステータス取得を行う。
　デフォルト値は 0

・zero_record_update_mode(zru)
　0件更新の際のモード。
   0：何もしない。
   1：choose_ignore_table_list_for_lockの対象子テーブルであった場合は、
      insertする。
　デフォルト値は 0

つまり、cit "2", cil "2", ctm "1", ist "1", zru "1"　という設定は、意味として、

VPテーブルに対して検索されたときにgift_newは使用しない
VPテーブルに対して検索されたときにgift_dummyを使用する
VPテーブルのステータスチェックにはgift_dummyのステータスを使用する
VPテーブルの更新対象が0件であった場合は、INSERTする

ということを示します。

次に、各テーブルをリネームします。 gift_dummyは必要なくなったのでgift_deleteに、元のgiftをVPの接続先であるgift_dummyに、VPテーブルのgift_vpをgiftに変更します。これにより、VPテーブルの接続先テーブルが、オリジナルのテーブルと新スキーマのテーブルに切り替わり、アプリケーションからのアクセスはVPテーブルに瞬時に切り替わります。

mysql> rename table 
  gift_dummy to gift_delete,  
  gift to gift_dummy,  
  gift_vp to gift;
Query OK, 0 rows affected (0.03 sec)

vp_copy_tables()を実行します。vp_copy_tablesはVPに付属されているUDFで、VPテーブルを介して、子テーブル間でデータコピーを行います。コピー元テーブルリストとコピー先テーブルリストのテーブルが、指定した親テーブルからのみ更新される場合、コピー中にそのVPテーブルに行われた更新もコピー先にコピーされるため、テーブルへの更新を止めずにコピーすることができます。

mysql> select vp_copy_tables('gift', 'gift_dummy', 'gift_new');

コピーが完了したら、テーブルのリネームを再度行います。新スキーマのgift_newをgiftに、giftだったVPテーブルをgift_vpに変更します。

mysql> rename table 
  gift to gift_vp,  
  gift_new to gift;

最後に、必要のなくなったテーブルを削除します。

mysql> drop table 
  gift_dummy,
  gift_vp, 
  gift_delete;

こうして、データアクセスをさせたまま、テーブル定義の変更とデータ移行をすることができました。
本日はここまで。

2011年9月12日月曜日

EC2でMySQL(VP編 VPってなんじゃ？)

今日はVPの話です。VPとはVertical Partitioning（垂直パーティショニング）の略で、Spiderと同じく斯波建徳さんの作ったMySQLのストレージエンジンです。Spiderの姉妹品といったところでしょうか。
このVPは、１つのテーブルを複数のテーブルにカラム分割します。

イメージとしては以下のような形です。

VIEWに似ていますが、

INSERTできる
複数の分割先のテーブル（子テーブル）でカラムの重複が可能。

などの点で、VIEWと異なりますが、
これだけの違いでさまざまなことが可能になります。

VPもSpiderと同じくCommentを利用して設定します。
インストール方法は、Spider編1で紹介したSpiderのバイナリにVPも含まれているので、そちらを参照してください。
まずは試してみましょう。
たとえば以下のようなテーブルがあるとすると、

create table gift(
  id int auto_increment,
  shop_id int,
  country_id int,
  gift_type int,
  gift_name varchar(255),
  description text,
  created_at datetime,
  primary key(id),
  key shop_idx(shop_id),
  key country_idx(country_id)
)engine=InnoDB;

は、以下のように分割できます。

create table gift_key(
  id int auto_increment,
  shop_id int,
  country_id int,
  gift_type int,
  gift_name varchar(255),
  primary key(id),
  key shop_idx(shop_id),
  key country_idx(country_id)
)engine=InnoDB;

create table gift_detail(
  id int,
  gift_type int,
  gift_name varchar(255),
  description text,
  created_at datetime,
  primary key(id)
)engine=InnoDB;

create table gift(
  id int auto_increment,
  shop_id int,
  country_id int,
  gift_type int,
  gift_name varchar(255),
  description text,
  created_at datetime,
  primary key(id),
  key shop_idx(shop_id),
  key country_idx(country_id)
)engine=VP
comment 'table_name_list "gift_key gift_detail"';

ここでVP化したgiftテーブルにデータを投入してみます。

mysql> INSERT INTO gift (shop_id, country_id, gift_type, gift_name, description, created_at)
    -> VALUES (1, 103, 1, 'ハム', 'ハムの人になれます。', CURRENT_TIMESTAMP);
Query OK, 1 row affected (0.03 sec)

普通にINSERTできました。
また、それぞれの子テーブルに重複したカラムも値が投入され、かつgiftテーブルから１つのテーブルのようにSELECTできることがわかります。

mysql> select * from gift_key;
+----+---------+------------+-----------+-----------+
| id | shop_id | country_id | gift_type | gift_name |
+----+---------+------------+-----------+-----------+
|  1 |       1 |        103 |         1 | ハム    |
+----+---------+------------+-----------+-----------+
1 row in set (0.00 sec)

mysql> select * from gift_detail;
+----+-----------+-----------+--------------------------------+---------------------+
| id | gift_type | gift_name | description                    | created_at          |
+----+-----------+-----------+--------------------------------+---------------------+
|  1 |         1 | ハム    | ハムの人になれます。 | 2011-09-12 22:10:16 |
+----+-----------+-----------+--------------------------------+---------------------+
1 row in set (0.00 sec)

mysql> select * from gift;
+----+---------+------------+-----------+-----------+--------------------------------+---------------------+
| id | shop_id | country_id | gift_type | gift_name | description                    | created_at          |
+----+---------+------------+-----------+-----------+--------------------------------+---------------------+
|  1 |       1 |        103 |         1 | ハム    | ハムの人になれます。 | 2011-09-12 22:10:16 |
+----+---------+------------+-----------+-----------+--------------------------------+---------------------+
1 row in set (0.00 sec)

今後いくつかVPの活用の仕方を紹介できればと思います。

以上です。

2011年8月31日水曜日

EC2でMySQL(Spider編3 Spiderの先にSpider)

EC2はあまり関係なくなってきていますが、Spiderの話題はまだ続きます。
Spiderの特性や構成を考えていくと、Spiderで分割するためのパーティショニングにより、さまざまな分割方法を考えることができます。
また、データが増加していくと、分散した先でもさらに負荷軽減が必要になる可能性も考えられます。

Spider編1で少し触れましたが、Spiderはストレージエンジンであり、リンク先のテーブルのストレージエンジンに制限はありません。
ここで、Spiderのリンク先にSpiderテーブルを指定したらどうなるか実験してみました。

構成は以下のとおりです。
ここでは全てTokyoリージョン内とします。
またIPは仮のものです。

SpiderノードA(111.111.0.1)
データノードA-1(111.111.0.2)
SpiderノードB(111.111.0.3)
データノードB-1(111.111.0.4)
データノードB-2(111.111.0.5)

SpiderノードBはSpiderノードAのデータノードとしての振る舞い、また一方でデータノードB-1,B-2のSpiderノードとしても振る舞います。
インストールやデータベースの作成、セキュリティグループの設定など基本的な部分は、前回、前々回と同じため省きます。

データノードの設定

SpiderノードA、SpiderノードB以外の全てのデータノードでmemberテーブルを作成します。

# mysql -u root
mysql> use cloudpack
Database changed
mysql> create table member(
id int(11) auto_increment,
name varchar(256),
primary key(id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

データノードA-1、SpiderノードBではSpiderノードAを許可します。

mysql> GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@"spider-a" IDENTIFIED BY 'remote_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@"111.111.0.1" IDENTIFIED BY 'remote_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@"ec2-111-111-0-1.ap-northeast-1.compute.amazonaws.com" IDENTIFIED BY 'remote_pass';

データノードB-1、B-2ではSpiderノードBを許可します。

mysql> GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@"spider-b" IDENTIFIED BY 'remote_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@"111.111.0.3" IDENTIFIED BY 'remote_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@"ec2-111-111-0-3.ap-northeast-1.compute.amazonaws.com" IDENTIFIED BY 'remote_pass';

SpiderノードAの設定

まず最終的に全てを束ねるSpiderノードAを設定します。
データノードA-1とSpiderノードBにリンクを張りますが、
ここでのパーティショニングはRANGEパーティションを採用します。
idの1～5がデータノードA-1、6～10がSpiderノードBに流れるように設定しました。

mysql> create table member(
id int(11) auto_increment,
name varchar(256),
primary key(id)
) engine = Spider DEFAULT CHARSET=utf8
CONNECTION ' table "member", user "remote_user", password "remote_pass" '
PARTITION BY RANGE (id) (
    PARTITION data-a1 VALUES LESS THAN (6) comment 'host "111.111.0.2", port "3306"',
    PARTITION spider-b VALUES LESS THAN (11) comment 'host "111.111.0.3", port "3306"'
);

SpiderノードBの設定

次にSpiderノードBを設定します。
データノードB-1,B-2を束ねますが、
ここではKEYパーティションで分散させます。

mysql> create table member(
id int(11) auto_increment,
name varchar(256),
primary key(id)
) engine = Spider DEFAULT CHARSET=utf8
CONNECTION ' table "member", user "remote_user", password "remote_pass" '
PARTITION BY KEY() (
    PARTITION data-b1 comment 'host "111.111.0.4", port "3306"',
    PARTITION data-b2 comment 'host "111.111.0.5", port "3306"'
);

これで設定は終了です。

確認

それではSpiderノードAで以下のように、10行追加してみます。

# mysql -u root
mysql> use cloudpack
Database changed
mysql> INSERT INTO member (name) VALUES ('ichiro'),('jiro'),('sub-Low'),('shiro'),('goro'),('rokuro'),('shichro'),('hachiro'),('kuro'),('juro');
Query OK, 10 rows affected (0.03 sec)
Records: 10  Duplicates: 0  Warnings: 0

SELECTすると正常に10件投入されていることが確認できます。

mysql> select * from member order by id;
+----+---------+
| id | name    |
+----+---------+
|  1 | ichiro  |
|  2 | jiro    |
|  3 | sub-Low |
|  4 | shiro   |
|  5 | goro    |
|  6 | rokuro  |
|  7 | shichro |
|  8 | hachiro |
|  9 | kuro    |
| 10 | juro    |
+----+---------+
10 rows in set (0.00 sec)

それでは、各ノードにどのように分配されているか見てみましょう。

●データノードA-1
ここにはRANGEパーティションのルールどおり、idの1～5が投入されています。

mysql> select * from member order by id;
+----+---------+
| id | name    |
+----+---------+
|  1 | ichiro  |
|  2 | jiro    |
|  3 | sub-Low |
|  4 | shiro   |
|  5 | goro    |
+----+---------+
5 rows in set (0.00 sec)

●SpiderノードB
こちらもRANGEパーティションのルールどおり、idの6～10が投入されています。

mysql> select * from member order by id;
+----+---------+
| id | name    |
+----+---------+
|  6 | rokuro  |
|  7 | shichro |
|  8 | hachiro |
|  9 | kuro    |
| 10 | juro    |
+----+---------+
5 rows in set (0.01 sec)

しかし、これは別のSpiderノードでもあり、データノードB-1,B-2にさらに分散されています。
さらに、その分散先を見てみます。

●データノードB-1

mysql> select * from member order by id;
+----+---------+
| id | name    |
+----+---------+
|  7 | shichro |
|  9 | kuro    |
+----+---------+
2 rows in set (0.00 sec)

●データノードB-2

mysql> select * from member order by id;
+----+---------+
| id | name    |
+----+---------+
|  6 | rokuro  |
|  8 | hachiro |
| 10 | juro    |
+----+---------+
3 rows in set (0.00 sec)

SpiderノードBの先で、さらにKEYパーティショニングによって、idのハッシュ値で均等に分散されていることがわかります。

このように、途中で負荷が増えても、さらに分散させて1ノードあたりの負荷を減らすことも可能です。

以上です。

登録: 投稿 (Atom)