Thread: PostgreSQL 9.3 abd CentOS 7 memory usage

PostgreSQL 9.3 abd CentOS 7 memory usage

From
Elías David
Date:

Hello all,

I have a question regarding the memory usage I'm seeing in one of my servers. This servers was recently installed using CentOS 7 and PostgreSQL 9.3

The server has 128GB of ram and we've configured postgresql.conf to use as much memory as it can (it's a dedicate postgres server after all)

What we're seeing is that even at high peaks of load the server doesn't consume more than 14GB, increasing the load doesn't increase ram usage equally. We find this odd since we've other servers that are CentOS 6.5 and postgres 9.3 and postgres 8.4, and all use all the ram available.

This particular server was a CentOS 6.5 and postgres 9.1 before and was able to use all 128GB of ram, now it doesn't for some reason.

I know that shared memory usage in postgres was changed on 9.3 from sys v to posix so changing things like shmmax and the like aren't needed anymore (although we've trying changing those to no effect)

We've even seen out of memory errors on postgres logs a few times when the server is just using something like 8GB out of 128GB of ram

Has anyone experienced a similar issue or have seen weird memory usage patrons with CentOS 7?

Re: PostgreSQL 9.3 abd CentOS 7 memory usage

From
Jeff Frost
Date:

On Oct 12, 2014, at 10:33 AM, Elías David <elias.moreno.tec@gmail.com> wrote:

I know that shared memory usage in postgres was changed on 9.3 from sys v to posix so changing things like shmmax and the like aren't needed anymore (although we've trying changing those to no effect)

We've even seen out of memory errors on postgres logs a few times when the server is just using something like 8GB out of 128GB of ram

Has anyone experienced a similar issue or have seen weird memory usage patrons with CentOS 7?


Any chance zone reclaim mode is enabled?

cat /proc/sys/vm/zone_reclaim_mode

Re: PostgreSQL 9.3 abd CentOS 7 memory usage

From
Elías David
Date:

That's negative, zone_reclaim_mode is disabled in this server

On Oct 12, 2014 1:26 PM, "Jeff Frost" <jeff@pgexperts.com> wrote:

On Oct 12, 2014, at 10:33 AM, Elías David <elias.moreno.tec@gmail.com> wrote:

I know that shared memory usage in postgres was changed on 9.3 from sys v to posix so changing things like shmmax and the like aren't needed anymore (although we've trying changing those to no effect)

We've even seen out of memory errors on postgres logs a few times when the server is just using something like 8GB out of 128GB of ram

Has anyone experienced a similar issue or have seen weird memory usage patrons with CentOS 7?


Any chance zone reclaim mode is enabled?

cat /proc/sys/vm/zone_reclaim_mode

Re: PostgreSQL 9.3 abd CentOS 7 memory usage

From
Glyn Astill
Date:
> From: Elías David <elias.moreno.tec@gmail.com>
>To: Jeff Frost <jeff@pgexperts.com>
>Cc: pgsql-admin <pgsql-admin@postgresql.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"
mighthelp. 


Re: PostgreSQL 9.3 abd CentOS 7 memory usage

From
Jeff Frost
Date:
On Oct 13, 2014, at 2:06 AM, Glyn Astill <glynastill@yahoo.co.uk> wrote:

>
>> From: Elías David <elias.moreno.tec@gmail.com>
>> To: Jeff Frost <jeff@pgexperts.com>
>> Cc: pgsql-admin <pgsql-admin@postgresql.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"
mighthelp. 

postgresql.conf and the log entries showing the out of memory condition would also be helpful.



Re: PostgreSQL 9.3 abd CentOS 7 memory usage

From
Elías David
Date:
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@pgexperts.com> wrote:

On Oct 13, 2014, at 2:06 AM, Glyn Astill <glynastill@yahoo.co.uk> wrote:

>
>> From: Elías David <elias.moreno.tec@gmail.com>
>> To: Jeff Frost <jeff@pgexperts.com>
>> Cc: pgsql-admin <pgsql-admin@postgresql.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.

Re: PostgreSQL 9.3 abd CentOS 7 memory usage

From
Jan Lentfer
Date:

Am 12. Oktober 2014 19:33:18 MESZ, schrieb "Elías David" <elias.moreno.tec@gmail.com>:
>Hello all,
>
>I have a question regarding the memory usage I'm seeing in one of my
>servers. This servers was recently installed using CentOS 7 and
>PostgreSQL
>9.3
>
>The server has 128GB of ram and we've configured postgresql.conf to use
>as
>much memory as it can (it's a dedicate postgres server after all)
>
>What we're seeing is that even at high peaks of load the server doesn't
>consume more than 14GB, increasing the load doesn't increase ram usage
>equally. We find this odd since we've other servers that are CentOS 6.5
>and
>postgres 9.3 and postgres 8.4, and all use all the ram available.
>
>This particular server was a CentOS 6.5 and postgres 9.1 before and was
>able to use all 128GB of ram, now it doesn't for some reason.
>
>I know that shared memory usage in postgres was changed on 9.3 from sys
>v
>to posix so changing things like shmmax and the like aren't needed
>anymore
>(although we've trying changing those to no effect)
>
>We've even seen out of memory errors on postgres logs a few times when
>the
>server is just using something like 8GB out of 128GB of ram
>
>Has anyone experienced a similar issue or have seen weird memory usage
>patrons with CentOS 7?

What's the on-disk size of your databases and can you estimate the actual working set? How long is this system up and
beingqueried? 
I have seen similar when upgrading from 8.4 to 9.3 on Solaris 10, Postgres running in zones. I could not yet
investigatefurther and performance is perfect still. 


Re: PostgreSQL 9.3 abd CentOS 7 memory usage

From
Jan Lentfer
Date:


Am 13.10.2014 um 20:12 schrieb Elías David:

>> What's the on-disk size of your databases and can you estimate the actual working set? How long is this system up and being queried?
>> I have seen similar when upgrading from 8.4 to 9.3 on Solaris 10, Postgres running in zones. I could not yet investigate further and performance is perfect still.

Ok, here it goes:

ccs1cl --> 41 MB
db_otrs_ub --> 14 MB
elmap --> 207 MB
elmap_int --> 182 MB
invent_equi --> 532 MB
invent_equi_fc --> 114 MB
jasper --> 13 MB
netasis --> 23 MB
otrs --> 361 MB
patint --> 615 MB
seg_alt --> 75 MB
seg_alt_par --> 75 MB

The average of transactions are around 85 per second and several of the databases (jasper, patint, elmap...) are accessed everyday, 365 days a year (it's part of an offering from the company that runs the services every single day of the year). Right now the system has been up for 17 days.


So, that sums up to 2,2GB of databases (is that the right unit? MB?) ... so even IF all of the data was read and stored in buffers that would not cause a significant amount of memory used data on a 128GB machine, When you look at "top" output, what the value for "cached" (5th line on the very right)?

Re: PostgreSQL 9.3 abd CentOS 7 memory usage

From
Elías David
Date:

Right now that value is:

6288536 cached Mem

I'm thinking the same about that the sum of the databases isn't enough to use all the memory, but I want to be sure

On Oct 13, 2014 2:15 PM, "Jan Lentfer" <Jan.Lentfer@web.de> wrote:


Am 13.10.2014 um 20:12 schrieb Elías David:

>> What's the on-disk size of your databases and can you estimate the actual working set? How long is this system up and being queried?
>> I have seen similar when upgrading from 8.4 to 9.3 on Solaris 10, Postgres running in zones. I could not yet investigate further and performance is perfect still.

Ok, here it goes:

ccs1cl --> 41 MB
db_otrs_ub --> 14 MB
elmap --> 207 MB
elmap_int --> 182 MB
invent_equi --> 532 MB
invent_equi_fc --> 114 MB
jasper --> 13 MB
netasis --> 23 MB
otrs --> 361 MB
patint --> 615 MB
seg_alt --> 75 MB
seg_alt_par --> 75 MB

The average of transactions are around 85 per second and several of the databases (jasper, patint, elmap...) are accessed everyday, 365 days a year (it's part of an offering from the company that runs the services every single day of the year). Right now the system has been up for 17 days.


So, that sums up to 2,2GB of databases (is that the right unit? MB?) ... so even IF all of the data was read and stored in buffers that would not cause a significant amount of memory used data on a 128GB machine, When you look at "top" output, what the value for "cached" (5th line on the very right)?