Error inserting 100 million record in MYSQL tables

Question

i have table Temp_load with Columns:

key bigint(19) UN PK 
plane_key bigint(20) PK 
locat_key bigint(20) PK 
time_period_key bigint(19) UN PK 
business_unit_key bigint(19) UN 
curret_allocated tinyint(1) 
value float 
valid_ind int(11) 
last_updated datetime

Above are the column which table Temp_load is containing in it.

i am trying to insert data into this table using below query

INSERT INTO <Schema_name>.`Temp_load` 
            (key, 
             plane_key, 
             locat_key, 
             time_period_key, 
             business_unit_key, 
             curret_allocated, 
             value) 
(SELECT DISTINCT 1, 
                 plane_key, 
                 locat_key, 
                 1, 
                 CASE 
                   WHEN current_area = 'HEALTH' THEN 1 
                   WHEN current_area = 'BEAUTY/PERSONAL' THEN 3 
                   WHEN current_area = 'GM' THEN 2 
                   WHEN current_area = 'CONSUMABLES' THEN 4 
                 end, 
                 current_flag, 
                 opt_metric_1 
 FROM   staging.curves
 WHERE  opt_metric_1 IS NOT NULL 
        AND current_area IS NOT NULL);

The Source table is having 29 million records in it. The above insert statment is running for more than 5 hours and still running. I am doing an insert of 29 Million in one go like this from the same table i need to do 3 times more insert on different column.

When i try to load using LOAD DATA INFILE it is throwing ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction i also tried increasing innodb_lock_wait_timeout to 120 but still we are facing the problem.

Also before loading i ave disabled below flags.

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;

Do we have any other optimal solution for this? where the insert can be done in much faster way.

Thanks

If integrity of the input data can be assumed, you could try removing the indexes from the target table beforehand, and only add them again after the insert. Indexes can slow down massive data inserts, because the index data has to be rewritten to often. Only adding the indexes afterwards can be a way to speed things up. (No guarantees though.) — CBroe, Oct 15 '14 at 13:57

score 0 · Answer 1 · answered Oct 15 '14 at 16:01

The Problem might be that the PRIMARY KEY integrity is checked for each row to insert. You should remove the PK before you insert your data. In MariaDB you can disable some of These checks, but I dont know if it is possible for MySQL, too.

An alternative might be to UNION your Temp_load and staging.curves into another table:

CREATE TABLE myNewTable 
SELECT ... FROM Temp_load
UNION
SELECT ... FROM staging.curves

Error inserting 100 million record in MYSQL tables

1 Answers1