MySQL version: 5.5.13
The query that I'm using:
SET AUTOCOMMIT=0; LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date FIELDS TERMINATED BY ','; COMMIT;
Normally it takes 3s to complete. But sometime, the insert query takes about 3-7 minutes to run:
Id: 97 User: tom Host: 192.168.6.31:27059 db: test Command: Query Time: 155 State: logging slow query Info: COMMIT
and my database is locked completely (very slow when connecting). The process Id 97 continues running after I try to kill it. The init script shows the [ FAILED ] flag when trying to restart MySQL, so I must use
kill -9 and start again.
I will try to disable the
unique_checks follow this guide. But I'm trying to find out why it's locked.
There are some
Waiting for table metadata lock states on temporary tables:
Id: 180098 User: jerry Host: 192.168.6.31:54909 db: test Command: Query Time: 142 State: Waiting for table metadata lock Info: DROP TABLE IF EXISTS norep_locationtmp
Waiting for table level lock state:
Id: 180233 User: bob Host: 192.168.6.31:43537 db: test Command: Query Time: 65 State: Waiting for table level lock Info: SELECT COUNT(DISTINCT A.`campaignid`) INTO _c FROM `ox_campaigns` A INNER JOIN `selfserving_users` B ON B.`user_id` = A.`uid` INNER JOIN `v3_cam_date` C ON C.`campaignid` = A.`campaignid` WHERE A.`revenue_type` = 5 AND A.`deleted` = 0 AND A.`expire` = DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY) AND A.`isExpired` = 0 AND IF( NAME_CONST('_permitid',3) = -1, 1=1, IF( NAME_CONST('_permitid',3) = 0, A.`uid` IN (SELECT C.`user_id` FROM `selfserving_users` C WHERE C.`groupid` = NAME_CONST('_groupid',12) ) ,A.`uid` = NAME_CONST('userid',388)))
There isn't anything related to the above table -
v3_zone_date and no deadlock detected in
SHOW ENGINE INNODB STATUS. Where should I take a look at first to troubleshoot this case? Let me know if you need any further information.
Reply to @RolandoMySQLDBA:
The scary part about the query is the self-reference
selfserving_usersacting in a self serving manner against itself.
I will ask the developer to rewrite it.
- You are hammering the InnoDB Buffer
- Some memory swapping may be going on
I have 40GB RAM and:
innodb_buffer_pool_size = 20G innodb_buffer_pool_instances = 8
The memory graph exhibit that there is no increase.
Possible full table locking issues that are affecting data pages outside the
v3_zone_datetable (such was with the
Do you have any ideas to avoid the full table locking issue?
There may be a way to throttle the LOAD DATA INFILE process on an InnoDB table. I cannot give you a solid answer on this one, but try this link from Baron Schwartz.
I will trying the fifo method and let you know the result.
UPDATE: Wed Feb 22 12:19:03 ICT 2012
Below is snippet of the
SHOW ENGINE INNODB STATUS output when hanging:
---TRANSACTION 1EF3CC26, ACTIVE (PREPARED) 332 sec 68 lock struct(s), heap size 14776, 6933 row lock(s), undo log entries 3465 MySQL thread id 4088, query id 11411947 192.168.6.31 bob COMMIT Trx read view will not see trx with id >= 1EF3CC27, sees < 1EF06C8E
Based on the thread id, I found out the culprit is a .NET process which is loading data into the database by following steps:
- LOCK TABLES;
- SET autocommit=0;
- SET unique_checks=0;
- SET foreign_key_checks=0;
- LOAD DATA;
- UNLOCK TABLES;
- SET unique_checks=1;
- SET foreign_key_checks=1;
It seems that the insert processing hangs at COMMIT step, the table cannot be unlock and freezing my database. Should I swap the COMMIT and UNLOCK TABLES step?
According to the MySQL document:
Beginning with MySQL 5.5.5, it is no longer possible to set @@session.sql_log_bin within a transaction or subquery. (Bug #53437)
As my understanding, 400K rows is not big enough, cannot find out why it locked my db?