pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres configuration for 8 CPUs, 6 GB RAM
5+ messages / 5 participants
[nested] [flat]
* Postgres configuration for 8 CPUs, 6 GB RAM
@ 2012-11-27 07:47 Syed Asif Tanveer <asif.tanveer@analytics.com.pk>
2012-11-27 13:43 ` Re: Postgres configuration for 8 CPUs, 6 GB RAM Heikki Linnakangas <hlinnakangas@vmware.com>
2012-11-27 13:56 ` Re: Postgres configuration for 8 CPUs, 6 GB RAM Andrew Dunstan <andrew@dunslane.net>
2012-11-27 19:53 ` Re: Postgres configuration for 8 CPUs, 6 GB RAM Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: Syed Asif Tanveer @ 2012-11-27 07:47 UTC (permalink / raw)
To: pgsql-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-performance
Attachments:
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
* Re: Postgres configuration for 8 CPUs, 6 GB RAM
2012-11-27 07:47 Postgres configuration for 8 CPUs, 6 GB RAM Syed Asif Tanveer <asif.tanveer@analytics.com.pk>
@ 2012-11-27 13:43 ` Heikki Linnakangas <hlinnakangas@vmware.com>
2 siblings, 0 replies; 5+ messages in thread
From: Heikki Linnakangas @ 2012-11-27 13:43 UTC (permalink / raw)
To: Syed Asif Tanveer <asif.tanveer@analytics.com.pk>; +Cc: pgsql-performance
On 27.11.2012 09:47, Syed Asif Tanveer wrote:
> 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.
The configuration looks OK to me at a quick glance. I'd suggest looking
at the access plans of the queries that are too slow (ie. EXPLAIN
ANALYZE). How low is "too low", and how fast do the queries need to be?
What kind of an I/O system does the server have? See also
https://wiki.postgresql.org/wiki/Slow_Query_Questions
- Heikki
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres configuration for 8 CPUs, 6 GB RAM
2012-11-27 07:47 Postgres configuration for 8 CPUs, 6 GB RAM Syed Asif Tanveer <asif.tanveer@analytics.com.pk>
@ 2012-11-27 13:56 ` Andrew Dunstan <andrew@dunslane.net>
2 siblings, 0 replies; 5+ messages in thread
From: Andrew Dunstan @ 2012-11-27 13:56 UTC (permalink / raw)
To: Syed Asif Tanveer <asif.tanveer@analytics.com.pk>; +Cc: pgsql-performance
On 11/27/2012 02:47 AM, Syed Asif Tanveer wrote:
>
> 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.
>
>
There is at least anecdotal evidence that Windows servers degrade when
shared_buffers is set above 512Mb. Personally, I would not recommend
using Windows for a high performance server.
Also, it makes no sense to have a lower setting for maintenance_work_mem
than for work_mem. You would normally expect maintenance_work_mem to be
higher - sometimes much higher.
Apart from that, it's going to be impossible to tell what your problem
is without seeing actual slow running queries and their corresponding
explain analyse output.
cheers
andrew
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres configuration for 8 CPUs, 6 GB RAM
2012-11-27 07:47 Postgres configuration for 8 CPUs, 6 GB RAM Syed Asif Tanveer <asif.tanveer@analytics.com.pk>
@ 2012-11-27 19:53 ` Scott Marlowe <scott.marlowe@gmail.com>
2012-11-28 00:29 ` Re: Postgres configuration for 8 CPUs, 6 GB RAM Dave Crooke <dcrooke@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Scott Marlowe @ 2012-11-27 19:53 UTC (permalink / raw)
To: Syed Asif Tanveer <asif.tanveer@analytics.com.pk>; +Cc: pgsql-performance
On Tue, Nov 27, 2012 at 12:47 AM, Syed Asif Tanveer
<asif.tanveer@analytics.com.pk> wrote:
> 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.
>
I notice that you've got autovac nap time of 60 minutes, so it's
possible you've managed to bloat your tables a fair bit. What do you
get running the queries from this page:
http://wiki.postgresql.org/wiki/Show_database_bloat
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgres configuration for 8 CPUs, 6 GB RAM
2012-11-27 07:47 Postgres configuration for 8 CPUs, 6 GB RAM Syed Asif Tanveer <asif.tanveer@analytics.com.pk>
2012-11-27 19:53 ` Re: Postgres configuration for 8 CPUs, 6 GB RAM Scott Marlowe <scott.marlowe@gmail.com>
@ 2012-11-28 00:29 ` Dave Crooke <dcrooke@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Dave Crooke @ 2012-11-28 00:29 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Syed Asif Tanveer <asif.tanveer@analytics.com.pk>; pgsql-performance
Asif:
1. 6GB is pretty small .... once you work through the issues, adding RAM
will probably be a good investment, depending on your time-working set
curve.
A quick rule of thumb is this:
- if your cache hit ratio is significantly larger than (cache size / db
size) then there is locality of reference among queries, and if the hit
ratio is less than high 90's percent, then there is a high probablility
that adding incremental RAM for caching by the OS and/or PG itself will
make things significantly better; this applies to both database-wide
averages and individual slow query types.
- Look for long-running queries spilling merges and sorts to disk; if these
are a concern then adding RAM and leaving it out of the buffer cache but
setting larger work_mem sizes will improve their performance
2. You also need to consider how many queries are running concurrently;
limiting the number of concurrent executions to a strict number e.g. by
placing the database behind a connection pooler. By avoiding contention for
disk head seeking
3. If I/O is a real bottleneck, especially random access, you might
consider more drives
4. If the data access is truly all over the place, or you have lots of
queries which touch large chucnks of the data, then depending on your
budget, a cheap high RAM machine built from a desktop motherboard which
will allow you have e.g. 128GB of RAM in low cost modules and thus have the
entire DB in RAM is definitely worth considering as a replica server on
which to offload some queries. I priced this out at around US$2000 here in
America using high quality parts.
These performance tweaks are all of course interrelated ... e.g. if the
access patterns are amenable to caching, then adding RAM will reduce I/O
load without any further changes, and item 3. may cease to be a problem.
Be careful of the bottleneck issue ... if you're a long way from the
performance you need, then fixing one issue will expose another etc. until
every part of the system is quick enough to keep up.
Don't forget that your time is worth money too, and throwing more hardware
at it is one of many viable strategies.
Cheers
Dave
On Tue, Nov 27, 2012 at 1:53 PM, Scott Marlowe <scott.marlowe@gmail.com>wrote:
> On Tue, Nov 27, 2012 at 12:47 AM, Syed Asif Tanveer
> <asif.tanveer@analytics.com.pk> wrote:
> > 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.
> >
>
> I notice that you've got autovac nap time of 60 minutes, so it's
> possible you've managed to bloat your tables a fair bit. What do you
> get running the queries from this page:
>
> http://wiki.postgresql.org/wiki/Show_database_bloat
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2012-11-28 00:29 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-11-27 07:47 Postgres configuration for 8 CPUs, 6 GB RAM Syed Asif Tanveer <asif.tanveer@analytics.com.pk>
2012-11-27 13:43 ` Heikki Linnakangas <hlinnakangas@vmware.com>
2012-11-27 13:56 ` Andrew Dunstan <andrew@dunslane.net>
2012-11-27 19:53 ` Scott Marlowe <scott.marlowe@gmail.com>
2012-11-28 00:29 ` Dave Crooke <dcrooke@gmail.com>
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