Wantedlyは今までRDSを初期設定のまま使っていました。ごめんなさい。
今回ちゃんとチューニングしてみたのでやってみた過程と結果を書きます。
ちなみにWantedlyはDBを幾つか持っていて、その中のDBの一つの最適化結果です。
NewRelic での測定の結果、平均31ms ぐらいかかっていたのが、 平均23ms ぐらいになっているので25％ぐらいの改善になりました。
インスタンスタイプ
使っているDBのインスタンスタイプです
- モデル: r3.4xlarge
- vCPU: 16
- メモリ: 122GB
- SSDストレージ: 1 x 320G
デフォルト値
RDSはパラメータグループを調節します。
それぞれのデフォルト値は書かれてないですが、以下のSQL出だすことができます。
=> SELECT name,setting,unit FROM pg_settings;
name | setting | unit
-------------------------------------+---------------------------------------------+------
allow_system_table_mods | off |
application_name | psql |
archive_command | /etc/rds/dbbin/pgscripts/rds_wal_archive %p |
archive_mode | on |
archive_timeout | 300 | s
array_nulls | on |
authentication_timeout | 60 | s
autovacuum | on |
autovacuum_analyze_scale_factor | 0.1 |
autovacuum_analyze_threshold | 50 |
autovacuum_freeze_max_age | 200000000 |
autovacuum_max_workers | 3 |
autovacuum_multixact_freeze_max_age | 400000000 |
autovacuum_naptime | 60 | s
autovacuum_vacuum_cost_delay | 20 | ms
autovacuum_vacuum_cost_limit | -1 |
autovacuum_vacuum_scale_factor | 0.2 |
autovacuum_vacuum_threshold | 50 |
backslash_quote | safe_encoding |
bgwriter_delay | 200 | ms
bgwriter_lru_maxpages | 100 |
bgwriter_lru_multiplier | 2 |
block_size | 8192 |
bonjour | off |
bonjour_name | |
bytea_output | hex |
check_function_bodies | on |
checkpoint_completion_target | 0.9 |
checkpoint_segments | 16 |
checkpoint_timeout | 300 | s
checkpoint_warning | 30 | s
client_encoding | UTF8 |
client_min_messages | notice |
commit_delay | 0 |
commit_siblings | 5 |
constraint_exclusion | partition |
cpu_index_tuple_cost | 0.005 |
cpu_operator_cost | 0.0025 |
cpu_tuple_cost | 0.01 |
cursor_tuple_fraction | 0.1 |
DateStyle | ISO, MDY |
db_user_namespace | off |
deadlock_timeout | 1000 | ms
debug_assertions | off |
debug_pretty_print | on |
debug_print_parse | off |
debug_print_plan | off |
debug_print_rewritten | off |
default_statistics_target | 100 |
default_tablespace | |
default_text_search_config | pg_catalog.simple |
default_transaction_deferrable | off |
default_transaction_isolation | read committed |
default_transaction_read_only | off |
default_with_oids | off |
effective_cache_size | 7856902 | 8kB
effective_io_concurrency | 1 |
enable_bitmapscan | on |
enable_hashagg | on |
enable_hashjoin | on |
enable_indexonlyscan | on |
enable_indexscan | on |
enable_material | on |
enable_mergejoin | on |
enable_nestloop | on |
enable_seqscan | on |
enable_sort | on |
enable_tidscan | on |
escape_string_warning | on |
event_source | PostgreSQL |
exit_on_error | off |
extra_float_digits | 0 |
from_collapse_limit | 8 |
fsync | on |
full_page_writes | on |
geqo | on |
geqo_effort | 5 |
geqo_generations | 0 |
geqo_pool_size | 0 |
geqo_seed | 0 |
geqo_selection_bias | 2 |
geqo_threshold | 12 |
gin_fuzzy_search_limit | 0 |
hot_standby | off |
hot_standby_feedback | off |
ignore_checksum_failure | off |
ignore_system_indexes | off |
integer_datetimes | on |
IntervalStyle | postgres |
join_collapse_limit | 8 |
krb_caseins_users | off |
krb_srvname | postgres |
lc_collate | en_US.UTF-8 |
lc_ctype | en_US.UTF-8 |
lc_messages | |
lc_monetary | C |
lc_numeric | C |
lc_time | C |
listen_addresses | * |
lo_compat_privileges | off |
local_preload_libraries | |
lock_timeout | 0 | ms
log_autovacuum_min_duration | -1 | ms
log_checkpoints | on |
log_connections | off |
log_destination | stderr |
log_disconnections | off |
log_duration | off |
log_error_verbosity | default |
log_executor_stats | off |
log_file_mode | 0644 |
log_hostname | on |
log_line_prefix | %t:%r:%u@%d:[%p]: |
log_lock_waits | off |
log_min_duration_statement | -1 | ms
log_min_error_statement | error |
log_min_messages | warning |
log_parser_stats | off |
log_planner_stats | off |
log_rotation_age | 60 | min
log_rotation_size | 10240 | kB
log_statement | none |
log_statement_stats | off |
log_temp_files | -1 | kB
log_timezone | UTC |
log_truncate_on_rotation | off |
logging_collector | on |
maintenance_work_mem | 16384 | kB
max_connections | 10230 |
max_files_per_process | 1000 |
max_function_args | 100 |
max_identifier_length | 63 |
max_index_keys | 32 |
max_locks_per_transaction | 64 |
max_pred_locks_per_transaction | 64 |
max_prepared_transactions | 0 |
max_stack_depth | 6144 | kB
max_standby_archive_delay | 30000 | ms
max_standby_streaming_delay | 30000 | ms
max_wal_senders | 5 |
password_encryption | on |
port | 5432 |
post_auth_delay | 0 | s
pre_auth_delay | 0 | s
quote_all_identifiers | off |
random_page_cost | 4 |
restart_after_crash | on |
search_path | "$user",public |
segment_size | 131072 | 8kB
seq_page_cost | 1 |
server_encoding | UTF8 |
server_version | 9.3.3 |
server_version_num | 90303 |
session_replication_role | origin |
shared_buffers | 3928451 | 8kB
sql_inheritance | on |
ssl | on |
ssl_ca_file | /rdsdbdata/rds-metadata/ca-cert.pem |
ssl_cert_file | /rdsdbdata/rds-metadata/server-cert.pem |
ssl_crl_file | |
ssl_key_file | /rdsdbdata/rds-metadata/server-key.pem |
ssl_renegotiation_limit | 524288 | kB
standard_conforming_strings | on |
statement_timeout | 0 | ms
superuser_reserved_connections | 3 |
synchronize_seqscans | on |
synchronous_commit | on |
synchronous_standby_names | |
syslog_facility | local0 |
syslog_ident | postgres |
tcp_keepalives_count | 2 |
tcp_keepalives_idle | 300 | s
tcp_keepalives_interval | 30 | s
temp_buffers | 1024 | 8kB
temp_file_limit | -1 | kB
temp_tablespaces | |
TimeZone | UTC |
timezone_abbreviations | Default |
trace_notify | off |
trace_recovery_messages | log |
trace_sort | off |
track_activities | on |
track_activity_query_size | 1024 |
track_counts | on |
track_functions | none |
track_io_timing | off |
transaction_deferrable | off |
transaction_isolation | read committed |
transaction_read_only | off |
transform_null_equals | off |
unix_socket_group | rdsdb |
unix_socket_permissions | 0700 |
update_process_title | on |
vacuum_cost_delay | 0 | ms
vacuum_cost_limit | 200 |
vacuum_cost_page_dirty | 20 |
vacuum_cost_page_hit | 1 |
vacuum_cost_page_miss | 10 |
vacuum_defer_cleanup_age | 0 |
vacuum_freeze_min_age | 50000000 |
vacuum_freeze_table_age | 150000000 |
vacuum_multixact_freeze_min_age | 5000000 |
vacuum_multixact_freeze_table_age | 150000000 |
wal_block_size | 8192 |
wal_buffers | 2048 | 8kB
wal_keep_segments | 32 |
wal_level | hot_standby |
wal_receiver_status_interval | 10 | s
wal_receiver_timeout | 30000 | ms
wal_segment_size | 2048 | 8kB
wal_sender_timeout | 30000 | ms
wal_sync_method | fdatasync |
wal_writer_delay | 200 | ms
work_mem | 1024 | kB
xmlbinary | base64 |
xmloption | content |
zero_damaged_pages | off |
キャッシュヒット率の確認
統計情報のDocument: https://www.postgresql.jp/document/9.2/html/monitoring-stats.html
特にヒット率は、pg_stat_databaseを使ってblks_hit * 100.0 / (blks_read + blks_hit)を計算するとわかる
=> select datname, blks_hit * 100.0 / (blks_read + blks_hit) AS cache_hit from pg_stat_database where datname = 'natsubatecat789';
datname | cache_hit
------------+---------------------
hogehogedb | 99.9978484645104419
他に以下の様なカラムがあります。
| カラム名 | 型 | 説明 |
|---|---|---|
| datid | oid | データベースID |
| datname | name | データベース名 |
| numbackends | integer | 接続中のバックエンドプロセス数 |
| xact_commit | bigint | コミットされたトランザクション数 |
| xact_rollback | bigint | ロールバックされたトランザクション数 |
| blks_read | bigint | ディスク読込ブロック数 |
| blks_hit | bigint | ブロック要求の際、キャッシュに存在したブロック数 (osキャッシュのヒットは含まない) |
| tup_returned | bigint | 表スキャンでの読み取り合計行数 |
| tup_fetched | bigint | インデックススキャンでの読み取り合計行数 |
| tup_inserted | bigint | データベース内のテーブルに挿入された合計行数 |
| tup_updated | bigint | データベース内のテーブルの更新された合計行数 |
| tup_deleted | bigint | データベース内のテーブルから削除された合計行数 |
| conflicts | bigint | コンフリクトによってクエリがキャンセルした数(スタンバイのDBにしか起こらない) |
| temp_files | bigint | Tempファイルが作られたクエリの数 |
| temp_bytes | bigint | Tempファイルが作られたデータ量 |
| deadlocks | bigint | デッドロックが起こっている量 |
| blk_read_time | double precision | バックエンドからデータファイルブロックを読んだ時間(ミリ秒) |
| blk_write_time | double precision | バックエンドからデータファイルブロックを書き込んだ時間(ミリ秒) |
| stats_reset | timestamp with time zone | 最後に統計情報がリセットした時刻 |
キャッシュ率のほかは、Tmpファイルが作られている量(Diskアクセスが起きている)temp_bytes, temp_filesを見ると良い
悪いところを見つけるためのlogの設定
RDSはデフォルトではほとんどログが出ない
以下はまず、やったら良い
- log_min_duration_statement = 250ms
- 遅いクエリをログに(デフォルト-1で出力しない)
- log_temp_files = 0
- Tempファイルを作ったらログ出力(単位はkb、デフォルト-1で出力しない
- work_memの設定値を調節できる
- 1MBとか設定している人がいるけど、実際あとちょっとでメモリに乗り切っていないのを一番知りたいので0が適切
- log_checkpoints = on
- checkpointのタイミングでログ出力
- checkpointをどのくらいの頻度で出すかはパフォーマンスに影響する
- log_lock_waits = on
- deadlockのタイムアウト時間を超えたら出力する
- deadlockによってパフォーマンスが出ていないかわかる
あとは、この人の書いてあることが良さそう
この人によると
- log_connections = on
- log_disconnections = on
もやっていた。僕らもやった。
ベンチマーク
データによらない基本的な性能は PostgreSQL 公式ベンチマークツールであるところの pgbench を使って測っておくと良い。
適当に同じインスタンスタイプのDBを作ってみて図ると以下のようになる。
pgbench -h dtan4-test.crp2azrgxykc.ap-northeast-1.rds.amazonaws.com -p 5432 -U dtan4 -c 20 -t 100 dtan4db
Password:
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 100
number of transactions actually processed: 2000/2000
tps = 483.005570 (including connections establishing)
tps = 500.757521 (excluding connections establishing)
実データのDBとは結果が異なるかもしれないが、一応これの値も参考値にすると良いはず。
実際よく効く&聞く設定項目
-
shared_buffers- デフォルト値: 3928451
- 単位は 8KB
- よく言われる設定値: 2GB以下: 全メモリの20%, 32GB以下: 全メモリの25%, 32GB以上: 8GB
- よく言われる設定値その２: 全メモリの 1/4 - 1/2
- ただ、OS の上限値 (shmmax) を上げないと増やせないので、OSをいじれないRDSではそんなに増やせない
-
work_mem- デフォルト値: 1024
- 単位は KB
- よく言われる設定値: 最初は32MB程度から始め、
log_temp_filesで吐かれたログを見つつ、倍々に増やしていく - よく言われる設定値その2: work_mem * connection最大数 が 全メモリの1/4を超えないぐらいにしておく
-
maintenance_work_mem- デフォルト値: 16384
- 単位は KB
- よく言われる設定値: システムメモリの10%。最大1GB
- よく言われる設定値その2: もしVACUUMの問題があればもっと大きく
-
effective_cache_size- デフォルト値: 7856902
- 単位は 8KB
- よく言われる設定値: 全メモリの 1/2 程度
設定項目の反映のタイミング
DB パラメータグループを使用する - Amazon Relational Database Service によると、
- パラメータグループ変更は、再起動 必要
- dynamic パラメータ変更は、再起動 不要
- static パラメータ変更は、再起動 必要
log周りとshared_buffers が static、残りは dynamic なパラメータ
ベンチマークが色んな値をめっちゃでっかくしたらよかった話
単位がKBだったり8KBだったりすることを知らず設定して
- work_mem: 33554432 => 32 GB
- maintenance_work_mem: 1073740800 => 1 TB
- effective_cache_size: 7995392 => 61 GB
にしたらベンチマークがかなり早くなった件
pgbench -h dtan4-test.crp2azrgxykc.ap-northeast-1.rds.amazonaws.com -p 5432 -U dtan4 -c 20 -t 100 dtan4db
Password:
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 100
number of transactions actually processed: 2000/2000
tps = 579.432185 (including connections establishing)
tps = 605.797176 (excluding connections establishing)
もちろんベンチ以外でそれをやると危険かもしれないけど、通説を信じずコードを読んで見る気になった!!
ソースコード
からダウンロード。
実際読んでみるとかなり綺麗に書かれている。スゴイ。
work_mem が大きくてもいいんじゃないか説について
コード見てみた。
work_memはtuplestore_begin_heapかtuplesort_begin_heapで使われている。
tuplestore_begin_heapについて
さて、メモリの確保をどれだけするかについてはtuplestore_begin_commonの中の
/*
* Initial size of array must be more than ALLOCSET_SEPARATE_THRESHOLD;
* see comments in grow_memtuples().
*/
state->memtupsize = Max(16384 / sizeof(void *),
ALLOCSET_SEPARATE_THRESHOLD / sizeof(void *) + 1);
state->growmemtuples = true;
state->memtuples = (void **) palloc(state->memtupsize * sizeof(void *));
で行われている 16384 = 16k で、ALLOCSET_SEPARATE_THRESHOLD の値は、初期状態で8kに設定されていたので、通常まず16kまでしか使わず、work_memに指定された値はあくまでMaxで、それに依存してメモリ確保しているわけではない模様。
work_memがセットされているmaxKBytesはどう使われているかというと
Tuplestorestate *state;
...
state->allowedMem = maxKBytes * 1024L;
state->availMem = state->allowedMem;
となっている。これはタプルの拡張grow_memtuplesのタイミングで使われている。
この拡張は、memtuplesは倍々に増えていく実装。なので、work_memは
16k, 32k, 64k, 128k, 256k, 512k, 1M, 2M, 4M, 16M, 32M, 64M, 128M, 256M, 512M, 1G...
のどれかであると良い。
/*
* Grow the memtuples[] array, if possible within our memory constraint. We
* must not exceed INT_MAX tuples in memory or the caller-provided memory
* limit. Return TRUE if we were able to enlarge the array, FALSE if not.
*
* Normally, at each increment we double the size of the array. When doing
* that would exceed a limit, we attempt one last, smaller increase (and then
* clear the growmemtuples flag so we don't try any more). That allows us to
* use memory as fully as permitted; sticking to the pure doubling rule could
* result in almost half going unused. Because availMem moves around with
* tuple addition/removal, we need some rule to prevent making repeated small
* increases in memtupsize, which would just be useless thrashing. The
* growmemtuples flag accomplishes that and also prevents useless
* recalculations in this function.
*/
static bool
grow_memtuples(Tuplestorestate *state)
tuplesort_begin_heapについて
確保の方法も使われ方もtuplestore_begin_heapと完全に同様。なので元の議論と同じことが言える。
つまり
大きくて良さそう。512M か 1G でも良さそう。
自分たちは大体最大で300コネクションぐらいだったので 128M までは増やしても安全そう。
maintenance_work_memについて
maintenance_work_memは
- src/backend/access/hash/hashsort.c
- hash index生成のためのソート
- backend/access/nbtree/nbtsort.c
- btree index生成のためのソート
- src/backend/commands/vacuumlazy.c
- lazy vaccum
で、使われている。
それぞれに
/*
* We size the sort area as maintenance_work_mem rather than work_mem to
* speed index creation. This should be OK since a single backend can't
* run multiple index creations in parallel. Note that creation of a
* unique index actually requires two BTSpool objects. We expect that the
* second one (for dead tuples) won't get very full, so we give it only
* work_mem.
*/
btKbytes = isdead ? work_mem : maintenance_work_mem;
とか書いてある。つまり、index生成は同時に起こることがないので、 work_memと違いコネクション数*work_memのメモリを消費してしまう可能性がない。なのでめっちゃ大きくても結構大丈夫。
結論
DBサイズをメモリ以下で運用している今は、maintenance_work_memはいくら大きくてもいい気がしてきた。最大でindexの生成に必要なメモリ分しか使用されない気がする。
実メモリの10%で1Gとかが最大とか言われているが、DB内最大のIndexのサイズぐらいはあっていい気がする。
10％であり続けて12GとかでOKだと思われる。
## effective_cache_size について
effective_cache_sizeに指定するのは、カーネルやPostgeSQLの共有バッファなど、PostgreSQLが使用するバッファ領域の大きさの推定値です。これはあくまでオプティマイザが参考にするための数字なので、正確な値は必要ありません。
https://thinkit.co.jp/cert/marugoto/2/1/12/2.htm
とある通り推定に使われている。
src/backend/optimizer/path/costsize.c
/*
* index_pages_fetched
* Estimate the number of pages actually fetched after accounting for
* cache effects.
*
* We use an approximation proposed by Mackert and Lohman, "Index Scans
* Using a Finite LRU Buffer: A Validated I/O Model", ACM Transactions
* on Database Systems, Vol. 14, No. 3, September 1989, Pages 401-424.
* The Mackert and Lohman approximation is that the number of pages
* fetched is
* PF =
* min(2TNs/(2T+Ns), T) when T <= b
* 2TNs/(2T+Ns) when T > b and Ns <= 2Tb/(2T-b)
* b + (Ns - 2Tb/(2T-b))*(T-b)/T when T > b and Ns > 2Tb/(2T-b)
* where
* T = # pages in table
* N = # tuples in table
* s = selectivity = fraction of table to be scanned
* b = # buffer pages available (we include kernel space here)
*
* We assume that effective_cache_size is the total number of buffer pages
* available for the whole query, and pro-rate that space across all the
* tables in the query and the index currently under consideration. (This
* ignores space needed for other indexes used by the query, but since we
* don't know which indexes will get used, we can't estimate that very well;
* and in any case counting all the tables may well be an overestimate, since
* depending on the join plan not all the tables may be scanned concurrently.)
*
* The product Ns is the number of tuples fetched; we pass in that
* product rather than calculating it here. "pages" is the number of pages
* in the object under consideration (either an index or a table).
* "index_pages" is the amount to add to the total table space, which was
* computed for us by query_planner.
*
* Caller is expected to have ensured that tuples_fetched is greater than zero
* and rounded to integer (see clamp_row_est). The result will likewise be
* greater than zero and integral.
*/
double
index_pages_fetched(double tuples_fetched, BlockNumber pages,
double index_pages, PlannerInfo *root)
{
double pages_fetched;
double total_pages;
double T,
b;
/* T is # pages in table, but don't allow it to be zero */
T = (pages > 1) ? (double) pages : 1.0;
/* Compute number of pages assumed to be competing for cache space */
total_pages = root->total_table_pages + index_pages;
total_pages = Max(total_pages, 1.0);
Assert(T <= total_pages);
/* b is pro-rated share of effective_cache_size */
b = (double) effective_cache_size *T / total_pages;
/* force it positive and integral */
if (b <= 1.0)
b = 1.0;
else
b = ceil(b);
/* This part is the Mackert and Lohman formula */
if (T <= b)
{
pages_fetched =
(2.0 * T * tuples_fetched) / (2.0 * T + tuples_fetched);
if (pages_fetched >= T)
pages_fetched = T;
else
pages_fetched = ceil(pages_fetched);
}
else
{
double lim;
lim = (2.0 * T * b) / (2.0 * T - b);
if (tuples_fetched <= lim)
{
pages_fetched =
(2.0 * T * tuples_fetched) / (2.0 * T + tuples_fetched);
}
else
{
pages_fetched =
b + (tuples_fetched - lim) * (T - b) / T;
}
pages_fetched = ceil(pages_fetched);
}
return pages_fetched;
}
つまり...
キャッシュヒット率の推定に使われている。
これが大きいほど、index使用時にsequential_accessのコストを使う => sequencial_scanは全部sequential_accessのコストなので、これが大きい方がindexが使われやすいということになる。
ただ、実際コードを読んだところ、ここのAssumptionが全然うまく行っていないような気がした。あんまり深追いしてもいいことないかも。
random_page_cost について
random_page_costには、テーブルの1ページ（8Kバイト固定長の領域）のアクセスにかかる時間を基準として、インデックスから目的の1ページをアクセスするのにかかる時間を設定します。デフォルトでは4、すなわちインデックスへのアクセスには4倍時間がかかるという設定になっています。メモリを1〜2Gバイト搭載したマシンでは、テーブルが1000万件以上あるようなケースを除くと、デフォルトの4という数字は大きすぎるようです。2または3程度にするのがよいでしょう（1以下にするのは不適切です）。
random_page_costの値を小さく設定すると、オプティマイザがインデックスを使用した問い合わせプランを選択する傾向が強くなります。
https://thinkit.co.jp/cert/marugoto/2/1/12/2.htm
つまり
4 から 2 や 1.1 ぐらいでもOKっぽい
synchronous_commit について
結構奥の手。これをoffにすることでDBが決済まわりのコードを含んでいない場合はOKだと思う。fsyncはonにしているままなわけだし。
自分は、Starbucks Does Not Use Two-Phase Commitを読んでから、どんどんトランザクションいらないよね派になっている。
現実世界ではトランザクションなんて使わなくてもうまく行っているということを、スタバでコーヒーを買って実際にコーヒーがサーブされるまでの事例を使って説明している。実際に複雑なモデル(例えばユーザ)作成の時だって、下手にトランザクションにして下手なエラー出すよりは、2重に作成させてしまって後で使われない方を消すほうがユーザ体験的にも良かったりするし。
cuzicさんのQiitaでの説明
fsync
off とすることでディスクへの書き込みが非同期化でき、高速化できます。fsync を off とするときは同時に full_page_writes も off にすることでページの一部のみの書き込みが許容されるようになり、さらなる高速化効果が得られます。ただし、代償に書き込み順の保証、クラッシュ時の復元可能性はなくなります。たいていの場合は、後述の synchronous_commit=off とすれば高速化効果としては十分で、fsync=off とする必要はありません。synchronous_commit
off とすると同期コミットが無効化され、高速化できます。秒間トランザクション数を増やしたい場合は特に有益です。ただし、クラッシュ時の復元可能性は担保されるものの、データ損失のリスクはあります。synchronous_commit=off はセッション単位で指定できるので、性能計測の結果、意味がある場合に、該当箇所のみ synchronous_commit=off とするのが良いやり方です。
公式の説明
synchronous_commit (enum)
トランザクションのコミットがクライアントに"success"の表示を返す前に、WALレコードがディスク上に書き込まれるまで待つかどうかの指定をします。 有効な値はon、remote_write、local、およびoffです。 デフォルトかつ安全な設定はonです。 offの場合、クライアントに成功を報告する時点とトランザクションが本当にサーバクラッシュに対して安全になるまでの間に遅延が発生します。 （最大の遅延は、wal_writer_delayの3倍です。） fsyncと異なり、このパラメータをoffに設定することによって、データベースの一貫性が損なわれる可能性はありません。 オペレーティングシステムやデータベースのクラッシュにより最近コミットされたということになっているトランザクションの一部が失われる可能性がありますが、これらのトランザクションが正常にアボートされた時とデータベースの状態は変わりません。 ですので、synchronous_commitを無効にすることは、トランザクションの信頼性が確実であることよりも性能が重要である場合に有効な方法です。 詳細は項29.3を参照してください。synchronous_standby_namesが設定されていると、このパラメータもやはり、トランザクションのWALレコードが、スタンバイサーバに複製されるまでトランザクションコミットを待機するか否かを制御します。 on に設定された場合、現在の同期スタンバイがトランザクションのコミットレコードを受け取り、記憶装置に既に書き込まれたことを確実視するまでコミットは待機は待機されます。 このことにより、プライマリおよびスタンバイがそれぞれのデータベース記憶装置の故障を被った場合を除いて、トランザクションが失われることはありません。 remote_write に設定された場合、現在の同期スタンバイがトランザクションのコミットレコードを受け取り、スタンバイのオペレーティングシステムに書き出されたことを確実視するまでコミットは待機は待機されます。しかし、データがスタンバイの記憶装置に安定して書き込まれたか否かは必須ではありません。 この設定は PostgreSQL のスタンバイインスタンスがクラッシュしたとしても、データ保護を保証するのに充分です。しかし、スタンバイがオペレーティングシステムのレベルでクラッシュした場合はこの限りではありません。
同期レプリケーションが使用されている場合、通常、ディスクに対してのローカルな書き込みとWALレコードのレプリケーションのいずれかを待つか、トランザクションに非同期でコミットさせるかどちらかの選択を行うよう実用的になっています。しかし、トランザクションに対し特別の値であるlocalが使用でき、同期レプリケーションではなく、ディスクへのローカルフラッシュの待機を要請することが可能です。 もし synchronous_standby_names が設定されていなければ、on、remote_write および local の設定は全て同一の同期レベルを提供します。トランザクションのコミットはローカルディスクへの書き込みのみ待機します。
このパラメータはいつでも変更可能です。 この設定により任意の1つのトランザクションのコミット時の動作が決まります。 したがって、一部のトランザクションのコミットを同期的に、その他を非同期的にすることが可能で、かつ、有用です。 例えば、デフォルトが同期コミットの場合に単一の複数文トランザクションを非同期にコミットさせるためには、トランザクション内でSET LOCAL synchronous_commit TO OFFを発行します。
pgbenchでの結果
pgbenchは、小さいトランザクションを大量生成する性質上、かなり結果が良くなる。
$ pgbench -h dtan4-test.crp2azrgxykc.ap-northeast-1.rds.amazonaws.com -p 5432 -U dtan4 -c 100 -t 100 dtan4db
Password:
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 100
number of threads: 1
number of transactions per client: 100
number of transactions actually processed: 10000/10000
tps = 1506.441544 (including connections establishing)
tps = 1690.038205 (excluding connections establishing)
ちなみに、これが on だったデフォルトの状態では
...
tps = 512.403626 (including connections establishing)
tps = 531.903605 (excluding connections establishing)
つまり...
日々の速度と、落ちた時の安全性のトレードオフを考え、前者が勝った場合 off にしよう。
あとは、そもそも論でトランザクションを完璧に実装できているプログラム以外 on でも意味が無いというのはあると思う。
結論
- shared buffer: そのまま
- work_mem: 1024 => 131072 [kb] (128Mに)
- maintenance_work_mem: 16384 => 8388608 [kb] (8Gに)
- random_page_cost: 4 => 1.1
- synchronous_commit: on => off
ログ出力まわり
- log_min_duration_statement: 250 [ms]
- log_temp_files: 0
- log_lock_waits: 1 (on)
- log_connections: 1 (on)
- log_disconnections: 1 (on)
結果
NewRelic での測定の結果 平均31ms ぐらいかかっていたのが、 平均23ms ぐらいになっているので25％ぐらいの改善にはなったかと