OSDL DBT-1によるPostgreSQLの設定ファイルのチューニングに関する考察

【1.概要】

　PostgreSQL8.0，PostgreSQL8.1の設定ファイルのそれぞれのパラメタを変更してDBT1を実行し、設定ファイルをデフォルトのまま実行させたBT値と比較した。
　また、設定ファイルのパラメタの中で、幾つかのパラメタ指定値についてDBT1の特徴だった部分についても、考察する。

【2.測定結果】

　PostgreSQL8.0，PostgreSQL8.1の設定ファイルのそれぞれのパラメタを変更し，DBT1を実行させたBT値を表2-1に示す。

表2-1 設定ファイル（postgresql.conf）のパラメタ値を変更させたDBT1の測定結果
パラメタ		オプション値	Ver8.0.6		Ver8.1.2
パラメタ		オプション値	BT/s	デフォルト比(※1)	BT/s	デフォルト比
shared_buffers(※2)		500	178.2	0.87	200.6	0.95
		1000	207.3	1.01	213.7	1.02
		5000	229.1	1.11	269.4	1.28
		10000	226.9	1.10	270.0	1.28
		50000	222.9	1.08	268.3	1.28
		10000	222.9	1.08	270.2	1.28
work_mem		64	199.8	0.95	207.3	0.96
work_mem		10240	212.1	1.00	220.1	1.02
max_fsm_pages		3000(※3)	211.7	1.00	217.9	1.01
max_fales_per_process		50	212.8	1.01	216.5	1.01
max_fales_per_process		5000	210.9	1.00	218.6	1.02
8.0	bgwriter_delay−(i)	(i)200，(ii)0，(iii)0	213.4	1.01
	bgwriter_percent−(ii)	(i)200，(ii)100，(iii)5	211.2	1.00
	bgwriter_maxpages−(iii)	(i)200，(ii)100，(iii)50	213.5	1.01
8.1	bgwriter_delay−(i)	(i)200，(ii)0，(iii)0，(iv)0，(v)0			215.4	1.00
	bgwriter_lru_percent−(ii)	(i)200，(ii)100，(iii)5，(iv)0，(v)0			218.1	1.01
	bgwriter_lru_maxpages−(iii)	(i)200，(ii)100，(iii)50，(iv)0，(v)0			218.2	1.02
	bgwriter_all_percent−(iv)
	bgwriter_all_maxpages−(v)
fysnc		off	209.2	0.99	271.3	1.26
full_page_writes		off			210.0	0.98
wal_sync_method		fsync	212.0	1.00	216.6	1.01
wal_sync_method		open_sync	207.6	0.98	261.4	1.22
wal_buffers		16	212.2	1.00	217.8	1.01
		32	213.4	1.01	217.8	1.01
		64	212.1	1.00	214.7	1.00
commit_delay−(i)		(i)10，(ii)5	212.8	1.01	213.1	0.99
commit_siblings−(ii)		(i)50，(ii)5	211.1	1.00	217.6	1.01
checkpoint_segments−(i)		(i)30，(ii)100	211.4	1.00	217.6	1.01
checkpoint_timeout−(ii)		(i)30，(ii)600	213.3	1.00	214.8	1.00
checkpoint_timeout−(ii)		(i)30，(ii)900	211.9	1.00	218.1	1.01
deadlock_timeout		100000	211.6	1.00	215.8	1.00
max_locks_per_transaction		300	211.9	1.00	213.8	0.99
walログ格納ディスク変更			206.4	0.98	268.7	1.25
noatimeオプション			211.8	1.03	210.5	0.98
Elevator=deadline			210.5	1.00	214.7	1.00

※1　デフォルト環境での測定値（eu＝2000）は以下の通り。shared_buffersを観点とした測定はPostgreSQLの統計情報を取得して測定したため，基準値もデフォルト環境に統計情報取得の指定を加えて測定した値（カッコ内）で比をとった。
PostgreSQL 8.0.6　211.3（205.6）
PostgreSQL 8.1.2 214.9（210.3）
※2 shmmaxを一律1073741824に変更して測定した。
※3　DB初期ロードのシェル実行時に"NOTICE: number of page slots needed (28352) exceeds max_fsm_pages (20000)”のメッセージが出力されていたため，30000で試行した。

【3.考察】

3.1 shared_buffersについて

　本評価では指定値1000〜3000の間でBT値が大きく変化し，それ以上に指定値を増やしてもBT値の大きな変化はなかった。図3-1，図3-2，図3-3に，それぞれshared_buffers を1000，5000，100000で測定したPostgreSQL 8.1.2 のアクセス統計情報を示す。shared_buffersを1000から5000に増やすことにより，ブロックリード回数が減少しており，5000から100000に増やしても1000と5000の間程の差異は無いことが分かる。このことから，アクセス頻度の多いテーブル（主にitemとauthor）がバッファに載り切ってしまえば，それ以上のそれ以上大きな値を指定しても，キャッシュ効果は性能に影響しないと言える。

図3-1．PostgreSQL 8.1.2　eu=2400，shared_buffers=1000の統計情報(table_info89.out)

図3-2．PostgreSQL 8.1.2　eu=2400，shared_buffers=5000の統計情報(table_info89.out)

図3-2．PostgreSQL 8.1.2　eu=2400，shared_buffers=100000の統計情報(table_info89.out)

　DBT1では，頻繁にアクセスされるitem，authorテーブルおよびインデクスが載り切る程度のバッファを確保することが好ましいと考える。ただし，PostgreSQL 8.0.6の探針測定の結果では，shared_buffers50000，100000 のケースは10000のケースに比べわずかながらBT値が劣化していることもあり，無条件で大きな値を指定するのではなく，データベース規模と実測値を考慮して最適値を求めることが必要と考える。

3.2 walログ格納ディスク変更について

　デフォルト環境とwalログの格納先を変更した場合のiostat -d の結果（DBT-1結果ファイルのio.txtから）を比較すると，データベースと更新ログの格納先を分けることにより，以下のようにI／O負荷（書き込みの負荷）がsdbからsdcに分散されたことで、CPU利用率を向上させることができたと判断する(特にPostgreSQL8.1.2)。

表3-1 PostgreSQL8.1.2に対してDBT1を実行した時のiostat(io.txt)の集計(eu=2000)
wal出力先	デバイス	tps	blk_read/s	blk_wrtn/s	blk_read	blk_wrtn
デフォルト	sda	0.57	0.66	20.72	6.59	207.33
	sdb	174.32	236.05	3071.44	2362.29	30738.96
	sdc	3.67	0.03	103.58	0.27	1036.64
wal格納先変更(sdc）/td>	sda	0.72	0.52	25.93	5.18	259.83
	sdb	119.30	281.90	2131.68	2823.60	21356.37
	sdc	34.03	0.05	961.82	0.47	9636.43

表3-2 PostgreSQL8.0.6に対してDBT1を実行した時のiostat(io.txt)の集計(eu=2000)
wal出力先	デバイス	tps	blk_read/s	blk_wrtn/s	blk_read	blk_wrtn
デフォルト	sda	0.66	0.10	19.36	1.01	193.68
	sdb	165.41	246.06	2984.28	2461.65	29853.65
	sdc	3.32	0.02	87.80	0.20	878.32
wal格納先変更(sdc）/td>	sda	0.74	0.39	23.97	3.90	239.73
	sdb	99.69	233.48	1719.75	2335.26	17201.28
	sdc	24.86	0.07	802.41	0.67	8025.88

表3-3 CPU利用率の変化
バージョン	wal格納先	user	system	iowait	idle
PostgreSQL8.1.2	デフォルト	57.41	16.09	13.72	12.77
PostgreSQL8.1.2	walログ出力先変更	73.23	22.42	1.69	2.66
PostgreSQL8.0.6	デフォルト	80.31	11.05	3.38	5.26
PostgreSQL8.0.6	walログ出力先変更	79.45	11.79	1.73	7.03

PostgreSQL 8.0.6については，デフォルト状態ですでにCPU利用率が90％以上と高く， iowaitはそれほど大きな値では無かったため，I／O関連のチューニングをしてもBT値の向上効果が出なかったと考える（上記数値は、関連考察データ：SQLチューニング実施後のデータ）。

3.3 PostgreSQL8.1とPostgreSQL8.0の比較

　PostgreSQLの設定ファイルのパラメタ変更してDBT1を実行した結果，性能向上に効果のあったパラメタ値を組合てDBT1を実行した場合と，デフォルトのままでDBT1を実行した場合の比較を，図3-4に示す。

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

【4.まとめ】

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

表4-3 DBT1に対するPostgreSQL設定ファイルのチューニング結果
設定ファイルのチューニング		PostgreSQL8.0	PostgreSQL8.1
postgresql.conf	shared_buffers	10000	100000
	wal_sync_method	open_sync	open_sync
	wal格納先	DB本体と異なるディスク	DB本体と異なるディスク
	shmmax	33554432	1073741824
BT値(eu=3200)	デフォルト値で実行した場合	193.80	216.20
	設定ファイルパラメタのチューニング値で実行した場合	216.20	333.50
	BT値の向上率	11%	54%

OSS iPedia