Mysql Locked Process Hang
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
Mysql Locked Process Hang
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
Re: MySQL 5.1.30 MyISAM table corrupts when it hits 1GB
Make sure you check you ulimit on file - I have hit that on AIX more than once. On Mon, Feb 9, 2009 at 2:42 PM, JD King jeff_d_k...@hotmail.com wrote: I am running MySQL 5.1.30 on AIX 5.3. When a table reaches 1GB the table gets marked as corrupt. Is there a setting that limits table size to 1GB? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=naclos...@gmail.com -- 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.
Re: Saving space disk (innodb)
If you do what Baron suggests, you may want to set Innodb to create a file-per-table - that way, in the future, you could save space when tables are dropped, or you could recreate innodb tables individually to save space, not have to dump all your innodb tables at one time. On 10/10/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Tiago Cruz wrote: Hello guys, I have one monster database running on MySQL 4.0.17, using InnoDB: 270GB Oct 10 14:35 ibdata1 I've deleted a lot of register of then, and I've expected that the size can be decreased if 50% (135 GB) but the ibdata was the same value than before clean... How can I force to save this space? You must dump your data to files, shut down MySQL, delete your current InnoDB tablespace and log files, reconfigure the server, restart MySQL and let InnoDB create new (empty) files. Then reload the data. You should probably save your current data and tablespace files until you are sure you complete this successfully. It's an annoying procedure but there is no other way. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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.
Behaviour of auto_increment
I have created a new table, with an auto_increment value. I would like the first auto_increment value to be 1001. So I - 1) inserted a fake record with an id of 1000 2) alter table tblname auto_increment=1000 (with and without a fake record) 3) alter table tblname auto_increment=1001 (with and without a fake record) Mysql version is 5.0.38 using MyIsam tables. Can someone explain auto_increment, more than what is in the manual? I have read it, and apparently I am missing something or don't understand it. Thanks! -- 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.
Query seemingly ignoring order by after upgrade
I upgraded to Mysql 5.0.42 from 4.1.14 yesterday. Afterwards, a query the I run every day to capture data on nodes that have increased backup load the most in TSM, seems to be ignoring my order by statement. Here is the query and the output: mysql select occupancy.node_name as NodeName,nodetrend.occupancy/1024 as Occupancy on 09/24/2006 (GB),sum(occupancy.physical_mb)/1024 as Occupancy Today (GB),(sum(occupancy.physical_mb) - nodetrend.occupancy)/1024 as Occupancy Increase (GB) from nodetrend,occupancy where nodetrend.date=2006-09-24 and occupancy.stgpool_name='BACKUPPOOL' and nodetrend.node_name=occupancy.node_name group by occupancy.node_name order by Occupancy Increase (GB) desc limit 10; +-+--+--+-+ | NodeName| Occupancy on 09/24/2006 (GB) | Occupancy Today (GB) | Occupancy Increase (GB) | +-+--+--+-+ | AMHFP01 | 213.378672 | 188.096826| - 25.281846 | | AMHFP01-S |15.284570 |25.520684| 10.236113 | | AMHROCP01 | 8.740791 |15.711035| 6.970244 | | ANDYC | 2.503848 | 2.503848| 0.00 | | BHSCAFAS01 | 7.057617 |11.109980| 4.052363 | | BJC11101828 | 5.401748 | 3.433613| - 1.968135 | | BJC3758 |15.911094 |15.911094| 0.00 | | BJCAMSQL01 |72.650596 | 102.609326| 29.958730 | | BJCAMWEB01 | 3.703057 | 8.468467| 4.765410 | | BJCBCA01|22.580410 |33.760918| 11.180508 | +-+--+--+-+ 10 rows in set (0.24 sec) Any suggestions? I can try backing off to 5.0.41 if anyone thinks that would help. Thanks -- 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.
