タグ: PostgreSQL

PostgreSQLカンファレンス2007

最近忙しすぎてブログの更新が全くできない状態がつづいていますが、PostgreSQLカンファレンス2007が2007/6/5(火)に秋葉原UDXにて開催されます。私もRoom C(定員60名: 16:00~16:55)で講師を務めさせていただきます。私がメールを読んでいなかった為、私の資料は印刷物には入っていません… この為当日自分で持って行くことになってしました…
# 関係者の皆様、ご迷惑をおけしました。

Room C(60名)16:00~16:55
WEB+DBアプリケーションのセキュリティ(仮)
PHPユーザ会/日本PostgreSQLユーザ会
大垣靖男 様

セキュリティ対策はまず基礎知識から、ということでPostgreSQLを安全に利用するための基礎知識を解説させていただきます。今回は初めてデモを予定しています。SQLインジェクションに脆弱だといかに簡単に不正にデータを取得したり、データベース設計を解析できるかをデモンストレーションする予定です。SQLインジェクションに再確認されたい方、SQLインジェクションは知っているがどの程度の攻撃が可能か詳しく知らない方であれば、PostgreSQLユーザのみでなく、全てのSQLデータベースを利用されている開発者に有用な講演になると思います。

ローソンチケットによるとまだチケット(2000円です。会場費などに利用されます)は買えるようです。いろいろ有用なセミナーがそろっています。都合が良い方は是非お越しください。

Sigres – PostgrSQLの高速化

新しいバージョンがリリースされたようです。

PostgreSQLのINSERT/UPDATEを高速化するSigresの0.1.3をリリースします。

http://sourceforge.jp/projects/sigres/

SigresはUPSの存在を前提に、信頼性を若干犠牲にする代わりに、挿入処理に関して大幅な性能向上を実現します。

いまなおpgsql-hackersからコメントをもらう段階にありますので、使用には注意してください

コミットされたデータが絶対にないと困る、アプリもありますがそうでないもの多くあります。例えば、何かのログなどは電源トラブルで最後の方のコミット済みのログレコードがいくつか無くなるかも知れない程度で高速化できるのであれば十分なアプリも多くあると思います。Pgpoolなどでレプリケーションしているので一つ壊れてもOKな場合もあると思います。

SigresはデフォルトインストールのPostgreSQLに対しては数倍~数十倍の性能向上を示します。しかしWALファイルをtmpfsにおいた場合では、せいぜい10%から19%程度の性能向上しか示しません。

「数倍~数十倍の性能向上」だとMySQLより随分速いですね。得意・不得意があるので簡単に比較できないですが、2,3倍でも十分MySQL以上の性能になります。気になるのはMySQLのBerkeleyDBと比べて安全性がどれくらい犠牲になっているか?という点です。そのうち調べよう。

安定版になったら使ってみます。

PostgreSQLでSHA1

MS Access 2003でSHA1を使おうと思ったらどうもうまく動作しませんでした。
.NETのmbcorlibにMD5,SHA1等のハッシュ関数が定義されていてVBからはMSDNに書いてるサンプルで動作するのですがMS AccessのVBAからはいろいろ試しても動作しませんでした。サンプル通りだとオブジェクトを生成する行でシンタックスエラーになり、多少変更しても動作しませんでした。
# 普段VBAでプログラミングはしないので私が無知なだけかもしれませんが…
# Windowsプログラマの方はハッシュはあまり使わない??

VBAでハッシュが使える方が良かったのですが、しかた無くPL/Pythonでハッシュ値を取得し返す関数を定義することにしました。

PL/Pythonの場合、

CREATE OR REPLACE FUNCTION SHA1HASH( TEXT ) RETURNS CHAR(40) AS ‘
import sha
hash = sha.new( args[0] )
return hash.hexdigest()
‘ LANGUAGE plpythonu;

言語を登録していない場合

CREATE LANGUAGE plpythonu;

を先に実行しておきます。8.0からplpythonはplpythonuに名前が変更されています。(Untrustedな言語なのでuが付く)

PL/Perlの場合は多分こんな感じ(こちらは試していません)

CREATE OR REPLACE FUNCTION SHA1HASH( TEXT ) RETURNS CHAR(40) AS ‘
use Digest::SHA1 qw(sha1);
return sha1($args[0]);
‘ LANGUAGE plperl;

同じようなコードでMD5, SHA256, SHA512等が簡単に作れます。

普通パスワードはハッシュ化したパスワードを保存するのでユーザ情報テーブルのパスワードフィールドはハッシュ値になっています。

SELECT * FROM user WHERE username = ‘USERNAME’ AND password = sha1hash(‘PASSWORD’);

といった感じでクエリできるようになります。アプリの言語側でハッシュが使える場合はアプリ側でハッシュ化する方が好ましいのですがVB等でライブラリが制限されている場合には便利かも知れません。

PostgreSQL 8.2 リリース

PostgreSQL 8.2がリリースされました。

主要な変更点

Among the features of this new version are:

* Higher performance (+20% on OLTP tests)
* Improved Warm standby databases
* Online index builds
* SQL2003 aggregates
* Improved TSearch2 with Generalized Inverted Indexes
* Support for DTrace probes
* Advisory Locks
* New ISN/ISBN and pgCrypto modules
* Selective pg_dump options

参考: アプリプログラマに影響が大きい変更点
http://blog.ohgaki.net/index.php/yohgaki/2006/09/29/postgresql_8_2_beta

PostgreSQL 8.2 Beta

PostgreSQL 8.2のリリースノートは非常に長いのですがアプリケーションプログラマのコーディングスタイルに大きく影響するのは次の変更(追加)だと思います。

Add INSERT/UPDATE/DELETE RETURNING (Jonah Harris, Tom)

This allows these commands to return values, such as the computed serial key for a new row. In the UPDATE case, values from the updated version of the row are returned.

MySQL等で挿入後のID番号が取得できる機能がPostgreSQLでも作れるようになりました。

create table test (
id serial,
msg text,

primary(id)
)

としてidフィールドの値が何になったのか分からないと困る場合が多かったので

create table test (
id int8,
msg text,

primary(id)
)

としてsequenceの次の値を取得してidにセットしていたと思います。今後はより気軽に

INSERT INTO test(msg) VALUES (‘abc’) RETURNING *;

として挿入後のデータが参照できるようです。
Ruleやストアドプロシージャでデータを加工している場合にも便利ですね。

PostgreSQLの場合、6.xのころからRuleを使うと比較的簡単に全ての変更をログできるのですが、この機能を使えばアプリケーション側でデータのログを取るコードが簡略化できます。データベースレベルではアプリケーションを操作しているユーザIDが分からない場合が多いので便利になります。

PostgreSQLに関する間違い情報

PostgreSQL 8.0は2005年1月にリリースされました。SAVEPOINTは8.0からサポートされている機能ですが、2006年4月の記事で「サポートしていない」と間違った情報が記載されているページを見つけました。

 ロールバックはトランザクションの開始処理まで戻るのが基本であるが,「セーブポイント」機能を利用すれば部分的な処理の取り消しが可能になる。図4[拡大表示]上に示したようにトランザクションの中に「SAVEPOINT(セーブポイント名)」を設定し,「ROLLBACK TO(セーブポイント名)」とすれば,ROLLBACKからSAVEPOINT間の処理を取り消すことができる。1つのトランザクションには複数のセーブポイントが設定可能である。セーブポイント機能はOracleにはあるがPostgreSQLには無い

しかし、よく見ると

出典:日経オープンシステム 2003年2月号 142ページより
(記事は執筆時の情報に基づいており,現在では異なる場合があります)

とあり書いた時点では正しい情報です。3年も経つとかなり変わりますから技術記事の再利用は難しいですね。この記事は新人対象のようですがこれを読むとそのまま信じてしまいそうです。

PostgreSQLのプリペアードクエリとUnitテスト

PostgreSQLのプリペアードクエリを使ったプログラムのユニットテストを書いてユニットテストの書き方の問題に気がつきました。普通にユニットテストを書くと

relation with OID ##### does not exist

というPL/PgSQLで良く見かけるエラーが発生してしまう場合があります。

通常ユニットテストを書く場合、テストのセットアップ関数(メソッド)で

create table test (a text, i int);

テスト終了時に

drop table test;

と書きます。

プリペアードクエリの場合、コンパイルされたSQL文がバックエンドにキャッシュされるので「Web環境などで永続的な接続」を行っている環境でこの様な初期化、終了ルーチンを持つユニットテストでPostgreSQLのプリペアードクエリを実行するテストを「2回以上」行うと

relation with OID ##### does not exist

とエラーが表示されてしまいます。キャッシュされた(プリペアされた)SQL文に対応するテーブルが削除されてしまった後もクエリはキャッシュされているので削除されたの存在しないテーブルアクセスしようとしているので上記のエラーがでます。
# プリペアされたクエリは名前でなくOIDでテーブルにアクセスするので
# このエラーが発生します。

コマンドラインからのユニットテストの場合、接続の種類は影響しませんがPHPでWebサーバ上でユニットテストを実行する場合、pg_prepare, pg_execute を使ったプログラムのユニットテストにはpg_connectを使うほうが良いです。pg_connectを使っていても同じ接続を使っている間にテーブルの削除/作成を行ってもpg_pconnectを使っている場合と同じ理由でこのエラーが発生します。コマンドラインでもすべてのテストを実行するスクリプトを利用している場合は注意が必要だと思います。PL/PgSQLのエラーが何故発生するのか理解していれば直ぐに原因に気が付くと思いますが、そうでなければなかなか気が付かないかもしれません。

私も最初は何の事だか解らずググってもPL/PgSQLのエラーに関連するページが表示されていました。pg_prepare/pg_executeについては少なくとも上位には表示されなかったので書いておきます。

PostgreSQLのプリペアードクエリの実装(と言うか実際にOID参照しているもの)を詳しく調べてから書いていないので「実装と違う」という場合は是非指摘してください。

# Javaのユニットテストでこの問題が発生する、というページ
# も無かったような気がします。当り前すぎ(FAQ)だから?

# 問題を回避するもう別の方法にDEALLOCATEを使ってプリペアされた
# SQL文を削除しておく方法もあります。削除するのは面倒ですがこちら
# の方法ならどのような環境でも困らないと思います。

MySQLからPostgreSQLに乗り換えた理由。MySQLより10倍速かったから?!

hackers-jp@ml.postgresql.jp に掲載されていたメールの話です。
色々興味深いです。

MyISAM -> InnoDBで容量が約10倍。InnoDBとPostgreSQLを比べても約3倍。
キャパシティプランニングはDB設計の重要な要素ですが、知らないと困ったことになりますね。

MySQLであまり大きなDBを取り扱ったことが無かったので知らなかったですがMyISAM->InnoDBで10倍にもなるケースもあるとは…

JPUGのMLに書かれていた以外の理由としては1/3のメモリで3倍速く、データのパーテションニング、パーシャルインデックス等のPostgreSQL機能を近い将来利用する事が出来ることもスイッチする理由だそうです。

For an application like FeedLounge, the faster counting and smaller row size of PostgreSQL are compelling reasons to use it instead of MySQL with InnoDB tables. Add that it runs in 1/3 the time, using 1/3 the memory (making it essentially 10x faster for us) and that we can start taking advantage of other Postgres features, like data partitioning and partial indices in the near future, and you have the reason we decided to make the switch.

軽くて速いPostgreSQLは彼らにとって「10倍速い」と言っています。何だか一般的に言われていること(MySQLは軽くて速い)と反対ですね。

どのDBを選択するか?は利用条件、要求事項によって最適な選択が異なります。MySQLの方が自分にとっては「10倍速い」と考えるユーザも多いと思います。どちらにしてもこの事例は参考になります。

以下、メール本文のコピーです。

—–
寺本@横須賀 です。

最近メールがないので…
雑談ですが、ちょっと面白いのでご紹介です。(via Planet Postgresql)

FeedLoungeという、RSSフィードのオンラインリーダーを提供している(らしい)サービスがあるのですが、そこがDBをMySQLからPostgreSQLに乗り換えた、という話です。

http://feedlounge.com/blog/2005/11/20/switched-to-postgresql/

何が理由で乗り換えたのかに興味があったので読んでみたんですが、

—-

Some of the reasons we decided to make the switch to PostgreSQL:
 * Database size - when we switched from MyISAM tables to InnoDB tables
   in MySQL, the size of our database grew from ~1GB to 10+GB! When we
   made the switch this weekend, the MySQL InnoDB database was using
   34 GB, and the same data in a PostgreSQL database is only 9.6 GB
   - this should keep our hardware costs down a bit.
 * Load time - The current MySQL setup takes over a day to restore the
   current database, the load of the data into the PostgreSQL database
   took just over 4 hours.

—-

という今まで聞いたことも無い理由で乗り換えたとのことでした。
# PGよりも3倍も容量が多くなっちゃったなんて!

その後にPG派とMy派でいろいろコメントがついてます。
どうもでっかいPrimary Keyがついてるらしく、それがまずいんじゃないのか?的話になっていうように読めました。(斜め読みです)

— Junji Teramoto / teramoto.junji (a) lab.ntt.co.jp Master Yoda : Don’t think…feel…be as one with the Source. Help you, it will.

pgpool 3.0.0 & global development team

http://pgfoundry.org/forum/forum.php?forum_id=668

によると

PgPool team is about to form “PgPool Global Development Group” whose concept is pretty similar to PostgreSQL Global Development Group. i.e. a virtual entity on the Internet. PgPool is currently being developed by Tatsuo Ishii. We want to switch to “PgPool Global Development Group” which will lower the barrier for hackers who wants to join this project.

と、チーム体制の開発に移行するそうです。素晴らしい  :)

PostgreSQL 8.1.2リリース

PostgreSQL 8.1.2, 8.0.6, 7.4.11 と 7.3.13がリリースされています。

8.0と8.1の変更

— Character string locale comparison bug. This may require a REINDEX
on text column indexes in some locales, such as Hungarian.
— Prevent accidental changes of locale by plperl
— Two fixes for Japanese encodings
— Two fixes for COPY CSV
— Fixes for functions returning RECORD
— Fixes to autovacuum, dblink and pgcrypto

pgsql-jpに載っていますが問題が発生した場合分かりづらい日本語エンコーディング問題の修正が含まれています。

PostgreSQL 8.1.1リリース

PostgreSQL 8.1.1がリリースされました。

http://www.postgresql.org/ のLATEST RELEASESからダウンロードできます。

Outer Join、CHECK文の不具合、sub selectの不具合などが修正されているそうです。他にも色々修正されているので不具合でお困りの方は試してみてはいかがでしょうか?

Athlon64 3200+/3GB/SATA HDD/1GbE/MomongaLinux x86_64開発版 にPostgreSQL 8.1.1をインストールしてPentiumDのマシンからpgbenchを実行した結果 (スケールファクタ 10、TCP接続設定以外はデフォルト)

[yohgaki@dev php-4.4.1]$ pgbench -p 5432 -h 192.168.100.120 -U yohgaki -c 10 -t 1000
starting vacuum…end.
transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 282.428887 (including connections establishing)
tps = 282.667102 (excluding connections establishing)
[yohgaki@dev php-4.4.1]$ pgbench -p 5432 -h 192.168.100.120 -U yohgaki -c 10 -t 1000 -S
starting vacuum…end.
transaction type: SELECT only
scaling factor: 10
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 4822.840200 (including connections establishing)
tps = 4892.360725 (excluding connections establishing)
[yohgaki@dev php-4.4.1]$ pgbench -p 5432 -h 192.168.100.120 -U yohgaki -c 10 -t 1000 -N
starting vacuum…end.
transaction type: Update only accounts
scaling factor: 10
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 301.807334 (including connections establishing)
tps = 302.361132 (excluding connections establishing)

share_buffers=20000 (デフォルトは1000)

[yohgaki@dev php-4.4.1]$ pgbench -p 5432 -h 192.168.100.120 -U yohgaki -c 10 -t 1000
starting vacuum…end.
transaction type: TPC-B (sort of)
scaling factor: 10
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 329.863325 (including connections establishing)
tps = 330.396068 (excluding connections establishing)
[yohgaki@dev php-4.4.1]$ pgbench -p 5432 -h 192.168.100.120 -U yohgaki -c 10 -t 1000 -S
starting vacuum…end.
transaction type: SELECT only
scaling factor: 10
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 5771.386160 (including connections establishing)
tps = 5875.513153 (excluding connections establishing)
[yohgaki@dev php-4.4.1]$ pgbench -p 5432 -h 192.168.100.120 -U yohgaki -c 10 -t 1000 -N
starting vacuum…end.
transaction type: Update only accounts
scaling factor: 10
number of clients: 10
number of transactions per client: 1000
number of transactions actually processed: 10000/10000
tps = 397.720948 (including connections establishing)
tps = 398.210553 (excluding connections establishing)

PostgreSQL 8.1リリース

実はリリースされていた事は知りつつスルーしていたのですが微力でも宣伝を。

PostgreSQL 8.1は本当に速いでも紹介していたPostgreSQL8.1がリリースされています。

速いだけでなくうれしい新機能も盛り沢山です。是非使ってみてください。

新しい、先進的データベース機能

ロール:PostgreSQLがデータベースロールをサポートするようになりました。ロールにより、データベース権限が複雑に関連し合うような大規模なユーザ管理が簡単に行うことができます。

IN/OUTパラメータ:PostgreSQLの関数がIN、OUT、INOUTパラメータをサポートするようになりました。これにより、J2EEや.NETアプリケーションにおける複雑なビジネスロジックのサポートが改良されました。

2相コミット(2PC:Two-Phase Commit):2PCはWAN環境におけるアプリケーションで長い間待ち望まれていた機能です。広域ネットワーク上に分散したサーバ間でACID特性を持つトランザクション処理を可能にします。(訳注:「ACID」とは、Atomicity(原子性)、Consistency(一貫性)、Isolation(隔離性)、Durability(耐久性)の頭文字です。)
性能の改善

マルチプロセッサ(SMP)環境における性能改善:バージョン8.1におけるバッファ管理機構は、8-way、16-wayシステムやデュアルコア、マルチコアCPUのサーバにおいて、プロセッサ数に対してほぼリニアにスケールします。

ビットマップスキャン:インデックスは状況に応じて自動的にメモリ内部でビットマップに変換されます。この機能によって、大規模テーブルに対する複雑な問い合わせのインデックス検索が20倍以上高速化されます。また、複数列に対するインデックスを作成する必要性が減少し、データベース管理が簡単になります。

テーブルパーティショニング:バージョン8.1では、制約による排他といわれる技法を用いて、問い合わせプランナが大規模なテーブル全体をスキャンしないようになりました。他のデータベース管理システムにおけるテーブルパーティショニング同様、これにより数ギガバイトのテーブルに対する性能とデータ管理が改良されました。

共有行ロック:PostgreSQLの「行レベルロックよりも優れた方法」に、共有行ロックの機能を追加することで、より高い同時実行性をサポートできるようになりました。共有ロックにより、大量のOLTPを必要とするアプリケーションでの挿入・更新の性能が向上するはずです。

Reliable Computer Solutionsのデータベース管理者であるMerlin Moncureは次のように述べています。「PostgreSQL 8.1によって(私たちの)Opteron デュアルプロセッサ運用用サーバで非常に大きな性能向上が実現しました。具体的にいうと、CPU負荷において20%、サーバの負荷特性においては20から40%もの性能向上が見られました。」

この他にも120項目以上におよぶ改良が行なわれています。詳しくは8.1 プレスキットを参照してください。

ダウンロード: http://www.postgresql.jp/PostgreSQL/download.html
日本PostgreSQLユーザ会: http://www.postgresql.jp/
PostgreSQL Project: http://www.postgresql.org/

PostgreSQL 8.1は”本当”に速い

@ITのDatabase Watch 10月版に「PostgreSQL 8.1は”速い”とうもっぱらのうわさ」と記事があります。

「うわさ」ではなく「本当」に8.1は速いです。

ミッションクリティカルなシステムでpgpoolを使用している所も多いと思いますが、8.1+pgpoolの相性は良いようです。pgpool無しで直接PostgreSQL8.1にアクセスした場合、pgpoolでPostgreSQL 8.1 2台でロードバランスさせてベンチマークするとINSERT、UPDATE、SELECT全てのケースで処理効率が向上している事が分かります。特に
SELECTのみクエリは100%以上の効率化となるベンチマーク結果(私が計測したケースでは102%)となる場合もあります。
# つまり2台にした場合、2台分よりも速くなるケースがある。
# 負荷を分散させたため各サーバがより効率良くクエリを実行できた
# ため論理値以上である102%の速度向上が見られたと考えられます。

私は試していませんが片岡さんに聞いたところ8.1では同時接続数が増えてもスループットの低下が少ないという情報も聞きました。500接続くらいではあまり速度低下が発生しないそうです。

私も8.1はまだまだテスト中ですがPostgreSQLの性能でお困りの方は8.1をテストしてみてはいかがでしょうか?