2010/10/26

11gR2からのフルスキャン

従来からフルスキャン(通常 db file scattered read)をバッファーキャッシュを迂回するdirect path readに変更する手法としていくつか手段がありました。

* 今回は、Parallel Queryは除外

1. "_serial_direct_read"を設定する

SQL> alter session set "_serial_direct_read"=true;

2. event 10355を設定

$ oerr ora 10355
10355, 00000, "turn on direct read path for scans"
// *Cause:
// *Action:  enable direct async read for scans

11gR2からは、フルスキャンでdb file scattered readを使うのか、direct path readを使うのか、Oracleが自動で決定しているようです。
勝手に選択されるのも、時と場合によっては問題になる可能性があるので、その仕組みを少し調査してみます。

以下の隠しパラメータが影響して、I/Oの制御を行っているようです。

"_small_table_threshold"
lower threshold level of table size for direct reads

"_very_large_object_threshold"
upper threshold level of object size for direct reads

セグメントサイズ > 5 * "_small_table_threshold" * blocksizeの場合にdirect path readを選択するようになります。

ただし、これは、event 10949で制御が可能です。

$ oerr ora 10949
10949, 00000, "Disable autotune direct path read for full table scan"
// *Cause:
// *Action:  Disable autotune direct path read for serial full table scan.

しかし、event 10949でDisableにしても

セグメントサイズ > "_very_large_object_threshold" (MB)の場合は、Disableにされません。

つまり

どうしてもdirect path readを選択したくない場合は、

1. 10949でdirect path readをdisableに設定する
さらに
2. "_very_large_object_threshold"を大きな値に設定する

* ただし、隠しパラメータですので、ご使用とご用法には注意してください。

2010/10/17

SQLパフォーマンスワークショップ

今回は、SQLパフォーマンスワークショップの話しを少しだけ。

第2回 SQLパフォーマンスワークショップ

エンバカデロ・テクノロジーズさんとインサイトテクノロジー共同開催でワークショップを行うことになりました。

このワークショップで何を話そうかなと今まさに考えているのですが、まずはパフォーマンスチューニングのベーシックな
部分をきちんとお話ししようかなと思っています。

あと、ケーススタディを多めにして、参加者の方が考えながら参加できる内容にしたいと思っています。是非とも参加して
みてください。

1. ディスクI/O系の話

データベースのボトルネックになりやすいのはディスクI/Oです。

データベースのディスクI/Oにはいくつかの種類が存在します。それらを上手く制御することがチューニングとなります。

では、ディスクI/Oを上手く制御できているのか? いないのか? はどうやって判断するのか?

2. コンテンション系の話


多くのデータベースは多数のユーザーが同時利用することを前提に設計されているので、1つのリソースを多数で共有する
ための排他制御が様々な場所に実装されています。

排他制御はデータベース・カーネル側で制御しているラッチや内部ロックとユーザー自身で制御するロック(いわゆる行ロック)などがあります。

この排他制御を上手く行わないと、スループットが上がらないわけです。

3. コンフィギュレーション系の話

さらに、データベースの基本設定(パラメータや物理設計)の良し悪しでも、パフォーマンスに影響があります。


さすがに全部話していると時間がない気がしますが、出来る限りギュッとまとめて話したいと思います。

2010/10/10

パーティション・ビューって..

Oracle EEのオプションで最も使うと思われるものがPartitioning Optionではないかと個人的に思っています。小幡さんのブログ(Storage Serverフィルタリング考察)にてPartition Viewという懐かしいモノが紹介されていました。7.2で鳴り物入りと書かれていましたが、すぐにPartitioning Tableがリリースされて陽の目を見なかったですね。。。

しかし、Partition ViewにはPartitioning Tableにない素晴らしい点があります。それは、EEじゃなくても使える。頑張って作りこめばPartitioning Optionみたいに使える(これは利点なのか...?)ことです。

というわけで、一回、Partition Viewをまとめてみます。

* 個人的には、昔、この機能を使いこなそうとかなり苦労しました。

まず、大前提です。

  1. 基本的には、Partitioning Tableのように論理的に一つのテーブルとして扱えません(あくまでも1つのビューです)
  2. なので、グローバルインデックスや、カラムの追加/削除、パーティションの追加/削除といったことは透過的に実行できません。
  3. さらに、DMLもビューに対して実行できません(union all viewなので)

もう一度書きますが、あくまでもビューです。

では、普通のビューとどう違うのか?
それは、たった1つなのですが、現在のPartitioning TableのようにPartition ViewへのQueryの実行計画をPartitionを考慮して立ててくれること。です。

事前に準備すべき事は以下となります。

  1. 各テーブルのPartition Keyとなるカラムにはチェック制約が必要
  2. 初期化パラメータ(partition_view_enabled)がTRUE(ただし、大昔から、このパラメータは無くなり_partition_view_enabledがデフォルトTRUEとなっていますので、余り気にしない)

1のチェック制約を見て、ナルホドと思いますね。単純というか何と言うか...

では、一応、やってみます。

-- 通常のビュー用
-- 2010 Q1
create table t2010q1 (term date
     , id number
     , text varchar2(4000));
-- 2010 Q2
create table t2010q2 (term date
     , id number
     , text varchar2(4000));
-- 2010 Q3
create table t2010q3 (term date
     , id number
     , text varchar2(4000));
-- 2010 Q4
create table t2010q4 (term date
     , id number
     , text varchar2(4000));

-- パーティションビュー用
-- 2010 Q1
create table p2010q1 (term date
     , id number
     , text varchar2(4000)
     , constraint p2010q1_chk
      check(term >= to_date('2010/01/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
      and   term <= to_date('2010/03/31 23:59:59', 'yyyy/mm/dd hh24:mi:ss'))
     );
-- 2010 Q2
create table p2010q2 (term date
     , id number
     , text varchar2(4000)
     , constraint p2010q2_chk
      check(term >= to_date('2010/04/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
      and   term <= to_date('2010/06/30 23:59:59', 'yyyy/mm/dd hh24:mi:ss'))
     );
-- 2010 Q3
create table p2010q3 (term date
     , id number
     , text varchar2(4000)
     , constraint p2010q3_chk
      check(term >= to_date('2010/07/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
      and   term <= to_date('2010/09/30 23:59:59', 'yyyy/mm/dd hh24:mi:ss'))
     );
-- 2010 Q4
create table p2010q4 (term date
     , id number
     , text varchar2(4000)
     , constraint p2010q4_chk
      check(term >= to_date('2010/10/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss')
      and   term <= to_date('2010/12/31 23:59:59', 'yyyy/mm/dd hh24:mi:ss'))
     );

-- 通常のビューの作成
create or replace view t2010 as
select * from t2010q1
union all
select * from t2010q2
union all
select * from t2010q3
union all
select * from t2010q4;

-- パーティションビューの作成
create or replace view p2010 as
select * from p2010q1
union all
select * from p2010q2
union all
select * from p2010q3
union all
select * from p2010q4;


先程、書きましたが、通常のビューとパーティションビューの違いは、メンバーとなるテーブルにチェック制約がついているか否かの違いです。
では、データを入れますが、ビュー経由で直接データのinsertはできません、やりたいなら、(Partitioning Tableのように扱いたいなら)ビューに対してinstead triggerなどを仕込む必要があります。(今回は、面倒なので、テーブルにinsertしています)
declare
 dt date;
 tbl varchar2(30);
begin
 for dy in 0 .. 364 loop
  dt := to_date('2010/01/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss') + dy;

  if dt >= to_date('2010/01/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss') and
     dt <= to_date('2010/03/31 23:59:59', 'yyyy/mm/dd hh24:mi:ss') then
   tbl := '2010q1';
  elsif dt >= to_date('2010/04/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss') and
     dt <= to_date('2010/06/30 23:59:59', 'yyyy/mm/dd hh24:mi:ss') then
   tbl := '2010q2';
  elsif dt >= to_date('2010/07/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss') and
     dt <= to_date('2010/09/30 23:59:59', 'yyyy/mm/dd hh24:mi:ss') then
   tbl := '2010q3';
  elsif dt >= to_date('2010/10/01 00:00:00', 'yyyy/mm/dd hh24:mi:ss') and
     dt <= to_date('2010/12/31 23:59:59', 'yyyy/mm/dd hh24:mi:ss') then
   tbl := '2010q4';
  else
   tbl := '';
  end if;

  if tbl is not null then
   for h in 0 .. 23 loop
    for m in 0 .. 60 loop
     begin
      execute immediate 'insert all ' ||
           'into ' || 't' || tbl || ' values (:1, :2, :3) ' ||
           'into ' || 'p' || tbl || ' values (:4, :5, :6) ' ||
           'select * from dual'
         using dt + h/24 + m/24/60
          , dy + h/100 + m/10000
          ,'sample'
          , dt + h/24 + m/24/60
          , dy + h/100 + m/10000
          ,'sample';
     exception
      when others then
       null;
     end;
    end loop;
    commit;
   end loop;
   commit;
  end if;
 end loop;
 commit;
end;
/

では、一番大事な、SELECTの実行計画を見てみます。
SQL> select count(*) from t2010 where term = to_date('2010/01/01','yyyy/mm/dd');

  COUNT(*)
----------
         1


実行計画
----------------------------------------------------------
Plan hash value: 709745821

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |     9 |   548   (1)| 00:00:07 |
|   1 |  SORT AGGREGATE      |         |     1 |     9 |            |          |
|   2 |   VIEW               | T2010   |    20 |   180 |   548   (1)| 00:00:07 |
|   3 |    UNION-ALL         |         |       |       |            |          |
|*  4 |     TABLE ACCESS FULL| T2010Q1 |     5 |    45 |   137   (1)| 00:00:02 |
|*  5 |     TABLE ACCESS FULL| T2010Q2 |     5 |    45 |   137   (1)| 00:00:02 |
|*  6 |     TABLE ACCESS FULL| T2010Q3 |     5 |    45 |   137   (1)| 00:00:02 |
|*  7 |     TABLE ACCESS FULL| T2010Q4 |     5 |    45 |   137   (1)| 00:00:02 |
--------------------------------------------------------------------------------

全てのパーティション(あえてパーティションと呼びます)に適切なインデックスは作成していないので、T2010Q1パーティションのフルスキャンは許せますが、全パーティションにフルスキャンが発生しています。何とかしたいですよね。
続いてパーティションビューの実行計画を見てみます。
SQL> select count(*) from p2010 where term = to_date('2010/01/01','yyyy/mm/dd');

  COUNT(*)
----------
         1


実行計画
----------------------------------------------------------
Plan hash value: 3622780762

---------------------------------------------------------------------------------

| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |     9 |   137   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE       |         |     1 |     9 |            |          |
|   2 |   VIEW                | P2010   |     8 |    72 |   137   (1)| 00:00:02 |
|   3 |    UNION-ALL          |         |       |       |            |          |
|*  4 |     TABLE ACCESS FULL | P2010Q1 |     5 |    45 |   137   (1)| 00:00:02 |
|*  5 |     FILTER            |         |       |       |            |          |
|*  6 |      TABLE ACCESS FULL| P2010Q2 |     5 |    45 |   137   (1)| 00:00:02 |
|*  7 |     FILTER            |         |       |       |            |          |
|*  8 |      TABLE ACCESS FULL| P2010Q3 |     5 |    45 |   137   (1)| 00:00:02 |
|*  9 |     FILTER            |         |       |       |            |          |
|* 10 |      TABLE ACCESS FULL| P2010Q4 |     5 |    45 |   137   (1)| 00:00:02 |
---------------------------------------------------------------------------------

先程と違いFILTERオペレーションが追加されました。これは、FILTERの結果FALSEなら、後続のオペレーションを実行する。という意味なので。
P2010Q1パーティションは必ずフルスキャンを実行しますが、その他のパーティションには、FILTERがかかり、フルスキャンを実行しない(というかテーブルへのアクセスもしない)ということになります。

かなり、かなり限定的(もしくは、相当いじり倒せば)Partition Viewも活躍できる場がありそうです。皆様も困った時に思い出してみてください。

2010/10/07

I/OスケジューラでSSDのパフォーマンスは変わるのか?

以前、Unbreakable Enterprise KernelのI/O schedulerがdeadlineに変更されたとブログに書きました。
また、SSDであればnoopの方が合っているかもしれないとコメントに書きました。

実際のところ、どうなのでしょうか? 検証してみます。

まずは、I/O schedulerを変更してみます。変更するには3通りあるのですが、

1. bootパラメータ(elevator)を変更する
2. /sys/block/<device>/queue/schedulerを直接変更する
3. udevのルールを変更する

今回は、検証なので、2の直接書き換え方式を使っていますが、通常はは、SSDのみをnoopとしたい、かつ、リブートで元に戻って欲しくない等になるので、3のudevルールで対応すると思います。

以下のudevのルールを/etc/udev/rule.dに作成します。

SUBSYSTEM=="block", SYSFS{queue/rotational}=="0", RUN+="/bin/sh -c 'echo noop > /sys$devpath/queue/scheduler'"

* queue/rotationalは磁気ディスクであれば1が設定され、SSDであれば0が設定されますが、USBのような安価なフラッシュ
  ディスクの場合は1が設定される場合があるようです


今回は、15セッションでTPC-Cのベンチマークを実施してみました。

1. deadline
$ su - root -c "echo deadline > /sys/block/sdc/queue/scheduler"
$ su - root -c "echo 3 > /proc/sys/vm/drop_caches"



Unbreakable Enterprise KernelではデフォルトとなっているdeadlineI/Oスケジューラのパフォーマンスをチェックしておきます。 今回のテストの結果では、平均のレスポンスタイムが22msとなっていました。

2. noop
$ su - root -c "echo noop > /sys/block/sdc/queue/scheduler"
$ su - root -c "echo 3 > /proc/sys/vm/drop_caches"



I/Oスケジューラを今回のテスト目的であるnoopに変更してのパフォーマンスをチェックしてみます。 今回のテストの結果では、平均のレスポンスタイムはdeadlineと同じ22msとなっていました。

3. cfq
$ su - root -c "echo cfq > /sys/block/sdc/queue/scheduler"
$ su - root -c "echo 3 > /proc/sys/vm/drop_caches"



更に、以前のカーネルでデフォルトであったcfqのパフォーマンスも一応見てみます。 平均のレスポンスタイムは23msとなり、他のI/Oスケジューラと遜色ないパフォーマンスでした。

ということ、今回のテストでは、I/Oスケジューラでのパフォーマンスにおける変化はみられませんでした。もう少し、I/Oが厳しい環境でテストすると状況が変わってくるかも知れませんが。。。

2010/10/04

続 filesystemio_optionsと非同期I/O

前回(filesystemio_optionsと非同期I/O)でfilesystemio_options=asynchの動作が非同期I/Oになっていないのではないか?と書きましたが、もう少し、状況証拠をとるためにTPCのベンチマークを取得しました。

filesystemio_options=noneの場合とasynchの場合でほぼ同一の結果となっており、ますます、filesystemio_options=asynchの動作の怪しさが増しています。

- filesystemio_options=none
swingbenchの結果

ASH Viewerの結果

commitクラスの待機イベント

- filesystemio_options=asynch
swingbenchの結果
ASH Viewerの結果

commitクラスの待機イベント

- filesystemio_options=setall
swingbenchの結果
ASH Viewerの結果

基本的に全てのケースでUser I/Oの待機イベントで待機しているのは変わりません。しかし、特徴的な待機イベントとしてCommitクラス(内容はlog file sync)があります。none と asynchの場合にのみ発生しています。

LGRWが log bufferからREDOログへ書き込んでいるのを待機しているわけですが、

OLTP系の処理であれば、commitはそれなりに頻繁に発行される + それなりに多くのセッションが同様の処理をする。という特徴を考えると、LGWRのアクティビティは高くなります。LGWRが同期I/Oをしている場合、commit I/Oの衝突により各セッションが待機するであろうことは想像に難くありません。

それが、none の場合(同期I/O)の場合とasynch(非同期I/Oのつもり)で、ほぼ同一のレスポンス時間、同一TPMかつ、待機イベントの傾向である。また、setall(DIRECTかつ非同期I/O)の場合で、レスポンス時間およびTPMが改善され、待機イベントにlog file syncが無いこと。

上記を考えると、やはり、filesystemio_options=asynchは、非同期I/Oでない気がしますね。。。

2010/10/02

filesystemio_optionsと非同期I/O

先日、filesystemio_options=asynchの場合のopenモードが不思議と書いたのですが、一応、サンプルソースを使って試してみました。
サンプルソースはLinux Foundationにあるaiocpを拝借してテストしました。

[oracle@kshinkub aio]$ wget http://devresources.linuxfoundation.org/daniel/AIO/aiocp.c
--2010-10-02 21:45:34--  http://devresources.linuxfoundation.org/daniel/AIO/aiocp.c
devresources.linuxfoundation.org をDNSに問いあわせています... 140.211.169.81
devresources.linuxfoundation.org|140.211.169.81|:80 に接続しています... 接続しました。
HTTP による接続要求を送信しました、応答を待っています... 200 OK
長さ: 11896 (12K) [text/x-csrc]
`aiocp.c' に保存中

100%[==================================================================>] 11,896      37.3K/s 時間 0.3s

2010-10-02 18:45:35 (37.3 KB/s) - `aiocp.c' へ保存完了 [11896/11896]

[oracle@kshinkub aio]$ gcc -laio -o aiocp aiocp.c


処理内容はざっと以下の通りです。
1. コピー元ファイルのopen
2. コピー先ファイルのopen
3. 1に対してreadを非同期I/Oで要求し、その完了をコールバック関数で待ち受ける
4. 3のread要求が完了した場合、コールバック関数が呼ばれ、さらに2に対して非同期でwrite要求が発行される
5. write要求も完了時にはコールバック関数が呼ばれる
6. 全てのread/writeの要求が発行された場合、その完了を待って終了

つまり、処理の重いwriteの間に処理の軽いreadを多く発行させて、全体のスループットが上がることを期待したサンプルと言えます。以下、本当にそうなっているか確認してみます。
* 今回は、10MBのファイルを別ファイルにコピーするテストを実施しています。その際のブロックサイズはOracleのブロックサイズと合わせ8KBとしました。(別に合わす必要もないですが...)

1. 初期ファイルの作成(10MB)
[oracle@kshinkub aio]$ dd if=/dev/zero of=srcfile bs=1M count=10
10+0 records in
10+0 records out
10485760 bytes (10 MB) copied, 0.0171325 seconds, 612 MB/s

2. ファイルキャシュクリア
[ora112d@RH5-64-112-node1-p ~]$ su - root -c "echo 3 > /proc/sys/vm/drop_caches"

3. O_SYNCの場合のAIO
[ora112d@RH5-64-112-node1-p ~]$ time ./aiocp -d -b 8K -f O_SYNC -f O_CREAT srcfile dstfile
rrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww
rrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww
rrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww
rrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww
(省略)
rrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww
real    0m1.360s
user    0m0.002s
sys     0m0.123s

4. ファイルキャシュクリア
[ora112d@RH5-64-112-node1-p ~]$ su - root -c "echo 3 > /proc/sys/vm/drop_caches"

5. O_SYNC|O_DIRECTの場合のAIO
[ora112d@RH5-64-112-node1-p ~]$ time ./aiocp -d -b 8K -f O_SYNC -f O_DIRECT -f O_CREAT srcfile dstfile
rrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrwwwwwwwwwwwwwwwwwwwwwwwwwrrrrrrr
rrrrrrrrrrrrrrrwwwwwwwwwwwwwrrrrrrwwrrrrrwwwwwwwwwwwwwwwrrrrrrrr
rrrrrwwwrrrrrwwwwrrrwwwrrrrwwwwwwwrrrrwwwwwwwwwwwwwwwwwwrrrrwwww
wwwwrrrrrrrrrrrrrrwwwwrrrwwwwwwwwwwwrrrrrrrrrrrrrwwwwwwwwwwwwwww
(省略)
rrrrrrrrrrrrrrrrrrrrrrrrwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwrrrrwwww
real    0m0.403s
user    0m0.000s
sys     0m0.061s

"r"が出力(readが完了した時点)される条件は以下の通りです。

static void rd_done(io_context_t ctx, struct iocb *iocb, long res, long res2)
{
(省略)  
        /* turn read into write */
        if (no_write) {
            --tocopy;
            --busy;
            free_iocb(iocb);
        } else {
            int fd;
            if (iocb->aio_fildes == srcfd)
                fd = dstfd;
            else
                fd = dstfd2;
            io_prep_pwrite(iocb, fd, buf, iosize, offset);
            io_set_callback(iocb, wr_done);
            if (1 != (res = io_submit(ctx, 1, &iocb)))
                io_error("io_submit write", res);
        }
        if (debug)
            write(2, "r", 1);
        if (debug > 1)
            printf("%d", iosize);
(省略)
}

つまり、io_submit(2)でread要求を発行し、そのコールバック関数rd_doneの中で、write用のio_submit(2)を発行し、そのwrite要求が成功した場合に"r"を出力しています。
O_DIRECTオプションを付けずにopenした場合は、"r"が続き、"w"が同数続いています。つまり、read完了後、write要求を出しているが 同期I/Oのため、次のread要求が出せないことを示しています。(非同期I/Oになっていない)
しかし、O_DIRECTオプション付きでopenした場合は、"r"が続き、更に"r"が続き、"w"が出てきて、途中で"r"が続き...のようになっています。これは、write要求のコールバック(完了)を待たずに、続けざまにreadを要求していることを示してます。
つまり、filesystemio_options=asynchとしても、openのモードにO_DIRECTが含まれていないので実際には同期I/Oとなっていると思われます。

仮にこの擬似コードの結果が正確なら、

非同期I/Oを使うには、filesystemio_optionsにsetallが必要ということになります。また、そうなるとファイルキャッシュは効き目なしになるので、それ相応のSGAの調整が必要になるということになります。(そもそもファイルキャッシュは当てにしていないですかね。。。)