Mysql – LOAD DATA stuck at Null State MySQL

bulk-insertinnodbinsertMySQLperformancequery-performance

I'm stuck trying to move one table to another, due to columns now no longer having proper references/meaning to their column titles. In doing this, I've created a new table with the same number of columns as the original, simply with the column names changed to better reflect the data coming in.

My problem is, the data is over 50 million rows, so I'm unable to simply alter the columns in place, so I've made a new table that I wish to use moving forward anyway. I've made the necessary column changes, but now when I try to load the data in, it looks like the process is simply stick.

+—–+——+———–+——+———+——+——-+———————————-

--------------------------------------------------------------------+
| Id  | User | Host      | db       | Command | Time | State | Info                                                                                                 |
+-----+------+-----------+------+---------+------+-------+------------------------------------------------------------------------------------------------------+
| 481 | root | localhost | database | Query   | 8617 | NULL  | LOAD DATA  LOCAL INFILE '/home/SQLBackUps/file.csv' INTO TABLE `tablename` FIELDS   |
| 483 | root | localhost | database | Sleep   | 7174 |       | NULL                                                                                                 |
| 485 | root | localhost | database | Query   |    0 | NULL  | show processlist                                                                                     |
+-----+------+-----------+------+---------+------+-------+------------------------------------------------------------------------------------------------------+

I ran the talk using mysqlimport with the necessary flags, so it should in theory at least be running, but as the state says null, I'm not sure what that really means as NULL, according to the documentation, only appears for show processlist. The file's permissions are as follows:

-rw-rw-rw-  1 mysql mysql 14141022004 May 15 16:42 file.csv

The file was created using SELECT INTO OUTFILE …

Is there anything I can do to speed up this process? Right now it's on a very limited Dev machine with 2GB of RAM and an okay processor, so I understand it will take a bit of time, but given it's been over 2 hours and it's still in a null state, I'm stuck wondering if something is wrong, or if there is anything I can do to speed this process up.

Are there other ways to import the data quickly? Are there any flags I can use to speed this process up? Any, and all help is appreciated.

Update:

I started the process again in the very early morning, and have let it run for about 7 and a half hours now, and the process list looks as follows:

mysql> show processlist;
+----+------+-----------+------+---------+-------+-------+------------------------------------------------------------------------------------------------------+
| Id | User | Host      | db   | Command | Time  | State | Info                                                                                                 |
+----+------+-----------+------+---------+-------+-------+------------------------------------------------------------------------------------------------------+
|  9 | root | localhost | db   | Query   | 22978 | NULL  | LOAD DATA INFILE '/home/SQLBackUps/file.csv' INTO TABLE table FIELDS TERMINATED |
| 15 | root | localhost | db   | Query   |     0 | NULL  | show processlist                                                                                     |
+----+------+-----------+------+---------+-------+-------+------------------------------------------------------------------------------------------------------+

Best Answer

Here is one thing that caught my eye when you replied to my comment: The target table is InnoDB and you are using LOAD DATA INFILE. I see two issues

ISSUE #1 : LOAD DATA INFILE

While LOAD DATA INFILE can load InnoDB tables, that command can be tuned for loading MyYSAM tables. There is only one option to do this: bulk_insert_buffer_size. either setting to very large or setting it to zero to disable it.

There is no synonymous provision for InnoDB.

ISSUE #2 : InnoDB Storage Engine

Let's take a look at the InnoDB Architecture

InnoDB Architecture

Now, picture yourself pushing 50 millions rows into one InnoDB table as a single transaction giving all the plumbing depicted in this elaborate illustration.

To ensure data consistency in the event of a crash, your data has to be written in three places:

There are 128 rollback segments in the System Tablespace (Physical File ibdata1). Your incoming table data must pile up on one Rollback Segment like defensive tackles on a quarterback.
You have an active Double Write Buffer in the System Tablespace. As the name implies, data is being written twice. InnoDB will write to the Double Write Buffer first before writing back to the .ibd files. Those data are used as source data for crash recovery.
The data are also being written in the Transaction Logs (Redo Logs in the Bottom Right Corner) via the Log Buffer. The physical files are ib_logfile0 and ib_logfile1.

My Perspective

InnoDB can handle 1024 current transactions but there are only 128 rollback segments. If there are other transactions going on, you got a New York City traffic jam on your hands. With all the InnoDB Internals to manage through your bulk insert, seeing NULL in the processlist should not be a surprise. You should look at four(4) things to make sure they are up-to-date:

filesize of ibdata1
timestamp on ibdata1
timestamp on ib_logfile0
timestamp on ib_logfile1

SUGGESTIONS

You could set one or more of the following

Disable the Double Write Buffer (set innodb_doublewrite to 0). please set it back to 1 afterwards.
Increase the InnoDB Buffer Pool (increase innodb_buffer_pool_size)
Increase the InnoDB Log File Size (set inndo_log_file_size to 2G)
Increase the InnoDB Log Buffer Size (set inndo_log_buffer_size to 512M)
Change Transaction Flush Behavior (set innodb_flush_log_at_trx_commit to 0). This will disables ACID Compliance (could lost 1 second of transactions upon a crash) but will increase InnoDB Write Performance.
Increase Write IO Thread (set innodb_write_io_threads to 64. You may as well set innodb_write_io_threads to 64)

GIVE IT A TRY !!!

OBSERVATION #1

I noticed you turned off autocommit. That will pile up so much data in ibdata1. Why?

There are seven(7) classes of information that is stored in ibdata1:

Data Pages for InnoDB Tables
Index Pages for InnoDB Tables
Data Dictionary
Double Write Buffer
- Safety Net to Prevent Data Corruption
- Helps Bypass OS for Caching
Insert Buffer (Streamlines Changes to Secondary Indexes)
Rollback Segments
Undo Logs
Click Here to see a Pictorial Representation of ibdata1

Some of this info is made visible to certain transactions depending on the isolation level. Such actions could produce unintended primary key locks and lots of phantom data. As these two things increase, you should expect a fair slow down.

Recommendation: Leave autocommit on

OBSERVATION #2

I see you have this:

alter table item_load disable keys;

DISABLE KEYS does not work with InnoDB. Here is why:

MyISAM : DISABLE KEYS simply shuts off Secondary Index updating for the MyISAM table. When you mass INSERT into a MyISAM table with keys disabled results in a fast table load along with a building of the PRIMARY KEY and all unique indexes. When you run ENABLE KEYS, all Secondary Indexes are built linearly on the table and appended to the .MYD.
InnoDB : As shown in the internals picture of InnoDB, the system tablespave ibdata1 has a structure dedicated to Secondary Index Insertions. At present, there is no provision to handle indexes the same as MyISAM.

To illustrate this, note my attempt to run DISABLE KEYS on an InnoDB table in MySQL

mysql> show create table webform\G
*************************** 1. row ***************************
       Table: webform
Create Table: CREATE TABLE `webform` (
  `nid` int(10) unsigned NOT NULL,
  `confirmation` text NOT NULL,
  `confirmation_format` tinyint(4) NOT NULL DEFAULT '0',
  `redirect_url` varchar(255) DEFAULT '<confirmation>',
  `status` tinyint(4) NOT NULL DEFAULT '1',
  `block` tinyint(4) NOT NULL DEFAULT '0',
  `teaser` tinyint(4) NOT NULL DEFAULT '0',
  `allow_draft` tinyint(4) NOT NULL DEFAULT '0',
  `submit_notice` tinyint(4) NOT NULL DEFAULT '1',
  `submit_text` varchar(255) DEFAULT NULL,
  `submit_limit` tinyint(4) NOT NULL DEFAULT '-1',
  `submit_interval` int(11) NOT NULL DEFAULT '-1',
  PRIMARY KEY (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table webform disable keys;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------+
| Level | Code | Message                                                     |
+-------+------+-------------------------------------------------------------+
| Note  | 1031 | Table storage engine for 'webform' doesn't have this option |
+-------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.27-log |
+------------+
1 row in set (0.00 sec)

mysql>

OBSERVATION #3

You noticed that MyISAM loads 20x faster than InnoDB. Would you like that to be more like 24-25 times faster? Then run the following:

ALTER TABLE item_load ROW_FORMAT=Fixed;

This will speed up INSERTs times 20-25% without any other DDL Changes. Side effect: The MyISAM table can grow 80%-100% in size, possibly larger.

You could run this on an InnoDB table as well, but the ACID-compliant behavior and MVCC of InnoDB would still be the bottleneck of its performance, especially if VARCHAR fields increase significantly are are written to ibdata1.

Mysql – LOAD DATA INFILE on a MyISAM table blocks user read-queries, can we get around this

If your table is a MyISAM engine you can try to specify CONCURRENT in your statement.

Load data ref.

Concurrent insert ref.

With LOAD DATA INFILE, if you specify CONCURRENT with a MyISAM table that satisfies the condition for concurrent inserts (that is, it contains no free blocks in the middle), other sessions can retrieve data from the table while LOAD DATA is executing. Use of the CONCURRENT option affects the performance of LOAD DATA a bit, even if no other session is using the table at the same time.

UPDATE FOR FREE BLOCK EXPLAIN

When you delete a row a free block can occur, if you have free blocks in the "middle" of your insert, for example if you replace deleted rows(index and/or primary key) with your load data, concurrent insert do not work, when free blocks are filled in future insert become concurrent again.