Re: reindex and copy - deadlock? - Mailing list pgsql-performance

Hi All,

It happened again.
This time it hangs when we drop/create index.

Here is gdb info with --enable-debug postgres.

Thank you for your help!

postgres 24533 24327  2 Jun28 ?        00:39:11
postgres: postgres
xxx xxx.xxx.x.xxx COPY waiting
postgres 23508 24327  0 03:23 ?        00:00:00
postgres: postgres
xxx xxx.xxx.x.xx SELECT waiting
root     23662 22727  0 03:24 ?        00:00:00
/xxx/bin/psql -t -A -q xxx -U postgres -c set
sort_mem=131072; DROP INDEX xxx_mod_ac_did_cre_idx;
CREATE INDEX xxx_mod_ac_did_cre_idx ON
xxx_module_action USING btree (domain_id, created);
postgres 23663 24327  2 03:24 ?        00:04:40
postgres: postgres
xxx [local] CREATE INDEX
postgres 24252 24327  0 03:26 ?        00:00:00
postgres: postgres
xxx xxx.xxx.x.xx SELECT waiting

bash-2.05a$ gdb /xxx/bin/postgres
GNU gdb Red Hat Linux (5.2-2)
Copyright 2002 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General
Public License, and you
are
welcome to change it and/or distribute copies of it
under certain
conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB.  Type "show
warranty" for
details.
This GDB was configured as "i386-redhat-linux"...
(gdb) attach 23663
Attaching to program: /xxx/bin.Linux/postgres, process
23663
Reading symbols from /usr/lib/libz.so.1...done.
Loaded symbols for /usr/lib/libz.so.1
Reading symbols from /usr/lib/libreadline.so.4...done.
Loaded symbols for /usr/lib/libreadline.so.4
Reading symbols from /lib/libtermcap.so.2...done.
Loaded symbols for /lib/libtermcap.so.2
Reading symbols from /lib/libcrypt.so.1...done.
Loaded symbols for /lib/libcrypt.so.1
Reading symbols from /lib/libresolv.so.2...done.
Loaded symbols for /lib/libresolv.so.2
Reading symbols from /lib/libnsl.so.1...done.
Loaded symbols for /lib/libnsl.so.1
Reading symbols from /lib/libdl.so.2...done.
Loaded symbols for /lib/libdl.so.2
Reading symbols from /lib/i686/libm.so.6...done.
Loaded symbols for /lib/i686/libm.so.6
Reading symbols from /lib/i686/libc.so.6...done.
Loaded symbols for /lib/i686/libc.so.6
Reading symbols from /lib/ld-linux.so.2...done.
Loaded symbols for /lib/ld-linux.so.2
Reading symbols from /lib/libnss_files.so.2...done.
Loaded symbols for /lib/libnss_files.so.2
0x420e8bb2 in semop () from /lib/i686/libc.so.6
(gdb) bt
#0  0x420e8bb2 in semop () from /lib/i686/libc.so.6
#1  0x080ff954 in PGSemaphoreLock (sema=0x4a2d83e8,
interruptOK=0 '\0')
at pg_sema.c:434
#2  0x0811635e in LWLockAcquire (lockid=21335,
mode=LW_EXCLUSIVE) at
lwlock.c:312
#3  0x0810f49e in LockBuffer (buffer=10657, mode=2) at
bufmgr.c:1848
#4  0x0807dea3 in _bt_getbuf (rel=0x40141b10,
blkno=4294967295,
access=2) at nbtpage.c:337
#5  0x080813d8 in _bt_blnewpage (index=0x40141b10,
buf=0xbfffe724,
page=0xbfffe728, flags=1) at nbtsort.c:188
#6  0x08081692 in _bt_buildadd (index=0x40141b10,
state=0x4e0b3e30,
bti=0x4fe20cb8) at nbtsort.c:373
#7  0x08081b77 in _bt_load (index=0x40141b10,
btspool=0x82bf7b8,
btspool2=0x0) at nbtsort.c:638
#8  0x080813b8 in _bt_leafbuild (btspool=0x82bf7b8,
btspool2=0x0) at
nbtsort.c:171
#9  0x0807e1d0 in btbuild (fcinfo=0xbfffe820) at
nbtree.c:165
#10 0x081630d7 in OidFunctionCall3 (functionId=338,
arg1=1075019120,
arg2=1075059472, arg3=137095072)
    at fmgr.c:1275
#11 0x08092093 in index_build
(heapRelation=0x40137d70,
indexRelation=0x40141b10, indexInfo=0x82be7a0)
    at index.c:1447
#12 0x080913d7 in index_create (heapRelationId=17618,
indexRelationName=0x82b9648 "xxx_mod_ac_did_cre_idx",
    indexInfo=0x82be7a0, accessMethodObjectId=403,
classObjectId=0x82be578, primary=0 '\0',
isconstraint=0 '\0',
    allow_system_table_mods=0 '\0') at index.c:765
#13 0x080b88ae in DefineIndex (heapRelation=0x82b9698,
indexRelationName=0x82b9648 "xxx_mod_ac_did_cre_idx",
    accessMethodName=0x82b96c0 "btree",
attributeList=0x82b9718,
unique=0 '\0', primary=0 '\0',
    isconstraint=0 '\0', predicate=0x0,
rangetable=0x0) at
indexcmds.c:211
#14 0x0811b250 in ProcessUtility (parsetree=0x82b9788,
dest=Remote,
completionTag=0xbfffea80 "") at utility.c:620
#15 0x08118df6 in pg_exec_query_string
(query_string=0x82b91e0,
dest=Remote, parse_context=0x82ade58)
    at postgres.c:789
#16 0x08119f0d in PostgresMain (argc=4,
argv=0xbfffecb0,
username=0x8240679 "postgres") at postgres.c:2013
#17 0x08102078 in DoBackend (port=0x8240548) at
postmaster.c:2302
#18 0x081019ca in BackendStartup (port=0x8240548) at
postmaster.c:1924
#19 0x08100bcd in ServerLoop () at postmaster.c:1009
#20 0x0810078e in PostmasterMain (argc=1,
argv=0x8227468) at
postmaster.c:788
#21 0x080dee2b in main (argc=1, argv=0xbffff644) at
main.c:210
#22 0x42017589 in __libc_start_main () from
/lib/i686/libc.so.6
(gdb) quit
The program is running.  Quit anyway (and detach it)?
(y or n) y
Detaching from program: /xxx/bin.Linux/postgres,
process 23663



--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Litao Wu <litaowu@yahoo.com> writes:
> > One difference between these two databases
> > is the one having REINDEX problem is using
> > NTFS file system.
>
> Oh?  That's interesting.
>
> > Is it possible the root of problem?
>
> I would not expect it to show this particular
> symptom --- if the
> backtrace is accurate.  But there are nearby places
> that might have
> FS-dependent behavior.  Can you do anything about my
> request for
> a stack trace from a debug-enabled build?
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
majordomo@postgresql.org




__________________________________
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail