Re: Memory leak (possibly connected to postgis) leading to server crash
Roman Cervenak <[email protected]> Fri, 13 Dec 2019 11:51:55 +0100
| Newsgroups | gmane.comp.db.postgresql.bugs |
|---|---|
| Message-ID | <CAGjExY2xQa8hxEYBTDDv6Y66V2=D0HEDsmLt42ZVupC3BxsLsQ@mail.gmail.com> |
With "postgresql-12-dbgsym" installed, call MemoryContextStats(TopMemoryContext) succeeded on postgres backend process with high memory consumption. I cut it from server logs, see attached file (I have redacted queries and table structures, I left SELECT parts where is visible that geometric operations are involved). Regards, Roman Cervenak On Wed, Dec 11, 2019 at 5:49 PM Tom Lane <[email protected]> wrote: > Roman Cervenak <[email protected]> writes: > > I am trying to do > > (gdb) p MemoryContextStats(TopMemoryContext) > > I think what you wanted to do is "call > MemoryContextStats(TopMemoryContext)". > "p" means "print". > > regards, tom lane >
pg_memory_context.txt
(text/plain, 16.9 KB)
TopMemoryContext: 135832 total in 7 blocks; 16424 free (23 chunks); 119408 used
TopTransactionContext: 8192 total in 1 blocks; 7424 free (0 chunks); 768 used
ExecutorState: 8192 total in 1 blocks; 304 free (0 chunks); 7888 used
SQL function: 49208 total in 4 blocks; 12608 free (4 chunks); 36600 used: z
ExprContext: 8192 total in 1 blocks; 7936 free (1 chunks); 256 used
SPI Exec: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
SPI Proc: 8192 total in 1 blocks; 6592 free (0 chunks); 1600 used
SPI TupTable: 8192 total in 1 blocks; 6288 free (0 chunks); 1904 used
PLpgSQL per-statement data: 8192 total in 1 blocks; 7648 free (0 chunks); 544 used
PLpgSQL per-statement data: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
expanded array: 1024 total in 1 blocks; 352 free (0 chunks); 672 used
pgstat TabStatusArray lookup hash table: 8192 total in 1 blocks; 416 free (0 chunks); 7776 used
PL/pgSQL function: 8192 total in 1 blocks; 1624 free (1 chunks); 6568 used: text_is_latin(text)
PL/pgSQL function: 65536 total in 4 blocks; 26952 free (3 chunks); 38584 used: [redacted]
PL/pgSQL function: 32768 total in 3 blocks; 9640 free (2 chunks); 23128 used: [redacted]
PL/pgSQL function: 65536 total in 4 blocks; 31784 free (1 chunks); 33752 used: [redacted]
PL/pgSQL function: 32768 total in 3 blocks; 18376 free (2 chunks); 14392 used: [redacted]
PL/pgSQL function: 32768 total in 3 blocks; 10648 free (2 chunks); 22120 used: [redacted]
PL/pgSQL function: 65536 total in 4 blocks; 9176 free (3 chunks); 56360 used: [redacted]
PL/pgSQL function: 65536 total in 4 blocks; 32264 free (2 chunks); 33272 used: [redacted]
PL/pgSQL function: 32768 total in 3 blocks; 2896 free (2 chunks); 29872 used: [redacted]
Type information cache: 24352 total in 2 blocks; 2624 free (0 chunks); 21728 used
PL/pgSQL function: 32768 total in 3 blocks; 3704 free (2 chunks); 29064 used: [redacted]
PL/pgSQL function: 65536 total in 4 blocks; 19816 free (2 chunks); 45720 used: [redacted]
Record information cache: 8192 total in 1 blocks; 1584 free (0 chunks); 6608 used
PL/pgSQL function: 32768 total in 3 blocks; 15496 free (5 chunks); 17272 used: [redacted]
TableSpace cache: 8192 total in 1 blocks; 2096 free (0 chunks); 6096 used
Operator lookup cache: 24576 total in 2 blocks; 10760 free (3 chunks); 13816 used
PLpgSQL cast info: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
PLpgSQL cast cache: 8192 total in 1 blocks; 1584 free (0 chunks); 6608 used
PL/pgSQL function: 32768 total in 3 blocks; 10592 free (5 chunks); 22176 used: [redacted]
Rendezvous variable hash: 8192 total in 1 blocks; 560 free (0 chunks); 7632 used
PLpgSQL function hash: 24528 total in 2 blocks; 2624 free (0 chunks); 21904 used
CFuncHash: 8192 total in 1 blocks; 560 free (0 chunks); 7632 used
RowDescriptionContext: 8192 total in 1 blocks; 3824 free (2 chunks); 4368 used
MessageContext: 8192 total in 1 blocks; 6752 free (0 chunks); 1440 used
Operator class cache: 8192 total in 1 blocks; 560 free (0 chunks); 7632 used
smgr relation table: 32768 total in 3 blocks; 8608 free (8 chunks); 24160 used
TransactionAbortContext: 32768 total in 1 blocks; 32512 free (0 chunks); 256 used
Portal hash: 8192 total in 1 blocks; 560 free (0 chunks); 7632 used
TopPortalContext: 8192 total in 1 blocks; 7392 free (0 chunks); 800 used
PortalContext: 3072 total in 2 blocks; 920 free (1 chunks); 2152 used: <unnamed portal 124258>
ExecutorState: 65536 total in 4 blocks; 18856 free (0 chunks); 46680 used
TupleSort main: 32824 total in 2 blocks; 6752 free (0 chunks); 26072 used
Caller tuples: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
TupleSort main: 32824 total in 2 blocks; 4272 free (0 chunks); 28552 used
Caller tuples: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
PortalContext: 1024 total in 1 blocks; 72 free (0 chunks); 952 used:
ExecutorState: 32824 total in 3 blocks; 3576 free (0 chunks); 29248 used
printtup: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
Table function arguments: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
Relcache by OID: 16384 total in 2 blocks; 1432 free (2 chunks); 14952 used
CacheMemoryContext: 2442624 total in 25 blocks; 952656 free (1 chunks); 1489968 used
CachedPlan: 17408 total in 5 blocks; 1264 free (1 chunks); 16144 used: SELECT ST_SimplifyPreserveTopology(ST_LineMerge(ST_Collect(td.geom)),simp), [redacted]
CachedPlan: 8192 total in 4 blocks; 3544 free (0 chunks); 4648 used: [redacted]
CachedPlanSource: 8192 total in 4 blocks; 1816 free (0 chunks); 6376 used: SELECT [redacted]
unnamed prepared statement: 32768 total in 3 blocks; 13688 free (2 chunks); 19080 used
CachedPlan: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: SELECT false
CachedPlanSource: 2048 total in 2 blocks; 264 free (0 chunks); 1784 used: SELECT false
CachedPlanQuery: 2048 total in 2 blocks; 976 free (1 chunks); 1072 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used: pg_toast_4874580_index
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used: pg_toast_4874640_index
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used: pg_toast_4874698_index
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used: pg_toast_14283960_index
CachedPlan: 35840 total in 6 blocks; 4360 free (1 chunks); 31480 used: SELECT [redacted]
CachedPlan: 8192 total in 4 blocks; 2408 free (0 chunks); 5784 used: SELECT [redacted]
CachedPlan: 17408 total in 5 blocks; 1248 free (1 chunks); 16160 used: SELECT ST_SimplifyPreserveTopology(ST_LineMerge(ST_Collect(td.geom)),simp), [redacted]
CachedPlan: 8192 total in 4 blocks; 3992 free (0 chunks); 4200 used: SELECT [redacted]
CachedPlan: 8192 total in 4 blocks; 760 free (0 chunks); 7432 used: SELECT ST_SimplifyPreserveTopology(f.geometry, simp), [redacted]
CachedPlan: 265216 total in 9 blocks; 114000 free (0 chunks); 151216 used: SELECT ST_SimplifyPreserveTopology(ST_LineMerge(ST_Collect(t.geom)),simp) [redacted]
CachedPlan: 17408 total in 5 blocks; 5472 free (1 chunks); 11936 used: SELECT ST_SimplifyPreserveTopology(ST_LineMerge(ST_Collect(t.geometry)),simp), [redacted]
CachedPlan: 8192 total in 4 blocks; 3048 free (0 chunks); 5144 used: SELECT ST_SimplifyPreserveTopology(w.geometry,simp), [redacted]
CachedPlan: 8192 total in 4 blocks; 2664 free (0 chunks); 5528 used: SELECT ST_SimplifyPreserveTopology(w.geometry,simp), [redacted]
CachedPlan: 33792 total in 6 blocks; 9744 free (1 chunks); 24048 used: SELECT ST_SimplifyPreserveTopology(t.geometry, simp), [redacted]
CachedPlan: 8192 total in 4 blocks; 3040 free (0 chunks); 5152 used: SELECT ST_SimplifyPreserveTopology(a.geometry, simp) [redacted]
CachedPlan: 8192 total in 4 blocks; 4000 free (0 chunks); 4192 used: SELECT p.geom [redacted]
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used: pg_toast_4874678_index
CachedPlan: 2048 total in 2 blocks; 576 free (1 chunks); 1472 used: SELECT true
CachedPlanSource: 2048 total in 2 blocks; 264 free (0 chunks); 1784 used: SELECT true
CachedPlanQuery: 2048 total in 2 blocks; 976 free (1 chunks); 1072 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 200 free (0 chunks); 1848 used: SELECT convert_to(input, 'iso-8859-15')
CachedPlanSource: 2048 total in 2 blocks; 48 free (0 chunks); 2000 used: SELECT convert_to(input, 'iso-8859-15')
CachedPlanQuery: 2048 total in 2 blocks; 648 free (1 chunks); 1400 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
index info: 2048 total in 2 blocks; 952 free (1 chunks); 1096 used: [redacted]
index info: 4096 total in 3 blocks; 1664 free (0 chunks); 2432 used: [redacted]
index info: 2048 total in 2 blocks; 744 free (2 chunks); 1304 used: [redacted]
index info: 2048 total in 2 blocks; 696 free (1 chunks); 1352 used: [redacted]
index info: 2048 total in 2 blocks; 376 free (0 chunks); 1672 used: [redacted]
index info: 4096 total in 3 blocks; 1632 free (1 chunks); 2464 used: [redacted]
index info: 2048 total in 2 blocks; 728 free (2 chunks); 1320 used: [redacted]
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used: [redacted]
CachedPlanSource: 32768 total in 6 blocks; 1696 free (0 chunks); 31072 used: SELECT t.geometry, [redacted]
CachedPlanQuery: 32768 total in 6 blocks; 8240 free (1 chunks); 24528 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 72 free (0 chunks); 1976 used: SELECT ('SET LOCAL search_path to ' || sch)
CachedPlanSource: 2048 total in 2 blocks; 152 free (0 chunks); 1896 used: SELECT ('SET LOCAL search_path to ' || sch)
CachedPlanQuery: 2048 total in 2 blocks; 520 free (1 chunks); 1528 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 608 free (0 chunks); 1440 used: SELECT schemas
CachedPlanSource: 2048 total in 2 blocks; 512 free (0 chunks); 1536 used: SELECT schemas
CachedPlanQuery: 2048 total in 2 blocks; 1008 free (2 chunks); 1040 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 320 free (0 chunks); 1728 used: SELECT [redacted]
CachedPlanSource: 4096 total in 3 blocks; 1864 free (2 chunks); 2232 used: SELECT [redacted]
CachedPlanQuery: 2048 total in 2 blocks; 96 free (1 chunks); 1952 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 312 free (0 chunks); 1736 used: SELECT [redacted]
CachedPlanSource: 2048 total in 2 blocks; 232 free (0 chunks); 1816 used: SELECT [redacted]
CachedPlanQuery: 2048 total in 2 blocks; 728 free (1 chunks); 1320 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 312 free (0 chunks); 1736 used: SELECT [redacted]
CachedPlanSource: 2048 total in 2 blocks; 232 free (0 chunks); 1816 used: SELECT [redacted]
CachedPlanQuery: 2048 total in 2 blocks; 728 free (1 chunks); 1320 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 312 free (0 chunks); 1736 used: SELECT [redacted]
CachedPlanSource: 2048 total in 2 blocks; 232 free (0 chunks); 1816 used: SELECT [redacted]
CachedPlanQuery: 2048 total in 2 blocks; 728 free (1 chunks); 1320 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 312 free (0 chunks); 1736 used: SELECT [redacted]
CachedPlanSource: 2048 total in 2 blocks; 232 free (0 chunks); 1816 used: SELECT [redacted]
CachedPlanQuery: 2048 total in 2 blocks; 728 free (1 chunks); 1320 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 8 free (0 chunks); 2040 used: SELECT [redacted]
CachedPlanSource: 4096 total in 3 blocks; 1776 free (1 chunks); 2320 used: SELECT [redacted]
CachedPlanQuery: 2048 total in 2 blocks; 456 free (1 chunks); 1592 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
index info: 2048 total in 2 blocks; 728 free (2 chunks); 1320 used: [redacted]
CachedPlanSource: 8192 total in 4 blocks; 2088 free (0 chunks); 6104 used: SELECT t.geom, [redacted]
CachedPlanQuery: 8192 total in 4 blocks; 3328 free (1 chunks); 4864 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 72 free (0 chunks); 1976 used: SELECT ('SET LOCAL search_path to ' || sch)
CachedPlanSource: 2048 total in 2 blocks; 152 free (0 chunks); 1896 used: SELECT ('SET LOCAL search_path to ' || sch)
CachedPlanQuery: 2048 total in 2 blocks; 520 free (1 chunks); 1528 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 608 free (0 chunks); 1440 used: SELECT schemas
CachedPlanSource: 2048 total in 2 blocks; 512 free (0 chunks); 1536 used: SELECT schemas
CachedPlanQuery: 2048 total in 2 blocks; 1008 free (2 chunks); 1040 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 4096 total in 3 blocks; 1384 free (0 chunks); 2712 used: SELECT [redacted]
CachedPlanSource: 4096 total in 3 blocks; 1264 free (0 chunks); 2832 used: SELECT [redacted]
CachedPlanQuery: 4096 total in 3 blocks; 1896 free (1 chunks); 2200 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 8 free (0 chunks); 2040 used: SELECT [redacted]
CachedPlanSource: 4096 total in 3 blocks; 1776 free (1 chunks); 2320 used: SELECT [redacted]
CachedPlanQuery: 2048 total in 2 blocks; 456 free (1 chunks); 1592 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used: pg_toast_14228756_index
CachedPlanSource: 16384 total in 5 blocks; 824 free (0 chunks); 15560 used: SELECT ST_SimplifyPreserveTopology(ST_LineMerge(ST_Collect(td.geom)),simp), [redacted]
CachedPlanQuery: 16384 total in 5 blocks; 4464 free (1 chunks); 11920 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 312 free (0 chunks); 1736 used: SELECT [redacted]
CachedPlanSource: 2048 total in 2 blocks; 232 free (0 chunks); 1816 used: SELECT [redacted]
CachedPlanQuery: 2048 total in 2 blocks; 728 free (1 chunks); 1320 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 72 free (0 chunks); 1976 used: SELECT ('SET search_path to ' || sch)
CachedPlanSource: 2048 total in 2 blocks; 152 free (0 chunks); 1896 used: SELECT ('SET search_path to ' || sch)
CachedPlanQuery: 2048 total in 2 blocks; 520 free (1 chunks); 1528 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 608 free (0 chunks); 1440 used: SELECT schemas
CachedPlanSource: 2048 total in 2 blocks; 512 free (0 chunks); 1536 used: SELECT schemas
CachedPlanQuery: 2048 total in 2 blocks; 1008 free (2 chunks); 1040 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 88 free (0 chunks); 1960 used: SELECT [redacted]
CachedPlanSource: 4096 total in 3 blocks; 1952 free (2 chunks); 2144 used: SELECT [redacted]
CachedPlanQuery: 2048 total in 2 blocks; 712 free (1 chunks); 1336 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
CachedPlan: 2048 total in 2 blocks; 8 free (0 chunks); 2040 used: SELECT [redacted]
CachedPlanSource: 4096 total in 3 blocks; 1776 free (1 chunks); 2320 used: SELECT [redacted]
CachedPlanQuery: 2048 total in 2 blocks; 456 free (1 chunks); 1592 used
SPI Plan: 1024 total in 1 blocks; 608 free (0 chunks); 416 used
index info: 4096 total in 3 blocks; 1664 free (0 chunks); 2432 used: [redacted]
index info: 2048 total in 2 blocks; 728 free (2 chunks); 1320 used: [redacted]
index info: 2048 total in 2 blocks; 680 free (1 chunks); 1368 used: [redacted]
CachedPlanSource: 4096 total in 3 blocks; 680 free (0 chunks); 3416 used: SELECT r.geometry, [redacted]
CachedPlanQuery: 4096 total in 3 blocks; 808 free (1 chunks); 3288 used
268 more child contexts containing 886784 total in 635 blocks; 282872 free (219 chunks); 603912 used
WAL record construction: 49768 total in 2 blocks; 6368 free (0 chunks); 43400 used
PrivateRefCount: 8192 total in 1 blocks; 2624 free (0 chunks); 5568 used
MdSmgr: 8192 total in 1 blocks; 5168 free (4 chunks); 3024 used
LOCALLOCK hash: 8192 total in 1 blocks; 560 free (0 chunks); 7632 used
Timezones: 104120 total in 2 blocks; 2624 free (0 chunks); 101496 used
ErrorContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
Grand total: 5683464 bytes in 1082 blocks; 2027536 free (362 chunks); 3655928 used