Re: strange slow query - lost lot of time somewhere

st 4. 5. 2022 v 16:08 odesílatel Jakub Wartak <Jakub(dot)Wartak(at)tomtom(dot)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.
>