森の夢

mysqlパーティショニングのまとめ① - 設定・再コンパイル
mysqlパーティショニングのまとめ② - パーティショニングのタイプ
mysqlパーティショニングのまとめ③ - パフォーマンス

さて、前回まではパーティショニングの設定やパーティショニングのタイプについて述べてきたが、
ここからはパフォーマンスの話をしていく。

サンプルテーブル

まず、以下のサンプルテーブルを用意した。
それぞれのテーブルに2倍にあたる245746件の郵政省のデータをいれてある。

・postal_normal

CREATE TABLE postal_normal (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` int(7) NOT NULL,
  ken_kana varchar(255) DEFAULT NULL,
  ctiy_kana varchar(255) DEFAULT NULL,
  town_kana varchar(255) DEFAULT NULL,
  ken varchar(255) DEFAULT NULL,
  citiy varchar(255) DEFAULT NULL,
  town varchar(255) DEFAULT NULL,
  flg1 tinyint(1) NOT NULL,
  flg2 tinyint(1) NOT NULL,
  flg3 tinyint(1) NOT NULL,
  flg4 tinyint(1) NOT NULL,
  flg5 tinyint(1) NOT NULL,
  flg6 tinyint(1) NOT NULL,
  entry timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY town (town),
  KEY flg1 (flg1),
  KEY entry (entry),
  KEY `code` (`code`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

・postal_hash16

CREATE TABLE postal_hash16 (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` int(7) NOT NULL,
  ken_kana varchar(255) DEFAULT NULL,
  ctiy_kana varchar(255) DEFAULT NULL,
  town_kana varchar(255) DEFAULT NULL,
  ken varchar(255) DEFAULT NULL,
  citiy varchar(255) DEFAULT NULL,
  town varchar(255) DEFAULT NULL,
  flg1 tinyint(1) NOT NULL,
  flg2 tinyint(1) NOT NULL,
  flg3 tinyint(1) NOT NULL,
  flg4 tinyint(1) NOT NULL,
  flg5 tinyint(1) NOT NULL,
  flg6 tinyint(1) NOT NULL,
  entry timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY town (town),
  KEY flg1 (flg1),
  KEY entry (entry),
  KEY `code` (`code`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8
PARTITION BY LINEAR HASH (id) PARTITIONS 16;

テストケース

上記のデータに対して、以下のテストを行う。

• id=240000で検索
• id IN (240000, 240001, 240002, 240003, 240004, 240005, 240006, 240007)で検索
• code=1000000で検索
• ken LIKE '%北海道%'
• １レコード追加

id=240000で検索

・パーティショニング

select * from postal_hash16 where id = 240000;
1 row in set (0.02 sec)

・ノーマル

select * from postal_normal where id = 240000;
1 row in set (0.01 sec)

さすがに、パーティションしていないとはいえインデックスの効いたたかが24万件なので差はあまり無いと思われる。
ただ、パーティションしてあるほうはたかだか1万ちょっとからの検索になるはずなので早いはずだが・・・

id IN (240000, 240001, 240002, 240003, 240004, 240005, 240006, 240007)で検索

・パーティショニング

select * from postal_hash16 where id IN (240000, 240001, 240002, 240003, 240004, 240005, 240006, 240007);
8 rows in set (0.00 sec);

・ノーマル

select * from postal_normal where id  IN (240000, 240001, 240002, 240003, 240004, 240005, 240006, 240007);
8 rows in set (0.00 sec)

うーん・・・検索件数を増やしてもスピードは変わらず・・・
やはり24万件程度では差がでないということか。

code=1000000で検索

・パーティショニング

 select * from postal_hash16 where code = 1000000;
2 rows in set (0.26 sec)

・ノーマル

 select * from postal_normal where code = 1000000;
2 rows in set (0.03 sec)

これは違いがはっきり出た。
やはりパーティションしているほうが遅いということである。この場合インデックスは効いていないのかも・・・。

ken LIKE '%北海道%'で検索

・パーティショニング

 select * from postal_hash16 where ken LIKE '%北海道%'
16454 rows in set (2.34 sec)

・ノーマル

 select * from postal_normal where ken LIKE '%北海道%'
16454 rows in set (1.74 sec)

なるほど、インデックスの効いていないカラムに対しては、
ノーマルもやはり遅いが、やはりパーティショニングしている方が遅い結果になった。

１レコード追加

・パーティショニング

INSERT INTO `test2`.`postal_hash16` (`code`, `ken_kana`, `ctiy_kana`, `town_kana`, `ken`, `citiy`, `town`, `flg1`, `flg2`, `flg3`, `flg4`, `flg5`, `flg6`, `entry`) VALUES ( "9071801","ｵｷﾅﾜｹﾝ","ﾔｴﾔﾏｸﾞﾝﾖﾅｸﾞﾆﾁﾖｳ","ﾖﾅｸﾞﾆ","沖縄県","八重山郡与那国町","与那国",0,0,0,0,0,0,NOW() );
Query OK, 1 row affected (0.04 sec)

・ノーマル

INSERT INTO `test2`.`postal_normal` (`code`, `ken_kana`, `ctiy_kana`, `town_kana`, `ken`, `citiy`, `town`, `flg1`, `flg2`, `flg3`, `flg4`, `flg5`, `flg6`, `entry`) VALUES ( "9071801","ｵｷﾅﾜｹﾝ","ﾔｴﾔﾏｸﾞﾝﾖﾅｸﾞﾆﾁﾖｳ","ﾖﾅｸﾞﾆ","沖縄県","八重山郡与那国町","与那国",0,0,0,0,0,0,NOW() );
Query OK, 1 row affected (0.02 sec)

パーティショニングを計算してインサートする分どうしても遅くなってしまう。

まとめ

今回の実験結果では、パーティショニングのキーとなったプライマリキーの検索ではパフォーマンスは同じであったが、
それ以外のカラムの検索ではパフォーマンスが大きく低下してしまう結果になった。
ただし、24万件ではなく、100万件、1000万件レベルのテストになるとパーティショニングした場合のプライマリキーでの検索に効果が見られる可能性はある。

現時点では他のカラムの検索や、インサートなどのパフォーマンスが劇的に落ちてしまうため、24万レコードレベルのテーブルではパーティショニングは導入すべきでないと考える。

もし、使用するとなれば、アクセスログやクエリーログをテーブル化し、データ量がそれこそ何千万件となったときに効果を発揮するのかもしれない。
是非、大容量のデータでテストをしてみたいが、なんせインサートが遅いのとマシンのスペックが追いつかないため今回は見送らせていただく。

実は仕事でパーティショニングを使ってみたかったが、今回のパフォーマンスではなかなか難しそうだなぁ～

written by YSU \\ tags: mysql, まとめ

mysqlパーティショニングのまとめ① - 設定・再コンパイル
mysqlパーティショニングのまとめ② - パーティショニングのタイプ
mysqlパーティショニングのまとめ③ - パフォーマンス
前回の記事では、mysqlのパーティショニングを使うための、mysql本体のセッティングについて述べたが、
今回はパーティショニングの種類について記述していく。

サンプルテーブル

まず、説明を始める前に今回はサンプルとして、郵政省の郵便番号データを扱う以下のテーブルを用意した。
いろいろカラムが分かれているが、code(郵便番号)にたいして、フラグやら県名を保持しているテーブルだ。

CREATE TABLE　`postal` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` int(7) NOT NULL,
  `ken_kana` varchar(255) DEFAULT NULL,
  `ctiy_kana` varchar(255) DEFAULT NULL,
  `town_kana` varchar(255) DEFAULT NULL,
  `ken` varchar(255) DEFAULT NULL,
  `citiy` varchar(255) DEFAULT NULL,
  `town` varchar(255) DEFAULT NULL,
  `flg1` tinyint(1) NOT NULL,
  `flg2` tinyint(1) NOT NULL,
  `flg3` tinyint(1) NOT NULL,
  `flg4` tinyint(1) NOT NULL,
  `flg5` tinyint(1) NOT NULL,
  `flg6` tinyint(1) NOT NULL,
  `entry` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `town` (`town`),
  KEY `flg1` (`flg1`),
  KEY `entry` (`entry`)
)
DEFAULT CHARSET=utf8
ENGINE=InnoDB;

パーティショニングの種類

さて、本題に入る。
mysqlのパーティショニングには以下の４つのタイプが存在する。

• RANGEパーティショニング
• LISTパーティショニング
• [LINEAR] HASHパーティショニング
• [LINEAR] KEYパーティショニング

RANGEパーティショニング

LANGEパーティショニングとは、値の範囲を絞って、パーティショニングする方法である。
あくまでも、パーティショニングする値がある程度決まっている場合などに使用できる。
今回は郵便番号は12万レコード前後で推移すると考え、ＩＤを30000毎に区切ってみる。
（サンプルでは、日付で絞る例がおおい）
「ENGINE=InnoDB」の後ろに以下のように書き加える。

DEFAULT CHARSET=utf8
ENGINE=InnoDB
PARTITION BY RANGE (id) (
PARTITION p1 VALUES LESS THAN (30000),
PARTITION p2 VALUES LESS THAN (60000),
PARTITION p3 VALUES LESS THAN (90000),
PARTITION p4 VALUES LESS THAN MAXVALUE
);

LISTパーティショニング

LISTパーティショニングは、あるカラムの値の候補が決まっている場合に使われる。
例えば、性別、誕生月、などであろうか・・・。
今回はいい例ではないかもしれないが、flg1を1,0に分けてみる。
「ENGINE=InnoDB」の後ろに以下のように書き加える。

DEFAULT CHARSET=utf8
ENGINE=InnoDB
PARTITION BY LIST (flg1) (
PARTITION p1 VALUES IN (0),
PARTITION p2 VALUES IN (1)
);

しかし、これはエラーになる。

 A PRIMARY KEY must include all columns in the table's partitioning function

プライマリーはパーティショニングの定義に入れなければならない・・・とのこと。なんやねん。
といわけで、MySQLのパーティショニングで必要そうな工夫を元に以下みたいな書き方をしてみる。

DEFAULT CHARSET=utf8
ENGINE=InnoDB
PARTITION BY LIST (( id * 0 ) + flg1) (
PARTITION p1 VALUES IN (0),
PARTITION p2 VALUES IN (1)
);

うーん・・・いけない・・・。
ちなみに、15.5. パーティショニングの制約と制限によれば、

とのこと。是非、取り除いてほしいですね。
どうやらlistを使用するにはprimaryを外さないと使えなさそうだが、いけてなさすぎるので今回はつかわない。

[LINEAR] HASHパーティショニング

HASHパーティショニングとは、行が格納されるパーティションを算出するのにMOD（剰余）を利用するものである。
RANGEやLISTと違い、あらかじめ値の範囲や候補をしらなくても動的にパーティショニングできる。
また、各パーティショニングのかたよりも無いと思われる。

また、LINER HASHというものがあるが、これは
15.2.3.1. LINEAR HASH パーティショニングによれば

とのことらしい、だが計算が難しくてよくわからないが漢(オトコ)のコンピュータ道によれば、

ということらしい。
なので、HASHを使う場合は「ENGINE=InnoDB」の後ろに以下のように書き加えるとよいだろう。
下記の例だと、idをキーにして1024パーティショニングすることになる。
１パーティショニングあたり、120くらい、めっちゃはやそう。
＊1024がパーティショニングの限界値

DEFAULT CHARSET=utf8
ENGINE=InnoDB
PARTITION BY LINEAR HASH (id) PARTITIONS 1024;

[LINEAR] KEYパーティショニング

KEYパーティショニングはPASSWORD()関数を使ってハッシュ値を算出する。PASSWORD関数を利用するので、文字列に対しても利用することが出来る。が、ここでもPrimaryの制約があり使用できそうにない（ためしていない）。
もし、教科書的に書くなら「ENGINE=InnoDB」の後ろに以下のように書き加える感じだと思う。

DEFAULT CHARSET=utf8
ENGINE=InnoDB
PARTITION BY LINEAR KEY(ken) PARTITIONS 1024
);

パーティショニング確認

以上がパーティショニングの説明となる。
これらのテーブルのパーティショニングの状態をチェックするには、

SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME=postal

というＳＱＬを投げれば確認できる。
では、次回はパーティショニングのパフォーマンスについて述べていこうと思う。

written by YSU \\ tags: mysql, まとめ

mysqlパーティショニングのまとめ① - 設定・再コンパイル
mysqlパーティショニングのまとめ② - パーティショニングのタイプ
mysqlパーティショニングのまとめ③ - パフォーマンス

mysqlのパーティショニングを試してみたかったが、
私のmysqlではパーティショニングが使えなかったようなので
今日はmysqlパーティショニングの再コンパイルについて

まずは、mysqlのパーティショニングが使えるかどうか調べる。

mysql> SHOW VARIABLES LIKE '%PARTITION%' ;
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.01 sec)

上記のようにhave_partitioningがYESとなっていたら、パーティショニングが使用可能である。
私は使用不可であったので、再コンパイルしなければならない。

まずは念のためmysqlをストップ

/usr/local/mysql/share/mysql/mysql.server stop

*パスは各自のパスをご確認ください。

以前のコンパイルオプションに

--with-ndbcluster--with-partition

をつけて

make
make install

で再コンパイル完了。
ちなみに、以前のコンパイルオプションは
mysqlのソースがはいっているディレクトリに移動して

grep "$ ./configure" config.log

とでも打てばいいだろう。

あとはmysqlを起動して、パーティショニングが使用できることを確認できればＯＫだ。

/usr/local/mysql/share/mysql/mysql.server start

[おまけ]現在の私のオプション

--with-charset=utf8
--with-extra-charsets=all
--with-mysqld-user=mysql
--with-innodb
--with-falcon
--with-maria
--with-heap
--with-myisam
--enable-local-infile
--prefix=/usr/local/mysql
--with-unix-socket-path=/tmp/mysql.sock
--with-ndbcluster
--with-partition
--with-blackhole-storage-engine

いろんなストレージを試してみたい。

written by YSU \\ tags: mysql, まとめ, インフラ, 自宅サーバー

ふと、シェルを調べてみたら

echo $SHELL
/bin/bash

defaultのbashだった。
bashじゃなんとなく嫌なので会社と同じtcshに変更しようとおもう。
（こういうのって本当はもっと早くやるべきなんでしょうね）

普通にシェルを変更しようと

 chsh -s /bin/tcsh
morinoyumeのシェルを変更します。
chsh: "/bin/tcsh" は存在しません。

tcshって標準で入っていないことが判明！
会社では普通に使っていたからデフォルトで入っているかとおもった。
インストール方法を調べるもこういう超基本的な導入系って中々でてこない・・・
とりあえずyumでコマンド打ってみる。

yum -y install tcsh

お、インストールできたっぽい！
簡単だね。

あとはログイン時のシェルも書き換えたいので、

chsh
morinoyumeのシェルを変更します。
新しいシェル [/bin/bash]: /bin/tcsh
シェルを変更しました。

これでログインしなおせばＯＫ！
あとは自分の好き放題に/home/morinoyume/.cshrc
を修正すればＯＫ！

やっぱ使い慣れているシェルがいいですね♪

written by YSU \\ tags: インフラ, 自宅サーバー

僕はインフラがとっても苦手。
そんな僕がサーバーをいじるとエラーの嵐である。
今日は、phpmyadminをインストールしていたら、

「mcrypt 拡張をロードできません。PHP の設定を確認してください。」

というエラーが・・・。
しらべてみると

yum install php-mcrypt

こんな簡単らしい・・・・

けど、できない。
そのほかにもrpmでいれるだの、php.iniを編集するだのいろいろ試すが一向に直らない。
僕の場合はphpのコンパイルからやり直さないとだめだったみたい。

--with-mcrypt --disable-posix-threads

よくわからんが、上記の2オプションつけたらうまくいきました。
インストールはやけに時間かかったけど、
再コンパイルはさっくりいけるんですね。

written by YSU \\ tags: mysql, php, インフラ

久々の投稿。
最近、暖かくなってきて休日は外にいることが多かったので、
なかなかサーバーをいじる時間がなかった・・・。

さて、突然mysqlが起動しなくなった。

Starting MySQL.Manager of pid-file quit without updating file

原因はいろいろあるらしいが、
今回はmy.cnfの記述ミス。
というより、mysqlの実行ユーザーを変えたことによる影響。

my.cnf

user=hogehoge

そのユーザーがmyslを実行する権限がなかったのが問題。
以下で解決。

choown -R hogehoge:hogegroup /usr/local/mysql

今回は記述ミスというより、
記述を変更したことによる影響であったが、
上記のエラーがでるようであれば、my.cnfの設定をまず疑ってみよう。

written by YSU \\ tags: mysql, インフラ, 自宅サーバー

先日、mysql6.0をインストールした。
mysqlを入れたからにはphpmyadminを使いたいので、phpのインストールをする。
めんどくさいからyumで。

yum update
yum -y install php php-mbstrinｇ

こんだけ。
バージョンの確認。

php -v

記事にするほどでもなかったか・・・。

written by YSU \\ tags: php, インフラ, 自宅サーバー

タイトルのとおり、myql6.0をインストールしてみた。
まずは、rootになっておく。

su - root

そして、mysqlの取得。最新版は（http://dev.mysql.com/downloads/）でチェック。
場所は、/usr/local/src/が適当だと思われる。

cd /usr/local/src/
wget http://dev.mysql.com/get/Downloads/MySQL-6.0/mysql-6.0.10-alpha.tar.gz/from/http://ftp.jaist.ac.jp/pub/mysql/

ダウンロードできたら、解凍と対象ディレクトリに移動

tar xvzf mysql-6.0.10-alpha.tar.gz
cd mysql-6.0.10-alpha

次にconfigure。

./configure --help

とやるとconfigureオプションが見れる。よくわからない。
とりあえず今回は下記のオプションで。

./configure --with-charset=utf8 --with-extra-charsets=all --with-mysqld-user=root --with-innodb --with-falcon --with-maria --with-heap --with-myisam --enable-local-infile --prefix=/usr/local/mysql --with-unix-socket-path=/tmp/mysql.sock

• --with-charset=utf8 : 文字コードはutf-8
• --with-extra-charsets=all : 他の文字コードは全部つかう
• --with-mysqld-user=root : mysqldを実行するユーザー。（rootにしないほうがよかったかも・・・）
• --with-innodb : innodbを使う。
• --with-falcon : falconを使う。
• --with-maria : mariaを使う。
• --with-heap : memoryを使う。
• --with-myisam : myisam を使う。
• --enable-local-infile : よくわからない・・・
• --prefix=/usr/local/mysql : インストール先
• --with-unix-socket-path=/tmp/mysql.sock : ソケットファイルのパス。よくわからない・・・。

こまったことにconfigure中にエラー発生。

checking for termcap functions library... configure: error: No curses/termcap library found

うーん・・・。
ググッってしらべたら

yum install ncurses-devel

で、解決。これもなぜ解決なのかよくわからない。
次にmake,make install

make
make install

makeは異常な時間がかかるので、お茶でも飲んでおく。
完了したらデータベースの初期化をしといたほうがいいらしい。

./scripts/mysql_install_db --user=root

以上でmysqlのインストールは完了。
mysql起動。

/usr/local/mysql/bin/mysqld_safe --user=root &

mysqlコマンドはよく使うので、
パスを通しておく。

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

これで、mysqlも準備完了。
だんだん環境が整ってきた。早く開発するのが楽しみだ。

参考：
http://www.y-kit.jp/saba/xp/mysqllinuxinst.htm

written by YSU \\ tags: mysql, インフラ, 自宅サーバー

mysql6.0の"configure"中に、

checking for termcap functions library... configure: error: No curses/termcap library found

と言われた。
よくわからないのでググッってみたら、

yum install ncurses-devel

yumで上記をインストールをすれば問題なくconfigureできました。
理由はよくわからないけど、できたからいいや。

written by YSU \\ tags: mysql, インフラ, 自宅サーバー

今日は日本勝ちましたね！
小笠原ナイスヒットです。イチローもっとがんばってください。
次のアメリカ戦は審判の誤審が気になるけど、きっと勝ってくれるはずです！！
月曜朝8時半からなので、朝からテンションマックスですね。
んなわけで、今日も自宅サーバーを構築中・・・。

今日はsambaを入れてみた。
全部コピペでなんとか入った感じなので、ぜんぜん理解していないけど、
とりあえず記事にしてみます（笑）。

sambaとは・・・

UNIXでSMBを使ったサービスを提供するためのソフトウェア。ネットワークを通じてWindowsマシンにファイル共有やプリンタ共有などのサービスを提供することを可能にする。GPLというライセンス体系に基づいてフリーソフトウェアとして公開されている。

ということらしい。

要は、サーバーにsambaをインストールしたらそのサーバーにwindows上からファイルを更新できたり共有できたりする感じ。
本来は共有用なのかもしれないけど、これをいれておくとwindows上からサーバーのファイル更新ができるのでとても便利。
俺は開発はテキストエディタでないとやりにくいので、windows上からプログラム書いていければいいなと思います。

sambaのインストールと設定はココを参考にしたが、
これととおりやると/home/のディレクトリしか操作できなくなってしまう。
できることなら/（ルートディレクトリ)から操作できるようにしたい。

そんときは、

[root directory]
path = /
writeable = yes
guest ok = yes
browseable = yes

を /etc/samba/smb.conf
に書き足してあげて、sambaを再起動すればルートディレクトリも操作できると思います。

上記設定を行ったときは、
絶対にローカルのＩＰ以外にsambaを開いてはいけません！！
/etc/samba/smb.conf　に↓を忘れずに！！

hosts allow = 192.168.1.

なお、sambaを開くには、windowsのファイル名から実行で、「\\192.168.1.1」みたいに
サーバーのローカルＩＰを指定してあげれば見れるはずです。

written by YSU \\ tags: インフラ, 自宅サーバー