Hatena::ブログ(Diary)

日々の記録 別館

2012-01-26

xml_fdw（JPUG しくみ分科会発表用）

そういえばこっちに貼っておくの忘れてた。
先週末(1/20)にあった、しくみ分科会の発表資料。
JPUGしくみ分科会資料-xml_fdw
JPUGのページに公開されているのはPDF版でちょっとフォントの関係で表示が微妙なとこもあるので・・・

Permalink | コメント(0) | トラックバック(0) | 00:15

2012-01-19

pgbenchでUNLOGGEDの効果を再確認（接続数=1)

測定環境/方式の変更

前回の検証結果が微妙だったので、永安様のコメント(shared_buffersの増加)と、autovacuum=offの設定を追加して、再度pgbenchの通常テーブル版とUNLOGGEDテーブル版の差異を確認してみた。

shared_buffers 2GB
checkpoint_segments 100
autovacuum off

また、同時接続数を1に絞って、競合を省いた状態で測定してみる。これが通常/UNLOGGEDテーブルでの基本的な差になるはず。

scale factor 100
connect 1
duration 600
その他 デフォルト

測定自体は1回勝負で。

測定結果(tps)

f:id:nuko_yokohama:20120119145538j:image

FILLFACTOR設定(100%） FILLFACTOR設定(80%）
通常テーブル版 395.016214 439.500559
UNLOGGED版 526.332513 585.762816

お、なんかそれっぽい結果になっている。
FILLFACTORが100%の場合も80%の場合も、通常テーブル版のtpsをベースにするとUNLOGGED版はだいたい30%程度の向上効果があるという結果になった。

測定結果(latencies)

f:id:nuko_yokohama:20120119145539j:image

処理＼実行設定 通常/FF=100 通常/FF=80 UL/FF=100 UL/FF=80
BEGIN 0.046743 0.044011 0.034662 0.038746
UPDATE accounts 0.196435 0.156118 0.468165 0.550434
SELECT abalance 0.123869 0.110331 0.100805 0.105568
UPDATE tellers 0.245281 0.217802 0.241394 0.240071
UPDATE branches 0.937295 0.494505 0.893534 0.598381
INSERT hinstory 0.109277 0.098475 0.087406 0.093225
END 0.835786 1.121117 0.045679 0.050481

これも予想どおりEND部分について、通常テーブル版とUNLOGGEDテーブル版ではっきりした差が見られる。

  • END(COMMIT)の背景でWALをファイルに書き出す処理の差が明確に出た結果になったと思う。
  • ただ、UPDATE accountsの処理が何故かUNLOGGEDのほうが時間がかかっている。ここが謎。

あとは、この環境で接続数を増やしてどういった傾向が見られるかを確認すればいいかな。接続数をどのくらい増やすと、WAL書き込み有無による差異がその他の競合により打ち消されていくのだろうか・・・？

Permalink | コメント(0) | トラックバック(0) | 14:50

2012-01-18

バイナリ形式COPY文の怪

ちょっと仕事の関係でバイナリフォーマットをCOPYしようと思って気がついたこと。
(環境はPostgreSQL 9.1.2)

PostgreSQLのCOPY文ではOPTIONでFOMATの指定が可能なのだが、text, csvは普通に記述できるけど、binaryを書くとsyntaxエラーになってしまう。(´・ω・｀)

db=# COPY foo FROM STDIN WITH ( FORMAT binary );
ERROR:  syntax error at or near "binary"

もちろんtextやcsvはそのまま記述可能。
db=# COPY foo FROM STDIN WITH ( FORMAT text );
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>

binaryの場合は二重引用符で引用しないと駄目っぽい。
db=# COPY foo FROM STDIN WITH ( FORMAT "binary" ););
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>>

これは一体どういうことなのか・・・。PostgreSQL文書のCOPYのページにも、特にbinaryのときには二重引用符で引用しなければならない、とは記述がない。
ちょいと不思議な気がしたので、PostgreSQL文書のSQLキーワードのページを見ると・・・

binary予約（関数または型として使用可）
csv未予約
text未予約

となっている。そう、binaryだけキーワードの扱いが違うのだ。
逆に言えば、csvやtextというトークンは実はそのまま名標に書けるということだ。どういうことかと言うと、csvという名前のカラムやtextという名前のテーブルが作成できるということ。binaryという名前は引用しないとそういうことは出来ない。

こうなった経緯は良くわからなかったが、PostgreSQL - bugs - COPY .... WITH (FORMAT binary) causes syntax error at or near "binary" | Threaded Viewを流し読みした感じでは旧バージョン(7.3以前)との互換性などが関係しているようだ。

過去の経緯はともかくとして、とりあえずのPostgreSQL文書COPYのページには「binaryは二重引用符で括らないと駄目よ」という注意書きを書いて欲しいなあと思うのであった。

Permalink | コメント(2) | トラックバック(0) | 14:27

url形式ドメイン（不完全版）

PostgreSQLにURL型みたいなのないの？と聞かれたので、正規表現でチェックするようななドメイン作ってあげればなんとかなるんじゃない？と答えておく。

とりあえずサンプルを作ってみる。本当はRFC 1738 - Uniform Resource Locators (URL)をきちんと読んで、それに合わせた正規表現パターンマッチを作らなきゃいけないけど、とりあえず仮の版ってことで。

test=# CREATE DOMAIN url AS text CHECK (VALUE ~ '^http[s]?://:alnum:]_+-]+(\.[
[:alnum:]_+-]+)+($|/.*$)' ) ;
CREATE DOMAIN
test=# CREATE TABLE test (name text, web url);
CREATE TABLE
test=# \d test
    Table "public.test"
 Column | Type | Modifiers
--------+------+-----------
 name   | text |
 web    | url  |

test=# insert into test VALUES ('nuko', 'http://d.hatena.ne.jp/nuko_yokohama’);
INSERT 0 1
test=# insert into test VALUES ('kuma', 'nttp://foo.bar/');
ERROR:  value for domain url violates check constraint "url_check"
test=#

Permalink | コメント(0) | トラックバック(0) | 10:58

2012-01-13

テーブルがUNLOGGEDで作られたものか調べたい

先日のpgbench UNLOGGEDの調査をしていて思ったんだけど、今のpsqlの\dコマンドでは、どのテーブルがUNLOGGEDになっているのかが一覧からは分からない・・・。

foo=# \d
       List of relations
 Schema | Name  | Type  | Owner
--------+-------+-------+-------
 public | foo   | table | pgxml
 public | foo_u | table | pgxml
(2 rows)

テーブル名を指定すると一応は表示されるけど・・・
foo=# \d foo_u
Unlogged table "public.foo_u"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
 data   | text    |

foo=#

測定のことを考えると、できれば一覧でUNLOGGEDか見たいな〜という気もするので、ビューで作って見ることにする。
とりあえず、\dの背景で生成されるSQL文をサーバログに出力し、そこにUNLOGGEDの情報を追加すればいいはず。
で、UNLOGGEDの情報ってどこで管理しているのかと調べると、pg_classのrelpersistenceに設定されているようだ。

  • "p"なら普通のテーブル(persistent)
  • "u"なら(unlogged)

なので、こんな感じのビューを作ってみる。

CREATE VIEW tablelsit_with_persistent AS (SELECT n.nspname as "Schema",
          c.relname as "Name",
          CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
          pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
          c.relpersistence as "Persistent"
        FROM pg_catalog.pg_class c
             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind IN ('r','v','S','f','')
              AND n.nspname <> 'pg_catalog'
              AND n.nspname <> 'information_schema'
              AND n.nspname !~ '^pg_toast'
          AND pg_catalog.pg_table_is_visible(c.oid)
        ORDER BY 1,2);

5行目にrelpersistenceのカラムを追加しただけ。CASE WHENとかで格好良く編集するのは面倒なのでやらないｗ

で、このビューを登録してSELECT文で参照すると
foo=# select * from tablelsit_with_persistent ;
 Schema |           Name            | Type  | Owner | Persistent
--------+---------------------------+-------+-------+------------
 public | foo                       | table | pgxml | p
 public | foo_u                     | table | pgxml | u
 public | tablelsit_with_persistent | view  | pgxml | p
(3 rows)

foo=#

うむ、一応出力はできた。
でも、いちいちSELECT文をタイプするのがちと面倒。なので、psqlの\setを使って短縮してみる。
foo=# \set DD  select * from "tablelsit_with_persistent"
foo=# :DD;
 Schema |           Name            | Type  | Owner | Persistent
--------+---------------------------+-------+-------+------------
 public | foo                       | table | pgxml | p
 public | foo_u                     | table | pgxml | u
 public | tablelsit_with_persistent | view  | pgxml | p
(3 rows)

foo=#

ビュー名を二重引用符で括っているのは、\setがブランク区切りで第2引数以降を解釈しちゃうから。二重引用符を付けないと :DD; で展開しようとしたときに
select*fromtablelsit_with_persistent;
のような不正なSQLになってしまう。ビュー名を二重引用符でくくると
select*from"tablelsit_with_persistent";
になるので解釈可能。ちょっと危うい感じだけど。

てゆーか、次のバージョンでPersistent情報を"\d+"とかで表示してくれると嬉しいかも・・・

Permalink | コメント(0) | トラックバック(0) | 18:38

2012-01-12

pgbenchでUNLOGGEDの効果を見てみたが・・・

先日、COPY操作に関するUNLOGGEDの効果は確認できたんだけど、ベンチマークではどうなんだろう？と思って、とりあえず簡単に試せそうなpgbenchで試してみようと思ったのだが・・・

pgbenchの改造

PostgreSQL 9.1のcontrib/pgbenchが-iオプションで作成するテーブルは通常のテーブルを生成するので、ここをUNLOGGED TABLEで生成するようにコードをちょっと変更する(詳細は末尾参照)。
UNLOGGDEDオプション追加とか面倒そうなのは今回はパス。

【追記】
過去にもpgbenchが生成するテーブルをUNLOGGEDにするスイッチを追加する提案([PostgreSQL-Hackers] pgbench --unlogged-tables - Grokbase)とかはあったようだけど、少なくともPostgreSQL 9.1のpgbenchには採用されていない(経緯は追うのが面倒なので不明)。

測定条件

PostgreSQL設定

あえてあまり弄らずに。

shared_buffers 32MB
checkpoint_segments 100
pgbench設定
scale factor 100
connect 10
duration 600秒
その他 -rつき

測定結果

tps
FILLFACTOR設定(100%） FILLFACTOR設定(80%）
通常テーブル版 305.912891 542.266888
UNLIGGED版 302.409096 533.564632

f:id:nuko_yokohama:20120112172024j:image
うーん、ほとんどUNLOGGEDの効果がない気がする(´・ω・｀)。
10分くらい(約200000〜300000トランザクションくらい)動かしているし、pgbenchの場合、1つのトランザクションで更新3回、挿入1回があるんだからそれなりにWALの書き出し量もあると思ったのだが・・・。
ついでに測定したFILLFACTORの適度な設定はpgbenchでは結構効果があるな。

【追記】
永安氏のA Hacker’s Diary: pgbench on UNLOGGED table(s), Round 2にも検証した結果があったけど、この結果を見る感じでは結構UNLOGGED化による効果が出ているよなあ・・・。
やっぱり測定方法が何かまずいのかも。

latencies

次にpgbenchの-rオプションで生成されるlatenciesの情報を見てみる。

処理＼実行設定 通常/FF=100 通常/FF=80 UL/FF=100 UL/FF=80
BEGIN 0.250474 0.232978 0.284016 0.217452
UPDATE accounts 22.835855 10.799313 22.93067 11.34712
SELECT abalance 6.318071 1.313955 5.772142 1.387759
UPDATE tellers 1.792527 4.441692 0.769518 3.942956
UPDATE branches 0.628328 0.884453 1.933925 0.943259
INSERT hinstory 0.398945 0.450582 1.013554 0.581062
END 0.28274 0.270541 0.316187 0.251898

f:id:nuko_yokohama:20120112172025j:image
latenciesを見てもSELECTが支配項になっているようには思えない・・・。accountsへの更新が結構重いが・・・accountsへの競合がそんなに起きるとも思えないしなあ。
ということで謎のままである。この謎解きは別途。

参考：pgbenchの修正コード

それぞれの箇所の"create table テーブル名"を"create unlogged table テーブル名"に修正するだけ。
オプション化して切り替えるところまでは今回はやらなかった。

  • 1235〜1247行目

        static char *DDLs[] = {
                "drop table if exists pgbench_branches",
                "create table pgbench_branches(bid int not null,bbalance int,filler char(88)) with (fillfactor=%d)",
                "drop table if exists pgbench_tellers",
                "create table pgbench_tellers(tid int not null,bid int,tbalance int,filler char(84)) with (fillfactor=%d)",
                "drop table if exists pgbench_accounts",
                "create table pgbench_accounts(aid int not null,bid int,abalance int,filler char(84)) with (fillfactor=%d)",
                "drop table if exists pgbench_history",
                "create table pgbench_history(tid int,bid int,aid int,delta int,mtime timestamp,filler char(22))"
        };

  • 1262〜1270行目

                /*
                 * set fillfactor for branches, tellers and accounts tables
                 */
                if ((strstr(DDLs[i], "create table pgbench_branches") == DDLs[i]) ||
                        (strstr(DDLs[i], "create table pgbench_tellers") == DDLs[i]) ||
                        (strstr(DDLs[i], "create table pgbench_accounts") == DDLs[i]))
                {
                        char            ddl_stmt[128];

Permalink | コメント(2) | トラックバック(1) | 17:32

プロフィール

nuko_yokohama

nuko_yokohama

ぬこ＠横浜です。

カレンダー
<< 2012/02 >>
日 月 火 水 木 金 土
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29
最新タイトル
最近のコメント
最近のトラックバック
最近書いたコメント