2012年5月31日木曜日

Amazon RDS の MySQL でトランザクション分離レベルを変更する

Amazon RDSのMySQLインスタンスはデフォルトで、トランザクション分離レベル(tx_isolation)が、"REPEATABLE READ"になっています。これは、RDSのMySQLはInnoDBしか選択できず、InnoDBのトランザクション分離レベルのデフォルトが"REPEATABLE READ"。
今回必要があり"READ COMMITTED"に変更する手順を調べました。

mysqlのクライアントから以下のコマンドを実行しても権限がないということで拒否されます。GLOBALでない場合は可能。
mysql>SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
RDSではデータベースの設定はパラメーターグループで管理されています。現在、パラメータグループはAWS Consoleから変更できないようなので、CLIを使って変更します。

また、デフォルトのパラメーターグループは変更できないので、あらかじめ新しいパラメータグループを作成し、変更したいインスタンスのパラメータグループを変更しておきます。

まず、RDS CLIインストールします。
Amazon RDS Command Line Toolkitをダウンロードします(今回は 1.8.002)。展開したフォルダを適当な場所に移動し、以下の環境変数を追加します。

  • AWS_RDS_HOME : C:\RDSCli-1.8.002
  • PATH : %PATH%;%AWS_RDS_HOME%\bin

認証情報は以下のいずれかの方法で指定します(証明書を使用することも可能)。
  1. 環境変数AWS_CREDENTIAL_FILE でキーを記載したファイルを指定
  2. --aws-credential-file オプションでキーを記載したファイルを指定
  3. --I <ACCESS_KEY> --S  <SECRET_KEY> のようにオプションで直接指定
1,2で使用するファイルは以下のような形式で記載します。
AWSAccessKeyId=<Write your AWS access ID> AWSSecretKey=<Write your AWS secret key>

rds-describe-db-instancesコマンドを実行して動作確認します。

C:\> rds-describe-db-instances --region ap-northeast-1 --headers
DBINSTANCE  DBInstanceId  Created                   Class        Engine  Storage  Master Username  Status     Endpoint Address          Port  AZ               Backup Retention  Multi-AZ Version  License

DBINSTANCE  mysql-master  2012-05-29T08:20:33.655Z  db.m1.small  mysql   10  user              available  mysql.xxxxxxxxxx.ap-northeast-1.rds.amazonaws.com  3306  ap-northeast-1a  7                 n         5.5.20   general-pu
blic-license
      SECGROUP  Name          Status
      SECGROUP  default       active
      PARAMGRP  Group Name        Apply Status
      PARAMGRP  default.mysql5.5  in-sync
      OPTIONGROUP  Name               Status
      OPTIONGROUP  default:mysql-5-5  in-sync
リージョンは、--region オプションで指定しますが、環境変数AWS_REGIONで指定することも可能です。

tx_isolationを変更します。gourp1の部分がパラメータグループです。method=immediateを指定すると変更は再起動を待たずすぐに反映されます。
C:\> rds-modify-db-parameter-group gourp1 --region ap-northeast-1 --parameters="name=tx_isolation, value=READ-COMMITTED, method=immediate"
DBPARAMETERGROUP group1

mysqlコマンドでtx_isolationが変更されていることを確認します。
mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation |
+-----------------------+----------------+
| READ-COMMITTED        | READ-COMMITTED |
+-----------------------+----------------+
ただし、変更の反映は少し時間(1分未満くらい?)がかかるようです。

2012年5月30日水曜日

mod_log_sql でアクセスログをMySQLに保存する

Apacheのmod_log_sqlモジュールを使うとアクセスログをデータベースに保存することができます。

mod_authn_dbdの場合と同様にOSはubuntu serverで、MySQLはRDSのインスタンスを使用しました。

MySQLの設定

RDSのインスタンスにてログ保存用のデータベースを作成しておきます。
create database logs;
create user apache identified by 'パスワード';
grant all on logs .* to apache;

mod_log_sqlのインストール

ubuntuのapt-getでインストールされるバージョンは古いので(確か1.10?)、ソースから1.99をインストールします。まずapt-getでapxs2をインストールします。
sudo apt-get install apache2-prefork-dev
mod_log_sql をインストールします。ソースとパッチをダウンロードし、makeしてインストールします。
$ wget http://www.outoforder.cc/downloads/mod_log_sql/mod_log_sql-1.99.tar.gz
$ tar -xzvf mod_log_sql-1.99.tar.gz
$ cd mod_log_sql-1.99
$ wget http://www.outoforder.cc/downloads/mod_log_sql/mod_log_sql-1.99.diff
$ patch -p0 < mod_log_sql-1.99.diff
$ wget http://www.outoforder.cc/downloads/mod_log_sql/mod_log_sql-1.99-2.diff
$ patch -p0 < mod_log_sql-1.99-2.diff
$ ./configure --with-apxs=/usr/bin/apxs2
$ make
$ sudo make install

以下の内容で/etc/apache2/mods-available/log_sql.loadを作成します。
LoadModule log_sql_module /usr/lib/apache2/modules/mod_log_sql.so
LoadModule log_sql_mysql_module /usr/lib/apache2/modules/mod_log_sql_mysql.so
モジュールをロードするよう設定します。
$ sudo a2enmod log_sql

ログ出力の設定

apacheの設定にて以下の記述を追加します。この設定では、access_logテーブルにデータを保存します。テーブルが存在しない場合には初回のアクセスログ記録時に作成されます。
LogSQLLoginInfo mysql://apache:パスワード@RDSのエンドポイント/logs
LogSQLCreateTables on
LogSQLTransferLogFormat AbHhmRSsTUuv
LogSQLTransferLogTable access_log
LogSQLDBParam port 3306
LogSQLMachineID web1 
LogSQLTransferLogFormatでログ出力対象の項目を指定しています。詳細はこちらのドキュメントにあります。パフォーマンスへの影響が気になるようであれば、不要な項目は取得しない方がよいかもしれません。
また、複数のWebサーバからログを保存する場合には、LogSQLMachineIDにてマシンごとのIDを指定することができます。

この設定で作成されるaccess_logテーブルは以下のようになります。
FieldTypeNullKeyDefault
idchar(19)YESNULL
agentvarchar(255)YESNULL
bytes_sentint(10) unsignedYESNULL
child_pidsmallint(5) unsignedYESNULL
cookievarchar(255)YESNULL
machine_idvarchar(25)YESNULL
request_filevarchar(255)YESNULL
referervarchar(255)YESNULL
remote_hostvarchar(50)YESNULL
remote_lognamevarchar(50)YESNULL
remote_uservarchar(50)YESNULL
request_durationsmallint(5) unsignedYESNULL
request_linevarchar(255)YESNULL
request_methodvarchar(10)YESNULL
request_protocolvarchar(10)YESNULL
request_timechar(28)YESNULL
request_urivarchar(255)YESNULL
request_argsvarchar(255)YESNULL
server_portsmallint(5) unsignedYESNULL
ssl_ciphervarchar(25)YESNULL
ssl_keysizesmallint(5) unsignedYESNULL
ssl_maxkeysizesmallint(5) unsignedYESNULL
statussmallint(5) unsignedYESNULL
time_stampint(10) unsignedYESNULL
virtual_hostvarchar(255)YESNULL
当然ですが、、LogSQLTransferLogFormatにかかわらずテーブル構造は同じで、どの列に値が入るが変わります。

これら以外にもCookieやHTTPヘッダを記録することもできるようです。

ファイルに保存したアクセスログを取り込む手間がないはよいのですが、アクセスの都度、DBにアクセスが発生するので、パフォーマンスが求められるようなサイトで使用するのは難しいかもしれません。全くチューニングしない状態で、apache benchで簡単に測定しただけですが、実際、mod_log_sqlを使うとスループットがかなり低下しました。

mod_authn_dbd + MySQL (RDS) でBasic認証

MySQLに保存したユーザ情報を使用して、ApacheでBasic認証を行う方法です。

データベースに格納したユーザー情報を使って認証を行うためのモジュールは、Apacheの1.3系と2系でも違いますし、いろいろあってどれを使うべきなのかわかりづらいです。はじめに、Apacheクックブック 第2版に載っていたmod_authn_dbiを使おうとしたのですが、全く情報がなくmod_authn_dbdにたどり着きました。

サーバはubuntu server、DBはAmazon RDS(Relational Database Service)のMySQLで試しました。RDS上のMySQLを使うからといって特別なことはなく、mysqlコマンドでリモート接続して、データベースおよび接続するユーザーを作成し、権限を付与しておきます。

mod_authn_dbdは標準でインストールされていたのですが、MySQL用dbdドライバーがインストールされていなかったので、apt-getでインストールします。
sudo apt-get install libaprutil1-dbd-mysql
a2enmodでauthn_dbdを有効にします。
sudo a2enmod authn_dbd
Apacheの設定ファイルにて、認証の設定を行います。
DBDriver mysql
DBDParams host=<RDSエンドポイント>,user=<ユーザーID>,pass=<パスワード>,dbname=<DB名>
DBDPersist On
DBDKeep 5
DBDMax 10
DBDMin 3
DBDExptime 600
<Directory /var/www>
    AuthBasicProvider dbd
    AuthDBDUserPWQuery "SELECT encrypt(password) FROM users WHERE user = %s"
    AuthName "Please Enter Your Password"
    Authtype Basic
    require valid-user
</Directory>
DBDParamsで接続先DBを指定し、AuthDBDUserPWQueryで認証時にユーザのパスワードを取得するSQLを指定します。%sの部分にユーザIDが入ります。また、DBDPersist をOnにするとコネクションをプールします。

注意点というほどのことではありませんが、"DB***"というディレクティブは<Directory>のなかにはかけません。

Apacheを再起動して、認証が正しく行われることを確認します。Internal Server Errorが発生するなど問題がある場合にはApacheのエラーログを確認します。