From: Syed Asif Tanveer <asif.tanveer@analytics.com.pk>
To: pgsql-performance@postgresql.org
Subject: Postgres configuration for 8 CPUs, 6 GB RAM
Date: Tue, 27 Nov 2012 12:47:03 +0500
Message-ID: <000601cdcc73$69f82d70$3de88850$@analytics.com.pk> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performanceAttachments:postgresql_conf.txt (text/plain, 11.0K) download
# -----------------------------
# 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: asif.tanveer@analytics.com.pk
Subject: Re: Postgres configuration for 8 CPUs, 6 GB RAM
In-Reply-To: <000601cdcc73$69f82d70$3de88850$@analytics.com.pk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox