Hi,
I am using PostgreSQL 9.1.5 for Data warehousing and OLAP puposes. Data size
is around 100 GB and I have tuned my PostgreSQL accordingly still I am
facing performance issues. The query performance is too low despite tables
being properly indexed and are vacuumed and analyzed at regular basis. CPU
usage never exceeded 15% even at peak usage times. Kindly guide me through
if there are any mistakes in setting configuration parameters. Below are my
system specs and please find attached my postgresql configuration parameters
for current system.
OS: Windows Server 2008 R2 Standard
Manufacturer: IBM
Mode: System X3250 M3
Processor: Intel (R) Xeon (R) CPU X3440 @ 2.53
GHz
Ram: 6 GB
OS Type: 64 bit
Thanks in advance
Syed Asif Tanveer
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
#
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
shared_buffers = 2048MB
# min 128kB
temp_buffers = 8MB
# min 800kB
#max_prepared_transactions = 0
# zero disables the feature
work_mem = 256MB
# min 64kB
maintenance_work_mem = 128MB
# min 1MB
#max_stack_depth = 2MB
# min 100kB
#max_files_per_process = 1000
# min 25
#shared_preload_libraries = ''
#vacuum_cost_delay = 0ms
# 0-100 milliseconds
#vacuum_cost_page_hit = 1
# 0-10000 credits
#vacuum_cost_page_miss = 10
# 0-10000 credits
#vacuum_cost_page_dirty = 20
# 0-10000 credits
#vacuum_cost_limit = 200
# 1-10000 credits
#bgwriter_delay = 200ms
# 10-10000ms between
rounds
#bgwriter_lru_maxpages = 100
# 0-1000 max buffers
written/round
#bgwriter_lru_multiplier = 2.0
# 0-10.0 multipler on buffers
scanned/round
#effective_io_concurrency = 1
# 1-1000. 0 disables prefetching
#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
#wal_level = minimal
# minimal, archive, or
hot_standby)
#fsync = on
# turns
forced synchronization on or off
#synchronous_commit = on
# synchronization
level; on, off, or local
#wal_sync_method = fsync
# the default is the
first option
#full_page_writes = on
# recover from partial
page writes
wal_buffers = 32MB
# min 32kB, -1
sets based on shared_buffers
wal_writer_delay = 500ms
# 1-10000 milliseconds
#commit_delay = 0
# range
0-100000, in microseconds
#commit_siblings = 5
# range 1-1000
checkpoint_segments = 64
# in logfile segments,
min 1, 16MB each
#checkpoint_timeout = 5min
# range 30s-1h
checkpoint_completion_target = 0.9
# checkpoint target duration,
0.0 - 1.0
#checkpoint_warning = 30s
# 0 disables
#archive_mode = off
# allows
archiving to be done
#archive_command = ''
# command to use to
archive a logfile segment
#archive_timeout = 0
# force a logfile
segment switch after this
#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
# - Master Server -
#max_wal_senders = 0
# max number of
walsender processes
#wal_sender_delay = 1s
# walsender cycle time,
1-10000 milliseconds
#wal_keep_segments = 0
# in logfile segments,
16MB each; 0 disables
#vacuum_defer_cleanup_age = 0
# number of xacts by which
cleanup is delayed
#replication_timeout = 60s
# in milliseconds; 0
disables
#synchronous_standby_names = ''
# standby servers that provide
sync rep
# - Standby Servers -
#hot_standby = off
# "on" allows
queries during recovery
#max_standby_archive_delay = 30s
# max delay before canceling
queries
#max_standby_streaming_delay = 30s
# max delay before canceling
queries
#wal_receiver_status_interval = 10s
# send replies at least this
often
#hot_standby_feedback = off
# send info from
standby to prevent
#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on
# - Planner Cost Constants -
#seq_page_cost = 1.0
# measured on an
arbitrary scale
#random_page_cost = 4.0
# same scale as above
#cpu_tuple_cost = 0.01
# same scale as above
cpu_index_tuple_cost = 0.0005
# same scale as above
#cpu_operator_cost = 0.0025
# same scale as above
effective_cache_size = 2GB
# - Genetic Query Optimizer -
#geqo = on
#geqo_threshold = 12
#geqo_effort = 5
# range 1-10
#geqo_pool_size = 0
# selects
default based on effort
#geqo_generations = 0
# selects default based
on effort
#geqo_selection_bias = 2.0
# range 1.5-2.0
#geqo_seed = 0.0
# range 0.0-1.0
# - Other Planner Options -
default_statistics_target = 1000
# range 1-10000
#constraint_exclusion = partition
# on, off, or partition
#cursor_tuple_fraction = 0.1
# range 0.0-1.0
#from_collapse_limit = 8
#join_collapse_limit = 8
# 1 disables collapsing
of explicit
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
log_destination = 'stderr'
# This is used when logging to stderr:
logging_collector = on
# These are only used if logging_collector is on:
#log_directory = 'pg_log'
# directory where log
files are written,
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
# log file name pattern,
#log_file_mode = 0600
# creation mode for log
files,
#log_truncate_on_rotation = off
#log_rotation_age = 1d
#log_rotation_size = 10MB
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
#silent_mode = off
# - When to Log -
#client_min_messages = notice
#log_min_messages = warning
#log_min_error_statement = error
#log_min_duration_statement = -1
# - What to Log -
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_error_verbosity = default
# terse, default, or verbose
messages
#log_hostname = off
log_line_prefix = '%t '
#log_lock_waits = off
# log lock waits >=
deadlock_timeout
#log_statement = 'none'
# none, ddl, mod, all
#log_temp_files = -1
#log_timezone = '(defaults to server environment setting)'
#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------
# - Query/Index Statistics Collector -
#track_activities = on
#track_counts = on
#track_functions = none
# none, pl, all
#track_activity_query_size = 1024
#update_process_title = on
#stats_temp_directory = 'pg_stat_tmp'
# - Statistics Monitoring -
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
autovacuum = on
# Enable
autovacuum subprocess? 'on'
#log_autovacuum_min_duration = -1
#autovacuum_max_workers = 3
autovacuum_naptime = 60min
# time between
autovacuum runs
#autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 1000
#autovacuum_vacuum_scale_factor = 0.2
# fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1
# fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000
# maximum XID age before forced vacuum
#autovacuum_vacuum_cost_delay = 20ms
# default vacuum cost delay for
#autovacuum_vacuum_cost_limit = -1
# default vacuum cost limit for
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '"$user",public'
# schema names
#default_tablespace = ''
# a tablespace name, ''
uses the default
#temp_tablespaces = ''
# a list of tablespace
names, '' uses
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#default_transaction_deferrable = off
#session_replication_role = 'origin'
#statement_timeout = 0
# in milliseconds, 0 is
disabled
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
#bytea_output = 'hex'
# hex, escape
#xmlbinary = 'base64'
#xmloption = 'content'
# - Locale and Formatting -
datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
#timezone = '(defaults to server environment setting)'
#timezone_abbreviations = 'Default'
#extra_float_digits = 0 # min -15, max 3
#client_encoding = sql_ascii
# These settings are initialized by initdb, but they can be changed.
lc_messages = 'English_United States.1252'
# locale for system error message
lc_monetary = 'English_United States.1252'
# locale for monetary formatting
lc_numeric = 'English_United States.1252'
# locale for number formatting
lc_time = 'English_United States.1252'
# locale for time formatting
# default configuration for text search
default_text_search_config = 'pg_catalog.english'
# - Other Defaults -
#dynamic_library_path = '$libdir'
#local_preload_libraries = ''
#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------
#deadlock_timeout = 1s
#max_locks_per_transaction = 64
# min 10
# lock table slots.
#max_pred_locks_per_transaction = 64 # min 10
#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------
# - Previous PostgreSQL Versions -
#array_nulls = on
#backslash_quote = safe_encoding
# on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = on
#lo_compat_privileges = off
#quote_all_identifiers = off
#sql_inheritance = on
#standard_conforming_strings = on
#synchronize_seqscans = on
# - Other Platforms and Clients -
#transform_null_equals = off
#------------------------------------------------------------------------------
# ERROR HANDLING
#------------------------------------------------------------------------------
#exit_on_error = off
# terminate session on
any error?
#restart_after_crash = on
# reinitialize after
backend crash?
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
#custom_variable_classes = ''
# list of custom variable class
names
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
