Postgres 14.2 Out of memory

Konfiguration & Verwaltung
1

Hallo zusammen,

Wir schreiben geringe Massendaten in eine Postgres 14.2. Das System darunter hat 32GB und 500GB Plattenplatz - das sollte reichen. Weder die Festplatte noch das RAM (im Taskmanager) zeigt irgendwie Überlastungen an.

Dennoch erhalten wir “out of memory”- Fehlermeldungen, die alle 3-4 Tage auftreten. Danach muss das System (Win2019 Server) neugestartet werden.

Die Konfiguration der Postgres ist auf die von Postgres vorgegebene Standardparameter eingestellt.

Kann jemand hier helfen und erkennen, an welchem Parameter für welchen Memory hier geschraubt werden kann/sollte, damit die Meldung out-of-memory eliminiert wird?

Vielen Dank

Die folgende Fehlermeldung erscheint im DB-Log:

TopMemoryContext: 460832 total in 10 blocks; 22936 free (26 chunks); 437896 used
  Statistics snapshot: 1024 total in 1 blocks; 760 free (0 chunks); 264 used
    Per-database function: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
    Per-database table: 1056768 total in 8 blocks; 37008 free (37 chunks); 1019760 used
    Databases hash: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
  AV worker: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
  Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
  smgr relation table: 16384 total in 2 blocks; 4592 free (2 chunks); 11792 used
  pgstat TabStatusArray lookup hash table: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
  TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
  Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
  TopPortalContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
  Relcache by OID: 16384 total in 2 blocks; 3504 free (2 chunks); 12880 used
  CacheMemoryContext: 524288 total in 7 blocks; 144800 free (3 chunks); 379488 used
    index info: 2048 total in 2 blocks; 496 free (1 chunks); 1552 used: pg_db_role_setting_databaseid_rol_index
    index info: 3072 total in 2 blocks; 1160 free (2 chunks); 1912 used: pg_opclass_am_name_nsp_index
    index info: 2048 total in 2 blocks; 952 free (2 chunks); 1096 used: pg_foreign_data_wrapper_name_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_enum_oid_index
    index info: 2048 total in 2 blocks; 688 free (2 chunks); 1360 used: pg_class_relname_nsp_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_foreign_server_oid_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_publication_pubname_index
    index info: 3072 total in 2 blocks; 1128 free (1 chunks); 1944 used: pg_statistic_relid_att_inh_index
    index info: 2048 total in 2 blocks; 688 free (2 chunks); 1360 used: pg_cast_source_target_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_language_name_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_transform_oid_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_collation_oid_index
    index info: 3072 total in 2 blocks; 1096 free (2 chunks); 1976 used: pg_amop_fam_strat_index
    index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: pg_index_indexrelid_index
    index info: 2048 total in 2 blocks; 688 free (2 chunks); 1360 used: pg_ts_template_tmplname_index
    index info: 3072 total in 2 blocks; 1160 free (2 chunks); 1912 used: pg_ts_config_map_index
    index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: pg_opclass_oid_index
    index info: 2048 total in 2 blocks; 952 free (2 chunks); 1096 used: pg_foreign_data_wrapper_oid_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_event_trigger_evtname_index
    index info: 2048 total in 2 blocks; 688 free (2 chunks); 1360 used: pg_statistic_ext_name_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_publication_oid_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_ts_dict_oid_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_event_trigger_oid_index
    index info: 3072 total in 2 blocks; 1096 free (2 chunks); 1976 used: pg_conversion_default_index
    index info: 3072 total in 2 blocks; 1096 free (2 chunks); 1976 used: pg_operator_oprname_l_r_n_index
    index info: 2048 total in 2 blocks; 688 free (2 chunks); 1360 used: pg_trigger_tgrelid_tgname_index
    index info: 2048 total in 2 blocks; 688 free (2 chunks); 1360 used: pg_enum_typid_label_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_ts_config_oid_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_user_mapping_oid_index
    index info: 3072 total in 2 blocks; 1160 free (2 chunks); 1912 used: pg_opfamily_am_name_nsp_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_foreign_table_relid_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_type_oid_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_aggregate_fnoid_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_constraint_oid_index
    index info: 2048 total in 2 blocks; 688 free (2 chunks); 1360 used: pg_rewrite_rel_rulename_index
    index info: 2048 total in 2 blocks; 688 free (2 chunks); 1360 used: pg_ts_parser_prsname_index
    index info: 2048 total in 2 blocks; 688 free (2 chunks); 1360 used: pg_ts_config_cfgname_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_ts_parser_oid_index
    index info: 2048 total in 2 blocks; 656 free (2 chunks); 1392 used: pg_publication_rel_prrelid_prpubid_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_operator_oid_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_namespace_nspname_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_ts_template_oid_index
    index info: 3072 total in 2 blocks; 1160 free (2 chunks); 1912 used: pg_amop_opr_fam_index
    index info: 3072 total in 2 blocks; 1128 free (1 chunks); 1944 used: pg_default_acl_role_nsp_obj_index
    index info: 3072 total in 2 blocks; 1160 free (2 chunks); 1912 used: pg_collation_name_enc_nsp_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_publication_rel_oid_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_range_rngtypid_index
    index info: 2048 total in 2 blocks; 688 free (2 chunks); 1360 used: pg_ts_dict_dictname_index
    index info: 2048 total in 2 blocks; 688 free (2 chunks); 1360 used: pg_type_typname_nsp_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_opfamily_oid_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_statistic_ext_oid_index
    index info: 2048 total in 2 blocks; 952 free (2 chunks); 1096 used: pg_statistic_ext_data_stxoid_index
    index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: pg_class_oid_index
    index info: 3072 total in 2 blocks; 1160 free (2 chunks); 1912 used: pg_proc_proname_args_nsp_index
    index info: 2048 total in 2 blocks; 952 free (2 chunks); 1096 used: pg_partitioned_table_partrelid_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_range_rngmultitypid_index
    index info: 2048 total in 2 blocks; 688 free (2 chunks); 1360 used: pg_transform_type_lang_index
    index info: 2048 total in 2 blocks; 448 free (1 chunks); 1600 used: pg_attribute_relid_attnum_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_proc_oid_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_language_oid_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_namespace_oid_index
    index info: 3072 total in 2 blocks; 776 free (1 chunks); 2296 used: pg_amproc_fam_proc_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_foreign_server_name_index
    index info: 2048 total in 2 blocks; 688 free (2 chunks); 1360 used: pg_attribute_relid_attnam_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_conversion_oid_index
    index info: 2048 total in 2 blocks; 656 free (2 chunks); 1392 used: pg_user_mapping_user_server_index
    index info: 2048 total in 2 blocks; 656 free (2 chunks); 1392 used: pg_subscription_rel_srrelid_srsubid_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_sequence_seqrelid_index
    index info: 2048 total in 2 blocks; 688 free (2 chunks); 1360 used: pg_conversion_name_nsp_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_authid_oid_index
    index info: 2048 total in 2 blocks; 656 free (2 chunks); 1392 used: pg_auth_members_member_role_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_subscription_oid_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_tablespace_oid_index
    index info: 3072 total in 2 blocks; 1160 free (2 chunks); 1912 used: pg_shseclabel_object_index
    index info: 2048 total in 2 blocks; 952 free (2 chunks); 1096 used: pg_replication_origin_roname_index
    index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: pg_database_datname_index
    index info: 2048 total in 2 blocks; 688 free (2 chunks); 1360 used: pg_subscription_subname_index
    index info: 2048 total in 2 blocks; 952 free (2 chunks); 1096 used: pg_replication_origin_roiident_index
    index info: 2048 total in 2 blocks; 656 free (2 chunks); 1392 used: pg_auth_members_role_member_index
    index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: pg_database_oid_index
    index info: 1024 total in 1 blocks; 0 free (0 chunks); 1024 used: pg_authid_rolname_index
  WAL record construction: 49768 total in 2 blocks; 6360 free (0 chunks); 43408 used
  PrivateRefCount: 8192 total in 1 blocks; 2616 free (0 chunks); 5576 used
  MdSmgr: 8192 total in 1 blocks; 7904 free (0 chunks); 288 used
  LOCALLOCK hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
  Timezones: 104120 total in 2 blocks; 2616 free (0 chunks); 101504 used
  ErrorContext: 8192 total in 1 blocks; 7928 free (0 chunks); 264 used
Grand total: 2512192 bytes in 174 blocks; 342736 free (150 chunks); 2169456 used
2023-06-20 23:08:25.398 GMT [2176] ERROR:  out of memory
2023-06-20 23:08:25.398 GMT [2176] DETAIL:  Failed on request of size 8000 in memory context "dynahash".
2023-06-20 23:09:23.590 GMT [652] FATAL:  could not reattach to shared memory (key=0000000000000168, addr=0000000004B60000): error code 1455
2

Mit den Internas kenne ich mich nicht aus, aber wenn ich mir die Release Notes der 14.x ansehe, sind ein paar Bugfixes seit der 14.2 gekommen die etwas memory leaks zu tun habe.

Der erste Schritt sollte auf jeden Fall sein, auf das aktuelle Minor Release(14.8) zu aktualisieren.

Unter Windows sollte man auf jeden Fall auch:

update_process_title = off

setzen. Hauptsächlich wegen Performance Problemen, aber in 14.3 wurde wohl auch ein Speicherproblem mit dem Feature unter Windows (oder zumindest Cygwin) gelöst.

3

Hallo,

Kann es sein, dass solche Parameter wie

shared_buffers = 128MB
work_mem = 4MB
effective_cache_size = 1GB
maintenance_work_mem = 64MB

zu niedrig gesetzt sind?

Was wären Empfehlungen für insg. 32GB an RAM? Oder sollte man langsam erhöhen?

4

Ich kann mir nicht vorstellen, dass ein “zu niedrig” angesetzter Wert zu einem Out-Of-Memory Fehler führen kann. Postgres will halt dann mehr Arbeit auf der Festplatte ausführen (z.B. Sortieren).

Bei 32GB RAM und einem dedizierten Server könnte man sicherlich shared_buffers hochsetzen (8-12GB), aber das würde sich nur in der Performance auswirken.

Wie gesagt, ihr solltet zuerst die aktuelle Bugfix Version der 14 installieren und dann weitersehen.

5

Der Memory Dump sagt nur, daß der Prozeß, der Out Of Memory gegangen ist, nicht viel Speicher belegt hatte. Der Prozeß, der das viele Memory verbraucht, muß ein anderer sein; vielleicht gar kein PostgreSQL-Prozeß.

6

Dieses Thema wurde automatisch 60 Tage nach der letzten Antwort geschlossen. Es sind keine neuen Antworten mehr erlaubt.