OSDL DBT-1によるPostgreSQLのSQLチューニングに関する考察 || OSS iPedia

OSDL DBT-1によるPostgreSQLのSQLチューニングに関する考察

【1.概要】

　PostgreSQL8.0，PostgreSQL8.1のSQLを解析し、設定ファイル(psotgresql.conf)のプランナに関するパラメタを変更し、DBT1に対する性能チューニングを実施した。
　

【2.解析結果】

　Seq scanでサーチした結果，PostgreSQL8.0.6で6件，PostgreSQL8.1.2で4件，Sortでサーチした結果PostgreSQL8.0.6， PostgreSQL8.1.2とも5件のSQLが該当した。そのうち，テーブル容量，条件のヒット件数からみて，実行コストが小さいと予測できるものを除き，チューニング対象を表2-1に示す4つのSQLに絞り込んだ。

表2-1 チューニング対照SQLの絞込み結果
SQL番号	SQLテキスト	SQL実行プラン分析
SQL番号	SQLテキスト	Seq scan	Sort	ジョイン方式
Q20	SELECT i_id, i_title, a_fname, a_lname FROM item, author WHERE i_subject = '%s' AND i_a_id = a_id ORDER BY i_pub_date DESC, i_title ASC;	無(8.0) 有(8.1)	有	Merge Join
Q34	SELECT i_id, i_title, a_fname, a_lname FROM item, author WHERE i_a_id=a_id AND a_lname LIKE '%%%s%%' ORDER BY i_title ASC;	無	有	Nested Loop
Q35	SELECT i_id, i_title, a_fname, a_lname FROM item, author WHERE i_subject = '%s' AND i_a_id = a_id ORDER BY i_title ASC;	無(8.0) 有(8.1)	有	Merge Join
Q36	SELECT i_id, i_title, a_fname, a_lname FROM item, author WHERE i_title LIKE '%%%s%%' AND i_a_id = a_id ORDER BY i_title ASC;	無	無	Nested Loop

【3.考察】

3.1 PostgreSQL8.0に関するSQLチューニングについて

　上記解析結果から、設定ファイル（psotgresql.conf）のプランナに関するパラメタを変更することでチューニングすることを考えた。表3-1にそのチューニング方式案と効果を示す。

表3-1 PostgreSQL8.0のSQLチューニング方式案と結果
チューニング・パターン名	チューニング内容	チューニング効果
チューニング・パターン名	チューニング内容	BT/s	SQL実行プランの変化，その他
SQLチューニング前		216.2
CASE1_8.0	Seq scanをIndex scanにするように， random_page_cost（：4[デフォルト]）を2に変更	284.1	Q20，Q35はSeq scanがIndex scanに改善された。（Q34,Q36はLIKE部分一致であるためSeq scanのまま改善はされず）
CASE2_8.0	CASE1_8.0の状態で，かつジョイン方式を変更するようにするように， enable_nestloop（：on[デフォルト]）をoffに変更	10.1	Q34，Q36で，Nested LoopはMerge Joinになった。（Q34,Q36はLIKE部分一致であるためSeq scanのまま改善なし）
CASE3_8.0	CASE1_8.0の状態で，ジョイン方式の変更，Merge Joinに伴うソートを回避するように，更にenable_mergejoin（：on[デフォルト]）をoffに変更	276.0	Q20，Q35で，Merge JoinはNested Loopとなった。
CASE4_8.0	CASE3_8.0の状態で，ORDER BYのソートを回避するように，更にenable_sort（：on[デフォルト]）をoffに変更	120.8	Q34，Q35，Q36について，order by指定に伴うSortは回避されたが，条件判定が行われなくなった。
CASE5_8.0	CASE1_8.0の状態で，HashJoinのした場合の性能検証のため enable_mergejoin （：on[デフォルト]）をoff， enable_nestloop（：on[デフォルト]）をoff， work_mem（：1024[デフォルト]）を102400に変更	118.3	HashJoinにならず。（全てNested Loopとなり,Q20のみソートが発生）
CASE4'_8.0	CASE4_8.0で，Q20，Q35の”条件評価とorder byのソート回避”を同時に行わせるように，インデクスを再定義（※1）　また，Q34ではi_i_titleインデクスを使用すると性能が劣化したため，このインデクスを削除	207.3	HQ20，Q35は，条件判定のみにインデクスが使用され，order byのソートは回避できなかった。

（※1）図3-1にマルチカラム・インデクスの再定義を示す。

図3-1．マルチカラム・インデクスの再定義

3.2 PostgreSQL8.1に関するSQLチューニングについて

　上記解析結果から、設定ファイル（psotgresql.conf）のプランナに関するパラメタを変更することでチューニングすることを考えた。表3-2にそのチューニング方式案と効果を示す。

表3-2 PostgreSQL8.1のSQLチューニング方式案と結果
チューニング・パターン名	チューニング内容	チューニング効果
チューニング・パターン名	チューニング内容	BT/s	SQL実行プランの変化，その他
SQLチューニング前		333.5
CASE1_8.1	ジョイン方式の変更，Merge Joinに伴うソートを回避するように enable_mergejoin（：on[デフォルト]）をoffに変更	346.0	Q20，Q35で，Merge JoinはNested Loopとなった。
CASE2_8.1	CASE1_8.1の状態で，random_page_cost（：4[デフォルト]）を2に変更	340.2	変化なし）
CASE3_8.1	ジョイン方式を変更するように， enable_nestloop（：on[デフォルト]）をoffに変更	2.1	Q34，Q36で，Nested LoopはMerge Joinとなった。
CASE4_8.1	CASE1_8.1の状態で，ORDER BY指定に伴うソートの回避するように， enable_sort（：on[デフォルト]）をoffに変更	216.3	Q34，Q35，Q36について，order by指定に伴うSortが回避されたが，条件判定が行われなくなった。
CASE5_8.1	CASE1_8.1の状態で，HashJoinのした場合の性能検証のため enable_mergejoin （：on[デフォルト]）をoff， enable_nestloop（：on[デフォルト]）をoff， work_mem（：1024[デフォルト]）を102400に変更	217.7	HashJoinにならず。（全てNested Loopとなった）
CASE4'_8.0	CASE4_8.0で，Q20，Q35の”条件評価とorder byのソート回避”を同時に行わせるように，インデクスを再定義（※1）　また，Q34ではi_i_titleインデクスを使用すると性能が劣化したため，このインデクスを削除	354.0	Q20，Q35で，インデクスが条件判定とorder byのソート回避に使用された。

（※1）図3-2にマルチカラム・インデクスの再定義を示す。

図3-2．マルチカラム・インデクスの再定義

3.3 総合チューニングについて

　設定ファイル(postgresql.conf)のパラメタのチューニングと、SQLのチューニングの両方を総合的に実施し、DBT1を実行した結果を図3-3に示す。

図3-3．PostgreSQL 8.1とPostgreSQL8.0の設定ファイルチューニング後とデフォルト状態との比較

【4.まとめ】

　DBT1では，設定ファイル(postgresql.conf)のパラメタによるチューニングに関してshared_buffers， wal_sync_method，walログの格納先の3項目について効果があることが分った。また，PsotgreSQL8.0と PostgreSQL8.1を比較して，PostgreSQL8.1で性能向上改善が図られたことが検証できた。
br>

表4-1 DBT1に対するPostgreSQL設定ファイルのチューニング結果
設定ファイルのチューニング		PostgreSQL8.0	PostgreSQL8.1
SQLプランナに関する設定	random_page_cost	2	4(デフォルト)
	enable_mergejoin	on(デフォルト)	off
	enable_sort	on(デフォルト)	off
	インデクス定義改善	デフォルトのまま(改善点なし)	Multi-columnインデクスの再定義が有効
postgresql.conf	shared_buffers	10000	100000
	wal_sync_method	open_sync	open_sync
	wal格納先	DB本体と異なるディスク	DB本体と異なるディスク
	shmmax	33554432	1073741824
BT値(eu=3200)	設定ファイルのみチューニング値で実行した場合	216.2	333.5
	設定ファイルパラメタの SQLチューニングも実行した場合	284.1	354.0
	BT値の向上率	31%	6%

このデータの性能データ

関連する性能データは登録されていません。

OSS iPedia