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

Hi Tom,

Here is gdb info.

This happens in our production database
3 times this week. It's totally unacceptable.
I have written a drop/create script to
avoid reindex. However, drop/create
seems to me take more time than reindex
for the whole database.

Your help is greatly appreciated!

Version:
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC
2.96


===========================================
su - postgres
ps -auxww|grep REINDEX
postgres 18903  1.2  8.1 195388 169512 ?     S
04:49   1:54 postgres:postgres db1 [local] REINDEX
postgres 13329  0.0  0.0  1768  620 pts/0    S
07:23   0:00 grep REINDEX
gdb /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"...(no
debugging symbols
found)...
(gdb) attach 18903
Attaching to program: /bin/postgres, process 18903
Reading symbols from /usr/lib/libz.so.1...(no
debugging symbols
found)...done.
Loaded symbols for /usr/lib/libz.so.1
Reading symbols from /usr/lib/libreadline.so.4...(no
debugging symbols
found)...done.
Loaded symbols for /usr/lib/libreadline.so.4
Reading symbols from /lib/libtermcap.so.2...(no
debugging symbols
found)...done.
Loaded symbols for /lib/libtermcap.so.2
Reading symbols from /lib/libcrypt.so.1...(no
debugging symbols
found)...done.
Loaded symbols for /lib/libcrypt.so.1
Reading symbols from /lib/libresolv.so.2...(no
debugging symbols
found)...done.
Loaded symbols for /lib/libresolv.so.2
Reading symbols from /lib/libnsl.so.1...(no debugging
symbols found)...done.
Loaded symbols for /lib/libnsl.so.1
Reading symbols from /lib/libdl.so.2...(no debugging
symbols found)...done.
Loaded symbols for /lib/libdl.so.2
Reading symbols from /lib/i686/libm.so.6...(no
debugging symbols
found)...done.
Loaded symbols for /lib/i686/libm.so.6
Reading symbols from /lib/i686/libc.so.6...(no
debugging symbols
found)...done.
Loaded symbols for /lib/i686/libc.so.6
Reading symbols from /lib/ld-linux.so.2...(no
debugging symbols
found)...done.
Loaded symbols for /lib/ld-linux.so.2
Reading symbols from /lib/libnss_files.so.2...(no
debugging symbols
found)...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  0x080ffa28 in PGSemaphoreLock ()
#2  0x08116432 in LWLockAcquire ()
#3  0x0810f572 in LockBuffer ()
#4  0x0807dea3 in _bt_getbuf ()
#5  0x080813ec in _bt_leafbuild ()
#6  0x080816a6 in _bt_leafbuild ()
#7  0x08081b8b in _bt_leafbuild ()
#8  0x080813cc in _bt_leafbuild ()
#9  0x0807e1d0 in btbuild ()
#10 0x081631c3 in OidFunctionCall3 ()
#11 0x080920a7 in index_build ()
#12 0x08092593 in reindex_index ()
#13 0x08092473 in IndexBuildHeapScan ()
#14 0x0809275d in reindex_relation ()
#15 0x080b9164 in ReindexTable ()
#16 0x08118ece in pg_exec_query_string ()
#17 0x08119fe5 in PostgresMain ()
#18 0x0810214c in ClosePostmasterPorts ()
#19 0x08101a9e in ClosePostmasterPorts ()
#20 0x08100ca1 in PostmasterMain ()
#21 0x08100862 in PostmasterMain ()
#22 0x080deed7 in main ()
#23 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: /bin/postgres, process 18903

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Litao Wu <litaowu@yahoo.com> writes:
> > I will use gdb next time. What's this right way to
> > get info as postgres owner?
>
>     $ gdb /path/to/postgres
>     gdb> attach PID-of-backend-process
>     gdb> bt
>     gdb> quit
>
> You might try this for practice on any idle backend;
> it shouldn't affect
> the state of the backend, except for freezing it
> while you issue the
> commands.
>
> If "bt" gives you just a list of numbers and no
> symbolic information,
> then it won't be much help; you'll need to rebuild
> the backend with
> debugging information so that we can make some sense
> of the trace.
>
>             regards, tom lane





__________________________________
Do you Yahoo!?
Friends.  Fun.  Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/