Message-ID: <44635DFE.1060509@modgraph-usa.com>
Date: Thu, 11 May 2006 08:53:34 -0700
From: "Craig A. James" <cjames@modgraph-usa.com>
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050716)
MIME-Version: 1.0
To: Chris <dmagick@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres gets stuck
References: <446135F9.1060608@modgraph-usa.com> <4461391D.2000800@gmail.com>
In-Reply-To: <4461391D.2000800@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Chris wrote:
> 
>> This is a deadly bug, because our web site goes dead when this 
>> happens, ...
> 
> Sounds like a deadlock issue.
> ...
> stats_command_string = true
> and restart postgresql.
> then you'll be able to:
> select * from pg_stat_activity;
> to see what queries postgres is running and that might give you some clues.

Thanks, good advice.  You're absolutely right, it's stuck on a mutex.  After doing what you suggest, I discovered that the query in progress is a user-written function (mine).  When I log in as root, and use "gdb -p <pid>" to attach to the process, here's what I find.  Notice the second function in the stack, a mutex lock:

(gdb) bt
#0  0x0087f7a2 in _dl_sysinfo_int80 () from /lib/ld-linux.so.2
#1  0x0096cbfe in __lll_mutex_lock_wait () from /lib/tls/libc.so.6
#2  0x008ff67b in _L_mutex_lock_3220 () from /lib/tls/libc.so.6
#3  0x4f5fc1b4 in ?? ()
#4  0x00dc5e64 in std::string::_Rep::_S_empty_rep_storage () from /usr/local/pgsql/lib/libchmoogle.so
#5  0x009ffcf0 in ?? () from /usr/lib/libz.so.1
#6  0xbfe71c04 in ?? ()
#7  0xbfe71e50 in ?? ()
#8  0xbfe71b78 in ?? ()
#9  0x009f7019 in zcfree () from /usr/lib/libz.so.1
#10 0x009f7019 in zcfree () from /usr/lib/libz.so.1
#11 0x009f8b7c in inflateEnd () from /usr/lib/libz.so.1
#12 0x00c670a2 in ~basic_unzip_streambuf (this=0xbfe71be0) at zipstreamimpl.h:332
#13 0x00c60b61 in OpenBabel::OBConversion::Read (this=0x1, pOb=0xbfd923b8, pin=0xffffffea) at istream:115
#14 0x00c60fd8 in OpenBabel::OBConversion::ReadString (this=0x8672b50, pOb=0xbfd923b8) at obconversion.cpp:780
#15 0x00c19d69 in chmoogle_ichem_mol_alloc () at stl_construct.h:120
#16 0x00c1a203 in chmoogle_ichem_normalize_parent () at stl_construct.h:120
#17 0x00c1b172 in chmoogle_normalize_parent_sdf () at vector.tcc:243
#18 0x0810ae4d in ExecMakeFunctionResult ()
#19 0x0810de2e in ExecProject ()
#20 0x08115972 in ExecResult ()
#21 0x08109e01 in ExecProcNode ()
#22 0x00000020 in ?? ()
#23 0xbed4b340 in ?? ()
#24 0xbf92d9a0 in ?? ()
#25 0xbed4b0c0 in ?? ()
#26 0x00000000 in ?? ()

It looks to me like my code is trying to read the input parameter (a fairly long string, maybe 2K) from a buffer that was gzip'ed by Postgres for the trip between the client and server.  My suspicion is that it's an incompatibility between malloc() libraries.  libz (gzip compression) is calling something called zcfree, which then appears to be intercepted by something that's (probably statically) linked into my library.  And somewhere along the way, a mutex gets set, and then ... it's stuck forever.

ps(1) shows that this thread had been running for about 7 hours, and the job status showed that this function had been successfully called about 1 million times, before this mutex lock occurred.

Any ideas?

Thanks,
Craig