Hi.
I encountered a very strange behavior.
For any query (even a simple count(*) to one specific table (a small 30MB table with 3 indexes, without any specific data types - everything is standard out of the box vanilla Postgres) - the query hangs dead. Waited more than 24 hours - the query did not complete).
I get a backtrace, from it - I then examined the pg_multixact directory, which at the time of the problem had swelled to 900MB and had several thousand files.
I excluded long and inactive transactions, as well as prepared statements.
The workaround in the end was this - truncate the table (it was successful), then vacuum freeze each DB, and after that the files from pg_multixact disappeared.
What could it be? vacuum\freeze\mulitxact settings are default.
At the same time, the value pg_database.datminmxid=1
Could the problem with the hang be related to the many old files in pg_multixact ? (judging by the backtrace - yes)
postgresql 16.9
backtrace:
#0 0x00007f83840d3bfa in clock_nanosleep@GLIBC_2.2.5 () from /lib64/libc.so.6
#1 0x00007f83840d8847 in nanosleep () from /lib64/libc.so.6
#2 0x00000000005b416c in pg_usleep (microsec=1000) at ../port/pgsleep.c:50
#3 pg_usleep (microsec=1000) at ../port/pgsleep.c:41
#4 GetMultiXactIdMembers (from_pgupgrade=<optimized out>, isLockOnly=<optimized out>, members=0x7ffd65135b40, multi=66664135) at access/transam/multixact.c:1393
#5 GetMultiXactIdMembers (multi=66664135, members=0x7ffd65135b40, from_pgupgrade=<optimized out>, isLockOnly=<optimized out>) at access/transam/multixact.c:1225
#6 0x0000000000a99df9 in MultiXactIdGetUpdateXid.constprop.0 (xmax=<optimized out>, t_infomask=<optimized out>) at access/heap/heapam.c:7073
#7 0x0000000000572355 in HeapTupleGetUpdateXid (tuple=0x7f8377fdceb0) at access/heap/heapam.c:7114
#8 HeapTupleSatisfiesVacuumHorizon (htup=<optimized out>, buffer=496, dead_after=0x7ffd65135c1c) at access/heap/heapam_visibility.c:1350
#9 0x0000000000577eee in heap_prune_satisfies_vacuum (buffer=496, tup=0x7ffd65135c20, prstate=0x7ffd65135ec0) at access/heap/pruneheap.c:504
#10 heap_page_prune (relation=relation@entry=0x7f83738fed70, buffer=buffer@entry=496, vistest=vistest@entry=0xd845f0 <GlobalVisDataRels.lto_priv.0>, old_snap_xmin=<optimized out>, old_snap_ts=<optimized out>, nnewlpdead=nnewlpdead@entry=0x7ffd65136ad0, off_loc=0x0)
at access/heap/pruneheap.c:350
#11 0x0000000000578b91 in heap_page_prune_opt (relation=0x7f83738fed70, buffer=496) at access/heap/pruneheap.c:208
#12 0x00000000005625cf in heapgetpage (sscan=sscan@entry=0x1038218, block=block@entry=5) at access/heap/heapam.c:418
#13 0x0000000000563304 in heapgettup_pagemode (scan=scan@entry=0x1038218, dir=ForwardScanDirection, nkeys=0, key=0x0) at access/heap/heapam.c:885
#14 0x00000000005637e4 in heap_getnextslot (sscan=0x1038218, direction=<optimized out>, slot=0x1025410) at access/heap/heapam.c:1149
#15 0x0000000000727e8a in table_scan_getnextslot (slot=0x1025410, direction=ForwardScanDirection, sscan=<optimized out>) at executor/../../../src/include/access/tableam.h:1066
#16 SeqNext (node=0x1025280) at executor/nodeSeqscan.c:80
#17 0x000000000070bc41 in ExecProcNode (node=0x1025280) at executor/../../../src/include/executor/executor.h:273
#18 fetch_input_tuple (aggstate=aggstate@entry=0x1024c88) at executor/nodeAgg.c:562
#19 0x000000000070e313 in agg_retrieve_direct (aggstate=0x1024c88) at executor/nodeAgg.c:2460
#20 ExecAgg (pstate=0x1024c88) at executor/nodeAgg.c:2180
#21 0x00000000006f8512 in ExecProcNode (node=0x1024c88) at executor/../../../src/include/executor/executor.h:273
#22 ExecutePlan (execute_once=<optimized out>, dest=0x1073f50, direction=<optimized out>, numberTuples=0, sendTuples=<optimized out>, operation=CMD_SELECT, use_parallel_mode=<optimized out>, planstate=0x1024c88, estate=0x1024a70) at executor/execMain.c:1670
#23 standard_ExecutorRun (queryDesc=0x1042660, direction=<optimized out>, count=0, execute_once=<optimized out>) at executor/execMain.c:365
#24 0x00000000008c7cc5 in ExecutorRun (execute_once=<optimized out>, count=0, direction=ForwardScanDirection, queryDesc=0x1042660) at executor/execMain.c:309
#25 PortalRunSelect (portal=portal@entry=0xf96df0, forward=forward@entry=true, count=0, count@entry=9223372036854775807, dest=dest@entry=0x1073f50) at tcop/pquery.c:924
#26 0x00000000008c95c6 in PortalRun (portal=portal@entry=0xf96df0, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true, run_once=run_once@entry=true, dest=dest@entry=0x1073f50, altdest=altdest@entry=0x1073f50, qc=0x7ffd65136f90) at tcop/pquery.c:768
#27 0x00000000008ca650 in exec_simple_query (query_string=0xed8430 "select count(*) from \"InboxState\";") at tcop/postgres.c:1274
#28 0x00000000008cc9df in PostgresMain (dbname=<optimized out>, username=<optimized out>) at tcop/postgres.c:4637
#29 0x000000000083c244 in BackendRun (port=0xf2eb50, port=0xf2eb50) at postmaster/postmaster.c:4464
#30 BackendStartup (port=0xf2eb50) at postmaster/postmaster.c:4192
#31 ServerLoop () at postmaster/postmaster.c:1782
#32 0x0000000000832c3d in PostmasterMain (argc=3, argv=0xe936d0) at postmaster/postmaster.c:1466
#33 0x000000000051bf51 in main (argc=3, argv=0xe936d0) at main/main.c:198
I encountered a very strange behavior.
For any query (even a simple count(*) to one specific table (a small 30MB table with 3 indexes, without any specific data types - everything is standard out of the box vanilla Postgres) - the query hangs dead. Waited more than 24 hours - the query did not complete).
Similarly, the vacuum process to the table hangs.
Only Kill -9 with a full restart helpsI get a backtrace, from it - I then examined the pg_multixact directory, which at the time of the problem had swelled to 900MB and had several thousand files.
I excluded long and inactive transactions, as well as prepared statements.
The workaround in the end was this - truncate the table (it was successful), then vacuum freeze each DB, and after that the files from pg_multixact disappeared.
What could it be? vacuum\freeze\mulitxact settings are default.
At the same time, the value pg_database.datminmxid=1
Could the problem with the hang be related to the many old files in pg_multixact ? (judging by the backtrace - yes)
postgresql 16.9
backtrace:
#0 0x00007f83840d3bfa in clock_nanosleep@GLIBC_2.2.5 () from /lib64/libc.so.6
#1 0x00007f83840d8847 in nanosleep () from /lib64/libc.so.6
#2 0x00000000005b416c in pg_usleep (microsec=1000) at ../port/pgsleep.c:50
#3 pg_usleep (microsec=1000) at ../port/pgsleep.c:41
#4 GetMultiXactIdMembers (from_pgupgrade=<optimized out>, isLockOnly=<optimized out>, members=0x7ffd65135b40, multi=66664135) at access/transam/multixact.c:1393
#5 GetMultiXactIdMembers (multi=66664135, members=0x7ffd65135b40, from_pgupgrade=<optimized out>, isLockOnly=<optimized out>) at access/transam/multixact.c:1225
#6 0x0000000000a99df9 in MultiXactIdGetUpdateXid.constprop.0 (xmax=<optimized out>, t_infomask=<optimized out>) at access/heap/heapam.c:7073
#7 0x0000000000572355 in HeapTupleGetUpdateXid (tuple=0x7f8377fdceb0) at access/heap/heapam.c:7114
#8 HeapTupleSatisfiesVacuumHorizon (htup=<optimized out>, buffer=496, dead_after=0x7ffd65135c1c) at access/heap/heapam_visibility.c:1350
#9 0x0000000000577eee in heap_prune_satisfies_vacuum (buffer=496, tup=0x7ffd65135c20, prstate=0x7ffd65135ec0) at access/heap/pruneheap.c:504
#10 heap_page_prune (relation=relation@entry=0x7f83738fed70, buffer=buffer@entry=496, vistest=vistest@entry=0xd845f0 <GlobalVisDataRels.lto_priv.0>, old_snap_xmin=<optimized out>, old_snap_ts=<optimized out>, nnewlpdead=nnewlpdead@entry=0x7ffd65136ad0, off_loc=0x0)
at access/heap/pruneheap.c:350
#11 0x0000000000578b91 in heap_page_prune_opt (relation=0x7f83738fed70, buffer=496) at access/heap/pruneheap.c:208
#12 0x00000000005625cf in heapgetpage (sscan=sscan@entry=0x1038218, block=block@entry=5) at access/heap/heapam.c:418
#13 0x0000000000563304 in heapgettup_pagemode (scan=scan@entry=0x1038218, dir=ForwardScanDirection, nkeys=0, key=0x0) at access/heap/heapam.c:885
#14 0x00000000005637e4 in heap_getnextslot (sscan=0x1038218, direction=<optimized out>, slot=0x1025410) at access/heap/heapam.c:1149
#15 0x0000000000727e8a in table_scan_getnextslot (slot=0x1025410, direction=ForwardScanDirection, sscan=<optimized out>) at executor/../../../src/include/access/tableam.h:1066
#16 SeqNext (node=0x1025280) at executor/nodeSeqscan.c:80
#17 0x000000000070bc41 in ExecProcNode (node=0x1025280) at executor/../../../src/include/executor/executor.h:273
#18 fetch_input_tuple (aggstate=aggstate@entry=0x1024c88) at executor/nodeAgg.c:562
#19 0x000000000070e313 in agg_retrieve_direct (aggstate=0x1024c88) at executor/nodeAgg.c:2460
#20 ExecAgg (pstate=0x1024c88) at executor/nodeAgg.c:2180
#21 0x00000000006f8512 in ExecProcNode (node=0x1024c88) at executor/../../../src/include/executor/executor.h:273
#22 ExecutePlan (execute_once=<optimized out>, dest=0x1073f50, direction=<optimized out>, numberTuples=0, sendTuples=<optimized out>, operation=CMD_SELECT, use_parallel_mode=<optimized out>, planstate=0x1024c88, estate=0x1024a70) at executor/execMain.c:1670
#23 standard_ExecutorRun (queryDesc=0x1042660, direction=<optimized out>, count=0, execute_once=<optimized out>) at executor/execMain.c:365
#24 0x00000000008c7cc5 in ExecutorRun (execute_once=<optimized out>, count=0, direction=ForwardScanDirection, queryDesc=0x1042660) at executor/execMain.c:309
#25 PortalRunSelect (portal=portal@entry=0xf96df0, forward=forward@entry=true, count=0, count@entry=9223372036854775807, dest=dest@entry=0x1073f50) at tcop/pquery.c:924
#26 0x00000000008c95c6 in PortalRun (portal=portal@entry=0xf96df0, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true, run_once=run_once@entry=true, dest=dest@entry=0x1073f50, altdest=altdest@entry=0x1073f50, qc=0x7ffd65136f90) at tcop/pquery.c:768
#27 0x00000000008ca650 in exec_simple_query (query_string=0xed8430 "select count(*) from \"InboxState\";") at tcop/postgres.c:1274
#28 0x00000000008cc9df in PostgresMain (dbname=<optimized out>, username=<optimized out>) at tcop/postgres.c:4637
#29 0x000000000083c244 in BackendRun (port=0xf2eb50, port=0xf2eb50) at postmaster/postmaster.c:4464
#30 BackendStartup (port=0xf2eb50) at postmaster/postmaster.c:4192
#31 ServerLoop () at postmaster/postmaster.c:1782
#32 0x0000000000832c3d in PostmasterMain (argc=3, argv=0xe936d0) at postmaster/postmaster.c:1466
#33 0x000000000051bf51 in main (argc=3, argv=0xe936d0) at main/main.c:198
