Thread: Postgres Performance Tuning

Postgres Performance Tuning

From

Adarsh Sharma

Date:

04 April 2011, 09:40:55

Dear all,

I have a Postgres database server with 16GB RAM.
Our application runs by making connections to Postgres Server from
different servers and selecting data from one table & insert into
remaining tables in a database.

Below is the no. of connections output :-

postgres=# select datname,numbackends from pg_stat_database;
      datname      | numbackends
-------------------+-------------
 template1         |           0
 template0         |           0
 postgres          |           3
 template_postgis  |           0
 pdc_uima_dummy    |         107
 pdc_uima_version3 |           1
 pdc_uima_olap     |           0
 pdc_uima_s9       |           3
 pdc_uima          |           1
(9 rows)

I am totally confused for setting configuration parameters in Postgres
Parameters :-

First of all, I research on some tuning parameters and set mu
postgresql.conf as:-

max_connections = 1000
shared_buffers = 4096MB
temp_buffers = 16MB
work_mem = 64MB
maintenance_work_mem = 128MB
wal_buffers = 32MB
checkpoint_segments = 3
random_page_cost = 2.0
effective_cache_size = 8192MB

Then I got some problems from Application Users that the Postgres Slows
down and free commands output is :-

[root@s8-mysd-2 ~]# free -g
             total       used       free     shared    buffers     cached
Mem:            15         15          0          0          0         14
-/+ buffers/cache:          0         14
Swap:           16          0         15
[root@s8-mysd-2 ~]# free
             total       used       free     shared    buffers     cached
Mem:      16299476   16202264      97212          0      58924   15231852
-/+ buffers/cache:     911488   15387988
Swap:     16787884     153136   16634748

I think there may be some problem in my Configuration parameters and
change it as :

max_connections = 700
shared_buffers = 4096MB
temp_buffers = 16MB
work_mem = 64MB
maintenance_work_mem = 128MB
wal_buffers = 32MB
checkpoint_segments = 32
random_page_cost = 2.0
effective_cache_size = 4096MB

but Still Postgres Server uses Swap Memory While SELECT & INSERT into
database tables.

Please check the attached postgresql.conf .

And also have some views on how to tune this server.

DO I need to Increase my RAM s.t I hit H/W limitation.



Thanks & best Regards,
Adarsh Sharma





# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# (The "=" is optional.)  Whitespace may be used.  Comments are introduced with
# "#" anywhere on a line.  The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal.  If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload".  Some
# parameters, which are marked below, require a server shutdown and restart to
# take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on".  Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units:  kB = kilobytes        Time units:  ms  = milliseconds
#                MB = megabytes                     s   = seconds
#                GB = gigabytes                     min = minutes
#                                                   h   = hours
#                                                   d   = days


#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

#data_directory = 'ConfigDir'        # use data in another directory
                    # (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf'    # host-based authentication file
                    # (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf'    # ident configuration file
                    # (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '(none)'        # write an extra PID file
                    # (change requires restart)


#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'        # what IP address(es) to listen on;
                    # comma-separated list of addresses;
                    # defaults to 'localhost', '*' = all
                    # (change requires restart)
port = 5432                # (change requires restart)
max_connections = 700            # (change requires restart)
# Note:  Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
#superuser_reserved_connections = 3    # (change requires restart)
#unix_socket_directory = ''        # (change requires restart)
#unix_socket_group = ''            # (change requires restart)
#unix_socket_permissions = 0777        # begin with 0 to use octal notation
                    # (change requires restart)
#bonjour_name = ''            # defaults to the computer name
                    # (change requires restart)

# - Security and Authentication -

#authentication_timeout = 1min        # 1s-600s
#ssl = off                # (change requires restart)
#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH'    # allowed SSL ciphers
                    # (change requires restart)
#password_encryption = on
#db_user_namespace = off

# Kerberos and GSSAPI
#krb_server_keyfile = ''
#krb_srvname = 'postgres'        # (Kerberos only)
#krb_caseins_users = off

# - TCP Keepalives -
# see "man 7 tcp" for details

#tcp_keepalives_idle = 0        # TCP_KEEPIDLE, in seconds;
                    # 0 selects the system default
#tcp_keepalives_interval = 0        # TCP_KEEPINTVL, in seconds;
                    # 0 selects the system default
#tcp_keepalives_count = 0        # TCP_KEEPCNT;
                    # 0 selects the system default


#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -

#shared_buffers = 2048MB            # min 128kB
shared_buffers = 4096MB
                    # (change requires restart)
temp_buffers = 16MB            # min 800kB
#max_prepared_transactions = 0        # zero disables the feature
                    # (change requires restart)
# Note:  Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
#work_mem = 8MB                # min 64kB
work_mem = 64MB
#maintenance_work_mem = 16MB        # min 1MB
maintenance_work_mem = 128MB
#max_stack_depth = 2MB            # min 100kB

# - Kernel Resource Usage -

#max_files_per_process = 1000        # min 25
                    # (change requires restart)
#shared_preload_libraries = ''        # (change requires restart)

# - Cost-Based Vacuum Delay -

#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

# - Background Writer -

#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

# - Asynchronous Behavior -

#effective_io_concurrency = 1        # 1-1000. 0 disables prefetching


#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------

# - Settings -

fsync = true                # turns forced synchronization on or off
#synchronous_commit = on        # immediate fsync at commit
#wal_sync_method = fsync        # the default is the first option
                    # supported by the operating system:
                    #   open_datasync
                    #   fdatasync
                    #   fsync
                    #   fsync_writethrough
                    #   open_sync
#full_page_writes = on            # recover from partial page writes
#wal_buffers = 128kB            # min 32kB
wal_buffers = 32MB
                    # (change requires restart)
#wal_writer_delay = 200ms        # 1-10000 milliseconds

#commit_delay = 0            # range 0-100000, in microseconds
#commit_siblings = 5            # range 1-1000

# - Checkpoints -

checkpoint_segments = 3        # in logfile segments, min 1, 16MB each

#checkpoint_segments = 32

checkpoint_timeout = 5min        # range 30s-1h
#checkpoint_timeout = 20min
#checkpoint_completion_target = 0.5    # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s        # 0 disables

# - Archiving -

#archive_mode = off        # allows archiving to be done
                # (change requires restart)
#archive_command = ''        # command to use to archive a logfile segment
#archive_timeout = 0        # force a logfile segment switch after this
                # number of seconds; 0 disables


#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------

# - Planner Method Configuration -

#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = 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
random_page_cost = 2.0
#cpu_tuple_cost = 0.01            # same scale as above
#cpu_index_tuple_cost = 0.005        # same scale as above
#cpu_operator_cost = 0.0025        # same scale as above

effective_cache_size = 4096MB

# - 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

# - Other Planner Options -

#default_statistics_target = 100    # 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
                    # JOIN clauses


#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

log_destination = 'stderr'        # Valid values are combinations of
                    # stderr, csvlog, syslog and eventlog,
                    # depending on platform.  csvlog
                    # requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on        # Enable capturing of stderr and csvlog
                    # into log files. Required to be on for
                    # csvlogs.
                    # (change requires restart)

# These are only used if logging_collector is on:
log_directory = '/hdd3-1/pg_log'        # directory where log files are written,
                    # can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'    # log file name pattern,
                    # can include strftime() escapes
#log_truncate_on_rotation = off        # If on, an existing log file of the
                    # same name as the new log file will be
                    # truncated rather than appended to.
                    # But such truncation only occurs on
                    # time-driven rotation, not on restarts
                    # or size-driven rotation.  Default is
                    # off, meaning append to existing files
                    # in all cases.
#log_rotation_age = 1d            # Automatic rotation of logfiles will
                    # happen after that time.  0 disables.
#log_rotation_size = 10MB        # Automatic rotation of logfiles will
                    # happen after that much log output.
                    # 0 disables.

# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

#silent_mode = off            # Run server silently.
                    # DO NOT USE without syslog or
                    # logging_collector
                    # (change requires restart)


# - When to Log -

client_min_messages = notice        # values in order of decreasing detail:
                    #   debug5
                    #   debug4
                    #   debug3
                    #   debug2
                    #   debug1
                    #   log
                    #   notice
                    #   warning
                    #   error

#log_min_messages = warning        # values in order of decreasing detail:
                    #   debug5
                    #   debug4
                    #   debug3
                    #   debug2
                    #   debug1
                    #   info
                    #   notice
                    #   warning
                    #   error
                    #   log
                    #   fatal
                    #   panic

#log_error_verbosity = default        # terse, default, or verbose messages

#log_min_error_statement = error    # values in order of decreasing detail:
                     #   debug5
                    #   debug4
                    #   debug3
                    #   debug2
                    #   debug1
                     #   info
                    #   notice
                    #   warning
                    #   error
                    #   log
                    #   fatal
                    #   panic (effectively off)

#log_min_duration_statement = -1    # -1 is disabled, 0 logs all statements
                    # and their durations, > 0 logs only
                    # statements running at least this number
                    # of milliseconds


# - What to Log -

#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
log_connections = on
log_disconnections = on
log_duration = on
#log_hostname = off
#log_line_prefix = '%t'            # special values:
                    #   %u = user name
                    #   %d = database name
                    #   %r = remote host and port
                    #   %h = remote host
                    #   %p = process ID
                    #   %t = timestamp without milliseconds
                    #   %m = timestamp with milliseconds
                    #   %i = command tag
                    #   %c = session ID
                    #   %l = session line number
                    #   %s = session start timestamp
                    #   %v = virtual transaction ID
                    #   %x = transaction ID (0 if none)
                    #   %q = stop here in non-session
                    #        processes
                    #   %% = '%'
                    # e.g. '<%u%%%d> '
#log_lock_waits = off            # log lock waits >= deadlock_timeout
log_statement = 'all'            # none, ddl, mod, all
#log_temp_files = -1            # log temporary files equal or larger
                    # than the specified size in kilobytes;
                    # -1 disables, 0 logs all temp files
#log_timezone = unknown            # actually, defaults to TZ 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'
                    # requires track_counts to also be on.
#log_autovacuum_min_duration = -1    # -1 disables, 0 logs all actions and
                    # their durations, > 0 logs only
                    # actions running at least this number
                    # of milliseconds.
#autovacuum_max_workers = 3        # max number of autovacuum subprocesses
#autovacuum_naptime = 1min        # time between autovacuum runs
#autovacuum_vacuum_threshold = 50    # min number of row updates before
                    # vacuum
#autovacuum_analyze_threshold = 50    # min number of row updates before
                    # analyze
#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
                    # (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for
                    # autovacuum, in milliseconds;
                    # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1    # default vacuum cost limit for
                    # autovacuum, -1 means use
                    # vacuum_cost_limit


#------------------------------------------------------------------------------
# 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
                    # only default tablespace
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#session_replication_role = 'origin'
#statement_timeout = 0            # in milliseconds, 0 is disabled
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
#xmlbinary = 'base64'
#xmloption = 'content'

# - Locale and Formatting -

datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
#timezone = unknown            # actually, defaults to TZ environment
                    # setting
#timezone_abbreviations = 'Default'     # Select the set of available time zone
                    # abbreviations.  Currently, there are
                    #   Default
                    #   Australia
                    #   India
                    # You can create your own file in
                    # share/timezonesets/.
#extra_float_digits = 0            # min -15, max 2
#client_encoding = sql_ascii        # actually, defaults to database
                    # encoding

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'C'            # locale for system error message
                    # strings
lc_monetary = 'C'            # locale for monetary formatting
lc_numeric = 'C'            # locale for number formatting
lc_time = 'C'                # 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
                    # (change requires restart)
# Note:  Each lock table slot uses ~270 bytes of shared memory, and there are
# max_locks_per_transaction * (max_connections + max_prepared_transactions)
# lock table slots.


#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------

# - Previous PostgreSQL Versions -

#add_missing_from = off
#array_nulls = on
#backslash_quote = safe_encoding    # on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = on
#regex_flavor = advanced        # advanced, extended, or basic
#sql_inheritance = on
#standard_conforming_strings = off
#synchronize_seqscans = on

# - Other Platforms and Clients -

#transform_null_equals = off


#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

#custom_variable_classes = ''        # list of custom variable class names

Re: Postgres Performance Tuning

From

tv@fuzzy.cz

Date:

04 April 2011, 10:28:18

> max_connections = 700
> shared_buffers = 4096MB
> temp_buffers = 16MB
> work_mem = 64MB
> maintenance_work_mem = 128MB
> wal_buffers = 32MB
> checkpoint_segments = 32
> random_page_cost = 2.0
> effective_cache_size = 4096MB

First of all, there's no reason to increase wal_buffers above 32MB. AFAIK
the largest sensible value is 16MB - I doubt increasing it further will
improve performance.

Second - effective_cache_size is just a hint how much memory is used by
the operating system for filesystem cache. So this does not influence
amount of allocated memory in any way.

> but Still Postgres Server uses Swap Memory While SELECT & INSERT into
> database tables.

Are you sure it's PostgreSQL. What else is running on the box? Have you
analyzed why the SQL queries are slow (using EXPLAIN)?

regards
Tomas

Re: Postgres Performance Tuning

From

Adarsh Sharma

Date:

04 April 2011, 10:39:42

tv@fuzzy.cz wrote:

max_connections = 700
shared_buffers = 4096MB
temp_buffers = 16MB
work_mem = 64MB
maintenance_work_mem = 128MB
wal_buffers = 32MB
checkpoint_segments = 32
random_page_cost = 2.0
effective_cache_size = 4096MB

First of all, there's no reason to increase wal_buffers above 32MB. AFAIK
the largest sensible value is 16MB - I doubt increasing it further will
improve performance.

Second - effective_cache_size is just a hint how much memory is used by
the operating system for filesystem cache. So this does not influence
amount of allocated memory in any way.

but Still Postgres Server uses Swap Memory While SELECT & INSERT into
database tables.

Are you sure it's PostgreSQL. What else is running on the box? Have you
analyzed why the SQL queries are slow (using EXPLAIN)?

Thanks , Below is my action points :-

max_connections = 300 ( I don't think that application uses more than 300 connections )
shared_buffers = 4096MB
temp_buffers = 16MB
work_mem = 64MB
maintenance_work_mem = 128MB
wal_buffers = 16MB ( As per U'r suggestions )
checkpoint_segments = 32
random_page_cost = 2.0
effective_cache_size = 8192MB ( Recommended 50% of RAM )

My Shared Memory Variables are as:-

[root@s8-mysd-2 ~]# cat /proc/sys/kernel/shmmax

6442450944

[root@s8-mysd-2 ~]# cat /proc/sys/kernel/shmall

6442450944

[root@s8-mysd-2 ~]

Please let me know if any parameter need some change.

As now I am going change my parameters as per the below link :-

http://airumman.blogspot.com/2011/03/postgresql-parameters-for-new-dedicated.html

But one thing I am not able to understand is :-

Start the server and find out how much memory is still available for the OS filesystem cache

U'r absolutely right I am also researching on the explain of all select statements and i find one reason of poor indexing on TEXT columns.

Thanks & best Regards,
Adarsh Sharma

regards
Tomas

Re: Postgres Performance Tuning

From

Scott Marlowe

Date:

04 April 2011, 10:44:07

On Mon, Apr 4, 2011 at 3:40 AM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:
> Dear all,
>
> I have a Postgres database server with 16GB RAM.
> Our application runs by making connections to Postgres Server from different
> servers and selecting data from one table & insert into remaining tables in
> a database.
>
> Below is the no. of connections output :-
>
> postgres=# select datname,numbackends from pg_stat_database;
>     datname      | numbackends
> -------------------+-------------
> template1         |           0
> template0         |           0
> postgres          |           3
> template_postgis  |           0
> pdc_uima_dummy    |         107
> pdc_uima_version3 |           1
> pdc_uima_olap     |           0
> pdc_uima_s9       |           3
> pdc_uima          |           1
> (9 rows)
>
> I am totally confused for setting configuration parameters in Postgres
> Parameters :-
>
> First of all, I research on some tuning parameters and set mu
> postgresql.conf as:-
>
> max_connections = 1000

That's a little high.

> shared_buffers = 4096MB
> work_mem = 64MB

That's way high.  Work mem is PER SORT as well as PER CONNECTION.
1000 connections with 2 sorts each = 128,000MB.

> [root@s8-mysd-2 ~]# free              total       used       free     shared
>    buffers     cached
> Mem:      16299476   16202264      97212          0      58924   15231852
> -/+ buffers/cache:     911488   15387988
> Swap:     16787884     153136   16634748

There is nothing wrong here.  You're using 153M out of 16G swap.  15.x
Gig is shared buffers.  If your system is slow, it's not because it's
running out of memory or using too much swap.

>
> I think there may be some problem in my Configuration parameters and change
> it as :

Don't just guess and hope for the best.  Examine your system to
determine where it's having issues.  Use
vmstat 10
iostat -xd 10
top
htop

and so on to see where your bottleneck is.  CPU?  Kernel wait?  IO wait? etc.

log long running queries.  Use pgfouine to examine your queries.

Re: Postgres Performance Tuning

From

Sethu Prasad

Date:

04 April 2011, 10:52:55

Also you can try to take the help of pgtune before hand.

pgfoundry.org/projects/pgtune/

On Mon, Apr 4, 2011 at 12:43 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

On Mon, Apr 4, 2011 at 3:40 AM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:
> Dear all,
>
> I have a Postgres database server with 16GB RAM.
> Our application runs by making connections to Postgres Server from different
> servers and selecting data from one table & insert into remaining tables in
> a database.
>
> Below is the no. of connections output :-
>
> postgres=# select datname,numbackends from pg_stat_database;
> datname | numbackends
> -------------------+-------------
> template1 | 0
> template0 | 0
> postgres | 3
> template_postgis | 0
> pdc_uima_dummy | 107
> pdc_uima_version3 | 1
> pdc_uima_olap | 0
> pdc_uima_s9 | 3
> pdc_uima | 1
> (9 rows)
>
> I am totally confused for setting configuration parameters in Postgres
> Parameters :-
>
> First of all, I research on some tuning parameters and set mu
> postgresql.conf as:-
>
> max_connections = 1000

That's a little high.

> shared_buffers = 4096MB
> work_mem = 64MB

That's way high. Work mem is PER SORT as well as PER CONNECTION.
1000 connections with 2 sorts each = 128,000MB.

> [root@s8-mysd-2 ~]# free total used free shared
> buffers cached
> Mem: 16299476 16202264 97212 0 58924 15231852
> -/+ buffers/cache: 911488 15387988
> Swap: 16787884 153136 16634748

There is nothing wrong here. You're using 153M out of 16G swap. 15.x
Gig is shared buffers. If your system is slow, it's not because it's
running out of memory or using too much swap.

>
> I think there may be some problem in my Configuration parameters and change
> it as :

Don't just guess and hope for the best. Examine your system to
determine where it's having issues. Use
vmstat 10
iostat -xd 10
top
htop

and so on to see where your bottleneck is. CPU? Kernel wait? IO wait? etc.

log long running queries. Use pgfouine to examine your queries.

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: Postgres Performance Tuning

From

Scott Marlowe

Date:

04 April 2011, 10:54:54

On Mon, Apr 4, 2011 at 4:43 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>
>> [root@s8-mysd-2 ~]# free              total       used       free     shared
>>    buffers     cached
>> Mem:      16299476   16202264      97212          0      58924   15231852
>> -/+ buffers/cache:     911488   15387988
>> Swap:     16787884     153136   16634748
>
> There is nothing wrong here.  You're using 153M out of 16G swap.  15.x
> Gig is shared buffers.  If your system is slow, it's not because it's
> running out of memory or using too much swap.

Sorry that's 15.xG is system cache, not shared buffers.  Anyway, still
not a problem.

Re: Postgres Performance Tuning

From

Raghavendra

Date:

04 April 2011, 11:03:10

Adarsh,

What is the Size of Database?

Best Regards,
Raghavendra
EnterpriseDB Corporation

On Mon, Apr 4, 2011 at 4:24 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

On Mon, Apr 4, 2011 at 4:43 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>
>> [root@s8-mysd-2 ~]# free total used free shared
>> buffers cached
>> Mem: 16299476 16202264 97212 0 58924 15231852
>> -/+ buffers/cache: 911488 15387988
>> Swap: 16787884 153136 16634748
>
> There is nothing wrong here. You're using 153M out of 16G swap. 15.x
> Gig is shared buffers. If your system is slow, it's not because it's
> running out of memory or using too much swap.

Sorry that's 15.xG is system cache, not shared buffers. Anyway, still
not a problem.

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: Postgres Performance Tuning

From

Adarsh Sharma

Date:

04 April 2011, 11:34:39

My database size is :-
postgres=# select pg_size_pretty(pg_database_size('pdc_uima_dummy'));
pg_size_pretty
----------------
49 GB
(1 row)

I have a doubt regarding postgres Memory Usage :-

Say my Application makes Connection to Database Server ( *.*.*.106) from (*.*.*.111, *.*.*.113, *.*.*.114) Servers and I check the top command as :-- Say it makes 100 Connections

top - 17:01:02 up 5:39, 4 users, load average: 0.00, 0.00, 0.00
Tasks: 170 total,   1 running, 169 sleeping,   0 stopped,   0 zombie
Cpu(s): 0.0% us, 0.2% sy, 0.0% ni, 99.6% id, 0.1% wa, 0.0% hi, 0.0% si, 0.0% st
Mem: 16299476k total, 16198784k used,   100692k free,    73776k buffers
Swap: 16787884k total,   148176k used, 16639708k free, 15585396k cached

PID USER      PR NI VIRT RES SHR S %CPU %MEM    TIME+ COMMAND
3401 postgres 20   0 4288m 3.3g 3.3g S    0 21.1   0:24.73 postgres
3397 postgres 20   0 4286m 119m 119m S    0 0.8   0:00.36 postgres
4083 postgres 20   0 4303m 104m 101m S    0 0.7   0:07.68 postgres
3402 postgres 20   0 4288m 33m 32m S    0 0.2   0:03.67 postgres
4082 postgres 20   0 4301m 27m 25m S    0 0.2   0:00.85 postgres
4748 postgres 20   0 4290m 5160 3700 S    0 0.0   0:00.00 postgres
4173 root      20   0 12340 3028 1280 S    0 0.0   0:00.12 bash
4084 postgres 20   0 4290m 2952 1736 S    0 0.0   0:00.00 postgres
4612 root      20   0 12340 2920 1276 S    0 0.0   0:00.06 bash
4681 root      20   0 12340 2920 1276 S    0 0.0   0:00.05 bash
4550 root      20   0 12208 2884 1260 S    0 0.0   0:00.08 bash
4547 root      20   0 63580 2780 2204 S

and free command says :--
[root@s8-mysd-2 8.4SS]# free -g
             total       used       free     shared    buffers     cached
Mem:            15         15          0          0          0         14
-/+ buffers/cache:          0         15
Swap:           16          0         15
[root@s8-mysd-2 8.4SS]#

Now when my job finishes and I close the Connections from 2 Servers , the top & free output remains the same :-

I don't know What is the reason behind this as I have only 3 Connections from the below command :

postgres=# select datname, client_addr,current_query from pg_stat_activity;
    datname     | client_addr |                          current_query
----------------+---------------+------------------------------------------------------------------
postgres       |               | select datname, client_addr,current_query from pg_stat_activity;
postgres       | 192.168.0.208 | <IDLE>
pdc_uima_s9    | 192.168.0.208 | <IDLE>
pdc_uima_s9    | 192.168.0.208 | <IDLE>
pdc_uima_dummy | 192.168.0.208 | <IDLE>
pdc_uima_dummy | 192.168.1.102 | <IDLE>
pdc_uima_dummy | 192.168.1.102 | <IDLE>
pdc_uima_dummy | 192.168.1.102 | <IDLE>
(8 rows)

PLease help me to understand how much memory does 1 Connection Uses and how to use Server parameters accordingly.

Thanks & best Regards,
Adarsh Sharma

Raghavendra wrote:

Adarsh,

What is the Size of Database?

Best Regards,
Raghavendra
EnterpriseDB Corporation

On Mon, Apr 4, 2011 at 4:24 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Mon, Apr 4, 2011 at 4:43 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>
>> [root@s8-mysd-2 ~]# free total used free shared
>> buffers cached
>> Mem: 16299476 16202264 97212 0 58924 15231852
>> -/+ buffers/cache: 911488 15387988
>> Swap: 16787884 153136 16634748
>
> There is nothing wrong here. You're using 153M out of 16G swap. 15.x
> Gig is shared buffers. If your system is slow, it's not because it's
> running out of memory or using too much swap.

Sorry that's 15.xG is system cache, not shared buffers. Anyway, still
not a problem.

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: Postgres Performance Tuning

From

Scott Marlowe

Date:

04 April 2011, 11:43:18

On Mon, Apr 4, 2011 at 5:34 AM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:
> Mem:  16299476k total, 16198784k used,   100692k free,    73776k buffers
> Swap: 16787884k total,   148176k used, 16639708k free, 15585396k cached
>
>   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+
> COMMAND
>  3401 postgres  20   0 4288m 3.3g 3.3g S    0 21.1   0:24.73
> postgres
>  3397 postgres  20   0 4286m 119m 119m S    0  0.8   0:00.36
> postgres
> PLease help me to understand how much memory does 1 Connection Uses and how
> to use Server parameters accordingly.

OK, first, see the 15585396k cached?  That's how much memory your OS
is using to cache file systems etc.  Basically that's memory not being
used by anything else right now, so the OS borrows it and uses it for
caching.

Next, VIRT is how much memory your process would need to load every
lib it might need but may not be using now, plus all the shared memory
it might need, plus it's own space etc.  It's not memory in use, it's
memory that might under the worst circumstances, be used by that one
process.  RES is the amount of memory the process IS actually
touching, including shared memory that other processes may be sharing.
 Finally, SHR is the amount of shared memory the process is touching.
so, taking your biggest process, it is linked to enough libraries and
shared memory and it's own private memory to add up to 4288Meg.  It is
currently actually touching 3.3G.  Of that 3.3G it is touching 3.3G is
shared with other processes.  So, the difference between RES and SHR
is 0, so the delta, or extra memory it's using besides shared memory
is ZERO (or very close to it, probably dozens or fewer of megabytes).

So, you're NOT running out of memory.  Remember when I mentioned
iostat, vmstat, etc up above?  Have you run any of those?

Re: Postgres Performance Tuning

From

Adarsh Sharma

Date:

04 April 2011, 11:51:22

Thanks Scott :

My iostat package is not installed but have a look on below output:

[root@s8-mysd-2 8.4SS]# vmstat 10
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b   swpd   free   buff cache   si   so    bi    bo   in    cs us sy id wa st
1 0 147664 93920 72332 15580748    0    1   113   170   47   177 6 1 92 1 0
0 0 147664 94020 72348 15580748    0    0     0     4 993   565 0 0 100 0 0
0 0 147664 93896 72364 15580748    0    0     0     5 993   571 0 0 100 0 0
0 0 147664 93524 72416 15580860    0    0     0   160 1015   591 0 0 100 0 0
0 0 147664 93524 72448 15580860    0    0     0     8 1019   553 0 0 100 0 0
0 0 147664 93648 72448 15580860    0    0     0     0 1019   555 0 0 100 0 0
0 0 147664 93648 72448 15580860    0    0     0     3 1023   560 0 0 100 0 0

[root@s8-mysd-2 8.4SS]# iostat
-bash: iostat: command not found
[root@s8-mysd-2 8.4SS]#

Best regards,
Adarsh

Scott Marlowe wrote:

On Mon, Apr 4, 2011 at 5:34 AM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:

Mem:  16299476k total, 16198784k used,   100692k free,    73776k buffers
Swap: 16787884k total,   148176k used, 16639708k free, 15585396k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+
COMMAND
 3401 postgres  20   0 4288m 3.3g 3.3g S    0 21.1   0:24.73
postgres
 3397 postgres  20   0 4286m 119m 119m S    0  0.8   0:00.36
postgres
PLease help me to understand how much memory does 1 Connection Uses and how
to use Server parameters accordingly.

OK, first, see the 15585396k cached?  That's how much memory your OS
is using to cache file systems etc.  Basically that's memory not being
used by anything else right now, so the OS borrows it and uses it for
caching.

Next, VIRT is how much memory your process would need to load every
lib it might need but may not be using now, plus all the shared memory
it might need, plus it's own space etc.  It's not memory in use, it's
memory that might under the worst circumstances, be used by that one
process.  RES is the amount of memory the process IS actually
touching, including shared memory that other processes may be sharing.Finally, SHR is the amount of shared memory the process is touching.
so, taking your biggest process, it is linked to enough libraries and
shared memory and it's own private memory to add up to 4288Meg.  It is
currently actually touching 3.3G.  Of that 3.3G it is touching 3.3G is
shared with other processes.  So, the difference between RES and SHR
is 0, so the delta, or extra memory it's using besides shared memory
is ZERO (or very close to it, probably dozens or fewer of megabytes).

So, you're NOT running out of memory.  Remember when I mentioned
iostat, vmstat, etc up above?  Have you run any of those?

Re: Postgres Performance Tuning

From

Achilleas Mantzios

Date:

04 April 2011, 12:14:24

You got to have something to compare against.
I would say, try to run some benchmarks (pgbench from contrib) and compare them
against a known good instance of postgresql, if you have access in such a machine.

That said, and forgive me if i sound a little "explicit" but if you dont know how to install iostat
then there are few chances that you understand unix/linux/bsd concepts properly
and therefore any efforts to just speed up postgresql in such an environment , at this point,
will not have the desired effect, because even if you manage to solve smth now,
tommorow you will still be in confusion about smth else that might arise.
So, i suggest:
1) try to get an understanding on how your favorite distribution works (read any relevant info, net, books, etc..)
2) Go and get the book "PostgreSQL 9.0 High Performance" by Greg Smith. It is a very good book
not only about postgresql but about the current state of systems performance as well.

Στις Monday 04 April 2011 14:51:13 ο/η Adarsh Sharma έγραψε:
>
> Thanks Scott :
>
> My iostat package is not installed but have a look on below output:
>
> [root@s8-mysd-2 8.4SS]# vmstat 10
> procs -----------memory---------- ---swap-- -----io---- --system--
> -----cpu------
>  r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy
> id wa st
>  1  0 147664  93920  72332 15580748    0    1   113   170   47   177  6
> 1 92  1  0
>  0  0 147664  94020  72348 15580748    0    0     0     4  993   565  0
> 0 100  0  0
>  0  0 147664  93896  72364 15580748    0    0     0     5  993   571  0
> 0 100  0  0
>  0  0 147664  93524  72416 15580860    0    0     0   160 1015   591  0
> 0 100  0  0
>  0  0 147664  93524  72448 15580860    0    0     0     8 1019   553  0
> 0 100  0  0
>  0  0 147664  93648  72448 15580860    0    0     0     0 1019   555  0
> 0 100  0  0
>  0  0 147664  93648  72448 15580860    0    0     0     3 1023   560  0
> 0 100  0  0
>
> [root@s8-mysd-2 8.4SS]# iostat
> -bash: iostat: command not found
> [root@s8-mysd-2 8.4SS]#
>
> Best regards,
> Adarsh
>
> Scott Marlowe wrote:
> > On Mon, Apr 4, 2011 at 5:34 AM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:
> >
> >> Mem:  16299476k total, 16198784k used,   100692k free,    73776k buffers
> >> Swap: 16787884k total,   148176k used, 16639708k free, 15585396k cached
> >>
> >>   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+
> >> COMMAND
> >>  3401 postgres  20   0 4288m 3.3g 3.3g S    0 21.1   0:24.73
> >> postgres
> >>  3397 postgres  20   0 4286m 119m 119m S    0  0.8   0:00.36
> >> postgres
> >> PLease help me to understand how much memory does 1 Connection Uses and how
> >> to use Server parameters accordingly.
> >>
> >
> > OK, first, see the 15585396k cached?  That's how much memory your OS
> > is using to cache file systems etc.  Basically that's memory not being
> > used by anything else right now, so the OS borrows it and uses it for
> > caching.
> >
> > Next, VIRT is how much memory your process would need to load every
> > lib it might need but may not be using now, plus all the shared memory
> > it might need, plus it's own space etc.  It's not memory in use, it's
> > memory that might under the worst circumstances, be used by that one
> > process.  RES is the amount of memory the process IS actually
> > touching, including shared memory that other processes may be sharing.
> >  Finally, SHR is the amount of shared memory the process is touching.
> > so, taking your biggest process, it is linked to enough libraries and
> > shared memory and it's own private memory to add up to 4288Meg.  It is
> > currently actually touching 3.3G.  Of that 3.3G it is touching 3.3G is
> > shared with other processes.  So, the difference between RES and SHR
> > is 0, so the delta, or extra memory it's using besides shared memory
> > is ZERO (or very close to it, probably dozens or fewer of megabytes).
> >
> > So, you're NOT running out of memory.  Remember when I mentioned
> > iostat, vmstat, etc up above?  Have you run any of those?
> >
>
>



--
Achilleas Mantzios

Re: Postgres Performance Tuning

From

Scott Marlowe

Date:

04 April 2011, 12:14:27

On Mon, Apr 4, 2011 at 5:51 AM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:
>
>
> Thanks Scott :
>
> My iostat package is not installed but have a look on below output:
>
> [root@s8-mysd-2 8.4SS]# vmstat 10
> procs -----------memory---------- ---swap-- -----io---- --system--
> -----cpu------
>  r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id
> wa st
>  1  0 147664  93920  72332 15580748    0    1   113   170   47   177  6  1
> 92  1  0
>  0  0 147664  94020  72348 15580748    0    0     0     4  993   565  0  0
> 100  0  0
>  0  0 147664  93896  72364 15580748    0    0     0     5  993   571  0  0
> 100  0  0
>  0  0 147664  93524  72416 15580860    0    0     0   160 1015   591  0  0
> 100  0  0
>  0  0 147664  93524  72448 15580860    0    0     0     8 1019   553  0  0
> 100  0  0
>  0  0 147664  93648  72448 15580860    0    0     0     0 1019   555  0  0
> 100  0  0
>  0  0 147664  93648  72448 15580860    0    0     0     3 1023   560  0  0
> 100  0  0

OK, right now your machine is at idle.  Run vmstat / iostat when it's
under load.  If the wa column stays low, then you're not IO bound but
more than likely CPU bound.

Re: Postgres Performance Tuning

From

tv@fuzzy.cz

Date:

04 April 2011, 12:28:23

>
>
> Thanks Scott :
>
> My iostat package is not installed but have a look on below output:
>
> [root@s8-mysd-2 8.4SS]# vmstat 10
> procs -----------memory---------- ---swap-- -----io---- --system--
> -----cpu------
>  r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy
> id wa st
>  1  0 147664  93920  72332 15580748    0    1   113   170   47   177  6
> 1 92  1  0
>  0  0 147664  94020  72348 15580748    0    0     0     4  993   565  0
> 0 100  0  0
>  0  0 147664  93896  72364 15580748    0    0     0     5  993   571  0
> 0 100  0  0
>  0  0 147664  93524  72416 15580860    0    0     0   160 1015   591  0
> 0 100  0  0
>  0  0 147664  93524  72448 15580860    0    0     0     8 1019   553  0
> 0 100  0  0
>  0  0 147664  93648  72448 15580860    0    0     0     0 1019   555  0
> 0 100  0  0
>  0  0 147664  93648  72448 15580860    0    0     0     3 1023   560  0
> 0 100  0  0

Is this from a busy or idle period? I guess it's from an idle one, because
the CPU is 100% idle and there's very little I/O activity. That's useless
- we need to see vmstat output from period when there's something wrong.

> [root@s8-mysd-2 8.4SS]# iostat
> -bash: iostat: command not found
> [root@s8-mysd-2 8.4SS]#

Then install it. Not sure what distro you use, but it's usually packed in
sysstat package.

Tomas

Re: Postgres Performance Tuning

From

Adarsh Sharma

Date:

04 April 2011, 12:34:17

Thank U all,

I know some things to work on & after some work & study on them , I will continue this discussion tomorrow .

Best Regards,
Adarsh

Raghavendra wrote:

Adarsh,

[root@s8-mysd-2 8.4SS]# iostat
-bash: iostat: command not found

/usr/bin/iostat

Our application runs by making connections to Postgres Server from different servers and selecting data from one table & insert into remaining tables in a database.

When you are doing bulk inserts you need to tune AUTOVACUUM parameters or Change the autovacuum settings for those tables doing bulk INSERTs. Insert's need analyze.

#autovacuum = on # Enable autovacuum subprocess? 'on'
   # requires track_counts to also be on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
   # their durations, > 0 logs only
   # actions running at least this number
   # of milliseconds.
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
   # vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
   # analyze
#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
   # (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
   # autovacuum, in milliseconds;
   # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
   # autovacuum, -1 means use
   # vacuum_cost_limit

These are all default AUTOVACUUM settings. If you are using PG 8.4 or above, try AUTOVACUUM settings on bulk insert tables for better performance. Also need to tune the 'autovacuum_naptime'

Eg:-
ALTER table <table name> SET (autovacuum_vacuum_threshold=xxxxx, autovacuum_analyze_threshold=xxxx);

wal_buffers //max is 16MB
checkpoint_segment /// Its very less in your setting
checkpoint_timeout
temp_buffer // If application is using temp tables

These parameter will also boost the performance.

Best Regards
Raghavendra
EnterpriseDB Corporation.
Scott Marlowe wrote:
On Mon, Apr 4, 2011 at 5:34 AM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote: 
Mem:  16299476k total, 16198784k used,   100692k free,    73776k buffers
Swap: 16787884k total,   148176k used, 16639708k free, 15585396k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+
COMMAND
 3401 postgres  20   0 4288m 3.3g 3.3g S    0 21.1   0:24.73
postgres
 3397 postgres  20   0 4286m 119m 119m S    0  0.8   0:00.36
postgres
PLease help me to understand how much memory does 1 Connection Uses and how
to use Server parameters accordingly.   
OK, first, see the 15585396k cached?  That's how much memory your OS
is using to cache file systems etc.  Basically that's memory not being
used by anything else right now, so the OS borrows it and uses it for
caching.

Next, VIRT is how much memory your process would need to load every
lib it might need but may not be using now, plus all the shared memory
it might need, plus it's own space etc.  It's not memory in use, it's
memory that might under the worst circumstances, be used by that one
process.  RES is the amount of memory the process IS actually
touching, including shared memory that other processes may be sharing.Finally, SHR is the amount of shared memory the process is touching.
so, taking your biggest process, it is linked to enough libraries and
shared memory and it's own private memory to add up to 4288Meg.  It is
currently actually touching 3.3G.  Of that 3.3G it is touching 3.3G is
shared with other processes.  So, the difference between RES and SHR
is 0, so the delta, or extra memory it's using besides shared memory
is ZERO (or very close to it, probably dozens or fewer of megabytes).

So, you're NOT running out of memory.  Remember when I mentioned
iostat, vmstat, etc up above?  Have you run any of those? 

Re: Postgres Performance Tuning

From

Raghavendra

Date:

04 April 2011, 12:38:06

Adarsh,

[root@s8-mysd-2 8.4SS]# iostat
-bash: iostat: command not found

/usr/bin/iostat

Our application runs by making connections to Postgres Server from different servers and selecting data from one table & insert into remaining tables in a database.

When you are doing bulk inserts you need to tune AUTOVACUUM parameters or Change the autovacuum settings for those tables doing bulk INSERTs. Insert's need analyze.

#autovacuum = on # Enable autovacuum subprocess? 'on'
   # requires track_counts to also be on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
   # their durations, > 0 logs only
   # actions running at least this number
   # of milliseconds.
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
   # vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
   # analyze
#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
   # (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
   # autovacuum, in milliseconds;
   # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
   # autovacuum, -1 means use
   # vacuum_cost_limit

These are all default AUTOVACUUM settings. If you are using PG 8.4 or above, try AUTOVACUUM settings on bulk insert tables for better performance. Also need to tune the 'autovacuum_naptime'

Eg:-

ALTER table <table name> SET (autovacuum_vacuum_threshold=xxxxx, autovacuum_analyze_threshold=xxxx);

wal_buffers //max is 16MB

checkpoint_segment /// Its very less in your setting

checkpoint_timeout

temp_buffer // If application is using temp tables

These parameter will also boost the performance.

Best Regards

Raghavendra

EnterpriseDB Corporation.

Scott Marlowe wrote:

On Mon, Apr 4, 2011 at 5:34 AM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:

Mem:  16299476k total, 16198784k used,   100692k free,    73776k buffers
Swap: 16787884k total,   148176k used, 16639708k free, 15585396k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+
COMMAND
 3401 postgres  20   0 4288m 3.3g 3.3g S    0 21.1   0:24.73
postgres
 3397 postgres  20   0 4286m 119m 119m S    0  0.8   0:00.36
postgres
PLease help me to understand how much memory does 1 Connection Uses and how
to use Server parameters accordingly.

OK, first, see the 15585396k cached?  That's how much memory your OS
is using to cache file systems etc.  Basically that's memory not being
used by anything else right now, so the OS borrows it and uses it for
caching.

Next, VIRT is how much memory your process would need to load every
lib it might need but may not be using now, plus all the shared memory
it might need, plus it's own space etc.  It's not memory in use, it's
memory that might under the worst circumstances, be used by that one
process.  RES is the amount of memory the process IS actually
touching, including shared memory that other processes may be sharing.Finally, SHR is the amount of shared memory the process is touching.
so, taking your biggest process, it is linked to enough libraries and
shared memory and it's own private memory to add up to 4288Meg.  It is
currently actually touching 3.3G.  Of that 3.3G it is touching 3.3G is
shared with other processes.  So, the difference between RES and SHR
is 0, so the delta, or extra memory it's using besides shared memory
is ZERO (or very close to it, probably dozens or fewer of megabytes).

So, you're NOT running out of memory.  Remember when I mentioned
iostat, vmstat, etc up above?  Have you run any of those?

Re: Postgres Performance Tuning

From

Scott Marlowe

Date:

04 April 2011, 16:23:00

Best of luck, the two standard links for this kind of problem are:

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
http://wiki.postgresql.org/wiki/SlowQueryQuestions

Note that in creating the information needed to report a problem you
may well wind up troubleshooting it and fixing it.  That's a good
thing :)

Re: Postgres Performance Tuning

From

Adarsh Sharma

Date:

05 April 2011, 07:33:27

Hi, Good Morning To All of You.

Yesterday I had some research on my problems. As Scott rightly suggest
me to have pre information before posting in the list, I aggreed to him.

Here is my first doubt , that I explain as:

My application makes several connections to Database Server & done their
work :

During this process have a look on below output of free command :

[root@s8-mysd-2 ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         15917      15826         90          0        101      15013
-/+ buffers/cache:        711      15205
Swap:        16394        143      16250

It means 15 GB memory is cached.

[root@s8-mysd-2 ~]#  cat /proc/meminfo
MemTotal:       16299476 kB
MemFree:           96268 kB
Buffers:          104388 kB
Cached:         15370008 kB
SwapCached:         3892 kB
Active:          6574788 kB
Inactive:        8951884 kB
Active(anon):    3909024 kB
Inactive(anon):   459720 kB
Active(file):    2665764 kB
Inactive(file):  8492164 kB
Unevictable:           0 kB
Mlocked:               0 kB
SwapTotal:      16787884 kB
SwapFree:       16640472 kB
Dirty:              1068 kB
Writeback:             0 kB
AnonPages:         48864 kB
Mapped:          4277000 kB
Slab:             481960 kB
SReclaimable:     466544 kB
SUnreclaim:        15416 kB
PageTables:        57860 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:    24904852 kB
Committed_AS:    5022172 kB
VmallocTotal:   34359738367 kB
VmallocUsed:      310088 kB
VmallocChunk:   34359422091 kB
HugePages_Total:      32
HugePages_Free:       32
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:        3776 kB
DirectMap2M:    16773120 kB
[root@s8-mysd-2 ~]#

Now Can I know why the cached memory is not freed after the connections
done their work & their is no more connections :

pdc_uima_dummy=# select datname,numbackends from pg_stat_database;
      datname      | numbackends
-------------------+-------------
 template1         |           0
 template0         |           0
 postgres          |           2
 template_postgis  |           0
 pdc_uima_dummy    |          11
 pdc_uima_version3 |           0
 pdc_uima_olap     |           0
 pdc_uima_s9       |           0
 pdc_uima          |           0
(9 rows)

Same output is when it has 100 connections.

Now I have to start more queries on Database Server and issue new
connections after some time. Why the cached memory is not freed.

Flushing the cache memory is needed & how it could use so much if I set

effective_cache_size = 4096 MB.

I think if i issue some new select queries on large set of data, it will
use Swap Memory & degrades Performance.

Please correct if I'm wrong.


Thanks & best Regards,
Adarsh Sharma














Raghavendra wrote:
> Adarsh,
>
>
>     [root@s8-mysd-2 8.4SS]# iostat
>     -bash: iostat: command not found
>
> /usr/bin/iostat
>
>     Our application runs by making connections to Postgres Server from
>     different servers and selecting data from one table & insert into
>     remaining tables in a database.
>
>
> When you are doing bulk inserts you need to tune AUTOVACUUM parameters
> or Change the autovacuum settings for those tables doing bulk INSERTs.
> Insert's need analyze.
>
>
>
>     #autovacuum = on                        # Enable autovacuum
>     subprocess?  'on'
>                                            # requires track_counts to
>     also be on.
>     #log_autovacuum_min_duration = -1       # -1 disables, 0 logs all
>     actions and
>                                            # their durations, > 0 logs
>     only
>                                            # actions running at least
>     this number
>                                            # of milliseconds.
>     #autovacuum_max_workers = 3             # max number of autovacuum
>     subprocesses
>     #autovacuum_naptime = 1min              # time between autovacuum runs
>     #autovacuum_vacuum_threshold = 50       # min number of row
>     updates before
>                                            # vacuum
>     #autovacuum_analyze_threshold = 50      # min number of row
>     updates before
>                                            # analyze
>     #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
>                                            # (change requires restart)
>     #autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost
>     delay for
>                                            # autovacuum, in milliseconds;
>                                            # -1 means use
>     vacuum_cost_delay
>     #autovacuum_vacuum_cost_limit = -1      # default vacuum cost
>     limit for
>                                            # autovacuum, -1 means use
>                                            # vacuum_cost_limit
>
>
> These are all default AUTOVACUUM settings. If you are using PG 8.4 or
> above, try AUTOVACUUM settings on bulk insert tables for better
> performance. Also need to tune the 'autovacuum_naptime'
>
> Eg:-
>  ALTER table <table name> SET (autovacuum_vacuum_threshold=xxxxx,
> autovacuum_analyze_threshold=xxxx);
>
> wal_buffers  //max is 16MB
> checkpoint_segment    /// Its very less in your setting
> checkpoint_timeout
> temp_buffer  // If application is using temp tables
>
>
> These parameter will also boost the performance.
>
> Best Regards
> Raghavendra
> EnterpriseDB Corporation.
>
>
>
>
>
>
>
>     Scott Marlowe wrote:
>>     On Mon, Apr 4, 2011 at 5:34 AM, Adarsh Sharma <adarsh.sharma@orkash.com> <mailto:adarsh.sharma@orkash.com>
wrote:
>>
>>>     Mem:  16299476k total, 16198784k used,   100692k free,    73776k buffers
>>>     Swap: 16787884k total,   148176k used, 16639708k free, 15585396k cached
>>>
>>>       PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+
>>>     COMMAND
>>>      3401 postgres  20   0 4288m 3.3g 3.3g S    0 21.1   0:24.73
>>>     postgres
>>>      3397 postgres  20   0 4286m 119m 119m S    0  0.8   0:00.36
>>>     postgres
>>>     PLease help me to understand how much memory does 1 Connection Uses and how
>>>     to use Server parameters accordingly.
>>>
>>     OK, first, see the 15585396k cached?  That's how much memory your OS
>>     is using to cache file systems etc.  Basically that's memory not being
>>     used by anything else right now, so the OS borrows it and uses it for
>>     caching.
>>
>>     Next, VIRT is how much memory your process would need to load every
>>     lib it might need but may not be using now, plus all the shared memory
>>     it might need, plus it's own space etc.  It's not memory in use, it's
>>     memory that might under the worst circumstances, be used by that one
>>     process.  RES is the amount of memory the process IS actually
>>     touching, including shared memory that other processes may be sharing.
>>      Finally, SHR is the amount of shared memory the process is touching.
>>     so, taking your biggest process, it is linked to enough libraries and
>>     shared memory and it's own private memory to add up to 4288Meg.  It is
>>     currently actually touching 3.3G.  Of that 3.3G it is touching 3.3G is
>>     shared with other processes.  So, the difference between RES and SHR
>>     is 0, so the delta, or extra memory it's using besides shared memory
>>     is ZERO (or very close to it, probably dozens or fewer of megabytes).
>>
>>     So, you're NOT running out of memory.  Remember when I mentioned
>>     iostat, vmstat, etc up above?  Have you run any of those?
>>
>
>

Re: Postgres Performance Tuning

From

Ákos Gábriel

Date:

05 April 2011, 12:47:25

On Apr 5, 2011, at 9:33 AM, Adarsh Sharma wrote:

> Now I have to start more queries on Database Server and issue new connections after some time. Why the cached memory
isnot freed. 

It's freed on-demand.

> Flushing the cache memory is needed & how it could use so much if I set

Why would forced flushing be needed? And why would it be useful? It is not.

> effective_cache_size = 4096 MB.

Watch the "cached" field of free's output and set effective_cache_size to that amount (given that your server is
runningpostgres only, has no major other tasks) 

> I think if i issue some new select queries on large set of data, it will use Swap Memory & degrades Performance.

Have you ever tried that? Will not.

> Please correct if I'm wrong.

You seem to know very little about Unix/Linux memory usage and how to interpret the tools' output.
Please read some (very basic) documentation for sysadmins regarding these subjects.
It will help you a lot to understand how things work.

--
Akos Gabriel
General Manager
Liferay Hungary Ltd.
Liferay Hungary Symposium, May 26, 2011 | Register today: http://www.liferay.com/hungary2011

--
Üdvözlettel,
Gábriel Ákos

Re: Postgres Performance Tuning

From

Scott Marlowe

Date:

05 April 2011, 13:08:15

On Tue, Apr 5, 2011 at 1:33 AM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:
>
> [root@s8-mysd-2 ~]# free -m
>            total       used       free     shared    buffers     cached
> Mem:         15917      15826         90          0        101      15013
> -/+ buffers/cache:        711      15205
> Swap:        16394        143      16250
>
> It means 15 GB memory is cached.

Note that the kernel takes all otherwise unused memory and uses it for
cache.  If, at any time a process needs more memory, the kernel just
dumps some cached data and frees up the memory and hands it over, it's
all automatic.  As long as cache is large, things are OK.  You need to
be looking to see if you're IO bound or CPU bound first.  so, vmstat
(install the sysstat package) is the first thing to use.

Re: Postgres Performance Tuning

From

Adarsh Sharma

Date:

05 April 2011, 13:20:13

Scott Marlowe wrote:

On Tue, Apr 5, 2011 at 1:33 AM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:

[root@s8-mysd-2 ~]# free -m
           total       used       free     shared    buffers     cached
Mem:         15917      15826         90          0        101      15013
-/+ buffers/cache:        711      15205
Swap:        16394        143      16250

It means 15 GB memory is cached.

Note that the kernel takes all otherwise unused memory and uses it for
cache.  If, at any time a process needs more memory, the kernel just
dumps some cached data and frees up the memory and hands it over, it's
all automatic.  As long as cache is large, things are OK.  You need to
be looking to see if you're IO bound or CPU bound first.  so, vmstat
(install the sysstat package) is the first thing to use.

Thanks a lot , Scott. :-)

Best Regards , Adarsh

Re: Postgres Performance Tuning

From

Scott Marlowe

Date:

05 April 2011, 13:50:02

On Tue, Apr 5, 2011 at 7:20 AM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:
> Scott Marlowe wrote:
>
> On Tue, Apr 5, 2011 at 1:33 AM, Adarsh Sharma <adarsh.sharma@orkash.com>
> wrote:
>
>
> [root@s8-mysd-2 ~]# free -m
>            total       used       free     shared    buffers     cached
> Mem:         15917      15826         90          0        101      15013
> -/+ buffers/cache:        711      15205
> Swap:        16394        143      16250
>
> It means 15 GB memory is cached.
>
>
> Note that the kernel takes all otherwise unused memory and uses it for
> cache.  If, at any time a process needs more memory, the kernel just
> dumps some cached data and frees up the memory and hands it over, it's
> all automatic.  As long as cache is large, things are OK.  You need to
> be looking to see if you're IO bound or CPU bound first.  so, vmstat
> (install the sysstat package) is the first thing to use.

BTW, just remembered that vmstat is it's own package, it's iostat and
sar that are in sysstat.

If you install sysstat, enable stats collecting by editing the
/etc/default/sysstat file and changing the ENABLED="false" to
ENABLED="true" and restarting the service with sudo
/etc/init.d/sysstat restart

Re: Postgres Performance Tuning

From

Ákos Gábriel

Date:

06 April 2011, 11:30:09

On Apr 5, 2011, at 9:33 AM, Adarsh Sharma wrote:

> Now I have to start more queries on Database Server and issue new connections after some time. Why the cached memory
isnot freed. 

It's freed on-demand.

> Flushing the cache memory is needed & how it could use so much if I set

Why would forced flushing be needed? And why would it be useful? It is not.

> effective_cache_size = 4096 MB.

Watch the "cached" field of free's output and set effective_cache_size to that amount (given that your server is
runningpostgres only, has no major other tasks) 

> I think if i issue some new select queries on large set of data, it will use Swap Memory & degrades Performance.

Have you ever tried that? Will not.

> Please correct if I'm wrong.

You seem to know very little about Unix/Linux memory usage and how to interpret the tools' output.
Please read some (very basic) documentation for sysadmins regarding these subjects.
It will help you a lot to understand how things work.

--
Akos Gabriel
General Manager
Liferay Hungary Ltd.
Liferay Hungary Symposium, May 26, 2011 | Register today: http://www.liferay.com/hungary2011