Thread: Help with postgresql memory issue
Hello All,
I'm new to postgres and it seems my server is unable to fork new
connections.
Here is the log:
LOG: could not fork new process for connection: Not enough space
LOG: could not fork new process for connection: Not enough space
TopMemoryContext: 84784 total in 8 blocks; 5584 free (10 chunks);
79200 used
Prepared Queries: 8192 total in 1 blocks; 1800 free (0 chunks);
6392 used
TopTransactionContext: 8192 total in 1 blocks; 7856 free (0
chunks); 336 used
Record information cache: 8192 total in 1 blocks; 1800 free (0
chunks); 6392 used
MessageContext: 8192 total in 1 blocks; 7128 free (0 chunks); 1064
used
smgr relation table: 8192 total in 1 blocks; 3840 free (0 chunks);
4352 used
TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0
chunks); 16 used
Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
PortalHeapMemory: 1024 total in 1 blocks; 976 free (0 chunks); 48
used
Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks); 4816
used
CacheMemoryContext: 405328 total in 19 blocks; 70888 free (1
chunks); 334440 used
unnamed prepared statement: 24576 total in 2 blocks; 7912 free (1
chunks); 16664 used
CachedPlan: 1024 total in 1 blocks; 904 free (0 chunks); 120 used
CachedPlanSource: 1024 total in 1 blocks; 840 free (0 chunks);
184 used
pg_ts_dict_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_language_name_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_ts_dict_dictname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_opfamily_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_opclass_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_ts_parser_prsname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_amop_fam_strat_index: 1024 total in 1 blocks; 128 free (0
chunks); 896 used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 240 free
(0 chunks); 784 used
pg_cast_source_target_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 280
free (0 chunks); 744 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 280 free
(0 chunks); 744 used
pg_ts_config_cfgname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_authid_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_ts_config_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_conversion_default_index: 1024 total in 1 blocks; 128 free (0
chunks); 896 used
pg_language_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_enum_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 192 free
(0 chunks); 832 used
pg_ts_parser_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_database_oid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 280 free
(0 chunks); 744 used
pg_class_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
pg_amproc_fam_proc_index: 1024 total in 1 blocks; 128 free (0
chunks); 896 used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 128 free
(0 chunks); 896 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 280 free
(0 chunks); 744 used
pg_authid_rolname_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 280
free (0 chunks); 744 used
pg_enum_typid_label_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_constraint_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_conversion_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_ts_template_tmplname_index: 1024 total in 1 blocks; 280 free
(0 chunks); 744 used
pg_ts_config_map_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_namespace_oid_index: 1024 total in 1 blocks; 304 free (0
chunks); 720 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_operator_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
pg_amop_opr_fam_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_proc_oid_index: 1024 total in 1 blocks; 344 free (0 chunks);
680 used
pg_opfamily_am_name_nsp_index: 1024 total in 1 blocks; 192 free
(0 chunks); 832 used
pg_ts_template_oid_index: 1024 total in 1 blocks; 344 free (0
chunks); 680 used
MdSmgr: 0 total in 0 blocks; 0 free (0 chunks); 0 used
LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280
used
Timezones: 48616 total in 2 blocks; 5968 free (0 chunks); 42648 used
ErrorContext: 8192 total in 1 blocks; 8176 free (3 chunks); 16 used
ERROR: out of memory
and the memory area from the config file:
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
shared_buffers = 128MB # min 128kB or max_connections*16kB
# (change requires restart)
temp_buffers = 16MB # min 800kB
#max_prepared_transactions = 5 # can be 0 or more
# (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).
#work_mem = 1MB # min 64kB
work_mem = 16MB
maintenance_work_mem = 300MB # min 1MB
#max_stack_depth = 2MB # min 100kB
# - Free Space Map -
max_fsm_pages = 400000 # min max_fsm_relations*16, 6 bytes each
# (change requires restart)
#max_fsm_relations = 1000 # min 100, ~70 bytes each
# (change requires restart)
# - 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 = 0 # 0-1000 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
Is there anything I can do to ensure I'm getting the most out of the
1GB of RAM on my server?
Thanks,
Brooks L.
2009/10/28 Brooks Lyrette <brooks.lyrette@gmail.com>: > Hello All, > > I'm new to postgres and it seems my server is unable to fork new > connections. > > Here is the log: > > LOG: could not fork new process for connection: Not enough space > LOG: could not fork new process for connection: Not enough space > TopMemoryContext: 84784 total in 8 blocks; 5584 free (10 chunks); 79200 used > Prepared Queries: 8192 total in 1 blocks; 1800 free (0 chunks); 6392 used > TopTransactionContext: 8192 total in 1 blocks; 7856 free (0 chunks); 336 > used > Record information cache: 8192 total in 1 blocks; 1800 free (0 chunks); > 6392 used > MessageContext: 8192 total in 1 blocks; 7128 free (0 chunks); 1064 used > smgr relation table: 8192 total in 1 blocks; 3840 free (0 chunks); 4352 > used > TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0 chunks); 16 > used > Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used > PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used > PortalHeapMemory: 1024 total in 1 blocks; 976 free (0 chunks); 48 used > Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks); 4816 used > CacheMemoryContext: 405328 total in 19 blocks; 70888 free (1 chunks); > 334440 used > unnamed prepared statement: 24576 total in 2 blocks; 7912 free (1 > chunks); 16664 used > CachedPlan: 1024 total in 1 blocks; 904 free (0 chunks); 120 used > CachedPlanSource: 1024 total in 1 blocks; 840 free (0 chunks); 184 used > pg_ts_dict_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 > used > pg_aggregate_fnoid_index: 1024 total in 1 blocks; 344 free (0 chunks); > 680 used > pg_language_name_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 > used > pg_statistic_relid_att_index: 1024 total in 1 blocks; 280 free (0 > chunks); 744 used > pg_ts_dict_dictname_index: 1024 total in 1 blocks; 280 free (0 chunks); > 744 used > pg_namespace_nspname_index: 1024 total in 1 blocks; 304 free (0 chunks); > 720 used > pg_opfamily_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 > used > pg_opclass_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 > used > pg_ts_parser_prsname_index: 1024 total in 1 blocks; 280 free (0 chunks); > 744 used > pg_amop_fam_strat_index: 1024 total in 1 blocks; 128 free (0 chunks); 896 > used > pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 192 free (0 > chunks); 832 used > pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 240 free (0 > chunks); 784 used > pg_cast_source_target_index: 1024 total in 1 blocks; 280 free (0 chunks); > 744 used > pg_auth_members_role_member_index: 1024 total in 1 blocks; 280 free (0 > chunks); 744 used > pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 280 free (0 > chunks); 744 used > pg_ts_config_cfgname_index: 1024 total in 1 blocks; 280 free (0 chunks); > 744 used > pg_authid_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 > used > pg_ts_config_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 > used > pg_conversion_default_index: 1024 total in 1 blocks; 128 free (0 chunks); > 896 used > pg_language_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 > used > pg_enum_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used > pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 192 free (0 > chunks); 832 used > pg_ts_parser_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 > used > pg_database_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 > used > pg_conversion_name_nsp_index: 1024 total in 1 blocks; 280 free (0 > chunks); 744 used > pg_class_relname_nsp_index: 1024 total in 1 blocks; 280 free (0 chunks); > 744 used > pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 280 free (0 > chunks); 744 used > pg_class_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used > pg_amproc_fam_proc_index: 1024 total in 1 blocks; 128 free (0 chunks); > 896 used > pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 128 free (0 > chunks); 896 used > pg_index_indexrelid_index: 1024 total in 1 blocks; 344 free (0 chunks); > 680 used > pg_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used > pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 280 free (0 > chunks); 744 used > pg_authid_rolname_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 > used > pg_auth_members_member_role_index: 1024 total in 1 blocks; 280 free (0 > chunks); 744 used > pg_enum_typid_label_index: 1024 total in 1 blocks; 280 free (0 chunks); > 744 used > pg_constraint_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 > used > pg_conversion_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 > used > pg_ts_template_tmplname_index: 1024 total in 1 blocks; 280 free (0 > chunks); 744 used > pg_ts_config_map_index: 1024 total in 1 blocks; 192 free (0 chunks); 832 > used > pg_namespace_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 > used > pg_type_typname_nsp_index: 1024 total in 1 blocks; 280 free (0 chunks); > 744 used > pg_operator_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 > used > pg_amop_opr_fam_index: 1024 total in 1 blocks; 280 free (0 chunks); 744 > used > pg_proc_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used > pg_opfamily_am_name_nsp_index: 1024 total in 1 blocks; 192 free (0 > chunks); 832 used > pg_ts_template_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); > 680 used > MdSmgr: 0 total in 0 blocks; 0 free (0 chunks); 0 used > LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used > Timezones: 48616 total in 2 blocks; 5968 free (0 chunks); 42648 used > ErrorContext: 8192 total in 1 blocks; 8176 free (3 chunks); 16 used > ERROR: out of memory > > and the memory area from the config file: > > #------------------------------------------------------------------------------ > # RESOURCE USAGE (except WAL) > #------------------------------------------------------------------------------ > > # - Memory - > > shared_buffers = 128MB # min 128kB or max_connections*16kB > # (change requires restart) > temp_buffers = 16MB # min 800kB > #max_prepared_transactions = 5 # can be 0 or more > # (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). > #work_mem = 1MB # min 64kB > work_mem = 16MB > maintenance_work_mem = 300MB # min 1MB > #max_stack_depth = 2MB # min 100kB > > # - Free Space Map - > > max_fsm_pages = 400000 # min max_fsm_relations*16, 6 bytes > each > # (change requires restart) > #max_fsm_relations = 1000 # min 100, ~70 bytes each > # (change requires restart) > > # - 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 = 0 # 0-1000 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 > > > Is there anything I can do to ensure I'm getting the most out of the 1GB of > RAM on my server? > What is the maximum number of connections as configured in your copy of postgresql.conf? And also are you running any other run-away processes that are taking up all available memory? Thom
2009/10/28 Brooks Lyrette <brooks.lyrette@gmail.com>: > There should be no other processes running, this system is dedicated to > running postgresql. > > Max connections is configured to: max_connections = 400 > Well it sounds like you've somehow run out of swap space. Are you able to run top and sort by resident memory and also swap memory to see where it's all going? Also use pg_top if you have it. That will tell you how much memory each connection is using. Thom
Brooks Lyrette <brooks.lyrette@gmail.com> writes:
> I'm new to postgres and it seems my server is unable to fork new
> connections.
> LOG: could not fork new process for connection: Not enough space
For what I suppose is a lightly loaded machine, that is just plain
weird. What's the platform exactly? Is it possible that the postmaster
is being launched under very restrictive ulimit settings?
If it's a Unix-ish machine, it would be useful to look at "top" and
"vmstat" output to see if the machine is under severe memory
pressure for some reason.
regards, tom lane
The machine is running a moderate load. This is running on a Solaris
Zone.
Top is showing:
load averages: 2.49, 4.00, 3.78; up
124
+
12
:
24
:
47
16
:04:21
46 processes: 45 sleeping, 1 on cpu
CPU states: 76.6% idle, 14.6% user, 8.8% kernel, 0.0% iowait, 0.0%
swap
Memory: 32G phys mem, 942M free mem, 76G swap, 74G free swap
PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
5069 postgres 1 52 0 167M 20M sleep 0:04 13.50% postgres
902 postgres 1 1 0 167M 21M sleep 0:12 6.39% postgres
5068 postgres 1 59 0 167M 21M sleep 0:01 4.92% postgres
5070 postgres 1 59 0 166M 20M sleep 0:00 3.72% postgres
27817 postgres 1 59 0 167M 22M sleep 0:23 1.43% postgres
903 postgres 1 59 0 157M 11M sleep 0:02 1.14% postgres
23594 postgres 1 59 0 148M 2096K sleep 0:10 0.11% postgres
5510 brooks 1 59 0 5624K 2184K cpu 0:00 0.10% top
23598 postgres 1 59 0 6404K 1680K sleep 0:11 0.10% postgres
23595 postgres 1 59 0 148M 1852K sleep 0:01 0.01% postgres
23597 postgres 1 59 0 6220K 1556K sleep 0:00 0.01% postgres
24870 root 30 39 0 7060K 3332K sleep 7:01 0.00% nscd
736 brooks 1 59 0 6292K 2060K sleep 0:00 0.00% sshd
23596 postgres 1 59 0 148M 2024K sleep 0:00 0.00% postgres
24828 root 13 29 0 9300K 2128K sleep 2:02 0.00% svc.st
And vmstat shows:
kthr memory page disk
faults cpu
r b w swap free re mf pi po fr de sr s0 s1 s2 s3 in sy cs
us sy id
0 0 0 74805352 2910024 373 4154 96 7 7 0 0 -0 52 19 19 4561 230770
6889 11 13 76
On 28-Oct-09, at 4:01 PM, Tom Lane wrote:
> Brooks Lyrette <brooks.lyrette@gmail.com> writes:
>> I'm new to postgres and it seems my server is unable to fork new
>> connections.
>
>> LOG: could not fork new process for connection: Not enough space
>
> For what I suppose is a lightly loaded machine, that is just plain
> weird. What's the platform exactly? Is it possible that the
> postmaster
> is being launched under very restrictive ulimit settings?
>
> If it's a Unix-ish machine, it would be useful to look at "top" and
> "vmstat" output to see if the machine is under severe memory
> pressure for some reason.
>
> regards, tom lane
There should be no other processes running, this system is dedicated to running postgresql. Max connections is configured to: max_connections = 400 Brooks L. On 28-Oct-09, at 3:46 PM, Thom Brown wrote: > 2009/10/28 Brooks Lyrette <brooks.lyrette@gmail.com>: >> Hello All, >> >> I'm new to postgres and it seems my server is unable to fork new >> connections. >> >> Here is the log: >> >> LOG: could not fork new process for connection: Not enough space >> LOG: could not fork new process for connection: Not enough space >> TopMemoryContext: 84784 total in 8 blocks; 5584 free (10 chunks); >> 79200 used >> Prepared Queries: 8192 total in 1 blocks; 1800 free (0 chunks); >> 6392 used >> TopTransactionContext: 8192 total in 1 blocks; 7856 free (0 >> chunks); 336 >> used >> Record information cache: 8192 total in 1 blocks; 1800 free (0 >> chunks); >> 6392 used >> MessageContext: 8192 total in 1 blocks; 7128 free (0 chunks); 1064 >> used >> smgr relation table: 8192 total in 1 blocks; 3840 free (0 chunks); >> 4352 >> used >> TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0 >> chunks); 16 >> used >> Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used >> PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used >> PortalHeapMemory: 1024 total in 1 blocks; 976 free (0 chunks); >> 48 used >> Relcache by OID: 8192 total in 1 blocks; 3376 free (0 chunks); >> 4816 used >> CacheMemoryContext: 405328 total in 19 blocks; 70888 free (1 >> chunks); >> 334440 used >> unnamed prepared statement: 24576 total in 2 blocks; 7912 free (1 >> chunks); 16664 used >> CachedPlan: 1024 total in 1 blocks; 904 free (0 chunks); 120 used >> CachedPlanSource: 1024 total in 1 blocks; 840 free (0 chunks); >> 184 used >> pg_ts_dict_oid_index: 1024 total in 1 blocks; 344 free (0 >> chunks); 680 >> used >> pg_aggregate_fnoid_index: 1024 total in 1 blocks; 344 free (0 >> chunks); >> 680 used >> pg_language_name_index: 1024 total in 1 blocks; 344 free (0 >> chunks); 680 >> used >> pg_statistic_relid_att_index: 1024 total in 1 blocks; 280 free (0 >> chunks); 744 used >> pg_ts_dict_dictname_index: 1024 total in 1 blocks; 280 free (0 >> chunks); >> 744 used >> pg_namespace_nspname_index: 1024 total in 1 blocks; 304 free (0 >> chunks); >> 720 used >> pg_opfamily_oid_index: 1024 total in 1 blocks; 344 free (0 >> chunks); 680 >> used >> pg_opclass_oid_index: 1024 total in 1 blocks; 344 free (0 >> chunks); 680 >> used >> pg_ts_parser_prsname_index: 1024 total in 1 blocks; 280 free (0 >> chunks); >> 744 used >> pg_amop_fam_strat_index: 1024 total in 1 blocks; 128 free (0 >> chunks); 896 >> used >> pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 192 free (0 >> chunks); 832 used >> pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 240 >> free (0 >> chunks); 784 used >> pg_cast_source_target_index: 1024 total in 1 blocks; 280 free (0 >> chunks); >> 744 used >> pg_auth_members_role_member_index: 1024 total in 1 blocks; 280 >> free (0 >> chunks); 744 used >> pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 280 >> free (0 >> chunks); 744 used >> pg_ts_config_cfgname_index: 1024 total in 1 blocks; 280 free (0 >> chunks); >> 744 used >> pg_authid_oid_index: 1024 total in 1 blocks; 304 free (0 >> chunks); 720 >> used >> pg_ts_config_oid_index: 1024 total in 1 blocks; 344 free (0 >> chunks); 680 >> used >> pg_conversion_default_index: 1024 total in 1 blocks; 128 free (0 >> chunks); >> 896 used >> pg_language_oid_index: 1024 total in 1 blocks; 344 free (0 >> chunks); 680 >> used >> pg_enum_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); >> 680 used >> pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 192 free >> (0 >> chunks); 832 used >> pg_ts_parser_oid_index: 1024 total in 1 blocks; 344 free (0 >> chunks); 680 >> used >> pg_database_oid_index: 1024 total in 1 blocks; 304 free (0 >> chunks); 720 >> used >> pg_conversion_name_nsp_index: 1024 total in 1 blocks; 280 free (0 >> chunks); 744 used >> pg_class_relname_nsp_index: 1024 total in 1 blocks; 280 free (0 >> chunks); >> 744 used >> pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 280 >> free (0 >> chunks); 744 used >> pg_class_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); >> 680 used >> pg_amproc_fam_proc_index: 1024 total in 1 blocks; 128 free (0 >> chunks); >> 896 used >> pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 128 >> free (0 >> chunks); 896 used >> pg_index_indexrelid_index: 1024 total in 1 blocks; 344 free (0 >> chunks); >> 680 used >> pg_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); >> 720 used >> pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 280 free (0 >> chunks); 744 used >> pg_authid_rolname_index: 1024 total in 1 blocks; 304 free (0 >> chunks); 720 >> used >> pg_auth_members_member_role_index: 1024 total in 1 blocks; 280 >> free (0 >> chunks); 744 used >> pg_enum_typid_label_index: 1024 total in 1 blocks; 280 free (0 >> chunks); >> 744 used >> pg_constraint_oid_index: 1024 total in 1 blocks; 344 free (0 >> chunks); 680 >> used >> pg_conversion_oid_index: 1024 total in 1 blocks; 344 free (0 >> chunks); 680 >> used >> pg_ts_template_tmplname_index: 1024 total in 1 blocks; 280 free (0 >> chunks); 744 used >> pg_ts_config_map_index: 1024 total in 1 blocks; 192 free (0 >> chunks); 832 >> used >> pg_namespace_oid_index: 1024 total in 1 blocks; 304 free (0 >> chunks); 720 >> used >> pg_type_typname_nsp_index: 1024 total in 1 blocks; 280 free (0 >> chunks); >> 744 used >> pg_operator_oid_index: 1024 total in 1 blocks; 344 free (0 >> chunks); 680 >> used >> pg_amop_opr_fam_index: 1024 total in 1 blocks; 280 free (0 >> chunks); 744 >> used >> pg_proc_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); >> 680 used >> pg_opfamily_am_name_nsp_index: 1024 total in 1 blocks; 192 free (0 >> chunks); 832 used >> pg_ts_template_oid_index: 1024 total in 1 blocks; 344 free (0 >> chunks); >> 680 used >> MdSmgr: 0 total in 0 blocks; 0 free (0 chunks); 0 used >> LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 >> used >> Timezones: 48616 total in 2 blocks; 5968 free (0 chunks); 42648 used >> ErrorContext: 8192 total in 1 blocks; 8176 free (3 chunks); 16 used >> ERROR: out of memory >> >> and the memory area from the config file: >> >> #------------------------------------------------------------------------------ >> # RESOURCE USAGE (except WAL) >> #------------------------------------------------------------------------------ >> >> # - Memory - >> >> shared_buffers = 128MB # min 128kB or >> max_connections*16kB >> # (change requires restart) >> temp_buffers = 16MB # min 800kB >> #max_prepared_transactions = 5 # can be 0 or more >> # (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). >> #work_mem = 1MB # min 64kB >> work_mem = 16MB >> maintenance_work_mem = 300MB # min 1MB >> #max_stack_depth = 2MB # min 100kB >> >> # - Free Space Map - >> >> max_fsm_pages = 400000 # min max_fsm_relations*16, >> 6 bytes >> each >> # (change requires restart) >> #max_fsm_relations = 1000 # min 100, ~70 bytes each >> # (change requires restart) >> >> # - 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 = 0 # 0-1000 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 >> >> >> Is there anything I can do to ensure I'm getting the most out of >> the 1GB of >> RAM on my server? >> > > What is the maximum number of connections as configured in your copy > of postgresql.conf? And also are you running any other run-away > processes that are taking up all available memory? > > Thom
On Wed, Oct 28, 2009 at 1:05 PM, Brooks Lyrette <brooks.lyrette@gmail.com> wrote: > The machine is running a moderate load. This is running on a Solaris Zone. > > Memory: 32G phys mem, 942M free mem, 76G swap, 74G free swap > > PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND > 5069 postgres 1 52 0 167M 20M sleep 0:04 13.50% postgres Hm, well 400 processes if each were taking 190M would be 76G. But that doesn't really make much sense since most of the 167M of that process is presumably the shared buffers. What is your shared buffers set to btw? And your work_mem and maintenance_work_mem? Fwiw ENOMEM is documented as "There is not enough swap space.". Perhaps you have some big usage spike which uses up lots of swap and causes postgres to start needing lots of new processes at the same time? -- greg
On Wed, 28 Oct 2009, Tom Lane wrote: > What's the platform exactly? Is it possible that the postmaster is > being launched under very restrictive ulimit settings? Now that Brooks mentioned this being run inside of a Solaris zone, seems like this might be running into some memory upper limit controlled by the zone configuration. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Wed, 28 Oct 2009, Greg Stark wrote: >> PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND >> 5069 postgres 1 52 0 167M 20M sleep 0:04 13.50% postgres > > Hm, well 400 processes if each were taking 190M would be 76G. But that > doesn't really make much sense since most of the 167M of that process > is presumably the shared buffers. What is your shared buffers set to > btw? And your work_mem and maintenance_work_mem? Pieced together from the upthread config file posts: shared_buffers = 128MB work_mem = 16MB max_connections = 400 So somewhere bewteen 128MB and 167MB of that SIZE is shared_buffers plus the other usual shared memory suspects. Let's say each process is using 40MB, which is on the high side. I'd guess this system might peak at 40MB * 400 connections+170MB~=16GB of database RAM used, which is so much less than physical RAM it seems more like a software limit is being run into instead. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Greg Smith <gsmith@gregsmith.com> writes:
> On Wed, 28 Oct 2009, Tom Lane wrote:
>> What's the platform exactly? Is it possible that the postmaster is
>> being launched under very restrictive ulimit settings?
> Now that Brooks mentioned this being run inside of a Solaris zone, seems
> like this might be running into some memory upper limit controlled by the
> zone configuration.
A bit of quick googling confirmed that there is (or can be) a per-zone
memory cap. I'll bet Greg has nailed it. The docs I found claim that
the cap management code is smart enough to count shared memory only
once, which would eliminate the most obvious way in which the cap might
be way off; but it still sounds like checking into that configuration
setting is job #1.
regards, tom lane
Thanks for all the help guys.
So this is what I get from all this. My solaris zone will cap me at around 900M-1000M RSS memory. Therefore using the math from a pervious reply I can only have about 23 connections to my database without maxing out the machines memory?
This seems a little low, won't postgres start swapping to disk once the available RAM is used up?
You'll have to excuse me if this seems like a newbie question.
Thanks again,
Brooks L.
On 28-Oct-09, at 5:56 PM, Tom Lane wrote:
Greg Smith <gsmith@gregsmith.com> writes:On Wed, 28 Oct 2009, Tom Lane wrote:What's the platform exactly? Is it possible that the postmaster isbeing launched under very restrictive ulimit settings?Now that Brooks mentioned this being run inside of a Solaris zone, seemslike this might be running into some memory upper limit controlled by thezone configuration.
A bit of quick googling confirmed that there is (or can be) a per-zone
memory cap. I'll bet Greg has nailed it. The docs I found claim that
the cap management code is smart enough to count shared memory only
once, which would eliminate the most obvious way in which the cap might
be way off; but it still sounds like checking into that configuration
setting is job #1.
regards, tom lane
On Mon, Nov 2, 2009 at 1:11 PM, Brooks Lyrette <brooks.lyrette@gmail.com> wrote: > Thanks for all the help guys. > So this is what I get from all this. My solaris zone will cap me at around > 900M-1000M RSS memory. Therefore using the math from a pervious reply I can > only have about 23 connections to my database without maxing out the > machines memory? > This seems a little low, won't postgres start swapping to disk once the > available RAM is used up? pgsql doesn't swap, the OS swaps, when it runs out of memory. Since pgsql is limited to 1G, the OS has no reason to swap. Can you simply remove the cap from this instance? It doesn't seem to be doing anything useful.
On Mon, Nov 2, 2009 at 3:56 PM, Brooks Lyrette <brooks.lyrette@gmail.com> wrote: > I wish the solution was that simple. I rent the zone and that is my > providers cap. Am I misunderstanding this? You rent an image with 32Gigs of ram. Your provider limits you to any single process / application being 1G total by a cap? Then what good is the 32Gigs of ram? It's like seeing the promised land but never allowed to enter. And what reason would they have for capping a single app inside the vm? It's already using 32Gig, so I don't see them saving any memory.
I wish the solution was that simple. I rent the zone and that is my providers cap.
On 2-Nov-09, at 5:21 PM, Scott Marlowe wrote:
On Mon, Nov 2, 2009 at 1:11 PM, Brooks Lyrette <brooks.lyrette@gmail.com> wrote:Thanks for all the help guys.So this is what I get from all this. My solaris zone will cap me at around900M-1000M RSS memory. Therefore using the math from a pervious reply I canonly have about 23 connections to my database without maxing out themachines memory?This seems a little low, won't postgres start swapping to disk once theavailable RAM is used up?
pgsql doesn't swap, the OS swaps, when it runs out of memory. Since
pgsql is limited to 1G, the OS has no reason to swap.
Can you simply remove the cap from this instance? It doesn't seem to
be doing anything useful.