アシアルブログ

アシアルの中の人が技術と想いのたけをつづるブログです

PDOにてMaster&Slaveのコネクションを操作するクラス(PHP)

皆様、ご無沙汰しております。笹亀です。

いよいよきたる、9/10に新しいiPhone5 S(仮名)が発表される予定ということで、iphone4を使っている自分は今回のタイミングで変更する予定なので、いまから発表が楽しみです。

さて、本日はフレームワークなどを利用している場合などであれば、あまり使用することはないですが、PDOを利用したレプリケーションしたデータベースのコネクションを切り分ける方法をご紹介したいと思います。PHPでも様々なフレームワークsymfony,Cake,ZendFramwork)を使い開発をされるようになってきており、あまりレプリケーションの切り分けを考える必要がなくなってきておりますが、切り分けを行う方法(考え方)という視点で見ていただけますと幸いです。
 ※尚、ご使用される場合は自己責任でお願い致します

概要について


レプリケーションのコネクションを切り分けることについては、一つのデータベースサーバであれば特に気にする必要はございません。下記の画像のように、改修後Master・Slaveのレプリケーション形式にスケールアウトしたい場合には更新系の処理(INSERT, UPDATE, DELETEなど)はMaster側にて処理をする必要があります。そしてデータ取得系の処理をSlaveを優先的に使用して、Masterと分散をさせるようにするのが一般的です。


スクリプト(クラスファイル)


コネクションを操作するクラスでは、実際に実行しようとしているSQL文をチェックして、Slaveで実行していいSQL(SELECT)か?Masterでないと実行してはいけないSQL(INSERT, UPDATE, DELETEなど)かを判定して、PDOのコネクションを切り分けてあげるようなクラスにします。

実際に上記の内容をクラス化したPHPスクリプトは下記よりご確認ください。(別ウィンドで立ち上がります)
こちら

それぞれのメソッド解説


上記のプログラムを開いた状態で下記の解説を照らしあわせて下さい。

__construct


1.Masterのコネクションなどの情報を格納する内部変数の初期化
2.MasterとSlaveのPDOコネクションを生成
※PdoDatabaseを生成するときの引数に「master」と渡すと、MasterのPDOのみを利用する機能も設けている

query, exec, prepare, getAssoc...


1.実行するSQLからPDOコネクション情報を取得(selectConnection)
2.PDOオブジェクトにてSQLを実行する
3.結果を返す
※基本的にはPDOオブジェクトで実行できるメソッドと同じ処理をしている。違う点は実行するSQL文によってコネクション情報を選択する部分。

lastInsertId


1.MasterのPDOコネクション情報を取得
2.lastInsertIdメソッドにて実行したIDを取得
3.結果を返す

beginTransaction


1.強制的にMasterへの接続する状態にし、MasterのPDOコネクション情報を取得
2.beginTransactionメソッドを実行

commit, rollBack


1.MasterのPDOコネクション情報を取得
2.commit, rollBackメソッドにて実行

setAttribute


1.MasterとSlaveのPDOコネクション情報を取得
2.それぞれのPDOにsetAttributeメソッドにて属性情報の設定を実行

setMasterMode


※強制的にMasterへの接続する状態に変更するメソッド

getConnection


※現在の実行しているコネクション情報(Master or Slave)を取得するメソッド

getMasterConnection


※MasterのPDOコネクション情報を取得するメソッド

getSlaveConnection


※SlaveのPDOコネクション情報を取得するメソッド

setConnection


※MasterとSlaveのPDOコネクション情報をセットするメソッド

selectConnection


※実行するSQLや設定情報からMasterとSlaveのPDOコネクション情報を選択して、コネクション情報を返すメソッド

isSQLDirty


※SlaveのPDOコネクションで実行してはいけないSQLかチェックするメソッド

isSelect


SQLがselect文かどうかチェックするメソッド

isMaster


※MasterのPDOにて実行したかどうかチェックするメソッド

使用方法サンプル


実際に正しくMasterとSlaveの切り分けができているかを確認するために、下記のスクリプトで試します。

テスト用のテーブルを作成している前提となります。


CREATE TABLE `test_table` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
 );




<?php
require_once('./pdo_database.php');

$pdo_db = new PdoDatabase();
$sql = 'INSERT INTO test_table values("1", "Sasagame Hiroshi")';
$rec = $pdo_db->exec($sql);
$sql = 'INSERT INTO test_table values("2", "Sasagame Hiroshi")';
$rec = $pdo_db->exec($sql);
var_dump($pdo_db->isMaster());
print_r($rec);

echo '<hr />';

$sql = 'SELECT * FROM test_table';
$sth = $pdo_db->prepare($sql);
$sth->execute();
$rec = $sth->fetchAll();
var_dump($pdo_db->isMaster());
print_r($rec);

echo '<hr />';


$pdo_db = new PdoDatabase();
$sql = 'SELECT * FROM test_table';
$sth = $pdo_db->prepare($sql);
$sth->execute();
$rec = $sth->fetchAll();
var_dump($pdo_db->isMaster());
print_r($rec);

echo '<hr />';

$pdo_db = new PdoDatabase();
$sql = 'DELETE FROM test_table';
$rec = $pdo_db->exec($sql);
var_dump($pdo_db->isMaster());
print_r($rec);


■実行結果

var_dumpがtrueだとMasterで実行
var_dumpがfalseだとslaveで実行
一度、Masterで実行されたPDOコネクションはずっとMaster側を利用するような仕様にしております。なぜなら、Insert文の実行後のlastinsertIDの実行やTransactionが実行された状態のSELECT文の実行の場合は必ずMaster側にて実行する必要があるからです。


実際にレプリケーションのコネクション操作はライブラリなどを利用するのもいいですが、実際にスクリプトを書いてみるとMaster側で実行しないといけないSQL(Transaction処理やTableLock処理)をSlaveのコネクションで実行させてはいけないという点で注意するが多いことがわかります。バグなどで間違えてSlaveで実行してしますとレプリケーションが崩れてしまいます。

実際に書いてみて初めて気付かされる点も多いので、ライブラリを利用する側でもいいですが、実際に便利なライブラリをお手製で作ってみて、実際にコーディングすることで大変さを学ぶのもエンジニアとしてのスキルアップにつながるものではないかと思います。

PostgreSQLのレプリケーション機能をつかってみた

皆様、ご無沙汰しております。笹亀@イネ花粉に苦しんでます。

昨日(7/10)、最近愛用しているPhpStormを開発しているJetBrainsのエバンジェリストとCEOの方が来日されて、講演をされるとのことで、参加をしてきました。
http://www.zusaar.com/event/844003
内容もよかったのですが、エバンジェリストの方がとてもユニークな方だったのが印象的でした。

さて、本日は少し前から興味を持っていたのですが、中々試す機会がなかった内容です。
今回はPostgreSQLレプリケーションの設定を解説しながらご紹介をしていきたいと思います。

PostgreSQLレプリケーションについて


まずは、マスターとスレーブにするサーバへPostgreSQLをインストールしておきます。
インストールするバージョンは必ず9.2以上でインストールしてください。また、インストールはそれぞれの環境でご準備くださいませ。インストール方法については割愛します。(googleさんにお聞きください(笑))

PostgreSQLレプリケーションだけではなく、マスター側で更新処理(登録・更新・削除など)があった場合にスレーブへ更新情報をやり取りする必要があります。PostgreSQLではその更新のやり取りをWAL(Write Ahead Logging)にて行われます。WALはテーブルに書き込みを行う前に変更情報を記録しているログです。ログにはアンドゥとリドゥ用の情報が含まれていることから、DBMSがクラッシュしたときなどには、クラッシュ直前までリカバリを行うこともできます。

PostgreSQLレプリケーションでは、WALの同期をさせるタイミングについて、以下の4つの設定することができます。


■同期
 マスターと1台のスレーブのディスクへのWALを書き込み後にユーザにレスポンスする

■メモリ同期
 マスタのディスクと1台のスレーブのメモリへのWALを書き込み後にユーザにレスポンスする

■スレーブ非同期
 マスターのディスクへのWALを書き込み後にユーザにレスポンスする(スレーブへの書き込みは確認しない)

■完全非同期
 マスター・スレーブともにWAL書き込みを待たずにユーザにレスポンスする(WALはマスターのメモリ上のみ)

スレーブ非同期がMySQLにて弊社でもよく使っているレプリケーションの同期設定です。
今回の設定も「スレーブ非同期」にて設定をしていきます。

レプリケーションの設定(マスター側)


それでは実際にマスター側のPostgreSQLの設定をしていきます!

1.レプリケーションをするためのPostgreのユーザを作成する


psqlコマンドでログインをして、下記のコマンドでユーザをつくります。


CREATE USER repl_user REPLICATION PASSWORD 'xxx';



2.postgres.confにマスターの設定を追加する


postgresql.confはPostgreSQLデータフォルダ内にあります。
エディターでファイルを開いて更新します。


wal_level = hot_standby        #WALの出力内容をレプリケーションに対応
synchronous_commit = local     #同期方式の設定
#synchronous_commitで指定できるレプリケーションの同期方式は下記の通りです。
# 同期:on
# メモリ同期:remote_write
# スレーブ非同期:local
# 完全非同期:off 
max_wal_senders = 2            #スレーブの数 + 1
synchronous_standby_names = 'slave1_test'
 #同期/メモリ同期レプリケーションで同期スレーブとして動作可能なサーバ
 #これを設定しないとスレーブ非同期レプリケーションで動作する。(今回はスレーブ非同期ですが、念の為に設定しておく。)
 #スレーブのrecovery.confに設定するprimary_conninfoのapplication_name=と同一にする


3.pg_hba.confにマスターの設定を追加する


pg_hba.confはPostgreSQLデータフォルダ内にあります。
エディターでファイルを開いて更新します。


host    replication     repl_user        x.x.x.x/xx          password
 #スレーブのrecovery.confに設定するprimary_conninfoのapplication_name=と同一にする


4.マスター側のPostgreSQLを再起動


コマンドは省略

5.マスター側のデータをスレーブで使用するためにバックアップする


pg_basebackupでスレーブサーバ用のデータを作成する


例)
/opt/local/lib/postgresql92/bin/pg_basebackup -h localhost -p 5432 -D /opt/local/var/db/postgresql92/data_slavedb/ --xlog --progress --verbose 



レプリケーションの設定(スレーブ側)


次にスレーブ側のPostgreSQLの設定をしていきます!

1.マスター側で作成したバックアップをスレーブのデータフォルダへコピーする


データフォルダーごと上書きして更新をする。

2.postgres.confにスレーブの設定を追加する


postgresql.confはPostgreSQLデータフォルダ内にあります。
エディターでファイルを開いて更新します。


hot_standby = on


3.recovery.confにスレーブの設定を追加する


サンプルからコピーしてrecovery.confとファイル名をします。Macの場合は「/opt/local/share/postgresql92/recovery.conf.sample」にありました。


standby_mode = 'on' 
primary_conninfo = 'host=master_hostname port=5432 user=repl_user password=xxx application_name=slave_hostname' #マスターへの接続情報
             #application_nameは同期/メモリ同期の場合にのみ必要
             #マスターのpostgres.confに設定したsynchronous_standby_namesと同一にする


4.スレーブを起動させる


起動しようとしたら【postmaster.opts を読み取ることに失敗しました】とエラーが出た場合は、
postmaster.optsで実行している-dで指定しているデータフォルダを正しい箇所に修正ください。
↓私の環境の場合となります


/opt/local/lib/postgresql92/bin/postgres "-D" "/opt/local/var/db/postgresql92/data_slavedb"

また余談ですが、Macの場合であったのですが、ローカル上で2つのデータフォルダを用意して、それぞれを別ポートで複数立ち上げる場合に、
Macカーネルで取得できるshared memoryの量がかなり低く設定されているので、shared bufferを確保できずにエラーになることがあります。
下記のコマンドを実行すると起動できるようになります。


sudo sysctl -w kern.sysv.shmmax=1073741824
sudo sysctl -w kern.sysv.shmall=1073741824


5.マスターとスレーブ間で正しく同期がされているかを確認する




■マスター側で実行
psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"

下記のようになっていれば同期していることになります。


マスター側にデータベースとテーブルとデータ登録をしてスレーブ側に同期されているかを確認します。


psql -p 5432
psql -p 5432 -c "create database sasa_test;"
psql -p 5432 sasa_test
# CREATE TABLE sasa_table (id int primary key,test_text text);
# insert into sasa_table values('1', 'sasa_test');


スレーブ側で反映されているか確認する


psql -p 5433 -l
 ※[データベースリスト]参照
psql -p 5433 sasa_test
# select * from sasa_table;
 ※[データ情報確認]参照

[データベースリスト]


[データ情報確認]


これでPostgreSQLでも同期がされていることが確認できました。今まではPostgreSQLではpg_poolなどを利用してプーリングをさせることが一般的でした。MySQLを使用していた理由としてレプリケーション機能は大きいものでした。これでPostgreSQLでも同様のことができることが確認できましたので、新しいシステムでも使用する頻度もおおくなりそうです。

今回のブログでは「http://codezine.jp/article/detail/7109」の情報を参考に記載させていただきました。この場をお借り致しましてお礼申し上げます。ありがとうございました。