パラメータチューニング †
Logging †
Memory †
- shared_buffers
| total memory | shared_buffers |
| 〜2GB | total ✕ 20% |
| 2GB〜32GB | total ✕ 25% |
| 32GB〜 | 8GB |
- work_mem
- 32MB〜64MB
- total = (work mem) ✕ (planner node)
- maintenance_work_mem
| total memory | maintenance_work_mem |
| 〜100GB | total ✕ 10% |
| 100GB〜 | 1GB |
- effective_cache_size
| total memory | effective_cache_size |
| total ✕ 50% |
-shared_buffers = 128MB # min 128kB
+shared_buffers = 512MB # min 128kB
# (change requires restart)
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
-#work_mem = 1MB # min 64kB
-#maintenance_work_mem = 16MB # min 1MB
+work_mem = 16MB # min 64kB
+maintenance_work_mem = 128MB # min 1MB
#max_stack_depth = 2MB # min 100kB
-#effective_cache_size = 128MB
+effective_cache_size = 128MB
Checkpoints †
- WAL (Write Ahead Log) : ログ先行書き込み = データベース更新に先立ってログ(todo list)を作成する。undo、redo
- Checkpoint で WAL を物理ディスクに反映する
#full_page_writes = on # recover from partial page writes
-#wal_buffers = -1 # min 32kB, -1 sets based on shared_buffers
+wal_buffers = 16MB # min 32kB, -1 sets based on shared_buffers
# (change requires restart)
#wal_writer_delay = 200ms # 1-10000 milliseconds
-#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
-#checkpoint_timeout = 5min # range 30s-1h
-#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
+checkpoint_segments = 32 # in logfile segments, min 1, 16MB each
+checkpoint_timeout = 30min # range 30s-1h
+checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s # 0 disables
- total = 3 ✕ (wal_buffers) ✕ (checkpoint_segments)
Planner †
| Disk | random_page_cost |
| RAID10 | 3.0 |
| SAN | 2.0 |
| Amazon EBS | 1.0 |
-#random_page_cost = 4.0 # same scale as above
+random_page_cost = 2.0 # same scale as above
暗号化ライブラリ pgcrypto の有効化 †
- パスワードを暗号化(ハッシュ化)したいときなど、pgcrypto を有効化する必要がある
- http://www.postgresql.org/docs/9.3/static/pgcrypto.html
demo=# CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION
demo=# SELECT encode(digest('password','sha256'),'hex') as pwdhash;
pwdhash
------------------------------------------------------------------
5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8
(1 row)
GIS
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
