Thread: postgresql infinite loop

postgresql infinite loop

From

郭勇

Date:

29 September 2016, 16:19:05

I have a postgresql infinite problem.

when inserting data, a postgresql process involve to infinite loop and cpu usage is 100%. perf top show that LWacquirelock is most costful.

Callstack is below.

Other processes are normal and can process insert operation.

But after some minutes, one another process involve to infinite loop.postgresql

I can reproduce the problem easily. About an hour, one of processes go into abnormal process firstly

postgresql version is 9.2.15

postgresql is running in a container which os is centos 7.2.1511. Os of physical machine is centos7.1.1503

#0 UnpinBuffer (buf=0x7fb47d11bdf8, fixOwner=<optimized out>) at bufmgr.c:1201

#1 0x000000000063947d in ReleaseAndReadBuffer (buffer=buffer@entry=2463, relation=relation@entry=0x7fb47cc58f98, blockNum=423) at bufmgr.c:1089

#2 0x0000000000482b6f in _bt_relandgetbuf (rel=rel@entry=0x7fb47cc58f98, obuf=obuf@entry=2463, blkno=<optimized out>, access=access@entry=1) at nbtpage.c:639

#3 0x00000000004863d8 in _bt_moveright (rel=rel@entry=0x7fb47cc58f98, buf=2463, keysz=keysz@entry=1, scankey=scankey@entry=0x178a350, nextkey=nextkey@entry=0 '\000', access=access@entry=1)

at nbtsearch.c:194

#4 0x000000000048669f in _bt_search (rel=rel@entry=0x7fb47cc58f98, keysz=keysz@entry=1, scankey=scankey@entry=0x178a350, nextkey=nextkey@entry=0 '\000', bufP=bufP@entry=0x7fff0e5f8064,

access=access@entry=2) at nbtsearch.c:86

#5 0x000000000048249f in _bt_doinsert (rel=rel@entry=0x7fb47cc58f98, itup=itup@entry=0x1788200, checkUnique=checkUnique@entry=UNIQUE_CHECK_YES, heapRel=heapRel@entry=0x7fb47cc581d8)

at nbtinsert.c:118

#6 0x000000000048520d in btinsert (fcinfo=<optimized out>) at nbtree.c:257

#7 0x0000000000728cfa in FunctionCall6Coll (flinfo=flinfo@entry=0x1758040, collation=collation@entry=0, arg1=arg1@entry=140413164162968, arg2=arg2@entry=140733434529104,

arg3=arg3@entry=140733434529360, arg4=arg4@entry=24658596, arg5=arg5@entry=140413164159448, arg6=1) at fmgr.c:1440

#8 0x000000000047f109 in index_insert (indexRelation=indexRelation@entry=0x7fb47cc58f98, values=values@entry=0x7fff0e5f8550, isnull=isnull@entry=0x7fff0e5f8650 "",

heap_t_ctid=heap_t_ctid@entry=0x17842a4, heapRelation=heapRelation@entry=0x7fb47cc581d8, checkUnique=checkUnique@entry=UNIQUE_CHECK_YES) at indexam.c:216

#9 0x000000000058db1d in ExecInsertIndexTuples (slot=slot@entry=0x1751b30, tupleid=tupleid@entry=0x17842a4, estate=estate@entry=0x1750fc0) at execUtils.c:1089

#10 0x000000000059adc7 in ExecInsert (canSetTag=1 '\001', estate=0x1750fc0, planSlot=0x1751b30, slot=0x1751b30) at nodeModifyTable.c:248

#11 ExecModifyTable (node=node@entry=0x17511e0) at nodeModifyTable.c:854

#12 0x0000000000584478 in ExecProcNode (node=node@entry=0x17511e0) at execProcnode.c:376

#13 0x0000000000581c40 in ExecutePlan (dest=0x176f238, direction=<optimized out>, numberTuples=0, sendTuples=0 '\000', operation=CMD_INSERT, planstate=0x17511e0, estate=0x1750fc0) at execMain.c:1411

#14 standard_ExecutorRun (queryDesc=0x1758850, direction=<optimized out>, count=0) at execMain.c:315

#15 0x000000000065e3ae in ProcessQuery (plan=<optimized out>,

sourceText=0x177ba80 "INSERT INTO token (id, expires, extra, valid, user_id, trust_id) VALUES ('d810c3f542414cfa86778249be70ab93', '2016-09-26T06:32:46.097976'::timestamp, '{\"token_data\": {\"token\": {\"methods\": [\"password\"]"..., params=0x0, dest=0x176f238, completionTag=0x7fff0e5f8c80 "") at pquery.c:185

#16 0x000000000065e5dd in PortalRunMulti (portal=portal@entry=0x1638380, isTopLevel=isTopLevel@entry=1 '\001', dest=dest@entry=0x176f238, altdest=altdest@entry=0x176f238,

completionTag=completionTag@entry=0x7fff0e5f8c80 "") at pquery.c:1275

#17 0x000000000065f1a8 in PortalRun (portal=0x1638380, count=9223372036854775807, isTopLevel=<optimized out>, dest=0x176f238, altdest=0x176f238, completionTag=0x7fff0e5f8c80 "") at pquery.c:812

#18 0x000000000065b100 in PostgresMain (argc=<optimized out>, argv=<optimized out>, dbname=<optimized out>, username=<optimized out>) at postgres.c:1073

#19 0x00000000006182c6 in BackendRun (port=0x163e9a0) at postmaster.c:3813

#20 BackendStartup (port=0x163e9a0) at postmaster.c:3484

#21 ServerLoop () at postmaster.c:1497

#22 0x0000000000619077 in PostmasterMain (argc=argc@entry=1, argv=argv@entry=0x1616690) at postmaster.c:1226

#23 0x000000000045bb45 in main (argc=1, argv=0x1616690) at main.c:230

Re: postgresql infinite loop

From

Tom Lane

Date:

29 September 2016, 16:26:08

郭 勇 <guoyongxhzhf@icloud.com> writes:
> I have a postgresql infinite problem. 
> when inserting data, a postgresql process involve to infinite loop and cpu usage is 100%. perf top show that
LWacquirelockis most costful.

> Callstack is below. 

Given that stack trace, I'd have to guess that this is caused by a
circular chain of right-links in a btree index.  How it got that
way is hard to say, but reindexing the index ought to fix it.

If you can show a recipe for producing an index that's broken
this way from a standing start, we'd be very interested.
        regards, tom lane