Mysql Locked Process Hang
am 06.05.2009 18:02:11 von Andrew Carlson
--0016e6ddfee6ab3d6404694084c5
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Any ideas about this? It's very aggravating and I have no idea how to debug
this any further. Thanks.
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.
--0016e6ddfee6ab3d6404694084c5--
Re: Mysql Locked Process Hang
am 06.05.2009 21:14:54 von Brent Baisley
You have 2 queries that are probably blocking everything. They are in
the "update" state.
| 30 | root | localhost | bsm | Query | 138 | update | insert into
upbcgww03
values("/var/tmp/zypp.067D9R/zypp-trusted-kr9rzhrO","trustdb .gpg",1200,"b18=
a1a
| 30 | root | localhost | bsm | Query | 138 | update | insert into
upbcgww03
values("/var/tmp/zypp.067D9R/zypp-trusted-kr9rzhrO","trustdb .gpg",1200,"b18=
a1a
But regardless, it doesn't matter which database you use or table
type, you are not going to get decent performance by doing many, many
single inserts. Even your selects are going to hurt things. To process
a file with only 100 lines, you're are going to do between 100 and 200
queries (100 selects+100 possible inserts).
You should try to batch your selects so you get a bunch of matches you
can filter on, rather than just one. Your inserts you should
absolutely use bulk inserts. Just build up a list of values that need
to be saved and when you hit 100 (or some other batch size), bulk
insert into the database and bulk write to the file.
Brent Baisley
On Wed, May 6, 2009 at 12:02 PM, Andrew Carlson wrote=
:
> Any ideas about this? =A0It's very aggravating and I have no idea how to =
debug
> this any further. =A0Thanks.
>
> Hi.
>
> I am having a problem with a program I am writing. =A0The 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. =A0I wrote a C pr=
ogram
> to do this, with multiple processes running at one time. =A0I have 15 odd
> nodes, with 40 files (one file per filesystem on the 15 nodes). =A0I kick=
of
> 40 processes at once. =A0It runs for some time, and hangs with the follow=
ing
> process list (not at the same point every time):
>
> mysql> show processlist;
> +----+------+-----------+------+---------+------+--------+-- -------------=
------------------------------------------------------------ ---------------=
------------+
> | Id | User | Host =A0 =A0 =A0| db =A0 | Command | Time | State =A0|
> Info
> |
> +----+------+-----------+------+---------+------+--------+-- -------------=
------------------------------------------------------------ ---------------=
------------+
> | =A02 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert=
into
> objects
> values("aztcd.ko",33246,"ed2c466b200d3fb38420f23c73b31da6")
> |
> | =A03 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert=
into
> objects
> values("ib_mad.ko",41540,"d1513ed2dafa6ea1ec53ca31f16a6ea6")
> |
> | =A04 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert=
into
> objects
> values("ib_ucm.ko",23704,"1df574fe480402cd1baa02bfe53dea25")
> |
> | =A05 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert=
into
> objects
> values("pam_make.so",4992,"9ecb181ecd67bd51f9d1c47381a02e8f" )
> |
> | =A06 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert=
into
> objects
> values("istallion.ko",38828,"c0f89798d35eed8bb447465f1771c13 c")
> |
> | 10 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("CIM_MemoryCheck.CIM_Check",11758,"1607681b1648d7ef2e 105dffc59f712=
2")
> |
> | 12 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("i2c-amd756.ko",8120,"2106913cdc436edbfbd79f4638f0f26 6")
> |
> | 15 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("ice_not.hpp",777,"b3da4cef03bb7ede418858da3e74d29f")
> |
> | 16 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("hand-pub.html",4069,"e03dee2afd9ddb0460307f58e01599a 9")
> |
> | 17 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("081107035101.dat",3969,"8aac4f8e16c8c450cfb14e3c573d 62e6")
> |
> | 19 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("iptable_nat.o",29116,"a156a3cdb6a9bfc85bad4c6016a90d 70")
> |
> | 20 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("SYS_LC_MESSAGES",63,"9e8cf9be98236c7327c479ea6544757 0")
> |
> | 18 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("proddon_backup.070512154501.log",2122,"917485542f6cf 8afccf610905d=
e809ba"
> |
> | 22 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("whfwdata6.htm",7283,"50337d6d9d0fa796aa7ebcc3edd26e0 a")
> |
> | 23 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("xfrm6_mode_transport.ko",7748,"f484c1aef7174af885211 3b2ced0aa9e")
> |
> | 24 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("stock_bluetooth.png",2490,"2c0325756b662464839152a62 f78ab8a")
> |
> | 25 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("231.lst",163,"31a38641a00279721cbfc0eaacabd6d0")
> |
> | 26 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("DEPEND",289,"0049695fe84c6117e007623d9db38ea8")
> |
> | 27 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("kioslaverc",36,"fba9d778b2bd00fddd07d9ff4b7c8afd")
> |
> | 28 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("49c1e690.000",7516,"c23080a03025e577c5641d1631dbf8dd ")
> |
> | 29 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("eurotechwdt.ko",25572,"3bba9a747bfa6179c8db168f8feaa 626")
> |
> | 30 | root | localhost | bsm =A0| Query =A0 | =A0138 | update | insert i=
nto
> upbcgww03
> values("/var/tmp/zypp.067D9R/zypp-trusted-kr9rzhrO","trustdb .gpg",1200,"b=
18a1a
> |
> | 33 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("ip6table_raw.ko",8360,"71f3981483a24326569d3ef654479 a03")
> |
> | 34 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("libntcp9.a",48626,"bcff3480b92854469d7cfd34fb6bf525" )
> |
> | 35 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("UpdatePOStatus.sh.log.03270855",578,"bd7e69c46efff7b 77f68f4ad2110=
2a4e")
> |
> | 36 | root | localhost | bsm =A0| Query =A0 | =A0138 | update | insert i=
nto
> objects
> values("oldprinterids",15685,"d6ba317977521647fe19e93f0e4fba 45")
> |
> | 37 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("3e10557d.000",33272,"d8bce12c08102840d2baf1853986015 2")
> |
> | 38 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("iso_2022_kr.py",994,"285db08e691745afd040d4f325c1329 f")
> |
> | 39 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("adi.ko",30304,"6ec1b31cd87dc5650fc3036be973ad9e")
> |
> | 40 | root | localhost | bsm =A0| Query =A0 | =A0138 | Locked | insert i=
nto
> objects
> values("get_prot_orderinfo_prc.sql",1813,"80c1b368e9be91d2ab 6634190a7c47b=
e")
> |
> | 42 | root | localhost | NULL | Query =A0 | =A0 =A00 | NULL =A0 | show
> processlist
> |
> +----+------+-----------+------+---------+------+--------+-- -------------=
------------------------------------------------------------ ---------------=
------------+
> 31 rows in set (0.00 sec)
>
> The objects table:
>
> CREATE TABLE `objects` (
> =A0`filename` varchar(256) COLLATE latin1_bin DEFAULT NULL,
> =A0`filesize` bigint(20) unsigned DEFAULT NULL,
> =A0`hash` varchar(32) COLLATE latin1_bin DEFAULT NULL,
> =A0UNIQUE KEY `nsh` (`filename`,`filesize`,`hash`)
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1 COLLATE=3Dlatin1_bin
>
> The files table (one per node):
>
> CREATE TABLE `tsweb` (
> =A0`path` varchar(4096) COLLATE latin1_bin DEFAULT NULL,
> =A0`filename` varchar(256) COLLATE latin1_bin DEFAULT NULL,
> =A0`filesize` bigint(20) unsigned DEFAULT NULL,
> =A0`hash` varchar(32) COLLATE latin1_bin DEFAULT NULL,
> =A0`backuptime` datetime DEFAULT NULL,
> =A0`status` enum('Active','Inactive','Deleted') COLLATE latin1_bin DEFAUL=
T
> NULL,
> =A0`objectid` bigint(20) unsigned DEFAULT NULL
> ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1 COLLATE=3Dlatin1_bin
>
> I will post the program if needed, but the basic gist of it is:
>
> while(data)
> =A0 =A0 select 1 where fields=3Ddata
> =A0 =A0 if mysql_num_rows==0
> =A0 =A0 =A0 =A0 =A0insert data into objects
> =A0 =A0 =A0 =A0 =A0insert data into files
> =A0 =A0 else
> =A0 =A0 =A0 =A0 =A0insert data into files
> =A0done
>
> I have tried 5.1.31. 5.1.32. 5.1.33. 6.0.9, 6.0.10, myisam and innodb. =
=A0I
> 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/mont=
h,
> The feeling of seeing the red box with the item you want in it:Priceless.
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg