Re: [HACKERS] strange behavior of UPDATE

Bruce Momjian wrote:
> 
> OK, can you attach to the running process and tell us what functions it
> is running.  That would help.
> 
> [Charset iso-8859-2 unsupported, filtering to ASCII...]
> > Hi,
> >
> > recently I tried to reproduce some benchmark results
> > when I discovered a very strange behavior. I did
> > my tests with the current snapshot of last week,
> > but other people who have performed the same bench-
> > mark with postgresql-6.4-2 reported the same problems.
> >
> > The setup is pretty simple: one table with 13
> > integer and 7 char(20) columns. For every column
> > an index is created. The postmaster is started with
> > -o -F and before each query a 'vacuum analyze' is
> > performed.
> >
> > When loading 100.000 rows into the table
> > everything works ok. Selects and updates
> > are reasonable fast. But when loading
> > 1.000.000 rows the select statements still
> > work, but a simple update statement
> > shows this strange behavior. A never ending
> > disk-activity starts. Memory consumption
> > increases up to the physical limit (384 MB)
> > whereas the postmaster uses only a few %
> > of CPU time. After 1 hour I killed the post-
> > master.
> >
> > It would be nice, if this could be fixed.
> > People from the german UNIX magazine IX
> > benchmarked Oracle, Informix and Sybase on Linux
> > and they claimed, that Postgres is totally unusable
> > because of this problem.
> >
> > If you need some additional info, just let me know.
> >
> >
> > Edmund


I can attach to the backend and print a backtrace. 
Is this what you expect ?


Edmund


(gdb) bt
#0  0x40186534 in __libc_read ()
#1  0x360 in ?? ()
#2  0x80de019 in mdread (reln=0x8222790, blocknum=1671,    buffer=0x40215c40 "ü\a\024\bđ\037") at md.c:413
#3  0x80dead5 in smgrread (which=0, reln=0x8222790, blocknum=1671,    buffer=0x40215c40 "ü\a\024\bđ\037") at
smgr.c:231
#4  0x80d5863 in ReadBufferWithBufferLock (reln=0x8222790, blockNum=1671,    bufferLockHeld=0) at bufmgr.c:292
#5  0x80d5758 in ReadBuffer (reln=0x8222790, blockNum=1671) at bufmgr.c:170
#6  0x8073153 in _bt_getbuf (rel=0x8222790, blkno=1671, access=0)   at nbtpage.c:337
#7  0x8074659 in _bt_searchr (rel=0x8222790, keysz=1, scankey=0x8236470,    bufP=0xbfffa36c, stack_in=0x849e498) at
nbtsearch.c:116
#8  0x8074547 in _bt_search (rel=0x8222790, keysz=1, scankey=0x8236470,    bufP=0xbfffa36c) at nbtsearch.c:52
#9  0x8070d31 in _bt_doinsert (rel=0x8222790, btitem=0x849e468,    index_is_unique=0 '\000', heapRel=0x8218c40) at
nbtinsert.c:65
#10 0x8073aea in btinsert (rel=0x8222790, datum=0x849e420,    nulls=0x849e408 " ", ht_ctid=0x82367d4,
heapRel=0x8218c40)at nbtree.c:369
 
#11 0x8109a13 in fmgr_c (finfo=0xbfffa40c, values=0xbfffa41c,    isNull=0xbfffa40b "") at fmgr.c:154
#12 0x8109cb7 in fmgr (procedureId=331) at fmgr.c:338
#13 0x806d540 in index_insert (relation=0x8222790, datum=0x849e420,    nulls=0x849e408 " ", heap_t_ctid=0x82367d4,
heapRel=0x8218c40)  at indexam.c:190
 
#14 0x80953a2 in ExecInsertIndexTuples (slot=0x8233368, tupleid=0x82367d4,    estate=0x8231740, is_update=1) at
execUtils.c:1210
#15 0x809293c in ExecReplace (slot=0x8233368, tupleid=0xbfffa540,    estate=0x8231740) at execMain.c:1472
#16 0x809255e in ExecutePlan (estate=0x8231740, plan=0x8231280,    operation=CMD_UPDATE, offsetTuples=0,
numberTuples=0,   direction=ForwardScanDirection, destfunc=0x8236350) at execMain.c:1086
 
#17 0x8091cae in ExecutorRun (queryDesc=0x8231728, estate=0x8231740,    feature=3, limoffset=0x0, limcount=0x0) at
execMain.c:359
#18 0x80e1098 in ProcessQueryDesc (queryDesc=0x8231728, limoffset=0x0,    limcount=0x0) at pquery.c:333
#19 0x80e10fe in ProcessQuery (parsetree=0x8220998, plan=0x8231280,    dest=Remote) at pquery.c:376
#20 0x80dfbb6 in pg_exec_query_dest (   query_string=0xbfffa67c "update bench set k500k = k500k + 1 where k100 = 30;
", dest=Remote, aclOverride=0) at postgres.c:742
#21 0x80dfab7 in pg_exec_query (   query_string=0xbfffa67c "update bench set k500k = k500k + 1 where k100 = 30;
") at postgres.c:642
#22 0x80e0abc in PostgresMain (argc=6, argv=0xbfffe704, real_argc=6,    real_argv=0xbffffcf4) at postgres.c:1610
---Type <return> to continue, or q <return> to quit---
#23 0x80cacaa in DoBackend (port=0x81c3c38) at postmaster.c:1584
#24 0x80ca77a in BackendStartup (port=0x81c3c38) at postmaster.c:1351
#25 0x80c9ec9 in ServerLoop () at postmaster.c:802
#26 0x80c9a0e in PostmasterMain (argc=6, argv=0xbffffcf4) at postmaster.c:596
#27 0x80a1836 in main (argc=6, argv=0xbffffcf4) at main.c:97
#28 0x400fbcb3 in __libc_start_main (main=0x80a17d0 <main>, argc=6,    argv=0xbffffcf4, init=0x8061878 <_init>,
fini=0x810f13c<_fini>,    rtld_fini=0x4000a350 <_dl_fini>, stack_end=0xbffffcec)   at
../sysdeps/generic/libc-start.c:78






-- 
Edmund Mergl          mailto:E.Mergl@bawue.de
Im Haldenhau 9        http://www.bawue.de/~mergl
70565 Stuttgart       fon: +49 711 747503
Germany