Un bout de DBA MySQL, En long et en large

Importation d’un CSV

On m’a soumis un problème il y a quelques semaines (désolé du délai, c’est l’été pour tout le monde hein.. ) à propos d’importation CSV qui était terriblement lente. J’ai reçu très peu d’information, donc voici des tests que j’ai effectués afin qu’il puissent être utilisés comme point de référence pour des comparaisons. Voici la problématique telle qu’on me l’a soumise

Pour un simple fichier CSV d’environ 1000 lignes (quelques Mo), j’ai largement le temps soit de manger, soit de faire une sieste, … pour une base de données MyISAM. Si j’utilise une base de données InnoDB, celle dont j’ai besoin, plus d’une journée. Je me base uniquement sur un seul utilisateur. A terme, j’aurai des To de données à gérer.

La Machine de tests:

Une VM avec 512 Mo de RAM avec un mauvais IO puisque d’autres VM roulent sur le même serveur. Voici un bout de la config que j’ai utilisé
key_buffer              = 40M
max_allowed_packet      = 16M
thread_stack            = 256K
thread_cache_size       = 8
thread_concurrency      = 2
table_cache             = 384
sort_buffer_size        = 768K
read_buffer_size        = 512K
read_rnd_buffer_size    = 512K

La table de tests:

CREATE TABLE `csvimport` (
`a` int(11) NOT NULL,
`b` varchar(255) NOT NULL,
`c` varchar(255) NOT NULL,
`d` date NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8

et un fichier CSV de 1114112 lignes (106 Mo) que j’ai fabriqué avec cette technique.

mysql> LOAD DATA INFILE ‘/tmp/testcsv.CSV’ INTO TABLE csvimport   FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘   LINES TERMINATED BY ‘\n’;
Query OK, 1114112 rows affected (10.14 sec)
Records: 1114112  Deleted: 0  Skipped: 0  Warnings: 0

——————–
La même table converti en INNODB

mysql> LOAD DATA INFILE ‘/tmp/testcsv.CSV’ INTO TABLE csvimport   FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘   LINES TERMINATED BY ‘\n’;
Query OK, 1114112 rows affected (27.71 sec)
Records: 1114112  Deleted: 0  Skipped: 0  Warnings: 0

——————–

La même table converti en MyISAM

mysql> LOAD DATA INFILE ‘/tmp/testcsv.CSV’ INTO TABLE csvimport   FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘   LINES TERMINATED BY ‘\n’;
Query OK, 1114112 rows affected (6.31 sec)
Records: 1114112  Deleted: 0  Skipped: 0  Warnings: 0

——————–
Je refais les mêmes tests, mais avec une table surindexée.

ALTER TABLE csvimport ADD INDEX(a), ADD INDEX(b), ADD INDEX(c), ADD INDEX(d);

——————–
MyISAM

mysql> LOAD DATA INFILE ‘/tmp/testcsv.CSV’ INTO TABLE csvimport   FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘   LINES TERMINATED BY ‘\n’;
Query OK, 1114112 rows affected (41.44 sec)
Records: 1114112  Deleted: 0  Skipped: 0  Warnings: 0

——————–
INNODB

mysql> LOAD DATA INFILE ‘/tmp/testcsv.CSV’ INTO TABLE csvimport   FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘   LINES TERMINATED BY ‘\n’;
Query OK, 1114112 rows affected (1 min 4.90 sec)
Records: 1114112  Deleted: 0  Skipped: 0  Warnings: 0

Conclusion

MyISAM est reconnu pour sa rapidité d’insertion. On peut le remarquer avec les résultats. Il est clair qu’il y a un problème si vous avez le temps de vous faire un sandwich lorsque vous importez un CSV de 1000 lignes. Ce problème peut être de plusieurs natures. Il est très difficile de savoir d’où vient le problème sans faire quelques tests. Peut-être que le problème vient des disques, de la carte RAID, du CPU, d’une très mauvaise configuration, etc.. Ça peut être n’importe quoi.