0

I have a 1 billion record stored in csv file. I'm using LOAD DATA INFILE to import the record to the table.

create table weibo_bind
(
    column1 bigint
        primary key,
    column2 bigint
);

And my csv file is column1,column2.

LOAD DATA INFILE '/path/to/demo.csv' IGNORE INTO TABLE weibo_bind FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

But how can I debug or how can I check the progress of importing? Because it takes a lot of time to import.

Or is there any way to speed up?

I'm using

mysql Ver 8.0.22, Innodb

8
  • 1
    Which MySQL version are you using? Is the table innodb? If so ensure large innodb buffer pool size and a large innodb log file size (~1G). With MariaDB SHOW PROCESSLIST will show the progress.
    – danblack
    Nov 25 '20 at 2:49
  • 1
    If you want to check from external application then you may obtain approximate information by checking table's .IBD file size.
    – Akina
    Nov 25 '20 at 7:27
  • @danblack SHOW PROCESSLIST shows the command which is executing, but still no progress information.
    – einverne
    Nov 25 '20 at 7:38
  • @Akina Thank you for your information, I checked my .IBD , the file becomes much large then the origin one. So I am still confused about how many records have been imported.
    – einverne
    Nov 25 '20 at 7:42
  • 1
    the file becomes much large then the origin one This is a norma. Moreover, the final size will depend on rows order in source file for the same data array, on the data present and the amount of not used space caused by delete/truncate in a table before importing.. that's why I tell that the info is approximate. the file becomes much large then the origin one. I think that the difference is not more than ~30%.. if it is more than the rows order in your file is very unfortunate.
    – Akina
    Nov 25 '20 at 7:47
0

Sort the incoming file by the PRIMARY KEY; this will speed up the LOAD. Don't have any secondary keys during the load.

There is no way (that I know of) to watch the progress in 8.0.

The .ibd file size is not a reliable indication -- you can't easily predict how big it will eventually be. Datatypes, data values, PK, other indexes, etc, impact the size.

Also, that file will grow in bursts.

Having just two BIGINTs (8 bytes each), the table might be about 40-60 bytes per row. The smaller range might be about right if the data is in PK order; the larger if not.

I hope the query does not timeout.

The process is probably I/O-bound, indicating that nothing you could do would speed it up.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.