Fusic Developers Weblog

Archive for the ‘PostgreSQL’ Category

{ 2010.12.12 }

PostgreSQLのログをデバッグに活用しよう

category：PostgreSQL｜author：s-kawano｜tag：fac2010, log｜comment：non

はじめての人もそうでない人もはじめまして。河野と申します。

Advent Calendar も中頃、私は３回目に突入しました。

さて、本日は「PostgreSQLのログをデバッグに活用しよう」というお話を皆様にしたいと思います。

ログを見る前に

さっそくログを見ていきたいところですが、まずはログの出力が行われているか確認します。
ログの出力先はOSやサーバによって違うかと思いますが、今回は以下のように設定されているものとします。

postgresql.conf


#log_destination = 'stderr'
redirect_stderr = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1440
log_rotation_size = 0

ざっくり言うと…

標準出力にログを吐き出し、吐き出されたログは pg_log 以下の postgresql-曜日.log へ保存。
ログは 1日ごとにローテーションするという設定です。

ざっくりすぎま…せんよね？

ちなみに「log_destination」を syslog に設定することで syslog を利用する事が可能ですが、メッセージの一部が syslog では出力されない可能性があるため信頼性は高くありません。

Linux であれば基本的に「log_destination」を stderr に。
「redirect_stderr」を on に設定して PostgreSQL にログローテーションまで任せましょう。

何を出力するか

さっそくログを見ていきたいところですが、まずは何を出力するか設定します。
デバッグに役立つ設定値をかいつまんで、紹介差し上げたいと思います。

● エラーのSQLを記録

SQLにエラーが発生した場合、エラーとなったSQLをログに出力したい時、以下の設定を error に設定することでログに保存できます。
開発中やテスト時には error に設定を変更する事をおすすめします。

私のサーバではデフォルトで panic となっていました。
これはエラー時もログを出力しない設定となります。


#log_min_error_statement = panic

● 時間のかかるSQLを記録

複雑なSQLやインデックスの貼り忘れ、テーブルの結合など、色々な条件が原因でレスポンスがなかなか帰って来ないことがあります。
その原因となったSQLを知りたい場合は「log_min_duration_statement」の設定をしましょう。

デフォルトでは -1 無効となっています。
ここの設定値はミリ秒単位ですので30秒ならば 30000 と設定しましょう。


#log_min_duration_statement = -1

データが少ない開発時より、運用されてからこの設定が生きてくることもあるかと思います。

● クライアントからの接続・切断を記録

ひとつのデータベースを複数のアプリケーションで参照する場合、以下の設定を行っておくと接続元のIPが記録されるため、どこからの接続が多いかがログから判断できます。

デフォルトでは off となっています。
有効にする場合は on に設定してください。


#log_connections = off
#log_disconnections = off

もし PostgreSQL の最大接続数に達するほど接続数が増えている場合は、安易に max_connections を増やさずにログを見てから判断しましょう。

● すべてのSQLを記録する

とにかく全てのSQLを記録したい。
レガシーなプログラムのデバッグでは有用になってくるかもしれない設定です。

そのようなプログラムのデバッグをせねばならない状況…お察しします。
この設定値で頑張ってください！

デフォルトでは none となっています。
全て出力したい場合は all と設定してください。


#log_statement = 'none'

PostgreSQLのログをデバッグに活用しよう

いかがでしたでしょうか？
データベース設計や運用・保守はメインではない（とお考えの）開発者ですとデータベースのログを見ることはあまり無いのではと思います。
とくに最近のフレームワークはいい具合にSQLのログなどを出力してくれる為、開発中は見なかったなんて方もいるかも…。

ログは運用されてから発生してくる問題の原因究明にこそ役立ちます（だから開発中見なかったはダメｗ）

さて、ログの設定は終わりましたか？
では、さっそくログを見ていきましょう！

{ Page Top }

{ 2010.12.8 }

PostgreSQLでクロス集計！　tablefunc　crosstab

category：PostgreSQL｜author：yasumoto｜tag：fac2010, PostgreSQL｜comment：1

皆様こんにちは、
新婚初日のウィークdayから午前帰宅で新妻にメールで泣かれた安元です。

結婚式の前後でAdvent　Calendarに当たるなんて、
きっと日頃の行いがいいからだと思います！
早くも２回目になっているのは気のせいです。

閑話休題

本日のお題はクロス集計です。
SQLを実行した結果は、基本的に下に々伸びるものです。
あなたも思ったことがあるのでは無いでしょうか？
「SQLの結果が横に伸びれば。。。。」
そうなんです、SQLの結果を横に伸ばすことが出来れば、クロス集計が行えるんです。
今日はそんなお話です。

まずはサクッとyumでpostgresql-contribをインストールしましょう
このpostgresql-contribには、postgreSQLをいろいろと便利にしてくれる関数などのSQLが格納されています
yum install postgresql-contrib
インストールが完了したら、以下のコマンドでDBに関数を生成します
postgrsql-contribの設置場所は環境に合わせて変更してください。
psql -U postgres -f /usr/share/doc/postgresql-contrib-8.4.1/tablefunc.sql dbname　 SET CREATE FUNCTION CREATE FUNCTION CREATE TYPE CREATE TYPE CREATE TYPE CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION
さて準備がととのいました。
pgAdminなどでDBを確認してみてください。関数などが増えているはずです。

今回使用するのは以下のようなデータです。
ID｜氏名｜性別｜登録日
12/1～12/5までに登録があったユーザーと性別が登録されています。
このデータから、日付と性別でクロス集計して見ましょう。


 id |  name   | sex |       created
----+---------+-----+---------------------
 1 | USER001 | f   | 2010-12-01 00:00:00
 2 | USER002 | t   | 2010-12-05 00:00:00
 3 | USER003 | t   | 2010-12-01 00:00:00
 4 | USER004 | t   | 2010-12-02 00:00:00
 5 | USER005 | f   | 2010-12-02 00:00:00
 6 | USER006 | t   | 2010-12-04 00:00:00
 7 | USER007 | t   | 2010-12-05 00:00:00
 8 | USER008 | f   | 2010-12-04 00:00:00
 9 | USER009 | f   | 2010-12-04 00:00:00
10 | USER010 | t   | 2010-12-01 00:00:00
11 | USER011 | f   | 2010-12-04 00:00:00
12 | USER012 | t   | 2010-12-04 00:00:00
13 | USER013 | f   | 2010-12-04 00:00:00
14 | USER014 | t   | 2010-12-05 00:00:00
15 | USER015 | t   | 2010-12-02 00:00:00
16 | USER016 | t   | 2010-12-05 00:00:00
17 | USER017 | f   | 2010-12-02 00:00:00
18 | USER018 | t   | 2010-12-01 00:00:00
19 | USER019 | t   | 2010-12-02 00:00:00
20 | USER020 | t   | 2010-12-05 00:00:00
(20 rows)

①GROUP BYによる日付ごと、性別ごとの人数を集計

集計といえば、GROUP BYとCOUNT()ですね。


SELECT
	created,
	sex,
	count(id)
FROM
	customer
GROUP BY
	created,
	sex
ORDER BY
	created,
	sex ;
       created       | sex | count
---------------------+-----+-------
 2010-12-01 00:00:00 | f   |     1
 2010-12-01 00:00:00 | t   |     3
 2010-12-02 00:00:00 | f   |     2
 2010-12-02 00:00:00 | t   |     3
 2010-12-04 00:00:00 | f   |     4
 2010-12-04 00:00:00 | t   |     2
 2010-12-05 00:00:00 | t   |     5
(7 rows)

②crosstab関数の利用

①で生成したSQLをcrosstab関数に投げ込みます


SELECT
	*
FROM
	crosstab(
                -- ここから①のSQL
                'SELECT
                        created,
                        sex,
                        count(id)
                 FROM
                        customer
                GROUP BY
                        created,
                        sex
               ORDER BY
                       created,
                       sex'
                -- ここまで
) AS customer(
        -- 最終的に出力するデータの型を指定します
        days timestamp,
        male bigint,
        female bigint
);

        days         | male | female
---------------------+------+--------
 2010-12-01 00:00:00 |    1 |      3
 2010-12-02 00:00:00 |    2 |      3
 2010-12-04 00:00:00 |    4 |      2
 2010-12-05 00:00:00 |    5 |
(4 rows)

さあ、これだけで、縦に日付、横に性別をもったクロス集計が出来上がっています！
SELECT * FROM crosstab('GROUP化SQL')　AS (結果表のデータ型指定)
たったこれだけで、あとはPHPなりRubyなりでぐるっとforeachでも回せば、
集計表の出力が行えます。
ただのグループ化よりも、はるかに簡潔なプログラムになることうけあいです！

余談ですが、このままでは12/3日の集計が出ていませんね。。。
12/3はだれも入会しなかったのでしょう。データないものはGROUP BYでも集計できません。
先日ご紹介したgenerate_seriesも合わせて利用して見ましょう


SELECT
	days,
	coalesce(F,0) AS male,
	coalesce(T,0) AS female
FROM
	crosstab(
'SELECT
	days,
	sex,
	count(id)
FROM
(
	SELECT
		date(''2010-12-1'') + a::int as days
	FROM
		generate_series(0,4) as a
) as generate
LEFT OUTER JOIN
	customer
ON
	(generate.days = customer.created)
GROUP BY 
	days,
	sex
ORDER BY 
	days,
	sex
'
) AS customer(
	days date,
	F bigint,
	T bigint
);

    days    | male | female
------------+------+--------
 2010-12-01 |    1 |      3
 2010-12-02 |    2 |      3
 2010-12-03 |    0 |      0
 2010-12-04 |    4 |      2
 2010-12-05 |    5 |      0
(5 rows)

これで3日と5日の「０」も集計できました。
FOR文やIF分の分岐が多い、またネストが多くなる場面で使用すると
驚くほどの速度向上が見込めることがありますので、おためしあれ。

{ Page Top }

{ 2010.12.3 }

Postgres generate_series関数によるデータの生成

category：PostgreSQL, SQL｜author：yasumoto｜tag：fac2010, PostgreSQL｜comment：non

はじめましてこんにちは、明日（１２／４）結婚式の安元です。

弊社期待の新人の活躍によりクジを引き当て、

結婚式の前日にAdvent Calendar2010に寄稿することになりました。

明日の結婚式でいきなりスピーチさせてやろうかと思います。。。。

というわけで、本日はPostgres　generate_series関数のご紹介です。

集合を返す関数　generate_seriesについて

さて、Postgres8系からgenerate_seriesという関数が使えるようになっております。
何をする関数か。以下をごらんいただければ一目瞭然


SELECT * FROM generate_series(1,5);

 generate_series
-----------------
               1
               2
               3
               4
               5
(5 rows)

このように連番を生成してくれます。

第3引数を渡すことで、刻み（増分値）の変更も出来ます


 SELECT * FROM generate_series(1,10,2);
 generate_series
-----------------
               1
               3
               5
               7
               9

日付計算を行うことで、カレンダーも生成出来ます


SELECT
    date('2010-12-1') + a::int as day
FROM
    generate_series(0,5) as a
    day
------------
 2010-12-01
 2010-12-02
 2010-12-03
 2010-12-04
 2010-12-05
 2010-12-06
(6 rows)

generate_seriesを利用した集計

さて、「連番が作れるのはわかった。それの何がうれしいの？」　という方もいらっしゃるかと思います。
DBは、データの集計は得意ですが、そこに存在しないデータは集計することは出来ません。
しかし、generate_seriesを利用すし、集計のキーとなる値を生成することで、「0」という集計を行えるのです。

以下のようなデータを日付ごとに合計します


 id |    days    | customer
----+------------+----------
  1 | 2010-12-01 |       10
  2 | 2010-12-01 |       15
  3 | 2010-12-03 |       14
  4 | 2010-12-03 |       13
  5 | 2010-12-03 |        8
  6 | 2010-12-04 |       12

(5 rows)

SELECT days , sum(customer) FROM customer GROUP BY days ORDER BY days;
    days    | sum
------------+-----
 2010-12-01 |  25
 2010-12-03 |  35
 2010-12-04 |  12
(3 rows)

GROUP BYを使うとそこに存在する日付だけが集計情報として表示されます。

generate_seriesを使ってみましょう。


SELECT

	generate.days,
	sum(customer) 
FROM 
(
	SELECT
		date('2010-12-1') + a::int as days
	FROM
		generate_series(0,4) as a
) as generate
LEFT OUTER JOIN
	customer 
ON ( generate.days = customer.days)
GROUP BY generate.days 
ORDER BY generate.days;


    days    | sum
------------+-----
 2010-12-01 |  25
 2010-12-02 |
 2010-12-03 |  35
 2010-12-04 |  12
 2010-12-05 |
(5 rows)

このままでは、「0」がとれませんのでcoalesce関数でもう一工夫


SELECT

	generate.days,
	coalesce(sum(customer) ,0) --←変更してのはココ
FROM 
(
	SELECT
		date('2010-12-1') + a::int as days
	FROM
		generate_series(0,4) as a
) as generate
LEFT OUTER JOIN
	customer 
ON ( generate.days = customer.days)
GROUP BY generate.days 
ORDER BY generate.days;


    days    | sum
------------+-----
 2010-12-01 |  25
 2010-12-02 |  0
 2010-12-03 |  35
 2010-12-04 |  12
 2010-12-05 |  0
(5 rows)

これで日付ごとの集計がそろいました。

プログラムで、IF分岐でも出来る処理ですが、ビジネスロジックが複雑な場合など
集計処理の一環として「存在しないデータを作る」とコードがすっきりすることもありますよ。

{ Page Top }

Fusic Developers Weblog

Archive for the ‘PostgreSQL’ Category

PostgreSQLのログをデバッグに活用しよう

ログを見る前に

何を出力するか

● エラーのSQLを記録

● 時間のかかるSQLを記録

● クライアントからの接続・切断を記録

● すべてのSQLを記録する

PostgreSQLのログをデバッグに活用しよう

PostgreSQLでクロス集計！ tablefunc crosstab

①GROUP BYによる日付ごと、性別ごとの人数を集計

②crosstab関数の利用

Postgres generate_series関数によるデータの生成

集合を返す関数 generate_seriesについて

generate_seriesを利用した集計

Official

tag

PostgreSQLでクロス集計！　tablefunc　crosstab

集合を返す関数　generate_seriesについて