In one of my present projects there is a requirement to upload tens of millions of records from several csv files into a database. The files are uploaded to the server with the help of Ajax controlled browser/server-transactions. On the server the file contents should be checked and then transferred into an appropriate database table. In my case a MyISAM table.
This is the first time that I had to deal with such big record numbers on a MySQL database. On an Oracle databases I worked with around 30 million record tables some years ago – but those data came from other sources and were supplied more continuously than in form of a one step csv file import.
Although the data structure and table structures for the import are flat the pure amount of data makes it necessary to think a little about the way of transferring them from file lines to records of a MySQL database table with the help of a PHP server program. Below, I really do not present fundamentally new insights – a MySQL expert will not find anything new. But maybe others who for the first time stumble into a big data import problem may find some of the hints below useful.
A simple, but time consuming standard approach for a csv import with PHP
In the past when I had to import data from files I often used a sequence of steps comprising
- the opening of the file for reading,
- the use of fgetcsv() to read in one line,
- the usage my own and problem specific perform check methods of the identified fields,
- the processing of a sequence of one INSERT SQL command for each of the file lines.
Reasons for such an approach were: You have complete control about the data and the import process. You may check the contents of each file line thoroughly already before inserting it. You may send intermediate positive or negative messages to the client via Ajax, etc..
However, after some thorough tests I found the following:
A data import based on the approach described above is really time consuming. To give you an impression: The typical import time I found for my hardware and virtualization conditions was
108 seconds for
- a standard MySQL 5 database on a small virtualized KVM server instance with one CPU core and 1GB RAM (256 MB reserved for MySQL; host and KVM guest both run Opensuse 13.1),
- a file with 1 million lines (each containing 6 fields – 1 field for a primary integer key / 3 integer fields together forming another unique key / 1 quantity value field )
- one INSERT for each line with parallel index building for each of the three integer columns.
That really meant trouble for my rather impatient customer. The extrapolated time for the import of similar contents of 8 additional files and a potential factor of 50 with respect to the final number of file lines/records made him nervous. And he was also aware of the fact that due to index building the total required import time may grow faster than linear with record number.
It may be hard to say good bye to good old habits. But when the upload and import time counts more than control one should look for other import methods. After some tests I found the following approaches which can lead to a significant improvement of import times:
Improvement 1: Work with bunches of records per INSERT
What is the first guess for time consuming operations? It is the overhead the preparation of an SQL statement takes. If such a statement is true one should reduce the amount
of INSERT statements. And really: After some trials I found that you may accelerate things significantly by avoiding to use one INSERT for each record/file line. Working with bunches of records bundled to be saved in just one INSERT statement really helps to save time. This seems in a way to be natural and understandable; see also the following article:
http://dev.mysql.com/doc/refman/5.1/en/insert-speed.html
So, one thing you could try is to put the result of several fgetcsv() calls into one INSERT statement. In a next step you would try to find an optimum number of records per INSERT by systematic testing. This may already give you a substantial acceleration factor.
Improvement 2: Use “LOAD DATA INFILE … “
If you need an acceleration factor above 10 for loading csv data, I strongly suggest that you use a special feature of the MySQL engine and its related SQL extension in your PHP loader program. See:
http://dev.mysql.com/doc/refman/5.6/en/extensions-to-ansi.html
http://dev.mysql.com/doc/refman/5.6/en/load-data.html
The article promises an acceleration factor up to 20. I can confirm a factor of 18:
In a case of a 1 million record csv file (each line for 6 fields, 4 indices generated on 4 columns) I got the following acceleration:
- Single INSERTS: 108 seconds
- LOAD DATA : 6 seconds
(Both loading processes were done with some defined and active indices that were built up during the filling of the table. Therefore, forget about the absolute numbers – without any indices you may load 1 million records in a time around or below one second.)
A relative factor of 18 is impressive. However, the usage of an optimized loop like program taking and saving (probably bunches of record data) also may have disadvantages. One question is: How are data with wrong field numbers or wrong data type formats handled? One can imagine that there might be a policy that the program cannot stop because of one defect in one line. The error handling would depend on a reasonable summary of which file lines/records were defect. We come back to this point below.
Improvement 3: Separate the index generation from the data filling process
Even if you use “LOAD DATA…” there is still room for more improvement if you separate the index generation from the filling of the table. This is described in detail in the documentation of the “LOAD DATA” statement. See:
http://dev.mysql.com/doc/refman/5.1/de/load-data.html
I have not tried or investigated this yet; so, I can neither give a general impression of this recipe nor an acceleration factor. But I consider it worth trying when I find the time. Maybe after the first 10 million record import 🙂 .
Addendum, 19.09.2014:
I can now confirm that separating the index creation from the loading process with “LOAD DATA INFILE” may give you another significant factor. This is especially true when unique indices are built in parallel to the data loading. Already omitting a simple auto-incremented unique index over one column may give you a factor of around 2. See a forthcoming article for more details.
Improvement 4: Combine quantities which have the same dependencies on keys in one file and insert them into one and
the same table
In my case we have to import 2 groups of data for N different quantities with the same key dependency – in our case 2 groups with 4 different quantities, each. Due to the requirements of using these data independently in different calculation steps, it seemed to be wise in a first approach to load all these quantities into different tables despite the very same key structure and key dependency (in our case on integer triples describing a 3-dimensional finite numerical grid structure).
However, for the sake of performance one should really reconsider and challenge such a separate table/file strategy and do some performance tests for a combined table/file strategy where all quantities with the same keys reside in one and the same file/table. Reasons:
- To deliver data for one and the same key combination in one combined file is also a matter of transfer efficiency to the server as the total amount of data to be transferred via the network/Internet gets less.
- It is not necessary to change your programming statements for calculations with data taken from separate tables if you work with appropriate views of the combined table that “simulate” such separate tables for you. In my case views of the type “MERGE” were appropriate. I could not see any significant performance reductions when using views instead of tables.
- Regarding csv import the most important effect is that you instead of N times importing a file of the same structure you only import data from one file. That reduces the amount of INSERTs by a factor of N. The question remains how that relates to the fact the each INSERT writes more columns of the import table in the database.
The last point is really interesting. In our test case which may be a bit special we got the following result when using the “LOAD DATA INFILE” statement for loading directly from a file with four quantities combined instead of just one:
For a million records the import time did not change very much in comparison to the time of 6 seconds required for importing a file for just one quantity – actually the difference for loading our file for 4 quantities was below 10%. That means that in our case we gained an additional factor of almost 4 for shortening the total required import time.
So, by using “LOAD DATA INFILE” AND combining data quantities with the same key depency in just one file I could reduce the total loading time for 4 files with each a million records by a factor of around 70.
A hint regarding error handling for LOAD DATA
In our case we still use the old “mysql_query” interface and not “mysqli”. Do not ask me for the reasons – most of it is laziness. From a professional point of view I advice against the usage of the old interface – especially as it may disappear with coming PHP versions. Nevertheless, if you use the old interface you will probably use statements like ( I simplify )
Wrong approach for error control
$sql_load = " LOAD DATA INFILE '" . $this->imp_file . "'" .
" INTO TABLE " . $this->tbl_name .
" FIELDS TERMINATED BY " . "';'" .
// " LINES TERMINTED BY " . "\r\n" .
" IGNORE 1 LINES";
if (!mysql_query($sql_load, $this->DB->db) === false) {
// Steps to control errors
}
(You may need the commented line in case of files generated on Windows).
Do not always expect something reasonable from this error detection approach! My impression is that it works reliably only in case of SQL syntax errors. But not in case of defect lines of the csv file. Instead you may have to explicitly look at the output of mysql_error():
Better approach
$sql_load = " LOAD DATA INFILE '" . $this->imp_file . "'" .
....
}
if ( mysql_error() != '' ) {
// Steps to control errors
}
Or you may even use “Show Warnings“. An explicit look at the existence of errors or warnings is also helpful if the whole import is part of an Ajax transaction and you want send appropriate error messages to the client in case of detected errors during the “LOAD DATA ..” process. This leads us to the next point.
What about the requested checks of the data ?
In our approach centered around “LOAD DATA INFILE ..” we have lost track of another requirement, namely that the file data to be imported should first be checked for errors. Well, in this early stage of experimenting I have two – probably not satisfactory – comments:
Many tests can be made before or after the “LOAD DATA” controlled database import. This holds e.g. for consistency checks like number comparisons. For such points you can perform pretty fast SELECT COUNT() statements on the filled database table. In our case e.g:
For all network nodes (described by tupels of integer key numbers): Are there equal numbers of records in the third key dimension (in our case time) for all nodes?
Other checks may, however, directly concern the quality and type consistency of the input data. So, what happens, if “LOAD DATA …” stumbles across a line with missing fields or a wrong type of data (compared to the database table column definitions) ?
Tn the documentation http://dev.mysql.com/doc/refman/5.1/en/load-data.html it is described what e.g. happens if a line has insufficient fields or too many fields. Automatic type conversions are probably tried, when the type of a field value does not fit.
I have not yet tested what really and exactly happens in such error cases. The official documentation is not at all clear to me regarding this point. It seems to be reasonable to assume that an automatic routine for the import of csv data lines would try anything to get a line into the database table. So, I would expect a standard procedure to compensate missing or too many fields and trying some automatic type conversions before a line is skipped. I would not relly expect that a faulty line will lead to a direct stop of the import process.
In addition I would expect something like a bad record log. Unfortunately, there are indications that such a log is not generated. See:
http://chrisjohnson.blogsite.org/php-and-mysql-data-import-performance/
The documentation http://dev.mysql.com/doc/refman/5.1/en/load-data.html says:
When the LOAD DATA INFILE statement finishes, it returns an information string in the following format:
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
So at least this information could in principle be evaluated. In addition the documentation says:
Warnings occur under the same circumstances as when values are inserted using the INSERT statement (see Section 13.2.5, “INSERT Syntax”), except that LOAD DATA INFILE also generates warnings when there are too few or too many fields in the input row.
You can use SHOW WARNINGS to get a list of the first max_error_count warnings as information about what went wrong. See Section 13.7.5.42, “SHOW WARNINGS Syntax”.
“Show Warnings” actually also informs about errors. See: http://dev.mysql.com/doc/refman/5.1/en/show-
warnings.html
However, the stupid thing is that you still may not get the relevant information (What record was wrong? What field had wrong values ? ) from this when the INSERT eventually worked. What we really would need is something like a bad record log.
So, right now it seems to me that the impressive reductions of load times when using “LOAD DATA INFILE …” also does have its drawbacks and disadvantages.
Therefore, I am thinking about precursor programs that would open the file and analyze the contents of each line before “LOAD DATA” is afterwards used on the whole file. The overhead for such precursor programs must of course be evaluated by tests. When I have gotten more experience with “LOAD DATA” I will come back to this point in this blog.
Conclusion
Using an SQL statement with “LOAD DATA INFILE …” will reduce the time to import flat CSV data into a corresponding database table significantly by factors of around 18 in comparison to standard methods based on INSERTS per file line. The official MySQL documentation promises a bit more – but the factor measured for a real world example is still impressive. A disadvantage of the “LOAD DATA ..” statement from my point of view is that the handling of errors in a PHP program remains unclear.
Nevertheless, for the time being I still would recommend the usage of “LOAD DATA INFILE …” in PHP programs for uploading csv files – if the performance of a csv import is a dominant objective in your project.
