Re: PostgreSQL 9.3 abd CentOS 7 memory usage

Hi, as requested, here's the postgresql.conf, the out of memory logs are
long gone, the appeared at the beginning but stopped after we changed
postgres configuration (this however didn't change the fact the a lot of
memory is not used by postgres)

The system has 16 cores, 128GB of ram (32x4GB DDR2)

listen_addresses = '*'
port = 5432
max_connections = 1536
superuser_reserved_connections = 10
unix_socket_directories = '/tmp'
unix_socket_group = ''
unix_socket_permissions = 0777
bonjour = off
bonjour_name = ''
authentication_timeout = 1min
ssl = off
ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH'
ssl_renegotiation_limit = 512MB
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = ''
ssl_crl_file = ''
password_encryption = on
db_user_namespace = off
krb_server_keyfile = ''
krb_caseins_users = off
tcp_keepalives_idle = 0
tcp_keepalives_interval = 0
tcp_keepalives_count = 0
shared_buffers = 25GB
temp_buffers = 128MB
max_prepared_transactions = 1536
work_mem = 256MB
maintenance_work_mem = 256MB
max_stack_depth = 4MB
temp_file_limit = -1
max_files_per_process = 4000
shared_preload_libraries = ''
vacuum_cost_delay = 0
vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20
vacuum_cost_limit = 200
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2.0
effective_io_concurrency = 500
wal_level = minimal
fsync = off
synchronous_commit = off
wal_sync_method = fsync
full_page_writes = off
wal_buffers = -1
wal_writer_delay = 200ms
commit_delay = 0
commit_siblings = 5
checkpoint_segments = 30
checkpoint_timeout = 15min
checkpoint_completion_target = 0.5
checkpoint_warning = 30s
archive_mode = off
archive_command = ''
archive_timeout = 0
max_wal_senders = 0
wal_keep_segments = 0
wal_sender_timeout = 60s
synchronous_standby_names = ''
vacuum_defer_cleanup_age = 0
hot_standby = off
max_standby_archive_delay = 30s
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = off
wal_receiver_timeout = 60s
enable_bitmapscan = on
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on
enable_indexonlyscan = on
enable_material = on
enable_mergejoin = on
enable_nestloop = on
enable_seqscan = on
enable_sort = on
enable_tidscan = on
seq_page_cost = 1.0
random_page_cost = 4.0
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025
effective_cache_size = 40GB
geqo = on
geqo_threshold = 12
geqo_effort = 5
geqo_pool_size = 0
geqo_generations = 0
geqo_selection_bias = 2.0
geqo_seed = 0.0
default_statistics_target = 100
constraint_exclusion = partition
cursor_tuple_fraction = 0.1
from_collapse_limit = 8
join_collapse_limit = 8
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
event_source = 'PostgreSQL'
client_min_messages = notice
log_min_messages = warning
log_min_error_statement = error
log_min_duration_statement = -1
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
log_hostname = off
log_line_prefix = '%d-%h-%p-%u-%t-%i'
log_lock_waits = off
log_statement = 'none'
log_temp_files = -1
log_timezone = 'America/Caracas'
track_activities = on
track_counts = on
track_io_timing = off
track_functions = none
track_activity_query_size = 1024
update_process_title = on
stats_temp_directory = 'pg_stat_tmp'
log_parser_stats = off
log_planner_stats = off
log_executor_stats = off
log_statement_stats = off
autovacuum = on
log_autovacuum_min_duration = -1
autovacuum_max_workers = 30
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
autovacuum_freeze_max_age = 200000000
autovacuum_multixact_freeze_max_age = 400000000
autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = -1
search_path = '"$user",public'
default_tablespace = ''
temp_tablespaces = ''
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
lock_timeout = 0
vacuum_freeze_min_age = 50000000
vacuum_freeze_table_age = 150000000
vacuum_multixact_freeze_min_age = 5000000
vacuum_multixact_freeze_table_age = 150000000
bytea_output = 'hex'
xmlbinary = 'base64'
xmloption = 'content'
datestyle = 'iso, mdy'
intervalstyle = 'postgres'
timezone = 'America/Caracas'
timezone_abbreviations = 'Default'
extra_float_digits = 0
client_encoding = utf-8
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
dynamic_library_path = '$libdir'
local_preload_libraries = ''
deadlock_timeout = 1s
max_locks_per_transaction = 64
max_pred_locks_per_transaction = 128
array_nulls = on
backslash_quote = 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
transform_null_equals = off
exit_on_error = off
restart_after_crash = on

On Mon, Oct 13, 2014 at 10:20 AM, Jeff Frost <jeff(at)pgexperts(dot)com> wrote:

>
> On Oct 13, 2014, at 2:06 AM, Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk> wrote:
>
> >
> >> From: Elías David <elias(dot)moreno(dot)tec(at)gmail(dot)com>
> >> To: Jeff Frost <jeff(at)pgexperts(dot)com>
> >> Cc: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
> >> Sent: Monday, 13 October 2014, 0:55
> >> Subject: Re: [ADMIN] PostgreSQL 9.3 abd CentOS 7 memory usage
> >>
> >>
> >>
> >> That's negative, zone_reclaim_mode is disabled in this server
> >>
> >
> >
> > I guess posting a little more info from that server might give us more
> to go on. The output of "cat /proc/meminfo", "free -tm", "sysctl -a | grep
> 'vm.'", "ps -axo pid,user,args,pmem --sort pmem" and as the postgres user
> "ulimit -a" might help.
>
> postgresql.conf and the log entries showing the out of memory condition
> would also be helpful.
>
>

--
Elías David.