pgFouine

PostgreSQLウォッチにpgFouineなる統計ソフトの紹介があった。
目の付け所はすごく良いんだけど、クエリパターンの分類が、SELECT, INSERT, UPDATE, DELETEのパターンと時間別だけってのは、はっきり言って使えない。これらの4種類が分類できたところでどの負荷分散ソフトを使えばいいかぐらいしか分からないからだ。
おそらく重要なことは、どういったパターンのSELECT文が発行されていて、どのテーブル(もっと言えば、どのカラム)へのfetchが多いのかを統計で出せるようにすればインデックスの張り方とか、チューニングポイントが分かるはずだからだ。
ANALYZEコマンドで統計情報は出せるけど、それはあるクエリパターンごとでしかない。もうすでに完成されているシステムがあって、だけど遅すぎるって時には、一つ一つ見ていくことになってしまうわけで、そういったときに統計ソフトがあるとどこから調べていけば良いかの道しるべになる。
なにか良いソフトがあるといいんだが。。。

“pgFouine” の続きを読む

IDENT authentication failed for user “****”

PostgreSQLでログインできない場合には、このエラーが出るわけだけど、多くのパターンでは、pg_hba.confの設定がログインできない設定になっているからだ。
昔はまったのは、違うpg_hba.confを編集していてログインできずに悩んだことはあったが、今回はそれとは違うことではまってしまった。
pg_hba.confはアクセス制御として上から順にマッチングするものをmethodを使って認証することになっている。だから、もしpg_hba.confに
local all all ident sameuser
# IPv4 local connections:
host all all 127.0.0.1/32 ident sameuser
# IPv6 local connections:
host all all ::1/128 ident sameuser
なんていうのがあったら、その後に何を書いたとしても、ローカル接続はPostgreSQL起動ユーザーでしかログインできないことになる。この場合には、この設定の前に書くというのが重要。
今日はこれに1時間ほど時間を費やしてしまいました。

LIMITとOFFSETがSQL92準拠ではない??

今日、Oracle 10gのiSQL*PlusからSQLを発行していたときにPostgreSQLで実行していたときと同じようにLIMIT句をSELECT文の最後に付けて発行したらなぜかエラーになった。
LIMITなんてしらねーよって。。。。
ま、まさかLIMITがSQL標準ではないのか??と思って探したら、やはりSQL標準ではないようです。(最後のほうに載っています)
PostgreSQLではランダムに取り出す ORDER BY random()というユーザー定義関数を使った並び替えもできるのだが、PostgreSQLに慣れるとどれが標準なのかわからずに使ってしまい、結果的に危ないかも。。。
でも確か、LIMITはMySQLでも使えたはずだから、事実上の標準であり、ただしOracleは対応していないと考えることもできるのかもしれない??

phpMyAdmin

phpMyAdminはVer.2.8.2以前と以降では認証方法が変わったらしい。特にデータベースへアカウント情報を保持しなければならなくなったので、少しは面倒になったようだ。
セキュリティ上の問題があるVer.2.7.0plを使う場合には、Basic認証等の何らかの第三者に使われないような仕組みを別途用意すべきだろう。旧バージョンはSourceForge.netからダウンロードできる。

Mysqlに関する管理コマンド

#rootユーザーのパスワードを設定
mysqladmin -u root password '(password)'
#DBの追加
mysql -u root -p
mysql> CREATE DATABASE hogehoge
なお、5.0では大文字小文字を区別するらしく、create databaseはエラーになる。
#ユーザーの追加(すべてのDBに権限を付与の場合)
mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON *.* TO (username)@localhost IDENTIFIED BY '(password)';
#ユーザーの追加(hogehogeデータベースの任意テーブルに権限を付与する場合)
mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON hogehoge.* TO (username)@localhost IDENTIFIED BY '(password)';

COALESCE

取り出したデータがNULLのとき、「-」表示をしたい場合などはどのようにされていらっしゃるだろうか?たとえばプログラム側でデータを取り出してきて、NULLだったら代わりに「-」を出力するというロジックが思いついた場合には、COALESCEというSQL92の関数の利用を検討すると良いと思う。
たとえば、hogehogeフィールドからデータを取得したい場合には、
SELECT COALESCE(hogehoge, ‘-‘)
FROM table_name;
とすることでhogehogeがNULLだった場合に-が返るようになる。SQL92対応なので、基本的にはどのRDBMSでも対応しているはず。COALESCEは引数を無制限にとるので、COALESCE(a, b, c)ならばaがNULLならbが、bがNULLならcが利用されるといった形になってとても便利である。
このようなテクニックが数多く掲載されているプログラマのためのSQL 第2版はSQLを日ごろ利用している人でも一見の価値があると思う。

pgpool-II

pgpool-IIなるものがリリースされていました。pgpoolでは2台までしか負荷分散できないという恐ろしい制約がありましたが、pgpool-IIではその制約もなくなり、pgpoolAdminというWebで設定可能なツールも用意されています。これは検証してみる価値はありそうですね。
http://pgpool.sraoss.jp/index.php
http://pgpool.projects.postgresql.org/pgpool-II/ja/

テスト環境がBladeSymphony (10 blade)っておそらく反則だと思います。そうそうこんな環境は用意できませんから。

PostgreSQLのエンタープライズ環境

PostgreSQLには高可用性と負荷分散に関する製品が存在するが、いずれもフリーだ。メリットとデメリットは以下のとおり。
Slony-I(スローニーワン)

シングルマスターマルチスレーブ方式。メリットはクラスター数に制限が無いこと。クラスター数を増やしても負荷が増えない(スレーブのスレーブにもできる)こと。デメリットはマスターからのコピー時間を制限できないこと。このことによりスレーブ間に時差が発生することがある。

pgpool(ピージープール)

マルチマスター方式。メリットは仕組みが非常に簡単(クエリを単純に自分自身とスレーブに投げるだけ)。デメリットはクラスター数が2台まで。完全にノード間が同じ状態であるか(クエリーエラーを考慮しないので、マスターとスレーブが同期されない可能性がある)は保証されない。

PGCluster(ピージークラスター)

マルチマスター方式。メリットはクラスター数に制限が無いこと。
クエリを負荷に応じて分散するロードバランサー、更新系クエリを他のノードにコピーしてクラスター間が正しい状態かどうかを監視するレプリケーション、そしてクラスターの最低3台が必要。もちろんすべてを1台で兼ねることもできるが、何ら意味は無い。
デメリットは、クラスターが増えるとノードすべてにコピーを反映させるのに時間がかかるようになり、指数関数的に待ち時間が増える(もちろん選択系クエリはクラスタが多いほうが早い)こと。負荷が多くなるとなぜかコピーが正常に行えない問題などがある。

おそらく選択系クエリと更新系クエリのどちらの頻度が多いかによって選ぶべきなのだろうと思われる。

“PostgreSQLのエンタープライズ環境” の続きを読む

DMLとDDL

DML(Data Manipulation Language)
SELECT, INSERT, UPDATE, DELETEなどレコードの追加や変更、削除に使われるSQL言語

DDL(Data Definition Language)
CREATE, ALTER, DROPなどテーブルの追加や変更、削除に使われるSQL言語