Mysql Locked Process Hang

--001485f7c48e8dadd50469202c80
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi.

I am having a problem with a program I am writing. The program reads a
file, checks an object file, and if the record doesn't exist, it inserts to
a node table (one table per node) and the objects file. I wrote a C program
to do this, with multiple processes running at one time. I have 15 odd
nodes, with 40 files (one file per filesystem on the 15 nodes). I kick of
40 processes at once. It runs for some time, and hangs with the following
process list (not at the same point every time):

mysql> show processlist;
+----+------+-----------+------+---------+------+--------+-- ------------------------------------------------------------ ----------------------------------------+
| Id | User | Host | db | Command | Time | State |
Info
|
+----+------+-----------+------+---------+------+--------+-- ------------------------------------------------------------ ----------------------------------------+
| 2 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("aztcd.ko",33246,"ed2c466b200d3fb38420f23c73b31da6")
|
| 3 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("ib_mad.ko",41540,"d1513ed2dafa6ea1ec53ca31f16a6ea6")
|
| 4 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("ib_ucm.ko",23704,"1df574fe480402cd1baa02bfe53dea25")
|
| 5 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("pam_make.so",4992,"9ecb181ecd67bd51f9d1c47381a02e8f" )
|
| 6 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("istallion.ko",38828,"c0f89798d35eed8bb447465f1771c13 c")
|
| 10 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("CIM_MemoryCheck.CIM_Check",11758,"1607681b1648d7ef2e 105dffc59f7122")
|
| 12 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("i2c-amd756.ko",8120,"2106913cdc436edbfbd79f4638f0f26 6")
|
| 15 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("ice_not.hpp",777,"b3da4cef03bb7ede418858da3e74d29f")
|
| 16 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("hand-pub.html",4069,"e03dee2afd9ddb0460307f58e01599a 9")
|
| 17 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("081107035101.dat",3969,"8aac4f8e16c8c450cfb14e3c573d 62e6")
|
| 19 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("iptable_nat.o",29116,"a156a3cdb6a9bfc85bad4c6016a90d 70")
|
| 20 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("SYS_LC_MESSAGES",63,"9e8cf9be98236c7327c479ea6544757 0")
|
| 18 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("proddon_backup.070512154501.log",2122,"917485542f6cf 8afccf610905de809ba"
|
| 22 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("whfwdata6.htm",7283,"50337d6d9d0fa796aa7ebcc3edd26e0 a")
|
| 23 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("xfrm6_mode_transport.ko",7748,"f484c1aef7174af885211 3b2ced0aa9e")
|
| 24 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("stock_bluetooth.png",2490,"2c0325756b662464839152a62 f78ab8a")
|
| 25 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("231.lst",163,"31a38641a00279721cbfc0eaacabd6d0")
|
| 26 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("DEPEND",289,"0049695fe84c6117e007623d9db38ea8")
|
| 27 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("kioslaverc",36,"fba9d778b2bd00fddd07d9ff4b7c8afd")
|
| 28 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("49c1e690.000",7516,"c23080a03025e577c5641d1631dbf8dd ")
|
| 29 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("eurotechwdt.ko",25572,"3bba9a747bfa6179c8db168f8feaa 626")
|
| 30 | root | localhost | bsm | Query | 138 | update | insert into
upbcgww03
values("/var/tmp/zypp.067D9R/zypp-trusted-kr9rzhrO","trustdb .gpg",1200,"b18a1a
|
| 33 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("ip6table_raw.ko",8360,"71f3981483a24326569d3ef654479 a03")
|
| 34 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("libntcp9.a",48626,"bcff3480b92854469d7cfd34fb6bf525" )
|
| 35 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("UpdatePOStatus.sh.log.03270855",578,"bd7e69c46efff7b 77f68f4ad21102a4e")
|
| 36 | root | localhost | bsm | Query | 138 | update | insert into
objects
values("oldprinterids",15685,"d6ba317977521647fe19e93f0e4fba 45")
|
| 37 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("3e10557d.000",33272,"d8bce12c08102840d2baf1853986015 2")
|
| 38 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("iso_2022_kr.py",994,"285db08e691745afd040d4f325c1329 f")
|
| 39 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("adi.ko",30304,"6ec1b31cd87dc5650fc3036be973ad9e")
|
| 40 | root | localhost | bsm | Query | 138 | Locked | insert into
objects
values("get_prot_orderinfo_prc.sql",1813,"80c1b368e9be91d2ab 6634190a7c47be")
|
| 42 | root | localhost | NULL | Query | 0 | NULL | show
processlist
|
+----+------+-----------+------+---------+------+--------+-- ------------------------------------------------------------ ----------------------------------------+
31 rows in set (0.00 sec)

The objects table:

CREATE TABLE `objects` (
`filename` varchar(256) COLLATE latin1_bin DEFAULT NULL,
`filesize` bigint(20) unsigned DEFAULT NULL,
`hash` varchar(32) COLLATE latin1_bin DEFAULT NULL,
UNIQUE KEY `nsh` (`filename`,`filesize`,`hash`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin

The files table (one per node):

CREATE TABLE `tsweb` (
`path` varchar(4096) COLLATE latin1_bin DEFAULT NULL,
`filename` varchar(256) COLLATE latin1_bin DEFAULT NULL,
`filesize` bigint(20) unsigned DEFAULT NULL,
`hash` varchar(32) COLLATE latin1_bin DEFAULT NULL,
`backuptime` datetime DEFAULT NULL,
`status` enum('Active','Inactive','Deleted') COLLATE latin1_bin DEFAULT
NULL,
`objectid` bigint(20) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin

I will post the program if needed, but the basic gist of it is:

while(data)
select 1 where fields=data
if mysql_num_rows==0
insert data into objects
insert data into files
else
insert data into files
done

I have tried 5.1.31. 5.1.32. 5.1.33. 6.0.9, 6.0.10, myisam and innodb. I
tried Maria and Falcon, but both were too slow at this point.

Thanks for any suggestions on how to debug this.

--
Andy Carlson
------------------------------------------------------------ ---------------
Gamecube:$150,PSO:$50,Broadband Adapter: $35, Hunters License: $8.95/month,
The feeling of seeing the red box with the item you want in it:Priceless.

--001485f7c48e8dadd50469202c80--