[mythtv-users] MySQL related BE deadlocks - collective wisdom needed

[Warpme]

@David,

 >A few thoughts based on those dumps. It is entirely possible that any of
 >these thoughts are misguided, since I know little about the code:
 >
Thx You found time to look into this issue.

 >1. I don't see anything here suggesting this is a deadlock in MythTV; 
most
 >of the threads are waiting to get a lock, but I'm guessing that the 
lock is
 >being held by the thread trying to run the DB query, which is waiting 
on the
 >database rather than waiting on a lock in the Myth code. Maybe this is a
 >terminology issue, but I think it makes a difference in terms of the
 >possible causes.

I'm far less skilled in reading stack traces but Daniel's K comments 
pointed me to get closer looks
on all collected traces. Reading some Internet leads me to exactly this 
conclusion. If You have time to read my last post in this topic - I'm 
currently fully inline with Your hypothesis and trying identify root 
cause in external to myth itself areas.

 >2. The thread trying to query the database is hanging while preparing a
 >statement rather than executing one. The database appears to be idle, 
unless
 >I'm misinterpreting the output or if you have some tables that are not
 >InnoDB. Thus the database does not appear to be blocking due to locks.

Reading InnoDB statistics shows me exactly what You say.
(seeing this first time I was little "disappointed" as from DB server 
all looks OK so we still don't know where is root cause).

Regarding to be sure that we see right statistics: here is mysql_tuner 
report about all schemes present in my DB:

-------- Storage Engine Statistics 
-------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in InnoDB tables: 147M (Tables: 148)
[!!] Total fragmented tables: 148
-------- Security Recommendations 
-------------------------------------------

 >3. The thread trying to run the DB query (0x98eafb70 = 2565536624) 
does not
 >appear in the list of threads in the DB status output. Again, I may be
 >misinterpreting the output, but if I'm correct then the thread is 
trying to
 >prepare a query on a connection that no longer open.

Oh THIS is VERY valid observation.
Reading MySQL forums and mysqlclient bug reports I saw reports having 
quite similar stack trace.
Identified root cause was situation where on Linux comm.socket failure 
wasn't properly propagated to upper levels of libs stack (mysqlclient 
lib as well). MySQL devs solution was based on timeouts. That was at 4.x 
MySQL level and was included into mainline sources - so I classified 
this as similar but already solved.
Called functions in my stack traces indeed are very often related to 
vio_read/vio_read_buffer/my_net_read.
At sometime I was suspecting that this might be related to mysql 
client<->server communication, but no any relevant entries in LOGs, the 
same PIDs for mysql/BE make me to refuse this hypothesis.

Currently I have big hopes with Mike's post about 
https://github.com/MythTV/mythtv/commit/528324d8
Currently it matches all checks If we assume that from some reason we 
are calling MSqlDatabase::Reconnect().
If I'm reading commit code properly, condition for forced DB reconnect 
is failed query AND error code = 2006.
Maybe mysqlclient is reacting with hang when short time earlier some 
other thread force DB reconnect ? (this is somehow inline with some 
mysql bug reports about mysqlclient hangs triggered by previous 
client-server comm failures. Solution for this is based on timeout. Do 
we use timeouts in mysql operations in myth ?)

For testing above hypothesis I think it is enough to comment commit's 
lines 182/183:

+    m_db.close();
+    m_db.open();


@Mike,
Pls confirm that commenting those 2 lines is enough to functionally 
furn-off this commit.
I will do such mod and try how it works.

Gents, thx again for trying help !

-br

-------------- next part --------------
A non-text attachment was scrubbed...
Name: warpme.vcf
Type: text/x-vcard
Size: 83 bytes
Desc: not available
Url : http://www.mythtv.org/pipermail/mythtv-users/attachments/20110726/477f1acb/attachment.vcf