アシアルブログ

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

非エンジニアのWebディレクターにオススメのGUIツール(Windows) MySQL/PostgreSQL編

こんにちは、鴨田です。

大分久しぶりの投稿になりますが、今回はコマンドラインが苦手な非エンジニアのWebディレクターにオススメする、MySQLPostgreSQLGUIツール「HeidiSQL」をご紹介いたします。



インストールの前に



これから行う説明に関して、PuTTYなどで普段からリモートサーバーへ接続を行っている前提で、話が進んだりしますので、その点だけご注意をお願いします。ローカル環境であれば、特に気にしないで問題ありません。また、今回の説明はMySQLを用います。



インストール



まずはHeidiSQLのサイトから、インストーラーをダウンロードします。

http://www.heidisql.com/download.php


「Installer」ボタンをクリックすると、最新版がダウンロードできます。32+64bit installerになっているので、OSのバージョンは気にしないでも大丈夫です。今回は、バージョン9.2.0.4948を使用します。

インストーラーを起動すると、こんな画面が表示されます。


インストール方法で気をつけるところは特にありません。どんどん進めます。


インストールが完了すると、この画面になります。早速起動して、設定を行いましょう。



設定



起動すると、こんな画面が表示されます。


「Unnamed」セッションを選択して、接続したいデータベースの情報を入力します。

○ローカルPC内のDBに接続する場合

[設定]タブ
ネットワーク種別:MySQL(TCP/IP)
ホスト名 / IP:127.0.0.1
ユーザー:DBのユーザー名
パスワード:DBのパスワード
ポート:DBのポート番号
データベース:DB名

DBを作成したときに設定した情報を入力してください。リモートサーバーの場合もほぼ同様で、「Hostname /IP」をサーバーのIPに変えるだけです。

SSH経由でリモートサーバーに接続する場合

[設定]タブ
ネットワーク種別:MySQL(SSH tunnel)
ホスト名 / IP:リモートサーバーのIP
ユーザー:DBのユーザー名
パスワード:DBのパスワード
ポート:DBのポート番号
データベース:DB名

[SSHトンネル]タブ
plink.exe:PuTTYplink.exeを参照
SSHホスト+ポート:踏み台サーバーのIP:port番号
ユーザー名:踏み台サーバーのユーザー名
パスワード:踏み台サーバーのパスワード
秘密鍵ファイル:踏み台サーバーの秘密鍵を参照
ローカルポート:ローカルポート番号

リモートサーバーの情報は、インフラエンジニアに聞くか、プログラムのDBの接続設定を覗いて、入力してください。踏み台サーバーの方は、PuTTYなどの設定を流用してください。

設定が完了したら、「保存」ボタンを押して、セッション情報を保存しておきましょう。



テーブルの内容表示



データベースに接続すると、画面表示が変わります。


画面左側にデータベースのテーブルが表示され、画面右側に選択したテーブルの詳細が表示されます。画面下部はGUIで操作した内容のコマンドなどが出力されます。

サンプルとして、郵便番号情報を格納した「post」テーブルを用意しました。約125000件のカラムが格納されています。

データの中身を見るには、画面左側のビューで「post」テーブルを選択します。


画面右側のビューでは、「データ」タブをクリックします。
すると、テーブルの内容が見られるようになります。




検索(フィルターパネル)



該当のテーブルから、検索したい文字列を含むカラムを絞り込みたいときは、フィルターパネルを表示します。メニューから「編集」→「フィルターパネル」を選択するか、「Ctrl+Alt+F」です。


パネルに、検索したい文字列を入力すると、カラムが絞り込まれます。




並び替え



カラムを並び替えることも出来ます。


並び替えパネルをクリックし、表示されたウィンドウで、「カラムの追加」をクリックすると、フィールドの選択をすることが出来ます。フィールドを選択したら、「ASC(昇順)」、「DESC(降順)」をトグル式で選択し、「OK」ボタンを押すと、データが並び替えられて表示されます。




データの操作



データの変更は変更したい値のところで、シングルクリックを2回(ゆっくりダブルクリック)すると、データを編集することが出来ます。


データの編集が終わったら、Enterもしくは違う場所をクリックすると、編集が完了します。


この状態だとまだ変更が確定していない(左上に赤い三角マークが付いている状態)ので、違う場所をクリックなどすると、コマンドが流れて確定します。


データのコピーや貼り付けなどは、右クリックから行うことが出来ます。NULLの挿入も右クリック→値の挿入→NULLで可能です。同様に、行の追加や削除などをすることが可能です。




データの取得範囲



初期設定では、カラムの取得件数は1,000件となっており、「次」で、次の1,000件を取得したり、「すべて表示」で10,000件まで取得します。

レスポンスが遅くなりますが、この設定は変更することが出来ます。メニューからツール→環境設定を選択してください。「Text formatting」タブを選択し、「Number of rows displayed in data tab(Step, Maximum)を変更してください。




以上です。

他にも、いろいろ機能はあるのですが、基本的な使い方のご紹介でした。なにか問題が起こったときの調査や、テスト時にデータベースの内容を変更したいときなどで使う分には、この程度の操作で充分だと思います。

次回は、GitのGUIツールをご紹介したいと思っています。それではまた。

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」の情報を参考に記載させていただきました。この場をお借り致しましてお礼申し上げます。ありがとうございました。

pgpoolを使ったPostgreSQLのレプリケーション

こんばんは、牧野です。
前回、その前の前とPostgreSQLのチューニングについてでしたが今日もPostgreSQL関連で、PostgreSQLでのレプリケーションについてです。
MySQLの場合、別のソフトウェアを使わなくてもMySQLだけでレプリケーションを実現できますが、PostgreSQLの場合は別途専用のソフトが必要になります。
今回はそんなソフトの1つ、pgpool-II(以下pgpoolと書いています)を使ったレプリケーションを紹介します。

pgpoolを使うと、PostgreSQLで手軽にマルチマスタ方式のレプリケーションを実現できます。
他にも、コネクションプーリング、アクティブスタンバイ、slony-I等のレプリケーションソフトと組み合わせたマスタ・スレーブ方式のレプリケーション、時間がかかる検索処理を複数サーバで並列処理させる(パラレルモード)というようなことができたりします。

詳しくはこちらのページをご覧下さい。
http://pgpool.projects.postgresql.org/pgpool-ja.html

まずインストールですが、インストールは簡単にできます。pgfoundryのページにソースがあるので、ダウンロードします。
http://pgfoundry.org/frs/?group_id=1000055&release_id=1187
ファイルを解凍、展開して、そのディレクトリに移動後、


./configure
make
make install

します。インストール場所を変更する場合はconfigure時に


./configure --prefix=/var/lib/pgpool

のようにprefixで指定します。今回は/var/lib/pgpoolにインストールしました。
PostgreSQLは、今回は2台のPC(colinuxCentOS)にPostgreSQL8.3.3をソースコンパイルでインストールしました。そのうちの1台にpgpoolも同居しています。

次に設定です。pgpoolの設定ファイルは3種類で、pgpoolのディレクトリ内のetcにあります。

■pgpool.conf
pgpoolのメイン設定ファイル。

pcp.conf
pgpool管理用コマンドを使うユーザ、パスワードを設定。

■pool_hba.conf
pgpoolにアクセスするホスト、ユーザの認証方式を設定。
PostgreSQLのpg_hba.confにあたります。

とりあえず、.sampleファイルをコピーして各ファイルを作成します。
以下、概要です。

pcp.confには
(ユーザ名):(md5で暗号化されたパスワード)
を書きます。pg_md5コマンドを使うと便利です。
pg_md5 パスワード
で、暗号化パスワードが出力されます。

pool_hba.confはpg_hba.confと同様の書き方ですが、認証方式にtrust、reject、pamしか使えないなどの制限があります。


pgpool.confについて、レプリケーションモードで使用する主な設定項目を挙げると、、、

■listen_address
■port
 pgpoolがアクセスを受け付けるアドレス、ポート番号

pcp_port
 pgpool用管理コマンドで使用するポート番号

■enable_pool_hba
 trueの場合、pool_hba.confを使った認証を行います

■num_init_children
 pgpoolへのアクセスを受付けるプロセスの数
 問い合わせを途中でキャンセルする時にはもう1つ別プロセスを使うことになるので、想定数よりも大きめの値を設定します

■logdir
 デフォルトで/tmpになっていますが、ちゃんとログを取りたい場合は専用のディレクトリを設定します

■health_check_period
 数字を指定すると、アクセスがない場合でも指定した秒間隔でデータベースにつながるかチェック(ヘルスチェック)するようになります

■health_check_user
 ヘルスチェックを行うユーザ名
 パスワードなしでデータベースにアクセスできるユーザを指定します

■backend_hostname#
■backend_port#
■backend_weight#
 pgpoolで使用するPostgreSQLのホスト名、ポート、負荷分散時のアクセスウェイトを設定
 #の部分にはデータベースの数に応じて0,1,2...を入れて設定します。2台の場合は


backend_hostname0 = 'localhost'
backend_port0 = 5432
backend_weight0 5
backend_hostname1 = '192.168.1.123'
backend_port1 = 5432
backend_weight1 = 5

のように2つ分設定します

■replication_mode
マルチマスタ型レプリケーションを行う時はtrueを設定

■load_balance_mode
select文を負荷分散させる時はtrueを設定

こんな感じで設定します。
認証についてですが、データベースにアクセスするには、pool_hba.confで設定したpgpoolの認証を通った後、データベースのpg_hba.confで設定してある認証を通る必要があります。
レプリケーションモードではデータベースの方のpg_hba.conf設定にも制限があって、
trust, reject, password, pam
しか使えないので注意して下さい。

あと各PostgreSQLにはデータベースを作る権限のある同一ユーザを作成しておきます。

設定できたら、各PostgreSQL、pgpoolを起動します。
pgpoolの起動ですが、redhat系の場合は自動起動用のスクリプトがpgpoolのソールディレクトリのredhat/pgpool.initにあります。
スクリプトの最初の方の変数を適宜環境に合せて書き換えて、


PGPOOLENGINE=/var/lib/pgpool/bin
PGPOOLDAEMON=$PGPOOLENGINE/pgpool
PGPOOLCONF=/var/lib/pgpool/etc/pgpool.conf
PGPOOLLOG=/var/lib/pgpool/log/pgpool.log

/etc/init.d/pgpoolにコピーします。
これで


/etc/init.d/pgpool start

で起動できます。



psql -h (pgpoolのホスト) -p (pgpool.confのport) -U (ユーザ名) (データベース名)

で接続できればとりあえず成功です。

せっかくなので、pgbenchでベンチマークを取ってみます。
pgbenchは、PostgreSQLソースディレクトリ内のcontrib/pgbench
へ移動して


make
make install

でインストールできます。



pgbench -i -h (pgpoolのホスト) -p (pgpool.confのport) -U (ユーザ名) (ベンチマーク用に新規作成するデータベース名)
pgbench -h (pgpoolのホスト) -p (pgpool.confのport) -U (ユーザ名) (上のコマンドで指定したデータベース名)


を実行すると、

pgpoolと2つのPostgreSQLの場合


starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
tps = 14.349405 (including connections establishing)
tps = 15.667645 (excluding connections establishing)


単独PostgreSQLの場合


starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
number of clients: 1
number of transactions per client: 10
number of transactions actually processed: 10/10
tps = 52.104772 (including connections establishing)
tps = 62.106015 (excluding connections establishing)

クライアント数が1だと3倍以上速くなっていることがわかります。クライアント数が増えたら差は小さくなりそうですが、思ったより速くなりました。

…以上、文章は長くなってしまいましたが、pgpoolを使う環境は簡単に作れます。PostgreSQLが重い場合は試してみてはいかがでしょうか。


最後おまけで、、pgpoolにはpgpoolAdminというウェブ管理ツールがあります。初期設定は設置後/install/にアクセスして行え、各ノードの確認や設定ファイルの変更が簡単にできたりと便利です。ソースはpgpoolと同様pgfoundryからダウンロードできます。

PostgreSQLの手軽なSQLチューニング

こんばんは、牧野です。
今日は前々回の話題に戻って、PostgreSQLのチューニングの話です。

この前は重いSQLをどうやって見つけるか紹介しました。今回は処理を速くするためのSQLの具体例を紹介します。

1.インデックスを使う
以前も書いたので省略しますが、データ数が多くなってくると(数万件以上とか)インデックスが正しく使えているかどうかで負荷のかかり方が大きく変わってきます。
複数カラムの条件検索の場合は、必要に応じて複合インデックスを作成します。その時は、プログラムの方でWHERE句の順番に気をつけましょう。

2.VACUUMとREINDEX
特にバッチプログラムで頻繁にデータ更新を行うようなテーブルがある時は要注意です。VACUUMをしないままで運用していくと大変なことになる場合があります。バッチ処理の後等定期的にVACUUMするようにしましょう。
自動バキュームを使うのも有効です。8.1、8.2の場合はpostgresql.confのautovacuumをonにします。
8.3だとデフォルトで自動バキュームがオンになっています。あと、8.3にするとvacuumそのものの必要性がかなり低下します。
データ更新が頻繁にあるテーブルでは、定期的にREINDEXもしましょう。
REINDEX TABLE テーブル名;
大量のデータを更新してしまっている場合、VACUUMだけではあまり改善しないことがあります。
そんな時はVACUUM FULLを実行します。

10万件余りのデータを10回INSERT、DELETEした場合の比較


test=> explain select count(*) from music;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Aggregate  (cost=10747.35..10747.35 rows=1 width=0)
   ->  Seq Scan on music  (cost=0.00..10476.28 rows=108428 width=0)
(2 rows)

test=> vacuum full music;
VACUUM
test=> explain select count(*) from music;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Aggregate  (cost=2209.35..2209.35 rows=1 width=0)
   ->  Seq Scan on music  (cost=0.00..1938.28 rows=108428 width=0)
(2 rows)

VACUUM FULL後は1/5くらいのコストになりました。でも、VACUUM FULL中はそのテーブルがロックされて読み書きともにできなくなります。本番環境で実行する時は気をつけて下さい。
また、テーブルのデータを全部消す時は、TRUNCATEを使った方がいい場合がほとんどです。DELETE時に実行するトリガはTRUNCATE時には実行されませんが、TRUNCATEだとVACUUM、VACUUM FULLの必要がなくDELETEよりも高速です。

3.COUNT()、MAX()、MIN()、DISTINCTに注意
COUNT()、MAX()、MIN()関数やDISTINCTを使うと、対象カラムにインデックスが張ってあってもデータ数が多くなってくると重くなることがあります。
MAX()、MIN()はORDER BYで、DISTINCTはGROUP BYで代用可能です。



test=> explain select max(second) from music;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Aggregate  (cost=2209.35..2209.35 rows=1 width=4)
   ->  Seq Scan on music  (cost=0.00..1938.28 rows=108428 width=4)
(2 rows)

test=> explain select second from music order by second desc limit 1;
                                              QUERY PLAN                              
---------------------------------------------------------------------------
 Limit  (cost=0.00..0.05 rows=1 width=4)
   ->  Index Scan Backward using music_second_index on music  (cost=0.00..5263.88 rows=108428 width=4)
(2 rows)




test=> explain select distinct artist_id from music;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Unique  (cost=11006.32..11548.46 rows=2143 width=4)
   ->  Sort  (cost=11006.32..11277.39 rows=108428 width=4)
         Sort Key: artist_id
         ->  Seq Scan on music  (cost=0.00..1938.28 rows=108428 width=4)
(4 rows)

test=> explain select artist_id from music group by artist_id;
                            QUERY PLAN                             
-------------------------------------------------------------------
 HashAggregate  (cost=2209.35..2209.35 rows=2143 width=4)
   ->  Seq Scan on music  (cost=0.00..1938.28 rows=108428 width=4)
(2 rows)

上の例では処理コストが1/5くらいにはなっていますが、インデックスが張ってないと余計時間がかかります。。。
COUNT()は、あまりいい方法を知りません。重くてどうしようもなかったら、データ数を入れるテーブルを作る、とか。。。何か他にうまい方法を知っている方がいましたらぜひ教えて下さい。

以上、気付いたことから順に挙げました。
MySQLではサブクエリを使うと極端に遅くなることがあった(100倍以上時間がかかった)のでPostgreSQLでも試してみましたが、PostgreSQLではそんなことはないようです。

PostgreSQLパフォーマンスチューニングのために ~重いSQLの見つけ方~

こんにちは、牧野です。
先日、あるウェブサイトで使用しているPostgreSQLを見直す機会がありました。

PostgreSQLのパフォーマンスを手軽に上げるには、
・データベースの設定ファイルのパラメータを変更する
・アプリケーションの方で重いSQLを発行している場所を見つけて、その部分を直していく
・vaccumを実行する
など、いくつか方法が考えられます。
今回は、PostgreSQLで実行時間が長いSQLを見つける方法について紹介します。

本題に入る前に、まずはテスト用データベースを作っておきます。



createdb test


そして、次のようなプログラムを作成、実行して、ダミーデータも入れておきます。



<?php
$db = pg_connect('dbname=test');

$sql = "
    CREATE TABLE artist (
        artist_id serial primary key,
        name text not null
    )
";
pg_query($sql);

$sql = "
    CREATE TABLE music (
        music_id serial primary key,
        artist_id integer not null,
        title text not null
    )
";
pg_query($sql);

for ($i = 0; $i < 10000; $i++) {
    $a_name = serialize(time() + $i);
    $sql = "INSERT INTO artist(name) VALUES('{$a_name}')";
    $res = pg_query($sql);

    $sql = "SELECT artist_id FROM artist WHERE name = '{$a_name}'";
    $res = pg_query($sql);
    
    $tmp = pg_fetch_array($res);
    $artist_id = $tmp['artist_id'];
    
    for ($j = 0; $j < 50; $j++) {
        $title = serialize(time() + $j + $i);
        $sql = "INSERT INTO music(artist_id, title) VALUES({$artist_id}, '{$title}')";
        pg_query($sql);
    }
}
?>


musicテーブルとartistテーブルの2テーブルだけで適当なデータですが、とりあえずこれでデータベースは準備できました。
次に、重いSQLが実行されたらログに出力するようにpostgresql.confを変更します。
postgresql.confは通常、PostgreSQLのデータディレクトリの中(例えば /var/lib/pgsql/data内)に置かれています。

自分のテストサーバはCentOS、PostgreSQL7.4系で、この場合だと以下の部分を変更します。



syslog = 1
log_min_duration_statement = 0


8系だとsyslog = 1ではなく、log_destination = 'syslog' というように変わっています。
log_min_duration_statementについてですが、設定された数字を上回る実行時間のSQLがログに出力されるようになります。
単位はミリセカンドです。※本番で行う場合は、最初は大きい値を設定しましょう。

設定についてはドキュメントに詳細が載っています。
http://www.postgresql.jp/document/pg831doc/html/runtime-config-logging.html

設定ファイルを再読み込みすると、/var/log/messagesに実行されたSQLが全て出力されると思います。


/etc/init.d/postgresql reload


PostgreSQL用のログファイルを作成する場合は、/etc/syslog.confに


local0.* /var/log/postgres.log

というように追記してsyslogを再起動します。



/etc/init.d/syslog restart


ログファイルを見ながらSQLを実行してみると、


tail -f /var/log/postgres.log

May 13 19:22:43 makmak postgres[14003]: [10-1] LOG:  duration: 4145.884 ms  statement: select * from music order by artist_id desc limit 10 offset 100;


こんな感じでログが出力されます。この例だと4秒以上かかっています。
重いSQLが見つかったらexplainを実行します。



test=>explain select * from music order by artist_id desc limit 10 offset 100;

                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Limit  (cost=76910.28..76910.31 rows=10 width=40)
   ->  Sort  (cost=76910.03..78160.04 rows=500001 width=40)
         Sort Key: artist_id
         ->  Seq Scan on music  (cost=0.00..12046.01 rows=500001 width=40)
(4 rows)


cost=の部分が実行時間に関わってきます。マニュアルを見ると、
http://www.postgresql.jp/document/pg831doc/html/using-explain.html

cost=(数字)..(数字)

の最初の数字が、最初の行が返されるまでの時間、次の数字が全ての行が返されるまでの合計時間とありますが、バージョンによって違う場合があるそうです。が、とにかく、artist_idでソートするところとlimitのところで膨大な時間がかかっていることがわかります。
そこで、artist_idにインデックスを定義して、



test=> create index artist_id_index on music(artist_id);


それから実行してみると、



May 13 20:42:58 makmak postgres[15680]: [1-1] LOG:  duration: 49.107 ms  statement: select * from music order by artist_id desc limit 10 offset 100;


実行時間が80分の1以下になりました。
再度explain文を実行すると、



test=> explain select * from music order by artist_id desc limit 10 offset 100;
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Limit  (cost=344.61..379.07 rows=10 width=40)
   ->  Index Scan Backward using artist_id_index on music  (cost=0.00..1723054.54 rows=500000 width=40)
(2 rows)


作ったインデックスが使用されていることがわかります。

…これは極端な例ですが、このような地道な作業の繰り返しで相当パフォーマンスを上げられる場合があります。ユーザが頻繁にアクセスするようなところに一箇所でも重い部分があると、全体のパフォーマンスに大きく影響します。
PostgreSQLが重くて困っている方は試してみてはいかがでしょうか。

Ludia (PostgreSQL + Senna) で全文検索

先日のデブサミで華々しく散ってきた森川です。最近 8.3 がリリースされたPostgreSQLにLudiaという全文検索モジュールを組み込んで、MySQLTritonnと比較してみました。

インストールについては、それぞれのサイトに書いてあるので割愛します(LudiaTritonn)。

今回使用したテキストは青空文庫から太宰治の作品を拝借しました。以下のようなテーブルに作品名と内容を入れています。



PostgreSQL:
  CREATE TABLE ludia_test (
    id serial primary key,
    name text,
    contents text
  );
  CREATE INDEX fulltext_contents_index ON ludia_test USING fulltext(contents);

MySQL:
  CREATE TABLE ludia_test (
    id integer not null auto_increment primary key,
    name text,
    contents longtext
  );
  CREATE FULLTEXT INDEX fulltext_contents_index ON ludia_test(contents);


MySQLでは、text型ではおさまりきらない作品もあったので、contentsをlongtext型としています。インデックスは両方ともMecabを使用した単語インデックスを使用しました。インデックスのサイズは変わりません(同じテキスト・ライブラリを使用しているので当然といえば当然ですね)。


Ludiaの検索についてですが、たとえば「人間」「失格」の2つの単語が入っているもの単語の多い順に調べる場合は、以下のようにします。pgs2getscoreを使用することで、検索スコアを取得することができます。



SELECT name, pgs2getscore(ludia_test.ctid, 'fulltext_contents_index') 
FROM ludia_test 
WHERE contents @@ '*D+ 人間 失格'


Tritonnでは以下のようします。MATCH AGAINSTをSELECTの中で使用することで検索スコアを取得します。IN BOOLEAN MODEを指定することで、Sennaの検索クエリを使用することが可能になります。



SELECT name, match(contents) against('*D+ 人間 失格') as score
FROM ludia_test 
WHERE match(contents) against('*D+ 人間 失格' IN BOOLEAN MODE) 
ORDER BY score DESC;


結果については、完全に同じと言いたいところだったのですが、なぜかスコアの値が若干変わってしまいます。おそらく内部で使用しているSennaバインディング部分が異なるからだと思うのですが、他のクエリでも完全にスコアを同じ値にすることはできませんでした。



Ludia
     name     | pgs2getscore 
--------------+--------------
 人間失格     |          455
 鉄面皮       |           40
 桜桃         |           20
 懶惰の歌留多 |           20
 俗天使       |           15
 二十世紀旗手 |           10

Tritonn
+--------------------+-------+
| name               | score |
+--------------------+-------+
| 人間失格           |   465 | 
| 鉄面皮             |    45 | 
| 桜桃               |    20 | 
| 懶惰の歌留多       |    20 | 
| 俗天使             |    15 | 
| 二十世紀旗手       |    10 | 
+--------------------+-------+


最後にSennaの検索クエリの「*S」演算子を紹介したいと思います。これを使用すると指定した文章と関連した(両方に含まれる単語の数で決まる)文書を検索することができます。たとえば、人間失格のあらすじ(Wikipedia)から関連する文章を取得するクエリは以下のようになります。

SELECT name, pgs2getscore(ludia_test.ctid, 'fulltext_contents_index')
FROM ludia_test
WHERE contents @@ '*S「自分」は人とは違う感覚を持っており... 省略 ...'



                     name                      | pgs2getscore 
-----------------------------------------------+--------------
 人間失格                                      |        12475
 畜犬談                                        |         4835
 思ひ出                                        |         3760
 お伽草紙                                      |         2695
 もの思う葦 ――当りまえのことを当りまえに語る。 |         2345
 男女同権                                      |         2060
 女の決闘                                      |         1575
 新釈諸国噺                                    |         1570
 苦悩の年鑑                                    |         1410
 善蔵を思う                                    |         1365
 パンドラの匣                                  |         1360
 津軽                                          |         1130
 正義と微笑                                    |         1060
 女神                                          |         1060
... 省略 ...
(47 rows)


一方Tritonnで同じことをすると以下のようになります。とりあえず似たような結果になりますが、結構違いますね。



+--------------------+-------+
| name               | score |
+--------------------+-------+
| 人間失格           |  5085 | 
| 猿面冠者           |  4225 | 
| 答案落第           |  4225 | 
| 畜犬談             |  3180 | 
| 服装に就いて       |  2120 | 
| 正義と微笑         |  1905 | 
| パンドラの匣       |  1410 | 
| お伽草紙           |  1060 | 
| 善蔵を思う         |  1060 | 
| 盲人独笑           |  1060 | 
... 省略 ...
20 rows in set (0.00 sec)


使い方に問題があるのかもしれませんが、使用してみた感じでは、完全にTritonnと同じ結果を出すのは難しいようです。ただ、普通に単語検索として使う分にはほとんど結果は変わらないので、問題は特に感じませんでした。

Sennaを使った全文検索といえば、MySQLというイメージがあったのですが、PostgreSQLでも特に問題ないということがわかりました。実際、公式サイトを見る感じでは、色々と実績はあるようなので、これから機会があれば使ってみたいと思います。