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 (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
