replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/

Source Link

edited Apr 13, 2017 at 12:42

1

From the link: LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?

I had changed the command order (unlock before commit + replace some queries to slave), and the db server runs pretty well . But I want to ask more question around why my db server hangs?

I'm not sure of the main reason that @RolandoMySQLDBA mention that lets the db server hang. You can test my command: Session1: (lock table and don't throw unlock tables command).

#session 1
Lock table  **v3_zone_date** WRITE;
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date 
 FIELDS TERMINATED BY  ','; 
COMMIT;
SET AUTOCOMMIT=1;#ok

#Session2: 
SELECT * FROM **v3_cam_date** LIMIT 1; #ok
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_cam_date.out' INTO TABLE v3_cam_date     
 FIELDS TERMINATED BY  ','; 
COMMIT;  
SET AUTOCOMMIT=1;#ok

So, if I tried to get x lock from table v3_domain_date in session1, and then running query to other tables in session 2, everything Ok (not lock or hang out db). Of course, I can't query to the v3_domain_date (be cause the table was locked with x lock).

can any one explain for me? Thanks,

From the link: LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?

I had changed the command order (unlock before commit + replace some queries to slave), and the db server runs pretty well . But I want to ask more question around why my db server hangs?

I'm not sure of the main reason that @RolandoMySQLDBA mention that lets the db server hang. You can test my command: Session1: (lock table and don't throw unlock tables command).

#session 1
Lock table  **v3_zone_date** WRITE;
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date 
 FIELDS TERMINATED BY  ','; 
COMMIT;
SET AUTOCOMMIT=1;#ok

#Session2: 
SELECT * FROM **v3_cam_date** LIMIT 1; #ok
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_cam_date.out' INTO TABLE v3_cam_date     
 FIELDS TERMINATED BY  ','; 
COMMIT;  
SET AUTOCOMMIT=1;#ok

So, if I tried to get x lock from table v3_domain_date in session1, and then running query to other tables in session 2, everything Ok (not lock or hang out db). Of course, I can't query to the v3_domain_date (be cause the table was locked with x lock).

can any one explain for me? Thanks,

From the link: LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?

I had changed the command order (unlock before commit + replace some queries to slave), and the db server runs pretty well . But I want to ask more question around why my db server hangs?

I'm not sure of the main reason that @RolandoMySQLDBA mention that lets the db server hang. You can test my command: Session1: (lock table and don't throw unlock tables command).

#session 1
Lock table  **v3_zone_date** WRITE;
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date 
 FIELDS TERMINATED BY  ','; 
COMMIT;
SET AUTOCOMMIT=1;#ok

#Session2: 
SELECT * FROM **v3_cam_date** LIMIT 1; #ok
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_cam_date.out' INTO TABLE v3_cam_date     
 FIELDS TERMINATED BY  ','; 
COMMIT;  
SET AUTOCOMMIT=1;#ok

So, if I tried to get x lock from table v3_domain_date in session1, and then running query to other tables in session 2, everything Ok (not lock or hang out db). Of course, I can't query to the v3_domain_date (be cause the table was locked with x lock).

can any one explain for me? Thanks,

added 40 characters in body

Source Link

edited Mar 2, 2012 at 8:56

Thanh Nguyen

41
2
6

From the link: LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?

I had changed the command order (unlock before commit + replace some queries to slave), and the db server runsprettyruns pretty well . But I want to ask you more question around why my db server hangs?

I'm not sure of the main reason that you@RolandoMySQLDBA mention above that lets the db server hang. You can test my command: Session1: (lock table and don't throw unlock tables command).

#session 1
Lock table  **v3_zone_date** WRITE;
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date 
 FIELDS TERMINATED BY  ','; 
COMMIT;
SET AUTOCOMMIT=1;#ok

#Session2: 
SELECT * FROM **v3_cam_date** LIMIT 1; #ok
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_cam_date.out' INTO TABLE v3_cam_date     
 FIELDS TERMINATED BY  ','; 
COMMIT;  
SET AUTOCOMMIT=1;#ok

So, if I tried to get x lock from table v3_domain_date in session1, and then running query to other tables in session 2, everything Ok (not lock or hang out db). Of course, I can't query to the v3_domain_date (be cause the table was locked with x lock).

can any one explain for me? Thanks,

From the link: LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?

I had changed the command order (unlock before commit + replace some queries to slave), and the db server runspretty well . But I want to ask you more question around why my db server hangs?

I'm not sure of the main reason that you mention above that lets the db server hang. You can test my command: Session1: (lock table and don't throw unlock tables command).

#session 1
Lock table  **v3_zone_date** WRITE;
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date 
 FIELDS TERMINATED BY  ','; 
COMMIT;
SET AUTOCOMMIT=1;#ok

#Session2: 
SELECT * FROM **v3_cam_date** LIMIT 1; #ok
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_cam_date.out' INTO TABLE v3_cam_date     
 FIELDS TERMINATED BY  ','; 
COMMIT;  
SET AUTOCOMMIT=1;#ok

So, if I tried to get x lock from table v3_domain_date in session1, and then running query to other tables in session 2, everything Ok (not lock or hang out db). Of course, I can't query to the v3_domain_date (be cause the table was locked with x lock).

From the link: LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?

I had changed the command order (unlock before commit + replace some queries to slave), and the db server runs pretty well . But I want to ask more question around why my db server hangs?

I'm not sure of the main reason that @RolandoMySQLDBA mention that lets the db server hang. You can test my command: Session1: (lock table and don't throw unlock tables command).

#session 1
Lock table  **v3_zone_date** WRITE;
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date 
 FIELDS TERMINATED BY  ','; 
COMMIT;
SET AUTOCOMMIT=1;#ok

#Session2: 
SELECT * FROM **v3_cam_date** LIMIT 1; #ok
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_cam_date.out' INTO TABLE v3_cam_date     
 FIELDS TERMINATED BY  ','; 
COMMIT;  
SET AUTOCOMMIT=1;#ok

So, if I tried to get x lock from table v3_domain_date in session1, and then running query to other tables in session 2, everything Ok (not lock or hang out db). Of course, I can't query to the v3_domain_date (be cause the table was locked with x lock).

can any one explain for me? Thanks,

edited title

Source Link

edited Mar 1, 2012 at 8:07

Thanh Nguyen

41
2
6

DB MySQL hang in load data infile command

From the link: LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?

I had changed the command order (unlock before commit + replace some queries to slave), and the db server runspretty well . But I want to ask you more question around why my db server hangs?

I'm not sure of the main reason that you mention above that lets the db server hang. You can test my command: Session1: (lock table and don't throw unlock tables command).

#session 1
Lock table  **v3_zone_date** WRITE;
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date 
 FIELDS TERMINATED BY  ','; 
COMMIT;
SET AUTOCOMMIT=1;#ok

#Session2: 
SELECT * FROM **v3_cam_date** LIMIT 1; #ok
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_cam_date.out' INTO TABLE v3_cam_date     
 FIELDS TERMINATED BY  ','; 
COMMIT;  
SET AUTOCOMMIT=1;#ok

So, if I tried to get x lock from table v3_domain_date in session1, and then running query to other tables in session 2, everything Ok (not lock or hang out db). Of course, I can't query to the v3_domain_date (be cause the table was locked with x lock).

(@RolandoMySQLDBA: do you have any ideas?)

DB hang in load data infile command

From the link: LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?

I had changed the command order (unlock before commit + replace some queries to slave), and the db server runspretty well . But I want to ask you more question around why my db server hangs?

I'm not sure of the main reason that you mention above that lets the db server hang. You can test my command: Session1: (lock table and don't throw unlock tables command).

#session 1
Lock table  **v3_zone_date** WRITE;
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date 
 FIELDS TERMINATED BY  ','; 
COMMIT;
SET AUTOCOMMIT=1;#ok

#Session2: 
SELECT * FROM **v3_cam_date** LIMIT 1; #ok
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_cam_date.out' INTO TABLE v3_cam_date     
 FIELDS TERMINATED BY  ','; 
COMMIT;  
SET AUTOCOMMIT=1;#ok

So, if I tried to get x lock from table v3_domain_date in session1, and then running query to other tables in session 2, everything Ok (not lock or hang out db). Of course, I can't query to the v3_domain_date (be cause the table was locked with x lock).

(@RolandoMySQLDBA: do you have any ideas?)

MySQL hang in load data infile command

From the link: LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?

I had changed the command order (unlock before commit + replace some queries to slave), and the db server runspretty well . But I want to ask you more question around why my db server hangs?

I'm not sure of the main reason that you mention above that lets the db server hang. You can test my command: Session1: (lock table and don't throw unlock tables command).

#session 1
Lock table  **v3_zone_date** WRITE;
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date 
 FIELDS TERMINATED BY  ','; 
COMMIT;
SET AUTOCOMMIT=1;#ok

#Session2: 
SELECT * FROM **v3_cam_date** LIMIT 1; #ok
SET AUTOCOMMIT=0; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_cam_date.out' INTO TABLE v3_cam_date     
 FIELDS TERMINATED BY  ','; 
COMMIT;  
SET AUTOCOMMIT=1;#ok

So, if I tried to get x lock from table v3_domain_date in session1, and then running query to other tables in session 2, everything Ok (not lock or hang out db). Of course, I can't query to the v3_domain_date (be cause the table was locked with x lock).