Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TdLJ6-0007Y4-2f for pgsql-performance@arkaria.postgresql.org; Tue, 27 Nov 2012 13:34:36 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TdLJ5-0004sh-DZ for pgsql-performance@arkaria.postgresql.org; Tue, 27 Nov 2012 13:34:35 +0000 Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TdFt7-0000FP-Oi for pgsql-performance@postgresql.org; Tue, 27 Nov 2012 07:47:25 +0000 Received: from bosmailout10.eigbox.net ([66.96.185.10]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TdFt3-0004UC-8H for pgsql-performance@postgresql.org; Tue, 27 Nov 2012 07:47:24 +0000 Received: from bosmailscan03.eigbox.net ([10.20.15.3]) by bosmailout10.eigbox.net with esmtp (Exim) id 1TdFt1-0003W7-Fh for pgsql-performance@postgresql.org; Tue, 27 Nov 2012 02:47:19 -0500 Received: from bosimpout01.eigbox.net ([10.20.55.1]) by bosmailscan03.eigbox.net with esmtp (Exim) id 1TdFt1-00082E-9C for pgsql-performance@postgresql.org; Tue, 27 Nov 2012 02:47:19 -0500 Received: from bosauthsmtp11.eigbox.net ([10.20.18.11]) by bosimpout01.eigbox.net with NO UCE id UXnK1k0020EKspE01XnKnB; Tue, 27 Nov 2012 02:47:19 -0500 X-Authority-Analysis: v=2.0 cv=INa1/HTG c=1 sm=1 a=sTi1PkVL3L2A3HjO22wWRQ==:17 a=yrDjQFE1iK4A:10 a=85FbnYpYUDsA:10 a=z6WNB4w6UhUA:10 a=aCik3l5nAAAA:8 a=g4X1nMtBHREA:10 a=e6VjrH_zuG-umriKrHQA:9 a=CjuIK1q_8ugA:10 a=yMhMjlubAAAA:8 a=SSmOFEACAAAA:8 a=wuIz1wIhfzRkXKEydaYA:9 a=gKO2Hq4RSVkA:10 a=UiCQ7L4-1S4A:10 a=hTZeC7Yk6K0A:10 a=frz4AuCg-hUA:10 a=SEHZQarWSiUqrByEQfUA:9 a=qzPl_YNBLWcxFdx8:21 a=hn7fIxI1bKZ4SDii:21 a=anyYG9rjTBM1sAjEBQ8Cew==:117 X-EN-OrigOutIP: 10.20.18.11 X-EN-IMPSID: UXnK1k0020EKspE01XnKnB Received: from [203.135.54.228] (helo=AsifPC) by bosauthsmtp11.eigbox.net with esmtpa (Exim) id 1TdFsz-0002Ht-03 for pgsql-performance@postgresql.org; Tue, 27 Nov 2012 02:47:19 -0500 From: "Syed Asif Tanveer" To: 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> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0007_01CDCC9D.52D05850" X-Mailer: Microsoft Outlook 14.0 Thread-Index: Ac3MceUQfmYismD/QDmpzRYBwHErkA== Content-Language: en-us X-EN-UserInfo: 39c90acacf8056cabcc542098d2d6469:b7c48b71f7ed5c1fc67677b9d029d54e X-EN-AuthUser: asif.tanveer@analytics.com.pk X-EN-OrigIP: 203.135.54.228 X-EN-OrigHost: unknown X-Pg-Spam-Score: -2.3 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_0007_01CDCC9D.52D05850 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0008_01CDCC9D.52D05850" ------=_NextPart_001_0008_01CDCC9D.52D05850 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_001_0008_01CDCC9D.52D05850 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

 

I am using= PostgreSQL 9.1.5 for Data warehousing and OLAP puposes. Data size is aroun= d 100 GB and I have tuned my PostgreSQL accordingly still I am facing perfo= rmance issues. The query performance is too low despite tables being proper= ly 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 spec= s and please find attached my postgresql configuration parameters for curre= nt system.

 

OS:          = ;            &n= bsp;            = ;      Windows Server 2008 R2 Standard<= /p>

Manufacturer:      &n= bsp;           IBM

Mode:       = ;            &n= bsp;            = ;  System X3250 M3

Processor: =             &nb= sp;            = Intel (R) Xeon (R) CPU  X3440 @ 2.53 GHz

Ram:          &nbs= p;            &= nbsp;           &nbs= p;  6 GB

OS Type:   =             &nb= sp;            =   64 bit

 

Thanks in advan= ce

 

Syed Asif Tanveer

 =

= ------=_NextPart_001_0008_01CDCC9D.52D05850-- ------=_NextPart_000_0007_01CDCC9D.52D05850 Content-Type: text/plain; name="postgresql_conf.txt" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="postgresql_conf.txt" # ----------------------------- # PostgreSQL configuration file # ----------------------------- # #=20 #--------------------------------------------------------------------------= ---- # RESOURCE USAGE (except WAL) #--------------------------------------------------------------------------= ---- # - Memory - shared_buffers =3D 2048MB # min 128kB temp_buffers =3D 8MB # min 800kB #max_prepared_transactions =3D 0 # zero disables the feature work_mem =3D 256MB # min 64kB maintenance_work_mem =3D 128MB # min 1MB #max_stack_depth =3D 2MB # min 100kB #max_files_per_process =3D 1000 # min 25 #shared_preload_libraries =3D '' #vacuum_cost_delay =3D 0ms # 0-100 milliseconds #vacuum_cost_page_hit =3D 1 # 0-10000 credits #vacuum_cost_page_miss =3D 10 # 0-10000 credits #vacuum_cost_page_dirty =3D 20 # 0-10000 credits #vacuum_cost_limit =3D 200 # 1-10000 credits #bgwriter_delay =3D 200ms # 10-10000ms between rounds #bgwriter_lru_maxpages =3D 100 # 0-1000 max buffers written/rou= nd #bgwriter_lru_multiplier =3D 2.0 # 0-10.0 multipler on buffers = scanned/round #effective_io_concurrency =3D 1 # 1-1000. 0 disables prefetching #--------------------------------------------------------------------------= ---- # WRITE AHEAD LOG #--------------------------------------------------------------------------= ---- #wal_level =3D minimal # minimal, archive, or hot_standby) #fsync =3D on # turns forced synchronization on or off #synchronous_commit =3D on # synchronization level; on, off, o= r local #wal_sync_method =3D fsync # the default is the first option #full_page_writes =3D on # recover from partial page writes wal_buffers =3D 32MB # min 32kB, -1 sets based on shared_buf= fers wal_writer_delay =3D 500ms # 1-10000 milliseconds #commit_delay =3D 0 # range 0-100000, in microseconds #commit_siblings =3D 5 # range 1-1000 checkpoint_segments =3D 64 # in logfile segments, min 1, 16MB = each #checkpoint_timeout =3D 5min # range 30s-1h checkpoint_completion_target =3D 0.9 # checkpoint target duratio= n, 0.0 - 1.0 #checkpoint_warning =3D 30s # 0 disables #archive_mode =3D off # allows archiving to be done #archive_command =3D '' # command to use to archive a logfile= segment #archive_timeout =3D 0 # force a logfile segment switch after= this #--------------------------------------------------------------------------= ---- # REPLICATION #--------------------------------------------------------------------------= ---- # - Master Server - #max_wal_senders =3D 0 # max number of walsender processes #wal_sender_delay =3D 1s # walsender cycle time, 1-10000 mill= iseconds #wal_keep_segments =3D 0 # in logfile segments, 16MB each; 0 = disables #vacuum_defer_cleanup_age =3D 0 # number of xacts by which clea= nup is delayed #replication_timeout =3D 60s # in milliseconds; 0 disables #synchronous_standby_names =3D '' # standby servers that provid= e sync rep # - Standby Servers - #hot_standby =3D off # "on" allows queries during recovery #max_standby_archive_delay =3D 30s # max delay before canceling = queries #max_standby_streaming_delay =3D 30s # max delay before cancelin= g queries #wal_receiver_status_interval =3D 10s # send replies at least th= is often #hot_standby_feedback =3D off # send info from standby to prev= ent #--------------------------------------------------------------------------= ---- # QUERY TUNING #--------------------------------------------------------------------------= ---- # - Planner Method Configuration - #enable_bitmapscan =3D on #enable_hashagg =3D on #enable_hashjoin =3D on #enable_indexscan =3D on #enable_material =3D on #enable_mergejoin =3D on #enable_nestloop =3D on #enable_seqscan =3D on #enable_sort =3D on #enable_tidscan =3D on # - Planner Cost Constants - #seq_page_cost =3D 1.0 # measured on an arbitrary scale #random_page_cost =3D 4.0 # same scale as above #cpu_tuple_cost =3D 0.01 # same scale as above cpu_index_tuple_cost =3D 0.0005 # same scale as above #cpu_operator_cost =3D 0.0025 # same scale as above effective_cache_size =3D 2GB # - Genetic Query Optimizer - #geqo =3D on #geqo_threshold =3D 12 #geqo_effort =3D 5 # range 1-10 #geqo_pool_size =3D 0 # selects default based on effort #geqo_generations =3D 0 # selects default based on effort #geqo_selection_bias =3D 2.0 # range 1.5-2.0 #geqo_seed =3D 0.0 # range 0.0-1.0 # - Other Planner Options - default_statistics_target =3D 1000 # range 1-10000 #constraint_exclusion =3D partition # on, off, or partition #cursor_tuple_fraction =3D 0.1 # range 0.0-1.0 #from_collapse_limit =3D 8 #join_collapse_limit =3D 8 # 1 disables collapsing of explicit #--------------------------------------------------------------------------= ---- # ERROR REPORTING AND LOGGING #--------------------------------------------------------------------------= ---- # - Where to Log - log_destination =3D 'stderr'=09 # This is used when logging to stderr: logging_collector =3D on=09 # These are only used if logging_collector is on: #log_directory =3D 'pg_log' # directory where log files are wr= itten, #log_filename =3D 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name p= attern, #log_file_mode =3D 0600 # creation mode for log files, #log_truncate_on_rotation =3D off #log_rotation_age =3D 1d=09 #log_rotation_size =3D 10MB=09=09 #syslog_facility =3D 'LOCAL0' #syslog_ident =3D 'postgres' #silent_mode =3D off # - When to Log - #client_min_messages =3D notice #log_min_messages =3D warning #log_min_error_statement =3D error #log_min_duration_statement =3D -1 # - What to Log - #debug_print_parse =3D off #debug_print_rewritten =3D off #debug_print_plan =3D off #debug_pretty_print =3D on #log_checkpoints =3D off #log_connections =3D off #log_disconnections =3D off #log_duration =3D off #log_error_verbosity =3D default # terse, default, or verbose m= essages #log_hostname =3D off log_line_prefix =3D '%t '=09 #log_lock_waits =3D off # log lock waits >=3D deadlock_timeout #log_statement =3D 'none' # none, ddl, mod, all #log_temp_files =3D -1=09 #log_timezone =3D '(defaults to server environment setting)' #--------------------------------------------------------------------------= ---- # RUNTIME STATISTICS #--------------------------------------------------------------------------= ---- # - Query/Index Statistics Collector - #track_activities =3D on #track_counts =3D on #track_functions =3D none # none, pl, all #track_activity_query_size =3D 1024=09=09=09=09=09=09=09=09=09=09=09=09 #update_process_title =3D on #stats_temp_directory =3D 'pg_stat_tmp' # - Statistics Monitoring - #log_parser_stats =3D off #log_planner_stats =3D off #log_executor_stats =3D off #log_statement_stats =3D off #--------------------------------------------------------------------------= ---- # AUTOVACUUM PARAMETERS #--------------------------------------------------------------------------= ---- autovacuum =3D on # Enable autovacuum subprocess? 'on' #log_autovacuum_min_duration =3D -1 #autovacuum_max_workers =3D 3=09 autovacuum_naptime =3D 60min # time between autovacuum runs #autovacuum_vacuum_threshold =3D 50 autovacuum_analyze_threshold =3D 1000=09 #autovacuum_vacuum_scale_factor =3D 0.2 # fraction of table size = before vacuum #autovacuum_analyze_scale_factor =3D 0.1 # fraction of table size= before analyze #autovacuum_freeze_max_age =3D 200000000 # maximum XID age before= forced vacuum #autovacuum_vacuum_cost_delay =3D 20ms # default vacuum cost dela= y for #autovacuum_vacuum_cost_limit =3D -1 # default vacuum cost limit= for #--------------------------------------------------------------------------= ---- # CLIENT CONNECTION DEFAULTS #--------------------------------------------------------------------------= ---- # - Statement Behavior - #search_path =3D '"$user",public' # schema names #default_tablespace =3D '' # a tablespace name, '' uses the de= fault #temp_tablespaces =3D '' # a list of tablespace names, '' uses #check_function_bodies =3D on #default_transaction_isolation =3D 'read committed' #default_transaction_read_only =3D off #default_transaction_deferrable =3D off #session_replication_role =3D 'origin' #statement_timeout =3D 0 # in milliseconds, 0 is disabled #vacuum_freeze_min_age =3D 50000000 #vacuum_freeze_table_age =3D 150000000 #bytea_output =3D 'hex' # hex, escape #xmlbinary =3D 'base64' #xmloption =3D 'content' # - Locale and Formatting - datestyle =3D 'iso, mdy' #intervalstyle =3D 'postgres' #timezone =3D '(defaults to server environment setting)' #timezone_abbreviations =3D 'Default'=20=20 #extra_float_digits =3D 0 # min -15, max 3 #client_encoding =3D sql_ascii # These settings are initialized by initdb, but they can be changed. lc_messages =3D 'English_United States.1252' # locale for system e= rror message lc_monetary =3D 'English_United States.1252' # locale for monetary= formatting lc_numeric =3D 'English_United States.1252' # locale for number fo= rmatting lc_time =3D 'English_United States.1252' # locale for time format= ting # default configuration for text search default_text_search_config =3D 'pg_catalog.english' # - Other Defaults - #dynamic_library_path =3D '$libdir' #local_preload_libraries =3D '' #--------------------------------------------------------------------------= ---- # LOCK MANAGEMENT #--------------------------------------------------------------------------= ---- #deadlock_timeout =3D 1s #max_locks_per_transaction =3D 64 # min 10 # lock table slots. #max_pred_locks_per_transaction =3D 64 # min 10 #--------------------------------------------------------------------------= ---- # VERSION/PLATFORM COMPATIBILITY #--------------------------------------------------------------------------= ---- # - Previous PostgreSQL Versions - #array_nulls =3D on #backslash_quote =3D safe_encoding # on, off, or safe_encoding #default_with_oids =3D off #escape_string_warning =3D on #lo_compat_privileges =3D off #quote_all_identifiers =3D off #sql_inheritance =3D on #standard_conforming_strings =3D on #synchronize_seqscans =3D on # - Other Platforms and Clients - #transform_null_equals =3D off #--------------------------------------------------------------------------= ---- # ERROR HANDLING #--------------------------------------------------------------------------= ---- #exit_on_error =3D off # terminate session on any error? #restart_after_crash =3D on # reinitialize after backend crash? #--------------------------------------------------------------------------= ---- # CUSTOMIZED OPTIONS #--------------------------------------------------------------------------= ---- #custom_variable_classes =3D '' # list of custom variable class= names ------=_NextPart_000_0007_01CDCC9D.52D05850 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ------=_NextPart_000_0007_01CDCC9D.52D05850--