[DBチューニングコンテスト とんがりナレッジ] 優勝への道② TPC-C チューニング編

投稿日: 2015年10月15日

今度は予選で実施したTPC-Cについて書いていきます。

競争条件については、下記の通りです。

<予選>
 (1) TPC-C 20セッション(1セッションごとに100万トランザクション)
 (2) TPC-H 4セッション(Scale Factor=1)
 ※TPC-Hのチューニングについてはここでは割愛します。

まずは、事前準備で実施した内容から。

【HammerDBの問題対応】
 <tpmを確認するため>
  grant select on sys.user$ to system;

 <WAREHOUSEのW_YTDが桁溢れするため>
  alter table WAREHOUSE modify W_YTD NUMBER(24,2);

それでは、本題です。

以下の優先順位でチューニング実施し、CPUネック(待ちもない状態)の状態にしていくことを考えました。

【チューニングの優先順位】
  1. Diskアクセスを減らす
  2. 減らせないものは、Diskからのアクセスを高速にする
  3. Memoryアクセスを減らす
  4. 減らせないものは、Memoryからのアクセスを高速にする
  5. CPUリソースを無駄に使用している処理を減らす
  6. 減らせない処理は、CPUリソースの使用方法を効率化する
  ※ネットワークは今回は関係ないため省略しています。

まずはDiskアクセスについてです。
TPC-CでDiskアクセスが多い個所と考えた時、真っ先に気になるのは、やはりREDOです。
また、REDO生成量(更新量)が多いということは、UNDOも多いはずです。
まずは、そこをポイントにチューニングしていきます。
※チューニング前後のベンチマーク結果は記載できないため、代わりに効果(高,中,低)という表現で記載することをご了承ください。

【REDOログの高速化】
まずは、REDOログです。
REDOログの書き込みを高速化する上で、大きく分けて方法は2パターンあります。
1. 書き込み量自体を減らす
2. 書き込み速度を向上させる

では、それぞれに対し実施した方法です。
1. 書き込み量自体を減らす
今回は、正攻法と邪道な方法の2つを考えました。

<実施内容>
 a. まともなやり方でREDOログ生成量を減らす。(効果:低)
 b. 邪道なやり方でREDOログ生成量を減らす。(効果:高)

a. まともなやり方でREDOログ生成量を減らす。(効果:低)
こちらは、12cからの新機能のTemporay Undoを使用しました。

<パラメータ変更内容>
 TEMP_UNDO_ENABLED=TRUE

b. 邪道なやり方でREDOログ生成量を減らす。(効果:高)
どう考えても、通常運用に耐えられませんが、REDOログ生成量を減らすとともに、COMMIT待ちも失くしました。

<変更パラメータ>
 _disable_logging=true
 commit_logging=batch
 commit_wait=nowait

2. 書き込み速度を向上させる
以下の2つを考えました。

<実施内容>
 a. REDOログをRAMディスクに配置する。(効果:高)
 b. 一度の書き込みを多くし、書き込み速度を高速化する。(効果:低)

a. REDOログをRAMディスクに配置する。(効果:高)
REDOログの生成量を減らし、待ちを失くした結果、RAMディスクにREDOログを配置した場合でも速度差が変わらない状況になりました。
そのため、メモリリソース節約のために、REDOログはRAMディスクには配置しませんでした。
※通常の状態でRAMディスクに配置した場合、

b. 一度の書き込みを多くし、書き込み速度を高速化する。(効果:低)
ログバッファやチェックポイント間隔を調整し、少しでも一度の書き込みを多くしようと考えました。

<変更パラメータ>
 log_checkpoint_interval=2147483647
 log_checkpoint_timeout=2147483647
 log_buffer=2147483648

【UNDOの高速化】
次にUNDOです。
UNDO自体は、REDOログとは違い、(テーブル構成やSQLを変えられない条件では)DBの性質上減らそうにも減らせません。
そのため、UNDOをRAM上に配置しました。

1. UNDOのRAMディスクへの配置(効果:中)

<実行コマンド>
 CREATE UNDO TABLESPACE RAMUNDO DATAFILE '/dev/shm/ramundo.dbf' SIZE 1G;

<変更パラメータ>
 undo_tablespace='RAMUNDO'
 undo_retention=5
 ※UNDO表領域のサイズ調整のため、undo_retentionも同時に変更しています。

【データファイルやオブジェクトの事前拡張】
100万トランザクション * 20セッション = 2000万トランザクション
のデータがどれくらいのサイズになるか不明だったため、今回の環境ではデータファイルのリサイズが発生していました。
リサイズが発生し始めたタイミングでパフォーマンスが落ち始めるため、事前にデータファイルを十分なサイズで作成し直しました。
また、合わせてオブジェクトも事前に拡張しました。

<実施内容>
 1. データファイルのリサイズ(効果:中)
 2. オブジェクトの事前拡張(効果:低)

1. データファイルのリサイズ(効果:中)

<実行コマンド>
 CREATE BIGFILE TABLESPACE "TPCC1" LOGGING DATAFILE '/opt/oracle/base/oradata/tpcc1/tpcc1.dbf' 
 SIZE 16384M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED 
 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
 ※実際には最初に作成したため、ALTERではなく、CREATEになっています。

2. オブジェクトの事前拡張(効果:低)

<オブジェクトの拡張>
 alter table tpcc1.order_line allocate extent ( SIZE 12G ) ;
 alter table tpcc1.history allocate extent ( SIZE 1G ) ;
 alter table tpcc1.orders allocate extent ( SIZE 500M ) ;
 alter index tpcc1.orders_i1 rebuild storage (INITIAL 500M);
 alter index tpcc1.orders_i2 rebuild storage (INITIAL 500M);

【制御ファイルのIO高速化】
冗長化ポイントは、性能面のみ考えた場合は無駄なIOとなってしまうため、制御ファイルは1つにしました。

1. 制御ファイルの削除(効果:低)

<パラメータ変更内容>
 control_files='/opt/oracle/base/oradata/orcl/control01.ctl'

【データファイルのIO高速化】
少しでも、データファイルへの書き込みを速くするため、DB Writerの数を調整しました。

<パラメータ変更内容>
 DB_WRITER_PROCESSES=8

【便利機能の停止】
便利な機能は、内部で更新等が発生しているため、それなりに負荷をかけています。
運用面を無視して速度のみを考えた場合はやはり停止したほうが速くなります。
実際に実施した内容は、以下の3つです。

<実施内容>
 1. 統計収集の停止(効果:中)
 2. 内部チェック、アドバイザ系、便利な機能の停止(効果:低)
 3.ダイナミック・サンプリングの無効化(効果なし)

1. 統計収集の停止(効果:中)
便利な分だけ、やはりかなり効果がありました。
ただし、チューニングに必要になる情報がほぼ見れなくなるため、設定は最後に実施しました。

<変更パラメータ>
 timed_statistics=false
 statistics_level=BASIC

2. 内部チェック、アドバイザ系、便利な機能の停止(効果:低)
こちらは、思ったほど効果がありませんでした。

<パラメータ変更内容>
 DB_BLOCK_CHECKING=FALSE
 DB_BLOCK_CHECKSUM=FALSE
 TRACE_ENABLED=FALSE
 _db_mttr_advice=OFF
 _library_cache_advice=FALSE
 _smm_advice_enabled=FALSE
 _smm_advice_enabled=FALSE
 db_cache_advice=OFF
 REPLICATION_DEPENDENCY_TRACKING=FALSE

3.ダイナミック・サンプリングの無効化(効果なし)
今回は、統計情報を事前に収集しているため、特に効果がありませんでしたが、統計情報を収集していない環境では問題になる場合もあるため、記載しておきます。

<パラメータ変更内容>
 optimizer_dynamic_sampling=0

【SGAの調整】
影響度的には一番大きいですが、チューニングポイントというには微妙なので、記載しておきます。
また、Diskとは関係ありませんが、SGA関連としてshared_pool_sizeの設定値も記載します。

<設定パラメータ>
 db_cache_size=15032385536
 shared_pool_size=1073741824
 ※それぞれ、TPC-Cを実行する上で必要な分を設定しました。

ここまでで、大分Diskネックの状態は解消できました。

次は、メモリアクセスについて考えます。

【ブロックサイズの調整】
実際、ここはそこまで手を出さなかった箇所でした。
そもそも、実際に実行されているSQL自体も最速の実行計画で実行されていたため、indexと行とで2ブロックのアクセスで完結しているSQLだけでもどうにかできないか考えました。

1. 毎回、2ブロックしかアクセスされないオブジェクトを2KBのブロックサイズに変更(効果:低)
DB自体は32KBのブロックサイズにしていたため(TPC-H環境と同居のため)、もう少し効果を期待したかったのですが、あまりありませんでした。

<実行コマンド>
 CREATE BIGFILE TABLESPACE "TPCC1_2" LOGGING DATAFILE '/opt/oracle/base/oradata/tpcc1/tpcc1_2.dbf' 
 SIZE 100M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED 
 EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO blocksize 2048;
 alter table warehouse move tablespace tpcc1_2;
 alter table district move tablespace tpcc1_2;
 alter index warehouse_i1 rebuild tablespace tpcc1_2;
 alter index district_i1 rebuild tablespace tpcc1_2;
 alter table tpcc1.customer move tablespace tpcc1_2;
 alter index tpcc1.customer_i1 rebuild tablespace tpcc1_2;
 alter index tpcc1.customer_i2 rebuild tablespace tpcc1_2;

<変更パラメータ>
 db_2k_cache_size=104857600

最後にCPUです。

【CPUリソースの効率化】
とにかく、無駄なオーバーヘッドや待ちを減らすことを考えました。
結果、実施したのは以下の3つです。

<実施内容>
 1. hugepageの設定(効果:中)
 2. PL/SQLの高速化(効果:低)
 3. latchの調整(効果:低)

1. hugepageの設定(効果:中)
hugepageを使用することで、ページ表(仮想メモリーから物理メモリーへのマッピング)は小さくなります。
ページ表を最新状態に保つオーバヘッドがなくなることで、CPUネックになる環境では効果があります。

<変更パラメータ(kernelパラメータ)>
  vm.nr_hugepages = 7000

2. PL/SQLの高速化(効果:低)
HammerDBの処理方式を確認していたところ、PL/SQLを使用して処理が行われていました。
そのため、HammerDBで作成されるプロシージャや乱数生成などのプロシージャの負荷を下げるために実施しました。

<実行内容>
 startup upgrade
 @$ORACLE_HOME/rdbms/admin/dbmsupgnv.sql
 shutdown
 startup
 @$ORACLE_HOME/rdbms/admin/utlrp.sql
 alter procedure tpcc1.neword compile plsql_code_type=native reuse settings;
 alter procedure tpcc1.delivery compile plsql_code_type=native reuse settings;
 alter procedure tpcc1.payment compile plsql_code_type=native reuse settings;
 alter procedure tpcc1.ostat compile plsql_code_type=native reuse settings;
 alter procedure tpcc1.slev compile plsql_code_type=native reuse settings;

<パラメータ変更内容>
 PLSQL_CODE_TYPE=NATIVE
 plsql_optimize_level=3

3. latchの調整(効果:低)
他にチューニングポイントはないかと待機イベントを眺めていたところ、latchが少し出ていたため、調整を行いました。
発生自体少しだっため、やはり効果は少なかったです。

<パラメータ変更内容>
 _db_block_hash_latches=32768
 _kgl_latch_count=9

【おまけ:プロセスの優先度の調整(効果:なし)】
DBWやLGWRなど、主にCPUを使用していたプロセスの優先度を調整して、更にパフォーマンス向上を目指せるかと思ったのですが、効果は出ませんでした。
むしろ、設定によっては、悪化している気配さえあり、少し検討に時間がかかりそうだったため、実施できませんでした。

<変更検討パラメータ>
 _high_priority_processes
 _highest_priority_processes

以上がTPC-Cで実施した内容となります。

TPC-H編に続く