Tag Archives: MySQL

MySQL – effiziente Prüfung von Tabellenspalten auf (ausschließlich) vorhandene Default-Einträge

Posted on by

Einige meiner Leser wissen, dass ich mich für PHP-basierte Simulationsrechnungen mit dem Thema des Uploads von CSV-Daten-Files für viele Tabellen befassen muss. Nun haben wir eine Situation, in der eine Tabelle zwar viele Spalten enthalten kann, aber für bestimmte Simulationen nicht alle Spalten zwingend mit Werten gefüllt werden müssen. Um die Upload-Zeiten gering zu halten, werden diese Spalten in den CSV-Dateien gar nicht übermittelt. Das durchzuführende LOAD DATA INFILE Statement wird entsprechend angepasst und versorgt die Spalten, für die das File keine Daten liefert, mit Default-Werten.

Nun erfordert jede unserer Simulationsrechnungen eine vorherige Prüfung von Konsistenzbedingungen. Dabei ist es u.a. wichtig, welche Spalten ausschließlich mit Default-Werten gefüllt wurden und welche nicht. Die Meinung eines beteiligten Entwicklers war, dass man diese Information – wenn nötig – unmittelbar aus dem tatsächlich gegebenen Tabellenzustand ermitteln sollte, um immer eine verlässliche Entscheidungsgrundlage zu haben.

Als Voraussetzung nehmen wir an, dass wir aus Platzgründen keine Indices für die einzelnen oder gekoppelten Daten-Spalten der zu untersuchenden Tabelle angelegt haben.

Abfrage über mehrere SQL-Statements für jede Spalte

Der erste naive Anlauf war dementsprechend, in mehreren SQL-Abfragen Spalte für Spalte auf die Anzahl der gesetzten Default-Werte zu prüfen und mit der Gesamtzahl der Tabellen-Records zu vergleichen. Das einzelne Select würde dann etwa so aussehen:

SQL-Typ1:
SELECT COUNT(*) FROM myTable WHERE colx=defaultValue;

Das Ganze pro Spalte für alle zu untersuchenden Tabellen. Ein solches Vorgehen erschien mir ineffizient – insbesondere wenn eine Tabelle viele zu untersuchende Spalten aufweist und man das Ganze für viele Tabellen machen muss. Der Grund:

Jede dieser Abfragen führt einen Full Table Scan durch, denn man wird in den seltensten Fällen einen Index über jede einzelne Spalte oder alle Spalten gelegt haben.

Wir reden in unserem Fall zudem über potentiell große Tabellen mit mehreren 10-Millionen Einträgen.

Ist die Abfrage in einem einzigen SQL-Statement effizienter?

Zunächst dachte ich deshalb darüber nach, ob man die Aufgabe nicht in einem einzigen Statement durchführen kann. Ergebnis:

SQL-Typ 2:
SELECT
SUM(IF(col1 = default_val_col1, 1,0)) as num_def_col1,
SUM(IF(col2 = default_val_col2, 1, 0)) as num_def_col2,
SUM(IF(col3 = default_val_col3, 1, 0)) as num_def_col3,
…
…
SUM(IF(coln = default_val_coln, 1, 0)) as num_def_coln
FROM myTable;

Das ist zwar schön und der Ansatz ist sicher für bestimmte andere Auswertungen auch von grundsätzlichem Interesse (s. etwa: http://www.randomsnippets.com/2008/10/05/how-to-count-values-with-mysql-queries/). So kann man ähnliche Statements z.B. einsetzen, wenn man herausfinden will, wie oft vorgegebene, unterschiedliche Werte in genau einer Spalte vorkommen.

Aber ist das in unserem Fall vom Zeitbedarf her wirklich effizienter als der erste Ansatz? Die Antwort lautet: Nur geringfügig.

Die folgenden zwei Abbildungen zeigen typische Werte für SQL-Typ 1 und SQL-Typ2 auf einem einfachen Testserver für eine Tabelle mit ca. 10 Millionen Einträgen und 6 untersuchten Wertespalten:

Messung für Statement vom SQL-Typ1 für eine Spalte

sqlff1

Messung für Statement vom SQL-Typ2 für 6 (!) Spalten

sqlff2

Wir kommen (nach Multiplikation mit der Anzahl der Spalten) auf etwa 6 sec für Typ1 und 5.3 sec Gesamtlaufzeit für Typ2. Der Unterschied ist also nicht weltbewegend. (Hinweis: Ein zusätzlich durchzuführendes Count(*) kostet praktisch keine Zeit; wir haben das hier deshalb weggelassen.)

Warum erhalten wir solch ein Ergebnis?
Nun die Anzahl der zu treffenden Entscheidungen ist praktisch identisch und gezählt wird in beiden Fällen. Demgegenüber tritt das mehrfache Durchlaufen der Tabelle bei Typ 1 zum systematischen Bereitstellen der Daten einer Zeile in den Hintergrund. Im besonderen, wenn die Tabelle schon im Cache vorliegen sollte.

Noch ineffizienter wird ein Ansatz, bei dem mit COUNT(Distinct) gearbeitet würde und bei den Spalten die nur einen Wert aufweisen, diesen Wert zusätzlich ermittelt würde :

Allein das Statement für unsere Testtabelle

SELECT COUNT( DISTINCT sssim ) AS dist_sssim, COUNT( DISTINCT mult ) AS dist_mult, COUNT( DISTINCT dobs ) AS dist_dobs, COUNT( DISTINCT sigmad ) AS dist_sigmad, COUNT( DISTINCT sigmalt ) AS dist_sigmalt, COUNT( DISTINCT fexp ) AS dist_fexp
FROM `alien_ss_comb_itm`

dauert hier 7.6 sec. Dies liegt daran, dass hier temporäre Datenstrukturen bzw. Tabellen erzeugt werden.

Die langsamste Abfrage-Alternative

Es gibt natürlich eine weitere Alternative zum Typ 1 – Statement:

SQL-Typ3:
SELECT colx as dist_colx, COUNT(colx) as num FROM myTable GROUP BY colx;

Die Anzahl der Treffer, der Ausgabewert “num” und der ggf. gefundene alleinige Wert können auch hier dafür verwendet werden, um festzustellen, ob die Spalte nur mit Default-Werten gefüllt ist.
Für dieses Statement dauert in unserer Testtabelle die Abfrage über eine einzige Spalte allerdings bereits 2.5 sec:

sqlff3

Auch hier ist das Anlegen von temporären Datenstrukturen und der erforderliche Einsatz von Filesort in der MySQL-DB die Ursache der schlechten Performance.

Zu kostspielig? Alternativen?

Offenbar ist so eine Prüfung für große Tabellen relativ kostspielig. Mehrere Sekunden, um festzustellen, welche Spalten mit regulären Daten gefüllt wurden? Geht gar nicht …

Zugegeben: Das hängt immer davon ab, in welchen Zusammenhang – also Art von Upload- oder Serverlauf – man eine solche Prüfung einbettet. Dauert ein Simulationslauf für große Tabellen 15 Minuten, so sind 5 sec egal. Auch bei Uploadzeiten von 50 sec kann man 10% Zuwachs verschmerzen. Auch zugegeben: Man ist von keiner weiteren Information außer dem aktuellen Tabellenzustand selbst abhängig.

Würde das Anlegen eines Index pro Spalte helfen?
Natürlich würde das SQL-Statements vom Typ 1 extrem beschleunigen! Aber kann man sich Indices pro Spalte denn bei großen Tabellen überhaupt leisten? Wohl eher nicht: Indices kosten u.U. und je nach Kardinalität enorm viel Platz und ihr Anlegen erhöht den Bedarf an CPU-Zeit im Rahmen eines Dateiimports beträchtlich. Wenn man also Indices über einzelne Daten-Spalten nicht noch für andere Zwecke benötigt, sollte man deshalb eher die Finger davon lassen.

Beschleunigung von erforderlichen Abfragen auf den Füllgrad von Spalten mit
Defaultwerten über eine Hilfstabelle

Muss man die vorgesehene Prüfung überhaupt über SQL-Abfragen auf den Spalten der zu untersuchenden Tabelle machen?

In unserem Fall, in dem es nur darum geht, ob in einer Spalte aussschließlich Default-Werte vorliegen, natürlich nicht! Der einzig sinnvolle Weg führt mal wieder über eine Hilfstabelle, die bereits auf Grund einer Analyse des Aufbaus der importierten CSV-Datei erstellt wird.

Im Fall der Verwendung von “LOAD DATA INFILE” genügt dabei ein Check der ggf. vorhandenen Header-Zeile mit Spaltenbezeichnungen und der ersten Datenzeile; ein solcher Check ist sowieso obligatorisch, um das “LOAD DATA INFILE” Statement um explizite Angaben zum Füllen der Spalten zu ergänzen, für die die CSV-Datei keine Daten liefert. Ein so ergänztes “LOAD DATA INFILE”-Statement ist viel schneller als das Füllen mit Defaultwerte der Bank selbst zu überlassen. Einen Check der Korrektheit der Struktur der übergebenen Daten benötigt man im übrigen sowieso.

Lösung: Auf Basis der CSV-Datei-Analyse füllt man nach einem erfolgreichen Laden der CSV-Daten in die Ziel-Tabelle zusätzlich eine kleine Hilfstabelle, in der für jede Tabelle und Spalte Werte eingetragen werden, die besagen, wie oft der Default-Wert vorliegt und ob er ausschließlich vorliegt.

Alle nachfolgenden Prüfungen greifen dann auf den Inhalt dieser Hilfstabelle zu. Das ist selbst bei einem zusätzlichen Vergleich von Zeitstempeln zwischen Hilfs-Tabelle und Original-Tabelle um Größenordnungen schneller als ein Statement vom Typ 2 – nicht nur bei großen Tabellen.

Sollte die Hilfstabelle aus irgendeinem Grunde mal nicht gefüllt sein, oder die Zeitstempel nicht passen, kann man ja in dem Programm, das die Belegung von Spalten mit Defaultwerten prüfen will, immer noch auf das Statement vom Typ 2 als Fallback zurückgreifen. Aber das ist maximal nur genau einmal erforderlich!

Genau so haben wir es dann in unserem Projekt auch gemacht!

Es liegt im übrigen auf der Hand, wie man mit ein wenig Parametrierung eine allgemeinere PHP-Funktion erstellt, die ermittelt, wie oft spaltenspezifische, vorgegebene Werte in einer importierten Datentabelle auftauchen. So ist die (dynamische) Erzeugung von SQL-Statements vom Typ 2 für Tabellenspalten ohne Index also für bestimmte Auswertungen ggf. doch von Nutzen.

MySQL – SELECT INTO OUTPUT FILE – vermeide zusätzliche UNION Statements!

Posted on by

Wenn es um den Export relativ großer Tabellen in CSV-Dateien geht, greift man bei MySQL-Datenbanken auf eine spezielle Form des SELECT Statements, nämlich “SELECT INTO OUTPUT FILE … “. Ich nenne das nachfolgend der Einfachheit halber ein “Export SELECT”. Siehe hierzu:
http://dev.mysql.com/doc/refman/5.7/en/select-into.html

Oftmals benötigt man aber nicht nur die reinen Daten sondern eine CSV-Datei mit einer führenden Zeile, in der die Bezeichnungen der einzelnen Felder/Spalten auftauchen (“column heading”). Die gängige Empfehlung vieler Beiträge zu diesem Thema ist, das Ergebnis des oben genannten Statements “SELECT INTO OUTPUT FILE … ” per UNION mit einem “künstlichen SELECT-Statement zu verbinden, dass genau die Strings der ersten Zeile erzeugt. Siehe hierzu etwa
http://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format (und dort einen entsprechenden Kommentar zum Lösungsansatz der Forums-Frage)
oder aber
http://www.mysqltutorial.org/mysql-export-table-to-csv/
und dort der Abschnitt zu “Export Data with Column Headings”.

Entsprechende Vorschläge folgen dem Muster A 

(SELECT 'Name of Column 1', 'Name of Column 2', 'Name of Column 3')
UNION
(SELECT column_name_1, column_name_2, column_name_3
FROM TABLE_NAME
INTO OUTFILE 'absolute_path_to_csv_file'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n');

Festzustellen ist hierzu Folgendes:

Ein solches Statement funktioniert zwar und produziert das gewünschte Ergebnis; aber es reduziert die Performance des Daten-Exports in eine Datei drastisch – speziell wenn man mit großen Tabellen und mehreren Millionenen oder gar Hunderten von Millionen Records arbeitet. Dann entfaltet sich das UNION-Statement zu einem echten Performance-Killer!

In einem konkreten Testbeispiel konnte ich den Export aus einer Datei mit einer Million Records vergleichen. Der Export mit einem Union Statement dauerte fast 50 Sekunden; läßt man die Kopfzeile dagegen nach dem Muster B

SELECT column_name_1, column_name_2, column_name_3
FROM TABLE_NAME
INTO OUTFILE 'absolute_path_to_csv_file'
FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

weg, so dauerte der Export nur 1,8 Sekunden. Das Verhältnis wird noch schlimmer bei größeren Record-Zahlen der Tabelle.

Ein wenig tieferes Nachdenken und eine SQL-Analyse mit “Explain” machen auch klar, woran das liegt: Der Einsatz von UNIONS erfordert das Zusammenfügen der Resultsets der verschiedenen SELECTs. Dazu muss jedes Resultset aber erstmal generiert werden. Dazu müssen temporäre Datenhaltungsstrukturen aufgebaut werden. Dies kollidiert jedoch mit der ansonsten speziell optimierten Funktionalität des “SELECT INTO OUTPUT FILE”-Statements, nämlich die Tabelleninhalte aus den Datenbankfiles mit speziellen Methoden direkt auszulesen und sofort in eine Datei zu schreiben.

Ich kann daher nur jedem raten, beim Export wirklich großer Tabellen in CSV-Dateien auf jegliche Kombination des “SELECT INTO OUTPUT FILE”-Statements mit anderen SELECTS unter Rückgriff auf “UNION” zu verzichten. Das zieht ggf. den Verzicht auf eine erste führende Zeile nach sich.

Änderung und Nachtrag 08.01.2015:
In Fällen mit kleineren Dateien von bis zu n x 100 MByte kann es – je nach RAM-Ausstattung des Systems
– u.U. besser sein, zunächst den reinen Daten-Export (gem. Muster B) durchzuführen und danach die erzeugte Datei mit PHP File-Funktionen oder Systemfunktionen nachzueditieren und die gewünschte erste Zeile mit der Feldinformation hinzuzufügen. Das ist aber zu testen. In meinem Testfall mit 1 Million Datensätzen war das File kleiner als 50 MByte und lag auf einer SSD. Zudem ist das System hinsichtlich der Plattenzugriffe gut gepuffert. In diesem Fall erhöhte sich die benötigte Zeit durch das Hinzufügen der Kopfzeile untr PHP kaum spür- und messbar über die 1,8 Sekunden hinaus. Die Änderung war gegenüber dem oben erwähnten Zeitunterschied des UNION-Statements völlig vernachlässigbar.

Bei Dateigrößen von mehreren hundert Mbyte oder gar im GByte-Bereich wird ein nachträgliches Editieren unter PHP aber ebenfalls zu Problemen führen, da das File erst einmal zu lesen und dann mit dem String für die erste Zeil zu konkatenieren ist. Man muss in Tests prüfen,

  • ob das für solche Operationen erlaubte Memory unter PHP hinreichend ist (und es ggf. anpassen)
  • und ob sich die Lesezeit unter PHP als kleiner oder größer als die von MySQL aufgebrachte Zeit für ein UNION herausstellt.

Am besten erscheint es mir, bei wirklich großen Datenmengen im Sinne der Performance schlicht und einfach auf die führende Kopfzeile zu verzichten. Derjenige, der das exportierte CSV-File in irgendeiner Weise nutzen will, muss eben über die Feldreihenfolge Bescheid wissen.

MySQL: LOAD DATA INFILE, multiple csv-files and index creation for big tables

Posted on by

In some of the last articles in this blog I have discussed the following aspects of a data import from csv-files into MySQL ISAM tables:

  • The use of the SQL command “LOAD DATA INFILE” may accelerate the import by a factor of at least 18 in comparison to reading the data line by line with fgetcsv() and inserting single records into the database table.
  • The creation of a (small) helper table may often be useful to improve the performance of repeated consistency checks based on the evaluation of “derived result sets” from COUNT(DISTINCT …) subselects or subqueries on big tables.

The first point implies a straightforward use of “LOAD DATA INFILE” in your PHP programs. The second point may be applicable if certain data structures are given and it requires a balanced use of indices.

See:
Importing large csv files with PHP into a MySQL MyISAM table
MySQL/PHP: LOAD DATA – import of large csv files – linearity with record number?
MySQL/Aggregation: Comparing COUNT(DISTINCT) values on big tables

In these articles I have more or less neglected two things:

  • A possible splitting of the import process into several sequential processes. Instead of loading one big file we may rather distribute the data records over several files. Each individual process loads data from just one file; so the data are loaded file after file into the table.
  • The impact of index creation on the data import time.

Reasons for a sequential data loading from multiple csv-files are: Better control of the loading process due to shorter times for well defined data packages and also an adaption to memory limits. However, without tests it is not clear whether such an approach may have an impact on the overall loading performance.

The second point raises the question whether and how index creation influences the efficiency of “LOAD DATA INFILE”. Note that the MySQL documentation explicitly recommends to separate index creation from the loading process. See:
http://dev.mysql.com/doc/refman/5.1/de/load-data.html

This article provides some numbers from test runs for both measures. We shall see how the combination of both points influences the overall performance of the data import. Furthermore, an optimized loading process may influence your strategy regarding consistency checks during a sequential loading of several files.

Loading data from a sequence of multiple files instead of loading from one big file

I briefly have a look at some of the aspects of loading data from a sequence of files. In the article PHP/MySQL/Linux: File upload, database import and access right problems I have already discussed that one may split the data to be imported into bunches of records and distribute them over several files. I have pointed out that the data splitting shall not be done by fields. Instead records should be distributed over multiple files; but each record should contain all of the associated (!) fields if you want to profit from the performance of “LOAD DATA INFILE”.

A further aspect you have to take care of are logical relations between the data records. Such relations may become relevant in case you already want to work with the data in between 2 file loads:

Lets say you have
data with the structure

nr,    m,n,    i    Q1, Q2, Q3

m,n,i shall represent keys which together form an unique key of each record. An [m,n]-combination may e.g. define an object called “node” and [i] may represent time periods. Let us further assume that after having loaded one file you for some reason want to check the complete SUM (Q2) over all (!) i-values for each “node” before you (or a PHP program) import the data of the next file. Then you must organize your data such that all records related to one and the same node are placed inside exactly one file – otherwise you would get a moving target for your SUM when loading your files. In general, the distribution of the records over several files must comply with logical conditions if you want to work with the data already during the loading process.

Another aspect of distributing records over several files may be memory considerations: In one of the articles named above we saw that the loading and index generation consume more and more memory for buffering as the absolute numbers of records grow. Therefore, if you want to avoid conflicts with limited RAM, it may be reasonable to load bunches of records (in form of several csv-files) instead of processing on one gigantic file with “LOAD DATA INFILE” .

In most cases the central objective of using data packages is a better control of the file transfer and loading processes: With multiple files you can build up a “file pipeline” on the server. The files in this “pipeline” are handled by a repeatedly running import job. The required time interval to transfer each file to the server and load its data into the base can be limited to a reasonable value by choosing an appropriate number of records per file. After each file is processed on the server you may analyze the new table status, the pipeline status and send success or failure messages to the client (e.g. a browser). With a web client the communication exchange can be managed by Ajax.

A sequential loading of files also provides you with the possibility to fill the file pipeline on the server in parallel to loading data of already completely received files into the database. Despite a more complicated communication structure between a browser client and the server you can still keep control over both the transfers from the client to the server as well as over a sequence of server processes loading loading data into the base – file by file.

I have realized the sequential handling of several csv-files on a web-server with Ajax and PHP in the following way:
A web client starts an Ajax controlled PHP job that analyzes the number of files in a pipeline (a special directory) on the server and chooses the next file to import by some given rules. After having loaded the data the job returns control to the client and sends information about the processed file, the loaded number of records and the new status of the database table. Depending on parameter settings there and of course depending on the quality of the status information from the server a new server import job for the next file may automatically be started from the client with the help of Ajax from the client. This continues until the pipeline is empty. The last job may create required indices on the filled database table – depending on user settings.

Is the total import time independent of a record distribution over multiple files?

In the article MySQL/PHP: LOAD DATA – import of large csv files – linearity with record number? I discussed the seemingly linear dependency of “LOAD DATA INFILE” on the total record number of ONE file. Now, we should in addition investigate possible performance variations for import processes

  • that load a given number of records sequentially loaded from
    several csv-files;
  • with or without a parallel building of (unique) indices on defined columns.
  • that perform the creation of required (unique) indices only AFTER all records have been loaded into the database table.

Importing 5 million records from one or two files

Let us again take the data structure

nr,    m,n,    i    Q1, Q2, Q3

All Q-values are floating point numbers with double accuracy (MySQL type DOUBLE) in our scenario. We assume that at the beginning of our import runs we have already defined some indices on our database table:

  • a unique index (with auto-incrementation) on the column “nr”
  • a unique index defined for the columns “[n,m,i]”

In the course of our tests we shall study the effect of

  • dropping these indices,
  • creating them whilst filling the table with data,
  • creating them after having filled the table with data.

For each test case with 5 million records we look at two different situations :

  • Importing one csv-file with all 5 million records.
  • Sequentially importing 2 csv-files – one with 3 million records and one with 2 million records.

Our test server is a relatively small one in form of a KVM virtualized LAMP server with 2 GByte assigned RAM, CPU INTEL Q9550, Raid 10 disk array. For the tests I worked with MySQL MyISAM tables. In my concrete test table I chose:

distinct m values : 1 (as an extreme situation)
distinct n values : 13580
distinct i values : 386

I got the following numbers:

import by 1 file import by 2 files with unique ai-index on nr with non-unique index on nr without any index on nr with unique index on [n,m,i] with non-unique index on [n,m,i] without any index on [n,m,i] response time
yes no yes no no yes no no 30 secs
no yes yes no no yes no no 30 secs
yes no no yes no yes no no 30 secs
no yes no yes no yes no no 30 secs
yes no no yes no no yes no 21,3 secs
no yes no yes no no yes no 24,8 secs
yes no no no yes yes no no 19.2 secs
no yes no no yes yes no no 19.4 secs
yes no no no yes no yes no 9.8 secs
no yes no no yes no yes no 13.3 secs
yes no no no yes no no yes 8.7 secs
no yes no no yes no no yes 8.8 secs

The accuracy of the response times is in the range of a second – depending on the general situation on the virtualization host and its disk system at the measurements. The response times do not include any file transfer times between the client and the server in our LAN. However, the response time for the situations with a sequential load of several files includes some common Ajax overhead in all cases plus the time to load the PHP program files into our Apache server again. But the system buffers and caches sufficiently. So, the interaction between the client and the server should only have a marginal impact.

What do we learn from the results ? I dare to derive 4 rules:

  • Rule 1: Any unique index built during “LOAD DATA INFILE” reduces performance (due to checks which have to be performed). So, one should carefully check whether a unique index really is required later on when you work with the data.
  • Rule 2: The total import time for several or one big file are comparable if a unique index is created whilst or after data loading.
  • Rule 3: Any building of a non-unique index during the import increases the total import time for a sequential load of several csv-files in comparison to a loading process for just one big csv-file.
  • Rule 4: Without any index defined the total loading time for several files is comparable to the loading file for one big csv-file.

The most surprising effect for me was that described in rule 3. However, regarding the accuracy in the range of a second we may have some doubts whether we see a real effect. So, we should have a look at a situation with more data records and more files to confirm this rule.

Now, it will of course be interesting to see a comparison between different loading processes with a creation of the [n,m,i]-index AFTER the processing of all files with “LOAD DATA INFILE”. For such cases technically we drop any index before we load any file data and create the index only after the data of all files have been loaded into the base. Note, that index dropping may take some time, too.

Table: Comparison of server response times for index creation AFTER and WHILST data loading 5 million records with “LOAD DATA INFILE”

n

import by 1 files import by 2 files without any index on nr with unique index building on [n,m,i] whilst loading with non-unique index building on [n,m,i] whilst loading with unique index creation on [n,m,i] after loading with non-unique index creation on [n,m,i] after loading response time
yes no yes yes no no no 19.2 secs
no yes yes yes no no no 19.4 secs
yes no yes no yes no no 9.3 secs
no yes yes no yes no no 13.2 secs
yes no yes no no yes no 19.5 / 18.5 secs
yes no yes no no yes no 20.7 / 19.1 secs
yes no yes no no no yes 11.9 / 10.8 secs
no yes yes no no no yes 11.4 / 11.1 secs

The first time value given in the last four lines includes an index drop time which varies a bit according to precise server conditions at the time of measurement.

The most interesting result is that the differences measured between a sequential load of several files and a situation with just one big file almost to disappear when we create the index after the pure data loading.

Importing 10 million records from one or five files

Now, let us turn to 10 million records and an equal distribution of records over 5 files (with 2 million records each) and see whether we confirm our results. For the test we have just doubled the number of nodes in comparison to the 5 million case.

Table: Server response times for loading 10 million records with “LOAD DATA INFILE”

import by 1 files import by 5 files without any index on nr with parallel unique index building on [n,m,i] with parallel non-unique index building on [n,m,i] with unique index creation on [n,m,i] after loading with non-unique index creation on [n,m,i] after loading response time
yes no yes yes no no no 42 secs
no yes yes yes no no no 42,4 secs
yes no yes no yes no no 20 secs
no yes yes no yes no no 37 secs
yes no yes no no yes no 43.3 / 42.0 secs
no yes yes no no yes no 41.3 / 40.0 secs
yes no yes no no no yes 22.9 / 21.8 secs
no yes yes no no no yes 22.5 / 21.4 secs

Again, we see the huge impact of the creation of an unique index – but the total
loading time is almost independent of whether you create the index whilst or after the data loading. This is understandable as the required checks for a uniqueness must be done in both situations.

However, at least for me, it was almost shocking to see the difference between lines 3 and 4:

There is a big difference between a sequential loading process for several files and a loading process for just one file, when a non-unique index was created whilst loading with “LOAD DATA INFILE”.

Note in contrast the considerable reduction of the required time if you create the non-unique index after all data have been loaded into the database. The measure discrepancy in total loading time is really remarkable – and actually I have no convincing explanation for these findings. The tree structure of the index is quite simple; I do not really see what costs the overhead. And I have tested the effect several times to exclude any extraordinary conditions on the server. I have also validated parameter settings and the steps done during the sequence of loading steps to exclude any unplanned work or actions on the server.

The [n,m,i] index in my case has a size of 215 MByte; the data themselves take another 400 MByte. All in all we speak about 600 MByte. So, I suspect some reading and writing from/to the disk at the end/beginning of the individual 5 loader jobs and some inefficient memory management. I have not yet investigated this in detail. Write me a mail if you know the reason.

Independent of the reasons: For me, it was really important to see this difference because I do not need a unique index and I want to work with many more files to cover much larger numbers of records.

So, our result is:

You should avoid the creation of a non-unique index whilst using “LOAD DATA INFILE” on multiple files by sequentially started (PHP) jobs on the server instead of loading just once from one file. Whenever you load your data from several files create the index after all data from all files have been imported into the database table.

Impact on consistency checks during a sequential load of several files

In the article MySQL/Aggregation: Comparing COUNT(DISTINCT) values on big tables I discussed the creation of a helper table and respective indices to get a good performance of consistency checks. Most of these consistency checks were based on an analysis of derived result sets for “SELECT COUNT(DISTINCT … ).. ” statements. My basic idea was to perform such checks after each import process when dealing with a sequence of files.

However, in the named article I also showed that a fast creation of a helper table required already some existing index on the big data table. But our present results favor a solution where an index is created only after all data of all files have been loaded. So, no intermediate consistency checks in between the loading processes for two csv-files !

After some thinking I came to the conclusion that this is no big disadvantage. Even if you consistency checks only after all data have been loaded, you will still be able to identify the records which are problematic. And with some effort, data and file analysis – which will cost some extra time, but maybe worth it – you even may find the file from which the record was loaded. So, you can give the user who tried to load the files a full report on the suspicious records and that caused inconsistencies and the files that contained them.

Conclusion

We have tested different combinations for loading data with one or multiple files and with or without creating indices during/after the data loading. Our tests have shown that if you import data sequentially from several
files and you want to create a non-unique index over some columns, it makes a huge difference whether you create this non-unique index during or after the sequential “LOAD DATA INFILE” processes. One should avoid creating such indices whilst loading the data with “LOAD DATA INFILE”. It should be created after all data from all files have been imported into the database table.

In contrast such an order of steps does not seem to be required when creating unique indices. However, a unique index costs in general significantly more time to be created than a non-unique one. You, therefore, should evaluate the necessity of unique indices.