思いつくままプログラミングネタを書き殴ります。すべてストアードプロシージャのすゝめちょっとした規模の開発でSQLの呼び出しをほとんどストアードプロシージャー(以下SP)で作りました。基本的にはうまくいったと思いますが、色々問題点もあったので、その反省と今後のやり方について書いてみます。今後も改善していくので、この文章もどんどん変えていく予定です。今回のシステムではメインがPHP5.4でYiiフレームワークを使っています。DBはPostgreSQL9.1です。更新系のSQLを一つにまとめられるいくつものテーブルを更新する場合、トランザクションにまとめて処理することになります。このときトランザクションの開始から、SQLの実行、コミットまで何度もDBにアクセスする必要があります。この通信コストがもったいないです。必要な更新を一回のSPコールで済ませるのなら、呼び出し回数が減らせます。試してはいませんが、オートコミットでもいけそうです。何行もの更新が必要な場合もPostgreSQLなら配列が使えます。数が多い行の更新では、分割して何度も呼んだ方がよいかもしれませんが、OLTPであまりそのような用途は無いと思います。ビジネスロジックの集中SQLはビジネスロジックが現れやすいところだと思います。ある条件の情報が欲しい場合WHERE句で表現することになりますが、これってまんまビジネスロジックです。なると、SQLにビジネスロジックを集中すればPHP側は必要なパラメーターを渡すだけになります。PHPを作る人が画面の動きに集中できて、SQLを書く人はビジネスロジックに集中できます。回ししないPHP脳でデータをとってくることを考えると、ループな考え方になりやすいです。SQL脳でデータをとってくることを考えると、集合論的な考え方になりやすいです。DBは集合論的なとりかたをすると、パフォーマンスが良いです。してもぐるぐる回しにしないとダメな場合もあるんで、その場合はSPの中でカーソルループさせた方がPHPとDB間と通信がないのでお得です。集約し過ぎ複数の画面で呼ばれるSPを色々パラメーターを渡して、SQLの作り方を変えていたらSPのソースが読みにくくなりました。画面ごとにそれぞれ別のSPにすればよかったです。共通する部分は別のSPにしてSPからSPを呼ぶようにすれば、重複も抑えられると思います。影響とも言えますが、ある画面で必要な項目は別の画面では必要なかったりします。ID固定で多少時間がかかるSQLでも問題無いけど、一覧系でパフォーマンスを悪くしたところがありました。結局、パフォーマンスチューニングの時に直接PHPにSQLを書いてしのいでしまったので、きちんと分けていれば完全にSPだけで構築できたと思います。検索と更新が名前で判断つかない名前で検索と更新をわけなかったため、レプリケーションしているDBに検索だけ渡すようなことができなくなりました。list_user_dataedit_user_dataのように検索だけならlist、更新系ならeditのようにprefixをつければpgpoolなどで選り分けできます。いうことで、今後は名前空間_list_名前空間_edit_とします。BYやLIMITをつけてくるので、チューニングしにくい。自前のDataproviderを用意すればよかったです。戻り値はtypeを定義する。CASCADEが使えるのが便利。動的SQLで構築する。動的SQLにしないとインデックスが効きません。SQLの結果を表示する。デバッグとチューニング用におすすめ。U0001からエラーを定義しました。PHP側でのエラーのハンドリングが楽になりました。パラメーター引数PostgreSQLではSPの呼び出しにパラメーター引数が使えます。引数の順番に依存するコードをPHPに書かない方が引数の数が増えた時にメンテナンスしやすいです。呼び出しコスト分嵩みますが、たいしたコストではないです。できあがるSQLは同じような形になるので変わりません。ビジネスロジックがSQLに集中するのが気分が悪い。SQLにビジネスロジックが集まりやすいです。こと全てをSQLにした方が楽です。▲PostgreSQLで16進数文字列を数字に変換PostgreSQLに数字を16進数文字列に変換する組み込み関数はto_hexです。X'ffff'::int;結果:65535で、うまくいくようなのですが、カラムに「UFFFF」みたいに入ってて、substringした結果などは適用できません。組み込みであると思うんですが検索できなかったので、plpgsqlで自作しました。plpgsql;これを使うと、こんな感じになります。4));結果:65535もし、組み込み関数でできる方法を知ってる人がいましたら、教えてください。▲pgpool-iiのPostgreSQL8.4への対応PostgreSQL8.4にpgpool-iiを使おうとしたら、リカバリーコマンドを作るところでエラーが発生しました。1どうやら、textoutが宣言されていないみたい。textoutの宣言をgrepで探しまわったら、utils/builtins.hにありました。いうことで、pgpool-recovery.cの上の方にincludeを追加したところ、コンパイルが成功しました。▲14のアンチパターンに学ぶデータベースの開発/運用(運用フェーズ)前回の続き、運用フェーズです。説明:昔の表が残っていて、データベースを占拠している。つかわれているか分からない表が残っている。感想:一時的に表を入れ替えたいとかあると、こういういらない表が増えますね。地道に消すか、運用ルールを決めて必ず削除するようにしないとダメだろうなぁ。無関心(エラーが起こるまで異常に気がつかない)説明:外部からの監視や正常動作の監視が抜けている。感想:きちんと生きていて、繋がるかのチェックを忘れちゃいかんということのようです。説明:データベースのメンテナンス(再構成など)をしていない感想:定期バッチにしこめばいいのにね。そもそも、そういう意識がないDBAが問題って話か。振り返れば相手はいない説明:DBサーバを再起動したら、APサーバにコネクションが残ってて接続できなくなる感想:なんとなく、APサーバ停止→DBサーバ停止→DBサーバ起動→APサーバ起動ってやっていたので、このトラブルは無かったです。言われてみれば重要なポイントです。自分で自分を診断する(自分を冷静に判断することはできない)説明:クラスタウェアがチェックに失敗する感想:「クラスタウェア使わなければ、いいじゃない」みたいなオチになっているようです。うーん、確かに自分で書いた方がシンプルに書けそうですね。■まとめ開発フェーズの方は負荷テストをしろだし、運用フェーズはリカバリや待機系えの切り替えなどのテストをしろ、ってことかと理解しました。当たり前のことも多いですけど、チェックリスト的に使えそうです。ちなみに、この特集の最後にOracleのコンサルに頼むといいよーって宣伝があって、麦茶吹いた。▲14のアンチパターンに学ぶデータベースの開発/運用(開発フェーズ)DBマガジン12月号の記事の感想。この記事読むためだけに買いました。説明:フルスキャンしている、インデックスが張っていない感想:SQL書いたらふつー実行プランくらい見ないの?って思いました。データが溜まっていくテーブルには要注意ですね。過ぎたるはなお及ばざるがごとし説明:インデックスの張りすぎで、更新が重くなる。感想:適当にインデックス決めるのはよくないようです。説明:直接による意味の無い莫大な処理が発生、結合条件の付け忘れ感想:これってアンチパターンというよりも、完全にバグでしょ。レビューで発見するしか手はないですね。先日も人のソースの中からこれ見つけて、潰しました。臭い物に蓋をすると、もっと臭くなる説明:ビューを多用しており性能がでない。ビューの中に無駄な処理が含まれる。感想:ビューもきちんとレビューしようよ。バケツRDBMS説明:RDBMSを、データ格納のための1つの大きな箱としか見ていない。ホストからの移行なんですがデータ構造がホストのやつそのまま。当然のごとくプロジェクトは破綻しました。感想:これもあったなぁ。パフォーマンスがでませんでした。DB設計は素人がやっちゃだめだと思います。(12/28)PostgreSQLとRubyを組み合わせてブラックリストチェックの速度を測ってみた。