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,"b18a1a
| 30 | root | localhost | bsm | Query | 138 | update | insert into
upbcgww03
values("/var/tmp/zypp.067D9R/zypp-trusted-kr9rzhrO","trustdb.gpg",1200,"b18a1a
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 <naclos...@gmail.com> wrote:
> 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,"c0f89798d35eed8bb447465f1771c13c")
> |
> | 10 | root | localhost | bsm | Query | 138 | Locked | insert into
> objects
> values("CIM_MemoryCheck.CIM_Check",11758,"1607681b1648d7ef2e105dffc59f7122")
> |
> | 12 | root | localhost | bsm | Query | 138 | Locked | insert into
> objects
> values("i2c-amd756.ko",8120,"2106913cdc436edbfbd79f4638f0f266")
> |
> | 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,"e03dee2afd9ddb0460307f58e01599a9")
> |
> | 17 | root | localhost | bsm | Query | 138 | Locked | insert into
> objects
> values("081107035101.dat",3969,"8aac4f8e16c8c450cfb14e3c573d62e6")
> |
> | 19 | root | localhost | bsm | Query | 138 | Locked | insert into
> objects
> values("iptable_nat.o",29116,"a156a3cdb6a9bfc85bad4c6016a90d70")
> |
> | 20 | root | localhost | bsm | Query | 138 | Locked | insert into
> objects
> values("SYS_LC_MESSAGES",63,"9e8cf9be98236c7327c479ea65447570")
> |
> | 18 | root | localhost | bsm | Query | 138 | Locked | insert into
> objects
> values("proddon_backup.070512154501.log",2122,"917485542f6cf8afccf610905de809ba"
> |
> | 22 | root | localhost | bsm | Query | 138 | Locked | insert into
> objects
> values("whfwdata6.htm",7283,"50337d6d9d0fa796aa7ebcc3edd26e0a")
> |
> | 23 | root | localhost | bsm | Query | 138 | Locked | insert into
> objects
> values("xfrm6_mode_transport.ko",7748,"f484c1aef7174af8852113b2ced0aa9e")
> |
> | 24 | root | localhost | bsm | Query | 138 | Locked | insert into
> objects
> values("stock_bluetooth.png",2490,"2c0325756b662464839152a62f78ab8a")
> |
> | 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,"3bba9a747bfa6179c8db168f8feaa626")
> |
> | 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,"71f3981483a24326569d3ef654479a03")
> |
> | 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,"bd7e69c46efff7b77f68f4ad21102a4e")
> |
> | 36 | root | localhost | bsm | Query | 138 | update | insert into
> objects
> values("oldprinterids",15685,"d6ba317977521647fe19e93f0e4fba45")
> |
> | 37 | root | localhost | bsm | Query | 138 | Locked | insert into
> objects
> values("3e10557d.000",33272,"d8bce12c08102840d2baf18539860152")
> |
> | 38 | root | localhost | bsm | Query | 138 | Locked | insert into
> objects
> values("iso_2022_kr.py",994,"285db08e691745afd040d4f325c1329f")
> |
> | 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,"80c1b368e9be91d2ab6634190a7c47be")
> |
> | 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.
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
