nkjmkzk.net

powered by Kazuki Nakajima

Archive for the ‘mysql’ tag

dovecot + MySQLでIMAPサーバ(over SSL)を構築する

その昔はIMAPサーバにはCourierIMAP + OpenLDAPを使用していましたが、このサーバの設定には中々苦労した思い出があります。 最近はdovecot + OpenLDAPという構成でやってきましたが、DBをMySQLやらPostgreSQLやらOpenLDAPやらといろいろ動かしているとメンテナンスや移設のときに結構しんどいので、現在VPSで新たに環境を構築する上ではDBは基本的にMySQLに集約することにしました。 (そもそも当初はいろんなサーバの構築ができるようにと勉強目的で複数種のDBを動かしていましたが、もういいや、ということで。)

環境はdovecot-1.0.3, MySQL-5.0.41, CentOS-5.0です。前提としてMySQLはすでに/srv/mysqlとしてインストールされているものとします。dovecotと連携させる上でMySQLを特段追加のオプションを付けてビルドしなければならないといったことはありません。MySQL側では認証用のDBとテーブルを用意するだけでOKです。

ちなみに僕はソースからビルドしたアプリケーションはすべて/srv/以下に置くことにしているのでdovecotのインストールパスもそれにしたがいます。

手順は以下のようなステップとなります。
・dovecotのインストール
・Unixユーザの追加
・メールボックスの作成
・設定ファイルの編集
・データベースの作成

では上記のステップを順を追って見ていきます。
まずdovecotをソースからビルドしていきます。まずconfigureを適切に行う必要があります。

# tar xvfz dovecot-1.0.3.tar.gz
# cd dovecot-1.0.3
# CFLAGS="-I/srv/mysql/include/mysql" LDFLAGS="-L./srv/mysql/lib/mysql" ./configure --prefix=/srv/dovecot --with-mysql

MySQLと連携させるためにはMySQLのヘッダファイルとライブラリファイルへのパスを認識させる必要があります。 今回の環境の場合ではMySQLが/srv/mysqlと通常のパス設定では認識できないところにあるのでこれをconfigure時に認識できるようにCFLAGSとLDFLAGSを設定してあげる必要があります。

configureの結果をみて以下の用に「Building with SQL drivers」のところにmysqlと表示されていればOKです。

Install prefix ...................... : /srv/dovecot
File offsets ........................ : 64bit
I/O loop method ..................... : poll
File change notification method ..... : dnotify
Building with SSL support ........... : yes (OpenSSL)
Building with IPv6 support .......... : yes
Building with pop3 server ........... : yes
Building with mail delivery agent .. : yes
Building with GSSAPI support ........ : no
Building with user database modules . : static prefetch passwd passwd-file checkpassword sql (modules)
Building with password lookup modules : passwd passwd-file shadow pam checkpassword sql (modules)
Building with SQL drivers ............: mysql

なお、今回はIMAPサーバへの接続をIMAP over SSLにするので「Building with SSL support」の結果も同時に確認しておく必要があります。 もしyesとなっていなければOpenSSLのライブラリが正しく認識できていないので、CFLAGS, LDFLAGSにOpenSSLのヘッダファイル、ライブラリファイルへのパスも含める必要があります。(OpenSSLが既にインストールされている前提)

あとは通常通りコンパイル、インストールを行います。

# make 
# make install

次に必要なUnixユーザを作成します。

# useradd -s /bin/false dovecot
# useradd -s /bin/false -u 501 vmail

dovecotはサーバ起動用のユーザ、vmailは実際にメールボックスを操作するユーザです。
後に作成するメールボックスのディレクトリツリーはこのユーザに所有される必要があります。なお、dovecotのデフォルトの設定上、vmailのユーザIDは500以上としておいた方がよいでしょう。これらのユーザにはシェルは必要ないのでログインシェルには/bin/false等を指定して無効化しておきます。

メールボックスのルートとなるディレクトリを作成します。

# mkdir /var/spool/vmail
# chown -R vmail:vmail /var/spool/vmail

これはどこでも構いませんが、先に作成したvmailユーザによって書き込めるようにパーミッションを設定しておく必要があります。

メインの設定ファイルを作成します。/srv/dovecot/etc/dovecot-example.confというテンプレートが用意されていますのでこれをベースに編集していきます。

# cd /srv/dovecot/etc # cp dovecot-example.conf dovecot.conf # vi dovecot.conf

ポイントとなる箇所を抜粋して記載します。特に重要なパラメータのみコメントしています。

protocols = imaps
disable_plaintext_auth = no
log_path = /var/log/dovecot.log
ssl_cert_file = /srv/dovecot/etc/ssl.crt
ssl_key_file = /srv/dovecot/etc/ssl.key

# メールボックスの形式にMaildirを指定し、その場所を指定します。後にデータベースで設定するメールアカウントには「home」というフィールドがあり、mail_locationを「~/」と設定することでこのhomeフィールドの値がそのまま各ユーザのメールボックスへのパスになります。

mail_location = maildir:~/

mail_debug = yes
protocol imas {
 ssl_listen = *:993
 login_executable = /srv/dovecot/libexec/dovecot/imap-login
 mail_executable = /srv/dovecot/libexec/dovecot/imap
}
auth_executable = /srv/dovecot/libexec/dovecot/dovecot-auth
auth_default {
 mechanisms = plain
 #passdb pam {
 #}

#認証用のデータベースにSQLを指定しています。dovecotでは既定でパスワード情報を取得するためのSQLクエリとユーザ情報を取得するためのクエリの2クエリを発行しますが、userdb prefetchを設定することによってこれらのクエリを1クエリにまとめることができます。

 passdb sql {
  args = args = /srv/dovecot/etc/dovecot-sql.conf
 }
 userdb sql {
  args = args = /srv/dovecot/etc/dovecot-sql.conf
 }
 userdb prefetch {
 }
}

SSL接続のためのRSA秘密鍵と証明書は既に作成してある前提ですが、もしまだ作成していなければ以下のように作成しておきます。

# openssl keygen 1024 > /srv/dovecot/etc/ssl.key
# openssl req -new -x509 -days 365 -key /srv/dovecot/etc/ssl.key -out /srv/dovecot/etc/ssl.crt

次にSQLの設定ファイルを作成します。

# cd /srv/dovecot/etc
# cp dovecot-sql-example.conf dovecot-sql.conf
# vi dovecot-sql.conf

以下が設定ファイルの中身です。

driver = mysql
connect = host=/tmp/mysql.sock dbname=mail user=root
default_pass_scheme = PLAIN
password_query = SELECT userid as user, password, home as userdb_home, uid as userdb_uid, gid as userdb_gid FROM users WHERE userid = '%u'
user_query = SELECT home, uid, gid FROM users WHERE userid = '%u'

僕の環境ではMySQLは同一サーバ上にあるのでアクセス制御を容易にするためにconnect=にhost=/tmp/mysql.sockとUNIX SOCKETを指定していますが、TCP/IPでMySQLに接続する場合はここにホスト名(またはIPアドレス)を指定します。user=にはrootを指定してうるぅあ!って感じで接続することになっていますが、適宜適切な権限のユーザを指定したりパスワードをpassword=パラメータで追加したりして下さい。
password_queryとuser_queryは後に作成するデータベースのテーブルスキーマに合わせて設定しています。

次に認証用のデータベースを作成します。

# mysql -u root
> CREATE DATABASE mail;
> USE mail
> CREATE TABLE users (
  userid VARCHAR(128) PRIMARY KEY NOT NULL,
  domain VARCHAR(128) NOT NULL,
  password VARCHAR(64) NOT NULL,
  home VARCHAR(255) NOT NULL,
  uid INTEGER NOT NULL,
  gid INTEGER NOT NULL
);
> INSERT INTO users VALUES ('nkjm','nkjmkzk.net','nkjm_passwd','/var/spool/vmail/test', 501,501);
> q

ここではまず「mail」データベースを作成し、実際にメールアカウントを格納するテーブル「users」を作成しています。このテーブルの中のdomainフィールドは今回特に気にする必要はありません。useridとpasswordがユーザがメールクライアントのアカウント設定でログインidとパスワードに指定するものになります。homeはこのユーザのメールデータが格納されるディレクトリになります。このディレクトリは初回ユーザがログインした際に自動的にdovecotが作成してくれます。なんて便利なんでしょう! uidとgidには先に作成したvmailユーザのuidとgidを指定します。最後にテストアカウントとして’nkjm’を作成しています。

これで構築は完了です。
/srv/dovecot/sbin/dovecotを実行してサーバを起動し、メールクライアントから接続できます。クライアントのアカウント設定では接続方式にSSLを指定して宛先ポートを993に指定することをお忘れなく。うまくいかないときは/var/log/dovecot.logのメッセージがシューティングに役立つと思います。

without comments

Written by 中嶋 一樹

12月 6th, 2008 at 7:02 pm

Posted in Uncategorized

Tagged with , ,

InnoDBのリカバリ機能を検証してみる

innodbはクラッシュ時のリカバリ機能を実装しており、mysqldがクラッシュした場合やOS自体がクラッシュした場合にもしコミットされたデータがテーブルから失われていても再起動時にib_logfileからコミットされたトランザクションを読み出し、それが実データ(デフォルトだとibdata)に反映されていなければ反映させるという処理が走ります。MySQLはinnodb_flush_log_at_trx_commitの値により、1秒毎、またはコミット毎にトランザクションをログファイルにフラッシュします。

今回は以下2点について検証を行いました。
1.クライアント側から発行したクエリがクラッシュ後にどこまで復旧されているか
2.リカバリにかかる時間の測定(ログのファイルサイズとの因果関係にも言及)

1の検証についてはinnodb_flush_log_at_trx_commitの設定を0と1に変えて結果をみてみました。

0の場合、コミットされたトランザクションは1秒毎にログファイルにフラッシュされ、コミット毎にはフラッシュされません。 つまり最大で1秒間の間にコミットされたトランザクションはクラッシュ時には失われる可能性があります。 検証はクライアントから 4000 transaction/sec程のクエリを発行しながら途中でブチっと電源を落とすという手順で行いました。 1秒間のトランザクションが失われる可能性があるということなので、クラッシュ後にデータベースに格納されているレコード数はクライアントがコミットしたトランザクション数と最大で4000レコード程差が出る可能性があると予想されます。 結果は以下の通りです。

クライアントがコミットしたトランザクション数:2405940
データベース上のレコード数: 2402151

おぉ。予想通り4000弱のデータが失われています。 いい感じです。
次にinnodb_flush_log_at_trx_commitを1にしたときの結果です。1の場合はコミットされたクエリは都度ログファイルにフラッシュされるので、失われるデータは理論上0となるはずです。ちなみに前回のポストでも言及しましたがinnodb_flush_log_at_trx_commitを1にするとWindowsでは極端にスループットが低下(30 transaction/sec)します。 今回はとりあえずこのWindowsでテストしてます。 結果は以下の通りです。

クライアントがコミットしたトランザクション数:8227
データベース上のレコード数: 8227

おぉぉ。 予想通りデータは完全に整合性がとれています。 やはりクリティカルなサービスを運用している場合はデータがなくなっちゃうかもしれないのでinnodb_flush_log_at_trx_commitを1にしておかないとダメ、ということがわかりました。 でもWIndowsだとイイDisk装置使わないとスループット全然でないからそこんとこは注意、ということですね。

次に2のリカバリ時間の検証ですが、1の検証はどちらも以下の設定で行いました。

innodb_log_file_size = 256M
innodb_log_files_in_group = 5

当初リカバリ時間はログのファイルサイズ設定に比例するのではと予想していました。 innodbは設定したサイズのファイルを起動時に作成してしまうので、リカバリ時のログのスキャンに時間がかかるであろうというのが根拠です。 上記の例ではデフォルトよりかなり大きな値を設定しており、したがってリカバリにもある程度の時間を要するのではと思っていました。

しかし結果は同じログファイルサイズでもinnodb_flush_log_at_trx_commitの値を変えてテストしたところリカバリ時間にはある程度の差が出ており、さらにリカバリ時間が短い方(innodb_flush_log_at_trx_commit = 1 でのテスト時)ではわずか2秒で処理を終了しています。 一方リカバリ時間が長い方(innodb_flush_log_at_trx_commit = 0でのテスト時)では120秒を要しています。

ということは、、と思って次に以下の設定でテストしてみました。
要はログファイルはデフォルト(小さい)で、スループットは高くなる設定です。

innodb_log_file_size = 5M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 0

結果、まずリカバリの精度については以下のようになりました。

クライアントがコミットしたトランザクション数: 2185089
データベース上のレコード数: 2184239

およそ1000トランザクション弱が失われています。失われるトランザクションはクラッシュするタイミングによって0からそのときのtransaction/secの間でブレることが予想されますので妥当な結果です。(本テストでもスループットは4000transaction/sec程度) そしてこのときのリカバリ時間は50秒弱でした。 120秒かかっていたケースと比較して短縮されていますが、これはログファイルサイズ設定によるものではなく、クラッシュしたタイミングでたまたまリカバリ対象のデータが減ったためだと思います。

この結果から導き出せる結論としては、リカバリ時間は単にログファイルの容量設定に比例するのではなく、テーブルに反映されていないトランザクションがどれほどログに存在するかに影響されるということだと思われます。 恐らくリカバリ時のスキャンは、まずテーブルをスキャンして最後尾のレコードのシーケンスを取得し、ログファイルはそのシーケンスをもとにファイルの途中からスキャンしていくというようなイメージではないかと思います。

結局のところリカバリ時間は、
(ログに記録されたトランザクション数) ー (テーブルに反映されてないトランザクション数)
に比例するのではないかと思います。

*上記の値を大きくするにはスループットをあげてクエリを投げつづけている状態でクラッシュさせることが必要になるので、その意味でinnodb_flush_log_at_trx_commit = 0としてテストしましたがLinux上等、innodb_flush_log_at_trx_commit = 1でも性能が出る環境であればこの設定でも問題なく、つまりリカバリ時間は最終的に失われてしまったトランザクション数に比例するのではなく、あくまで(ログに記録されたトランザクション数) ー (テーブルに反映されてないトランザクション数)に比例すると思われます。

今回は検証データやマニュアルを元にinndbの仕様を推察しましたが、今後ソースファイルを読み進めてそのあたりを明らかにしていこうと思います。

without comments

Written by 中嶋 一樹

12月 6th, 2008 at 6:54 pm

Posted in Uncategorized

Tagged with ,

MySQL on WindowsのInnodb書き込み性能について

WindowsのInnodbでINSERTがめちゃ遅い。
ベンチマークしてたら異常に遅いことに気づいた。 どれくらい遅いかというと、

Linux: 1613 transaction / sec
Windows: 30 transaction / sec

という具合。 ちなみにハードはHP Proliant DL360 Xeon 3.4GHz 2Gbyte RAM Ultra320 SCSI 12000rpm 80Gbyte。 スキーマは(c1 INT PRIMARY KEY, c2 TEXT)という単純なもの。クエリは’INSERT INTO tbl (c2) VALUES (‘I drink coffee every morning’)をひたすら。

同じWindowsでもMyISAMと比較するとこれまた以下のように桁違い。

MyISAM: 4676 transaciton/ sec
Innodb: 30 transaction / sec

なにやらWindowsでは異常にdisk I/O同期処理が重たい模様。
上記の結果は以下のパラメータで実施していました。

sync_binlog = 0
innodb_flush_log_at_trx_commit = 1

innodb_flush_log_at_trx_commit が「1」だと、トランザクションコミット毎に物理ディクスに対するログのフラッシュが行われます。 原因はこれの模様。

innodb_flush_log_at_trx_commit = 0

としてやると、たちまち改善。

Innodb: 4065 transaction / sec

となりました。
ちなみにinnodb_flush_log_at_trx_commit = 0 としていても sysc_binlog = 1としてしまうとこれまたトランザクションコミット毎にバイナリログのdiskへのフラッシュが起きてしまうので 30 transaction /sec くらいになってしまいます。

MySQLのマニュアル通りですが、innodb_flush_log_at_trx_commitの各値での動作は以下の通り。

0 : 1秒毎にlog bufferからログファイルへの書き込み処理を行い、さらにOSのファイルシステムキャッシュから実際の物理diskへフラッシュが行われます。

1 : 毎トランザクションコミット毎にlog bufferからログファイルへの書き込み処理を行い、さらにOSのファイルシステムキャッシュから実際の物理diskへフラッシュが行われます。

2 : 1秒毎にlog bufferからログファイルへの書き込み処理を行い、さらにOSのファイルシステムキャッシュから実際の物理diskへフラッシュが行われます。 かつ、毎トランザクションコミット毎にlog bufferからログファイルへの書き込み処理を行います。 OSのファイルシステムキャッシュから実際の物理diskへフラッシュは行われません(OSに任されます)。

*なお、イイdisk装置を搭載している場合は物理diskへフラッシュしたと思った後でも実際にはdisk cacheに乗っかって最終的なdiskへのフラッシュは行われないことがあります。

without comments

Written by 中嶋 一樹

12月 6th, 2008 at 6:53 pm

Posted in Uncategorized

Tagged with ,