スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

すべてストアードプロシージャのすゝめ

ちょっとした規模の開発でSQLの呼び出しをほとんどストアードプロシージャー(以下SP)で作りました。
まあ、基本的にはうまくいったと思いますが、色々問題点もあったので、その反省と今後のやり方について書いてみます。今後も改善していくので、この文章もどんどん変えていく予定です。
今回のシステムではメインがPHP5.4でYiiフレームワークを使っています。DBはPostgreSQL9.1です。

■良かったこと

1. 更新系のSQLを一つにまとめられる
いくつものテーブルを更新する場合、トランザクションにまとめて処理することになります。
このときトランザクションの開始から、SQLの実行、コミットまで何度もDBにアクセスする必要があります。
この通信コストがもったいないです。
必要な更新を一回のSPコールで済ませるのなら、呼び出し回数が減らせます。
試してはいませんが、オートコミットでもいけそうです。

何行もの更新が必要な場合もPostgreSQLなら配列が使えます。
数が多い行の更新では、分割して何度も呼んだ方がよいかもしれませんが、OLTPであまりそのような用途は無いと思います。

2. ビジネスロジックの集中
SQLはビジネスロジックが現れやすいところだと思います。
ある条件の情報が欲しい場合WHERE句で表現することになりますが、これってまんまビジネスロジックです。
となると、SQLにビジネスロジックを集中すればPHP側は必要なパラメーターを渡すだけになります。
PHPを作る人が画面の動きに集中できて、SQLを書く人はビジネスロジックに集中できます。

3. ぐるぐる回ししない
PHP脳でデータをとってくることを考えると、ループな考え方になりやすいです。
SQL脳でデータをとってくることを考えると、集合論的な考え方になりやすいです。
DBは集合論的なとりかたをすると、パフォーマンスが良いです。
まあ、どうしてもぐるぐる回しにしないとダメな場合もあるんで、その場合はSPの中でカーソルループさせた方がPHPとDB間と通信がないのでお得です。

■悪かったこと

1. 集約し過ぎ
複数の画面で呼ばれるSPを色々パラメーターを渡して、SQLの作り方を変えていたらSPのソースが読みにくくなりました。
画面ごとにそれぞれ別のSPにすればよかったです。
共通する部分は別のSPにしてSPからSPを呼ぶようにすれば、重複も抑えられると思います。

2. チューニングしにくい
1. の影響とも言えますが、ある画面で必要な項目は別の画面では必要なかったりします。
ID固定で多少時間がかかるSQLでも問題無いけど、一覧系でパフォーマンスを悪くしたところがありました。
結局、パフォーマンスチューニングの時に直接PHPにSQLを書いてしのいでしまったので、きちんと分けていれば完全にSPだけで構築できたと思います。

3. 検索と更新が名前で判断つかない
名前で検索と更新をわけなかったため、レプリケーションしているDBに検索だけ渡すようなことができなくなりました。
list_user_data
edit_user_data
のように検索だけならlist、更新系ならeditのようにprefixをつければpgpoolなどで選り分けできます。
ということで、今後は
名前空間_list_
名前空間_edit_
とします。

4. Dataprovider
Yiiの一覧系を表示する仕組みが勝手にORDER BYやLIMITをつけてくるので、チューニングしにくい。
自前のDataproviderを用意すればよかったです。

■ SPを作る時のポイント

1. 戻り値はtypeを定義する。
SPを入れ直す時にDROP TYPE CASCADEが使えるのが便利。

2. 動的SQLで構築する。
動的SQLにしないとインデックスが効きません。

3. SQLの結果を表示する。
デバッグとチューニング用におすすめ。

w_sql := 'SELECT * FROM t_user';
RAISE NOTICE '%', w_sql;

4. エラーは例外で返す。
U0001からエラーを定義しました。
PHP側でのエラーのハンドリングが楽になりました。

RAISE SQLSTATE 'U0003' USING MESSAGE = 'Not found user ID: ' || p_user_id;

5. パラメーター引数
PostgreSQLではSPの呼び出しにパラメーター引数が使えます。
引数の順番に依存するコードをPHPに書かない方が引数の数が増えた時にメンテナンスしやすいです。

select * from aaa_list_uesr(p_user_name := 'aaa', p_age := 10);

■ FAQ
Q1. SQLよりSPの方がパフォーマンスが悪いのではないか?
A1. 呼び出しコスト分嵩みますが、たいしたコストではないです。

Q2. SQLよりSPの方がチューニングがしにくいのではないか?
A2. できあがるSQLは同じような形になるので変わりません。

Q3. ビジネスロジックがSQLに集中するのが気分が悪い。
A3. SQLにビジネスロジックが集まりやすいです。いっそのこと全てをSQLにした方が楽です。
スポンサーサイト

PostgreSQLで16進数文字列を数字に変換

PostgreSQLに数字を16進数文字列に変換する組み込み関数はto_hexです。

select to_hex(65535);
結果:ffff

でこの逆関数が欲しいのですが・・・あれ、無いの?
リテラルなら

select X'ffff'::int;
結果:65535

で、うまくいくようなのですが、カラムに「UFFFF」みたいに入ってて、substringした結果などは適用できません。たぶん組み込みであると思うんですが検索できなかったので、plpgsqlで自作しました。
-- 16進数文字列から数字に変換する関数
CREATE OR REPLACE FUNCTION to_int_from_hex(p_hex text)
RETURNS integer AS $$
DECLARE
    w_hex text;
    w_result int := 0;
    w_length int;
    w_base text := '0123456789ABCDEF';
    w_index int;
BEGIN
    -- 入力チェック
    IF p_hex is null or p_hex = '' THEN
        RETURN null;
    END IF;
   
    -- 長さ取得
    w_length := char_length(p_hex);

    -- 大文字化
    w_hex := upper(p_hex);
  
    -- 一文字づつ変換
    FOR i IN 1..w_length LOOP
        w_index := position(substring(w_hex, i, 1) in w_base);
        IF 0 = w_index THEN
            RETURN null;
        END IF;
        w_result := w_result * 16 + w_index - 1;
    END LOOP;
    RETURN w_result;
END;
$$ LANGUAGE plpgsql;

これを使うと、こんな感じになります。

select to_int_from_hex(substring('UFFFF', 2, 4));
結果:65535

もし、組み込み関数でできる方法を知ってる人がいましたら、教えてください。

Tag : PostgreSQL

pgpool-iiのPostgreSQL8.4への対応

PostgreSQL8.4にpgpool-iiを使おうとしたら、リカバリーコマンドを作るところでエラーが発生しました。

pgpool-recovery.c: In function ‘pgpool_recovery’:
pgpool-recovery.c:47: error: ‘textout’ undeclared (first use in this function)
pgpool-recovery.c:47: error: (Each undeclared identifier is reported only once
pgpool-recovery.c:47: error: for each function it appears in.)
pgpool-recovery.c: In function ‘pgpool_remote_start’:
pgpool-recovery.c:79: error: ‘textout’ undeclared (first use in this function)
make: *** [pgpool-recovery.o] エラー 1

どうやら、textoutが宣言されていないみたい。
textoutの宣言をgrepで探しまわったら、utils/builtins.hにありました。
ということで、pgpool-recovery.cの上の方にincludeを追加したところ、コンパイルが成功しました。

#include "postgres.h"
#include "fmgr.h"
#include "miscadmin.h"
#include "executor/spi.h"
#include "funcapi.h"
#include "utils/builtins.h"

Tag : PostgreSQL

14のアンチパターンに学ぶデータベースの開発/運用(運用フェーズ)

前回の続き、運用フェーズです。

■運用フェーズ
1. 不法占拠
説明:昔の表が残っていて、データベースを占拠している。つかわれているか分からない表が残っている。
感想:一時的に表を入れ替えたいとかあると、こういういらない表が増えますね。地道に消すか、運用ルールを決めて必ず削除するようにしないとダメだろうなぁ。

2. 無関心(エラーが起こるまで異常に気がつかない)
説明:外部からの監視や正常動作の監視が抜けている。
感想:きちんと生きていて、繋がるかのチェックを忘れちゃいかんということのようです。プロセスの生き死にの確認だけで満足しちゃいそう・・・

3. 掃除しない部屋
説明:データベースのメンテナンス(再構成など)をしていない
感想:定期バッチにしこめばいいのにね。そもそも、そういう意識がないDBAが問題って話か。

4. タイタニックシンドローム(絶対に沈まないと思って緊急時の備えを怠る)
説明:準備不足でリカバリできない、万が一の備えが足りない
感想:・・・・リカバリのテストをしる!

5. 振り返れば相手はいない
説明:DBサーバを再起動したら、APサーバにコネクションが残ってて接続できなくなる
感想:なんとなく、APサーバ停止→DBサーバ停止→DBサーバ起動→APサーバ起動ってやっていたので、このトラブルは無かったです。でも、言われてみれば重要なポイントです。

6. 自分で自分を診断する(自分を冷静に判断することはできない)
説明:クラスタウェアがチェックに失敗する
感想:「クラスタウェア使わなければ、いいじゃない」みたいなオチになっているようです。うーん、確かに自分で書いた方がシンプルに書けそうですね。

■まとめ
開発フェーズの方は負荷テストをしろだし、運用フェーズはリカバリや待機系えの切り替えなどのテストをしろ、ってことかと理解しました。当たり前のことも多いですけど、チェックリスト的に使えそうです。ちなみに、この特集の最後にOracleのコンサルに頼むといいよーって宣伝があって、麦茶吹いた。

14のアンチパターンに学ぶデータベースの開発/運用(開発フェーズ)

DBマガジン12月号の記事の感想。この記事読むためだけに買いました。

■開発フェーズ
1. データ量の暴力
説明:フルスキャンしている、インデックスが張っていない
感想:SQL書いたらふつー実行プランくらい見ないの?って思いました。特にどんどんデータが溜まっていくテーブルには要注意ですね。

2. 過ぎたるはなお及ばざるがごとし
説明:インデックスの張りすぎで、更新が重くなる。
感想:適当にインデックス決めるのはよくないようです。

3. 組み合わせ爆発
説明:直接による意味の無い莫大な処理が発生、結合条件の付け忘れ
感想:これってアンチパターンというよりも、完全にバグでしょ。レビューで発見するしか手はないですね。つい先日も人のソースの中からこれ見つけて、潰しました。

4. ねずみ算
説明:結合するテーブルが多すぎる
感想:え?これ問題なの?確かに一般的に結合するテーブルが多くなると重くなるって言われてますが、フルスキャンとかに比べたらかわいいもんで、そんなにパフォーマンスに影響しないと思うけど・・・

5. 大河ドラマ「大作SQL」
説明:巨大なSQL、パズルのようなSQLで性能がでない
感想:巨大SQL大好き\(^o^)/ でも、性能は出るように書いてるよ! っていうか、複数回SQL呼ぶよりも1回の方が早くね?

6. 臭い物に蓋をすると、もっと臭くなる
説明:ビューを多用しており性能がでない。ビューの中に無駄な処理が含まれる。
感想:ビューもきちんとレビューしようよ。そういえば以前やったプロジェクトでは商品マスタにテストデータ入れようとおもったら実はビューになってて、そのビューの中のテーブルにデータ入れようと思ったらまたビューでさらにまたビューだった時はあきらめましたね・・・

7. バケツRDBMS
説明:RDBMSを、データ格納のための1つの大きな箱としか見ていない。
感想:あったこんなプロジェクトorz ホストからの移行なんですがデータ構造がホストのやつそのまま。当然のごとくプロジェクトは破綻しました。

8. 太っちょ(多くの列を持つ表)
説明:基本的な正規化が行われていない、列が多すぎる表
感想:これもあったなぁ。やはりパフォーマンスがでませんでした。DB設計は素人がやっちゃだめだと思います。

運用フェーズは次回。
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。