Postgres Pro
Downloads
Home   >   mailing lists

Re: strange slow query - lost lot of time somewhere - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: strange slow query - lost lot of time somewhere
Date
Msg-id CAFj8pRCcq3UUy5D9Spxn-kxHomaME9xAQW=h1vjKa=kv48oFDw@mail.gmail.com
Whole thread Raw
In response to RE: strange slow query - lost lot of time somewhere  (Jakub Wartak <Jakub.Wartak@tomtom.com>)
Responses RE: strange slow query - lost lot of time somewhere
List pgsql-hackers
Tree view


st 4. 5. 2022 v 16:08 odesílatel Jakub Wartak <Jakub.Wartak@tomtom.com> napsal:

Additional three ways to figure that one (all are IMHO production safe):
a) already mentioned perf with --call-graph dwarf -p PID
b) strace -p PID -e 'mmap' # verify if mmap() NULL is not having MAP_ANONYMOUS flag, size of mmap() request will somehow match work_mem sizing
c) gdb -p PID and then breakpoint for mmap and verify each mmap() # check MAP_ANONYMOUS as above


I have not debug symbols, so I have not more details now

Breakpoint 1 at 0x7f557f0c16c0
(gdb) c
Continuing.

Breakpoint 1, 0x00007f557f0c16c0 in mmap64 () from /lib64/libc.so.6
(gdb) bt
#0  0x00007f557f0c16c0 in mmap64 () from /lib64/libc.so.6
#1  0x00007f557f04dd91 in sysmalloc () from /lib64/libc.so.6
#2  0x00007f557f04eaa9 in _int_malloc () from /lib64/libc.so.6
#3  0x00007f557f04fb1e in malloc () from /lib64/libc.so.6
#4  0x0000000000932134 in AllocSetAlloc ()
#5  0x00000000009376cf in MemoryContextAllocExtended ()
#6  0x00000000006ad915 in ExecInitMemoize ()
#7  0x000000000068dc02 in ExecInitNode ()
#8  0x00000000006b37ff in ExecInitNestLoop ()
#9  0x000000000068dc56 in ExecInitNode ()
#10 0x00000000006b37ff in ExecInitNestLoop ()
#11 0x000000000068dc56 in ExecInitNode ()
#12 0x00000000006b37de in ExecInitNestLoop ()
#13 0x000000000068dc56 in ExecInitNode ()
#14 0x00000000006b37de in ExecInitNestLoop ()
#15 0x000000000068dc56 in ExecInitNode ()
#16 0x0000000000687e4d in standard_ExecutorStart ()
#17 0x00007f5579b5ad2d in pgss_ExecutorStart () from /usr/pgsql-14/lib/pg_stat_statements.so
#18 0x000000000062e643 in ExplainOnePlan ()
#19 0x000000000062e83d in ExplainOneQuery ()
#20 0x000000000062ee6f in ExplainQuery ()
#21 0x00000000007f9b15 in standard_ProcessUtility ()
#22 0x00007f5579b599c7 in pgss_ProcessUtility () from /usr/pgsql-14/lib/pg_stat_statements.so
#23 0x00000000007f7fef in PortalRunUtility ()
#24 0x00000000007f83af in FillPortalStore ()
#25 0x00000000007f86dd in PortalRun ()
#26 0x00000000007f48cb in exec_simple_query ()
#27 0x00000000007f610e in PostgresMain ()
#28 0x0000000000776b8a in ServerLoop ()
#29 0x0000000000777a03 in PostmasterMain ()
#30 0x00000000004fe413 in main ()
(gdb) p
The history is empty.
(gdb) c
Continuing.

Breakpoint 1, 0x00007f557f0c16c0 in mmap64 () from /lib64/libc.so.6
(gdb) bt
#0  0x00007f557f0c16c0 in mmap64 () from /lib64/libc.so.6
#1  0x00007f557f04dd91 in sysmalloc () from /lib64/libc.so.6
#2  0x00007f557f04eaa9 in _int_malloc () from /lib64/libc.so.6
#3  0x00007f557f04fb1e in malloc () from /lib64/libc.so.6
#4  0x0000000000932134 in AllocSetAlloc ()
#5  0x00000000009376cf in MemoryContextAllocExtended ()
#6  0x00000000006ad915 in ExecInitMemoize ()
#7  0x000000000068dc02 in ExecInitNode ()
#8  0x00000000006b37ff in ExecInitNestLoop ()
#9  0x000000000068dc56 in ExecInitNode ()
#10 0x00000000006b37ff in ExecInitNestLoop ()
#11 0x000000000068dc56 in ExecInitNode ()
#12 0x00000000006b37de in ExecInitNestLoop ()
#13 0x000000000068dc56 in ExecInitNode ()
#14 0x0000000000687e4d in standard_ExecutorStart ()
#15 0x00007f5579b5ad2d in pgss_ExecutorStart () from /usr/pgsql-14/lib/pg_stat_statements.so
#16 0x000000000062e643 in ExplainOnePlan ()
#17 0x000000000062e83d in ExplainOneQuery ()
#18 0x000000000062ee6f in ExplainQuery ()
#19 0x00000000007f9b15 in standard_ProcessUtility ()
#20 0x00007f5579b599c7 in pgss_ProcessUtility () from /usr/pgsql-14/lib/pg_stat_statements.so
#21 0x00000000007f7fef in PortalRunUtility ()
#22 0x00000000007f83af in FillPortalStore ()
#23 0x00000000007f86dd in PortalRun ()
#24 0x00000000007f48cb in exec_simple_query ()
#25 0x00000000007f610e in PostgresMain ()
#26 0x0000000000776b8a in ServerLoop ()
#27 0x0000000000777a03 in PostmasterMain ()
#28 0x00000000004fe413 in main ()
(gdb) c
Continuing.

there was 2 hits of mmap

Regards

Pavel


 
[1] - https://www.postgresql.org/message-id/CAFj8pRAo5CrF8mpPxMvnBYFSqu4HYDqRsQnLqGphckNHkHosFg%40mail.gmail.com

-J.

pgsql-hackers by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: strange slow query - lost lot of time somewhere
Next
From: Tom Lane
Date:
Subject: Re: fix cost subqueryscan wrong parallel cost