Re: Error: "could not fork new process for connection: Cannot allocate memory"

Here are settings as suggested in the wiki to run a query to
display those changed from defaults:

name | current_setting | source
------------------------------+-------------------+----------------------
application_name | psql | client
autovacuum | on | configuration file
autovacuum_analyze_threshold | 500 | configuration file
autovacuum_naptime | 1d | configuration file
autovacuum_vacuum_threshold | 1000 | configuration file
checkpoint_segments | 12 | configuration file
client_encoding | UTF8 | client
client_min_messages | notice | configuration file
effective_cache_size | 4TB | configuration file
escape_string_warning | off | configuration file
lc_messages | en_US.UTF-8 | configuration file
lc_monetary | en_US.UTF-8 | configuration file
lc_numeric | en_US.UTF-8 | configuration file
lc_time | en_US.UTF-8 | configuration file
listen_addresses | * | configuration file
log_destination | stderr | configuration file
log_directory | pg_log | configuration file
log_duration | on | configuration file
log_filename | postgresql-%a.log | configuration file
log_line_prefix | <%t> | configuration file
log_min_error_statement | debug1 | configuration file
log_min_messages | info | configuration file
log_rotation_age | 1d | configuration file
log_rotation_size | 0 | configuration file
log_truncate_on_rotation | on | configuration file
logging_collector | on | configuration file
maintenance_work_mem | 160MB | configuration file
max_connections | 500 | configuration file
max_stack_depth | 2MB | environment variable
shared_buffers | 1GB | configuration file
standard_conforming_strings | off | configuration file
statement_timeout | 1h | configuration file
work_mem | 10MB | configuration file

On Mon, Dec 21, 2020 at 9:17 AM frank picabia <fpicabia(at)gmail(dot)com> wrote:

>
> This error occurred a couple of days ago and it is unlikely to repeat as
> it was a series of exams being done in moodle with over 500 students.
> However I'd like to tune Postgres to see if it can be prevented.
>
> First of all, we did not run out of memory within the OS. This system is
> monitored by cacti and
> Real Used memory never exceeded 30 GB on a server having 64 GB ram plus
> some swap.
>
> I can also see in the messages log, the Linux kernel OOM killer was never
> triggered.
>
> The postgres user running the DB has no ulimits on memory which might
> apply.
>
> Here is the entry in the Postgres log:
>
> <2020-12-19 13:19:29 GMT>LOG: could not fork new process for connection:
> Cannot allocate memory
> TopMemoryContext: 136704 total in 13 blocks; 6688 free (4 chunks); 130016
> used
> smgr relation table: 24576 total in 2 blocks; 13904 free (4 chunks); 10672
> used
> TopTransactionContext: 8192 total in 1 blocks; 6152 free (5 chunks); 2040
> used
> <2020-12-19 13:19:29 GMT>LOG: could not fork new process for connection:
> Cannot allocate memory
> TransactionAbortContext: 32768 total in 1 blocks; 32728 free (0 chunks);
> 40 used
> Portal hash: 8192 total in 1 blocks; 1672 free (0 chunks); 6520 used
> PortalMemory: 0 total in 0 blocks; 0 free (0 chunks); 0 used
>
> My conclusion is this memory error must be related to limits within
> Postgres tunables, or perhaps something like SHM memory, which we have not
> set up in the sysctl.conf , so this is just a default amount.
>
> The values we have in postgres.conf are mostly suggested by pgtune
> script. The one exception being max_connections=500, which was increased
> to try to avoid bottlenecks being reached with Apache client numbers, and
> gradually over time as we saw we still had lots of memory available with a
> higher connection limit.
>
> Which tunables do you need to see?
>
>