アルパカDiary

■[データベース]Oracle黒本が邪魔くさい件 22:43

息子がいたずらするので手の届かないところに移動したけど、

そもそもこれ場所取ってるだけで

全くもって読んでないのでいらないし！

GW過ぎたらジャンプと一緒に捨てる！*1

ちなみに、息子は何故か「パフォーマンスチューニング編」だけ読んでました。

お気に入りらしい。

ツイートする

■[Postgres][データベース]Postgres8.4のベンチマークを取ってみた 00:12

※2009/7/19追記

こちらの方がおそらく正確でしょう。

先週行う予定だったベンチの結果です。

http://www.techscore.com/tech/sql/pgbench/6.html

http://www.techscore.com/tech/sql/pgbench/7.html

http://www.techscore.com/tech/sql/pgbench/8.html

こちらのベンチ方法を元に各バージョンのベンチマークを比較してみました。

postgresql.confはメモリとかWALとかコネクション関係とか

設定を合わせた方が良いかなーと思いつつ変更していないため、

100%の比較にならないのかもしれません。

でも指標くらいにはなるでしょう。*1

ハード環境（仮想サーバ上のLinux）

CPU	Intel(R) Core(TM)2 Quad CPU Q6600 @ 2.40GHz　（これの１コアのみ）
メモリ	1024MB
OS	CentOS 5

バージョン毎の共通手順

• postgresとpgbenchを普通にインストール
• initdbでDB初期化
• Postgres起動
• DB作成（データベース名＝test）-createdb
• テストデータ作成
  • pgbench -i -s 25 test
• テストが終わったらPostgres停止

テスト方法（こちらのパクリ：http://www.techscore.com/tech/sql/pgbench/6.html）

1. 正確なデータを計測するために、同条件で5回テストを実行しその平均をとります。
2. テスト結果に用いられる値は、tpsの算出にクライアントとの接続の確立にかかった時間を含めないものを使用しています。

• ベンチ 1
  • 1クライアントあたりのトランザクション数を100で固定し、クライアント数のみ増加（10→50→100）
• ベンチ 2
  • クライアント数を1で固定し、1クライアントあたりのトランザクション数を増加（1000→3000→5000）
• ベンチ 3
  • クライアント数・1クライアントあたりのトランザクション数の双方を増加（10/100→30/300→50/500）
• ベンチ 4（検索のみ）
  • クライアント数・1クライアントあたりのトランザクション数の双方を増加（10/100→30/300→50/500）

テスト結果

ベンチ１

-	10/100	50/100	100/100
8.4.0	166.5	170.25	172.75
8.3.7	148.75	-	139.75
8.2.13	129	-	149.2
8.1.17	108.4	-	113
8.0.21	116.8	-	113.8
7.4.25	129.4	-	123.8

ベンチ２

-	1/1000	1/3000	1/5000
8.4.0	135.5	191.75	181.25
8.3.7	132.8	-	175
8.2.13	163.2	-	119.2
8.1.17	102.6	-	111
8.0.21	106.8	-	120.8
7.4.25	119.8	-	136.5

ベンチ３

-	10/100	30/300	50/500
8.4.0	148	222.5	218.75
8.3.7	156.6	-	194.6
8.2.13	144.2	-	130
8.1.17	112.25	-	111.4
8.0.21	114.6	-	126.2
7.4.25	114.6	-	133.4

ベンチ４

-	10/100	30/300	50/500
8.4.0	1037	1205.4	3405.2
8.3.7	965.6	-	1867.2
8.2.13	1064	-	1347.5
8.1.17	1053	-	2280.4
8.0.21	1089	-	2645.6
7.4.25	1004.6	-	1642.5

8.4以外、真ん中の値が無いのはただの手抜きですｗ

バージョンが上がるにつれて、順当にパフォーマンスも良いですね。

ただSELECT（ベンチ４）の結果は結構ばらつきが多く、５回ではなく１０回くらいで行った方がよさそうです。

というか、ベンチ４の結果が怪しいのは

パラメータ設定を合わせてないからのような気がします…

コンフィグパラメータ合わせてのベンチも余裕があればやりたいですね。*2

それにしても集計が結構面倒だったな…

これPerlスクリプトにしてYAML管理出来るようにすればちょっとは需要あるかな？

てか自分が欲しいので後で作ってみよう。

ツイートする

■[Postgres][データベース]Postgresql ビューでdblinkを使用 00:22

postgresqlでカレントデータベースから他のデータベースに接続する場合は

dblinkというcontrib*1をインストールして実現できるのですが、

そのまま使おうとすると非常に使いづらい…*2

なわけで、よく使うテーブルをあらかじめビューにしておくと良いのでは？

と思い調査してみました。

まずはdblinkのインストール*3

# cd /usr/local/src/postgresql-8???/contrib/dblink
# make
# make install
# psql -U postgres -d データベース名 < dblink.sql

これだけでOK。

ちなみに、リンク元のデータベースだけにインストールすればいいみたい。

dblinkを使ってみる

単純に使いたいなら以下の方法（もちろん、hostやportも指定可能）。

select * from 
 dblink(
  'dbname=データベース user=ユーザ password=パスワード',
  'select 列名 [, ..] from テーブル [where ..]')
 as t1(列名 型名 [, ..]);

このように、SELECTする列をいちいち指定しないといけないのがかなり面倒。

「無指定のときは全部TEXTで見る」のようにしてくれたらだいぶ楽になるのになー。

接続識別子を使いまわしたいなら以下の方法。

select dblink_connect('接続識別子','dbname=データベース user=ユーザ password=パスワード');

select * from 
 dblink(
  '接続識別子',
  'select 列名 [, ..] from テーブル [where..]')
 as t1(列名 型名 [, ..]);

select dblink_disconnect('接続識別子','dbname=データベース');

非スーパーユーザで「ユーザ／パス」を省略したい場合は

dblink_connect_u で接続すると良い。要EXECUTE権限。

あと、「insert」「update」「delete」など更新系のsqlを投げる場合は

dblink_exec や dblink_build_sql_???? を使用すればOKです。

ビューにしてみる

上記のように、毎回上記のようなことをやるのは非常に面倒。

なのでビューにしてみました。

create view myremote_pg_proc as
 select * from 
  dblink(
   'dbname=データベース user=ユーザ password=パスワード',
   'select 列名 [, ..] from テーブル  [where..]')
  as t1(列名 型名 [, ..]);

最初の方法でビュー定義しただけですねｗ

これだけでかなり楽になります。

pl/pgsqlにしてみる

dblinkはリモート側の問い合わせ結果をローカルシステム側に渡す前にすべて取り込みます。問い合わせが大量の行を返すと想定される場合、dblink_openでカーソルを開き、一度に管理可能な行数を取り出す方が良いでしょう。

PostgreSQL日本語ドキュメント　dbkink

大量データを取ってくる場合は、カーソルを使った方が良さそうです。

カーソルを使う場合のストアドを書いてみました。

create or replace function myremote_plpgsql(
 OUT col1 text,
 OUT col2 text,
 [..]
)
RETURNS SETOF RECORD AS $$
DECLARE
  rec record;
  dmy  text;
  connect_string  text := '接続識別子';
  cursor_string  text  := 'カーソル名';
BEGIN

  select dblink_connect(connect_string,'dbname=データベース user=ユーザ password=パスワード') into dmy;
  select dblink_open(connect_string,cursor_string,'select 列名 [, ..] from テーブル名 [where..]') into dmy;

  LOOP
    select *
     into rec
      from dblink_fetch(connect_string,cursor_string,1) as (列名 型名 [, ..]);
    IF NOT FOUND THEN EXIT; END IF;
    col1 := rec.列名１;
    col2 := rec.列名２;
    [..]
    RETURN NEXT;
  END LOOP;
  
  select dblink_close(connect_string, cursor_string) into dmy;
  select dblink_disconnect(connect_string) into dmy;

  EXCEPTION
    WHEN OTHERS THEN
      select dblink_disconnect(connect_string) into dmy;  RETURN;
END;
$$ LANGUAGE plpgsql;

全部のストアドに「ユーザ／パス」を書くのはよろしくないので、

dblink_connect_u で接続した方が望ましいでしょう。

（dblinkドキュメント）

http://www.postgresql.jp/document/pg835doc/html/dblink.html

ツイートする

■[データベース][Postgres]Postgresql システム時刻取得関数について発見 23:36

バッチ処理にpl/pgsqlも使ってたりするんですが、

先週たまたまアボートしてログ見てる時に気付いたことが。

あれ、開始と終了の時刻が一緒。

ログ出力で、ストアドの開始と終了に実行日付出してるんですが、

どのストアドみても開始と終了の時刻が一緒になってるんです。

なんで？バグ？

と思って、ドキュメント見直したら仕様でした！

時刻取得にnow()を使ってたんですが、トランザクション内で常に同じ値を返すらしい。

now()はCURRENT_TIMESTAMPと同じもので、伝統的なPostgreSQL関数です。 transaction_timestamp()はCURRENT_TIMESTAMP同様のものですが、明確に何が返されるかを示す名前になっています。 statement_timestamp()は現在の文の実行開始時刻を返すものです（より具体的にいうと、直前のコマンドメッセージをクライアントから受け取った時刻です）。 statement_timestamp()およびtransaction_timestamp()はトランザクションの最初のコマンドでは同じ値を返しますが、その後に引き続くコマンドでは異なる可能性があります。 clock_timestamp()は実際の現在時刻を返しますので、その値は単一のSQLコマンドであっても異なります。

PostgreSQL日本語ドキュメント　9.9. 日付/時刻関数と演算子

なるほどー。トランザクション内で使う場合は

用途で使い分ける必要があるわけですね。

試してみた。

トランザクションなし

postgres=# select now();
              now
-------------------------------
 2009-02-21 23:34:48.343367+09
(1 row)

postgres=# select now();
             now
------------------------------
 2009-02-21 23:34:50.44536+09
(1 row)

普通に現在時刻を返す。

トランザクション設定 -> now(), current_timestamp, transaction_timestamp()

postgres=# begin;
BEGIN
postgres=# select now();
              now
-------------------------------
 2009-02-21 23:35:00.143057+09
(1 row)

postgres=# select now();
              now
-------------------------------
 2009-02-21 23:35:00.143057+09
(1 row)

postgres=# select current_timestamp;
              now
-------------------------------
 2009-02-21 23:35:00.143057+09
(1 row)

postgres=# select transaction_timestamp();
     transaction_timestamp
-------------------------------
 2009-02-21 23:35:00.143057+09
(1 row)

何度実行しても結果は一緒。

トランザクション設定 -> statement_timestamp(), clock_timestamp();

postgres=# select now();
              now
-------------------------------
 2009-02-21 23:35:00.143057+09
(1 row)

postgres=# select statement_timestamp();
     statement_timestamp
------------------------------
 2009-02-21 23:35:39.90937+09
(1 row)

postgres=# select statement_timestamp();
      statement_timestamp
-------------------------------
 2009-02-21 23:35:45.633752+09
(1 row)

postgres=# select clock_timestamp();
        clock_timestamp
-------------------------------
 2009-02-21 23:35:52.520483+09
(1 row)

トランザクション内でも、その時点の時刻を取得。

どれを使ったらいいかはよくわからんので、

とりあえず名前だけで clock_timestamp() に決めました。

全部のストアド置き換えなきゃ…

※ちなみに今回はPostgres8.3.5で試してみましたが、

　Postgres8.1.xだとstatement_timestamp()やclock_timestamp()が存在しないっぽい…

　旧バージョンではどのような代替手段があるんだろう？

ツイートする

■[データベース]Codezin：データベースの基本 01:07

まったくの初心者もこれでバッチリ

12のキーワードから学ぶデータベース基本中のキホン

http://codezine.jp/article/detail/3261

http://codezine.jp/article/detail/3293

『DB Magazine 2007年05月号』 の転載記事らしいです。

まだきちんと読んでいませんが、

DB入門としてはかなり良さげな記事っぽいですよ。

ツイートする

■[データベース]DB設計の考察 14:25

現在アクセスログツールを開発中だったりします。

で、GoogleAnalytics（以下GA）の結果と照らし合わせて

テストを行ったりもします。

基本的な集計結果はGAとほぼ一致するんですが、一つだけわからん事が。

その前に大量ログのさばき方

アクセスログともなると、生ログだけで持っていると

際限なくボリュームが増えていってしまいます。

PVが多いサイトですと月に数百万や数千万PVというのはあたりまえ。

さらにGAはほぼアクセス解析の標準みたいな感じで

大抵のサイトでは導入されています。*1

全部合わせると数十億レコードは下らないでしょう。

（一サイトで月間1億PVのサイトでも導入してるみたいですし）

なので普通は時間軸*2＋集計軸*3で

あらかじめレコードを集計します。

もちろんGAも例外ではないでしょう。画面から大体の物理テーブル設計は見えます。

ユニークユーザの計算方法

まず初めに、ユニークユーザ（以下UU）の計算方法を考えてみます。

ユーザAさんは以下のような行動履歴を取ったとします。

2009/1/1	トップページ→ページA→ページB（離脱）
2009/1/4	トップページ（離脱）
2009/1/5	トップページ→ページC（離脱）

ユーザBさんは以下のような行動履歴を取ったとします。

2009/1/4

トップページ（離脱）

ユーザCさんは以下のような行動履歴を取ったとします。

2009/1/5

トップページ→ページD（離脱）

（Ａ）この時、2009/1/5のUU数は２人（ＡさんとＢさんＣさん*4）です。

（Ｂ）次に2009/1/1〜2009/1/10のUU数は何人でしょうか？３人ですよね。

（Ｃ）では、これを日別に集計したら何人になりますか？

• 2009/1/1→１人
• 2009/1/4→２人
• 2009/1/5→２人

この結果を集計してしまうと、５人になってしまいます。

つまり、期間内UU数はあらかじめ集計しておくことが出来ないという事です。

GAはどうなっているか

GAの結果と自作ログ解析を比べてみると、

GAは「期間内のUU数は上記（Ｂ）の結果と同様」という事がわかりました。

GAの仕様から、日付の範囲は自由に選択することが可能です。

つまり

リアルで生ログを集計している

としか考えられないんです。

生ログとは言わず、「ユニークユーザ別アクセス履歴」に丸める事は可能ですが、

それでもかなりのレコード数になってしまうと思われます。

それを毎回再集計しているんでしょうか？

でもGAのレスポンスは特別悪いわけでもないんですよねー。

Googleだから普通じゃないDB持ってる

と済ませれば簡単ですが、それじゃつまらないじゃないですかｗ

何か他に方法が無いかと考察中です…*5

ツイートする