Início / dba / Perguntas / 338760
Accepted
Peter
Asked: 2024-04-18 15:46:46 +0800 CST

Postgres: Analisar problema pg_log_backend_memory_contexts

  • 772

(PG14.1) Abaixo está a saída de pg_log_backend_memory_contextsum PID para o qual pg_proctab()diz que rssé 216MB. Adicionei alguns pontos para facilitar a leitura de números grandes e anonimizei algumas coisas.

Não consigo entender:

  • Como o TopMemoryContext tem apenas 5 MB, mas o total geral é de 100 MB?
  • CacheMemoryContext tem 69 MB, mas 44 MB são gratuitos e 25 MB são usados. O que há nesses 25 MB? Não é exatamente isso que pg_log_backend_memory_contexts deveria me dizer (mas não diz)? Todas as entradas de ‘nível 2’ não chegam nem perto de 25 MB.
  • Esses 44 MB gratuitos serão devolvidos ao sistema enquanto a sessão estiver ativa?
  • Quando eu encerrar a sessão, ela liberará 216 MB de RAM (número pc_proctab) ou 100 MB (número pg_log_backend_memory_contexts) ou outro número?
logging memory contexts of PID 15744
level: 0; TopMemoryContext: 4.264.856 total in 104 blocks; 2.074.448 free (8510 chunks); 2190408 used
level: 1; pgstat TabStatusArray lookup hash table: 65536 total in 4 blocks; 4672 free (11 chunks); 60864 used
level: 1; RdsSuperUserCache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
level: 1; PL/pgSQL function: 8192 total in 1 blocks; 3792 free (1 chunks); 4400 used: get_date_to_char(timestamp without time zone,character varying)
level: 1; PL/pgSQL function: 16384 total in 2 blocks; 7032 free (5 chunks); 9352 used: get_number_to_char(numeric,character varying)
level: 1; PL/pgSQL function: 8192 total in 1 blocks; 3824 free (3 chunks); 4368 used: get_to_number(character varying,character varying)
level: 1; PL/pgSQL function: 8192 total in 1 blocks; 4504 free (1 chunks); 3688 used: <FUNCTION>
level: 1; PL/pgSQL function: 16384 total in 2 blocks; 6616 free (4 chunks); 9768 used: <FUNCTION2>
level: 1; Sequence values: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
level: 1; Btree proof lookup cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
level: 1; PL/pgSQL function: 16384 total in 2 blocks; 5752 free (5 chunks); 10632 used: get_date_interval(character varying,numeric)
level: 1; PL/pgSQL function: 32768 total in 3 blocks; 16608 free (5 chunks); 16160 used: <FUNCTION3>
level: 1; Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
level: 1; RI compare cache: 16384 total in 2 blocks; 6656 free (3 chunks); 9728 used
level: 1; RI query cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
level: 1; RI constraint cache: 40648 total in 2 blocks; 2616 free (0 chunks); 38032 used
level: 1; PL/pgSQL function: 8192 total in 1 blocks; 4592 free (1 chunks); 3600 used: get_systimestamp()
level: 1; Type information cache: 24376 total in 2 blocks; 2616 free (0 chunks); 21760 used
level: 1; PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
level: 1; PLpgSQL cast expressions: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
level: 1; PL/pgSQL function: 16384 total in 2 blocks; 5992 free (4 chunks); 10392 used: <FUNCTION4>
level: 1; Function stat entries: 16384 total in 2 blocks; 6640 free (2 chunks); 9744 used
level: 1; CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
level: 1; Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
level: 1; PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
level: 1; Prepared Queries: 16384 total in 2 blocks; 6656 free (3 chunks); 9728 used
level: 1; TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
level: 1; RowDescriptionContext: 57400 total in 3 blocks; 24272 free (7 chunks); 33128 used
level: 1; MessageContext: 8192 total in 1 blocks; 6888 free (1 chunks); 1304 used
level: 1; Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
level: 1; smgr relation table: 2.097.152 total in 9 blocks; 269224 free (33 chunks); 1827928 used
level: 1; TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
level: 1; Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
level: 1; TopPortalContext: 8192 total in 1 blocks; 7928 free (2 chunks); 264 used
level: 1; Relcache by OID: 1.048.576 total in 8 blocks; 500672 free (16 chunks); 547904 used
level: 1; CacheMemoryContext: 69.478.096 total in 208 blocks; 44.098.912 free (106056 chunks); 25.379.184 used
level: 2; CachedPlanSource: 4096 total in 3 blocks; 1960 free (1 chunks); 2136 used: <INSERT>
level: 3; unnamed prepared statement: 16384 total in 2 blocks; 6824 free (1 chunks); 9560 used
level: 2; CachedPlan: 8.420.408 total in 15 blocks; 1409824 free (1 chunks); 7010584 used: <SELECT>
level: 2; index info: 2048 total in 2 blocks; 528 free (1 chunks); 1520 used: pg_toast_295195_index
level: 2; CachedPlan: 8192 total in 4 blocks; 2480 free (0 chunks); 5712 used: <DELETE>
level: 2; index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: pg_sequence_seqrelid_index
level: 2; CachedPlan: 2048 total in 2 blocks; 280 free (0 chunks); 1768 used: <OTHER UNIQUE SQL>
level: 2; CachedPlan: 2048 total in 2 blocks; 528 free (0 chunks); 1520 used: <OTHER UNIQUE SQL>
level: 2; CachedPlan: 2048 total in 2 blocks; 16 free (0 chunks); 2032 used: <OTHER UNIQUE SQL>
level: 2; CachedPlan: 2048 total in 2 blocks; 160 free (1 chunks); 1888 used: <OTHER UNIQUE SQL>
level: 2; CachedPlan: 2048 total in 2 blocks; 528 free (0 chunks); 1520 used: <OTHER UNIQUE SQL>
level: 2; index info: 3072 total in 2 blocks; 696 free (1 chunks); 2376 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: <OTHER UNIQUE INDEX NAME>
level: 2; CachedExpression: 1024 total in 1 blocks; 488 free (0 chunks); 536 used
level: 2; CachedPlan: 2048 total in 2 blocks; 208 free (1 chunks); 1840 used: TO_NUMBER(p_input, p_fmt)
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 856 free (1 chunks); 2216 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 920 free (0 chunks); 1128 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3544 total in 3 blocks; 960 free (1 chunks); 2584 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 4096 total in 3 blocks; 1912 free (3 chunks); 2184 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 840 free (0 chunks); 1208 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 504 free (0 chunks); 1544 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 856 free (1 chunks); 2216 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 4024 total in 3 blocks; 816 free (1 chunks); 3208 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 7032 total in 4 blocks; 1928 free (2 chunks); 5104 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 4744 total in 3 blocks; 240 free (0 chunks); 4504 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 7032 total in 4 blocks; 1928 free (2 chunks); 5104 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3304 total in 3 blocks; 784 free (1 chunks); 2520 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1080 free (1 chunks); 1992 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3304 total in 3 blocks; 784 free (1 chunks); 2520 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 448 free (1 chunks); 1600 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 8192 total in 4 blocks; 3824 free (2 chunks); 4368 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 544 free (2 chunks); 1504 used: <OTHER UNIQUE INDEX NAME>
level: 2; CachedPlan: 2048 total in 2 blocks; 528 free (0 chunks); 1520 used: v_interval::INTERVAL
level: 2; CachedPlan: 2048 total in 2 blocks; 528 free (0 chunks); 1520 used: (v_interval IS NOT NULL)
level: 2; CachedPlan: 2048 total in 2 blocks; 144 free (1 chunks); 1904 used: v_interval := p_interval_length || ' second'
level: 2; CachedPlan: 2048 total in 2 blocks; 224 free (1 chunks); 1824 used: (p_interval = 'SECOND')
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1096 free (2 chunks); 1976 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1160 free (1 chunks); 1912 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1096 free (2 chunks); 1976 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1160 free (1 chunks); 1912 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1096 free (2 chunks); 1976 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1160 free (1 chunks); 1912 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1096 free (2 chunks); 1976 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1160 free (1 chunks); 1912 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1080 free (1 chunks); 1992 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1080 free (1 chunks); 1992 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1080 free (1 chunks); 1992 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1080 free (1 chunks); 1992 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1080 free (1 chunks); 1992 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 1; 4141 more child contexts containing 12.748.648 total in 8713 blocks; 3.899.520 free (3559 chunks); 8.849.128 used
level: 1; WAL record construction: 49768 total in 2 blocks; 6360 free (0 chunks); 43408 used
level: 1; PrivateRefCount: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
level: 1; MdSmgr: 524.288 total in 7 blocks; 412.640 free (8661 chunks); 111648 used
level: 1; LOCALLOCK hash: 1.048.576 total in 8 blocks; 215936 free (27 chunks); 832640 used
level: 1; Timezones: 104120 total in 2 blocks; 2616 free (0 chunks); 101504 used
level: 1; ErrorContext: 8192 total in 1 blocks; 7928 free (4 chunks); 264 used
Grand total: 100.635.144 bytes in 9342 blocks; 53.175.720 free (127010 chunks); 47459424 used
postgresql

1 respostas

  1. Best Answer

    Como o TopMemoryContext tem apenas 5 MB, mas o total geral é de 100 MB?

    O tamanho de um contexto de memória não inclui o tamanho dos contextos de memória subordinados.

    CacheMemoryContext tem 69 MB, mas 44 MB são gratuitos e 25 MB são usados. O que há nesses 25 MB?

    src/backend/utils/mmgr/READMEdiz lhe:

    CacheMemoryContext --- permanent storage for relcache, catcache, and
related modules.  This will never be reset or deleted, either, so it's
not truly necessary to distinguish it from TopMemoryContext.  But it
seems worthwhile to maintain the distinction for debugging purposes.
(Note: CacheMemoryContext has child contexts with shorter lifespans.
For example, a child context is the best place to keep the subsidiary
storage associated with a relcache entry; that way we can free rule
parsetrees and so forth easily, without having to depend on constructing
a reliable version of freeObject().)

    Portanto, são metadados de tabela armazenados em cache. Você tem muitas mesas? E em relação ao espaço livre: você cria e elimina tabelas com frequência?

    Esses 44 MB gratuitos serão devolvidos ao sistema enquanto a sessão estiver ativa?

    Não.

    Quando eu encerrar a sessão, ela liberará 216 MB de RAM (número pc_proctab) ou 100 MB (número pg_log_backend_memory_contexts) ou outro número?

    Isso liberará 100 MB do total geral, além da pouca memória que o processo retém fora dos contextos de memória.

    • 2

relate perguntas