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
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@xxxxxxxxx> 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?
