Discussion :

[PoelQ]

Bonjour,

Je rencontre depuis plusieurs jours un problème que je n'ai toujours pas réussi à résoudre : J'essaye d'importer un dump d'une base de données, d'une taille de 38GB, dans ma base de données MySQL.
Le problème est qu'au bout d'un certains nombre de lignes insérées dans la base de données, le serveur commence à ralentir à tel point que l'importation durera plusieurs jours.
J'entend bien que l'importation d'un fichier de 38GB n'est pas une mince affaire pour le serveur, mais l'importation devient tellement lente que je suppose qu'un problème survient.

Comme le titre l'explique l'importation devient très, très lente après environ 400 millions de lignes insérées dans la base de données. Pour palier à ce problème j'ai testé plusieurs solutions sans aucun succès :

• Utilisation de Percona Server 5.7 à la place de MySQL 5.7
• Découper le fichier de 38GB en fichier de 1GB
• Ajouter au début de chaque fichier :

  Code :

  Sélectionner tout - Visualiser dans une fenêtre à part

  1
  2
  3
  SET autocommit=0;
  SET unique_checks=0;
  SET foreign_key_checks=0;

• Ajouter à la fin de chaque fichier :

  Code :

  Sélectionner tout - Visualiser dans une fenêtre à part

  1
  2
  3
  COMMIT;
  SET unique_checks=1;
  SET foreign_key_checks=1;

• Modifier mon fichier my.cnf pour obtenir quelque chose qui ressemble à ça :
  

  Code :

  Sélectionner tout - Visualiser dans une fenêtre à part

  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  [mysqld]
  key-buffer-size                = 32M
  max-allowed-packet             = 16M
  max-connect-errors             = 1000000
  tmp-table-size                 = 32M
  max-heap-table-size            = 32M
  query-cache-type               = 0
  query-cache-size               = 0
  max-connections                = 500
  thread-cache-size              = 50
  open-files-limit               = 65535
  table-definition-cache         = 1024
  table-open-cache               = 100
  innodb-flush-method            = O_DIRECT
  innodb-log-files-in-group      = 2
  innodb-log-file-size           = 2G
  innodb-flush-log-at-trx-commit = 2
  innodb-file-per-table          = 1
  innodb-buffer-pool-size        = 26G

• Utiliser Percona Server Wizard pour obtenir un fichier de configuration sur-mesure
• Utiliser un RAID 0 à la place d'un RAID 1 pour le serveur

Toutes ces pistes mènent au même point, autour de 400 millions de lignes l'importation devient incroyablement lente, ce qui représente environ 40% de l'importation.
Quand je parle de ralentissement c'est qu'en règle générale le fichier de 1GB met environ 30 minutes pour s'importer entièrement, et quand c'est lent je parle de plus d'une demi-journée pour le même fichier.

Chaque fichier de 1GB contient exactement 1000 requêtes INSERT INTO qui ajoute environ 30 millions de lignes par fichier de 1GB.

Table où est importé le fichier (Le moteur de base de données est InnoDB) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
+-------------------+-----------------+------+-----+---------+-------+
| Field             | Type            | Null | Key | Default | Extra |
+-------------------+-----------------+------+-----+---------+-------+
| pl_from           | int(8) unsigned | NO   | PRI | 0       |       |
| pl_namespace      | int(11)         | NO   | PRI | 0       |       |
| pl_title          | varbinary(255)  | NO   | PRI |         |       |
| pl_from_namespace | int(11)         | NO   | MUL | 0       |       |
+-------------------+-----------------+------+-----+---------+-------+

Je n'ai pas la main sur le serveur qui réalise le dump, donc il m'est impossible de modifier la façon dont le fichier est généré.

Configuration du Serveur :
Intel Xeon E3-1270v6 4c/8t - 3,8 GHz/4,2 GHz
32Go DDR4 ECC 2400 MHz
SoftRaid 2x2To
Ubuntu Server 16.04

Je me tourne donc vers vous pour savoir si je suis passé à côté de quelque chose dans la configuration, ou avoir des conseils pour l'importation de ce gros fichier.

Merci d'avance.

Lien du fichier de backup :
https://wikimedia.bytemark.co.uk/enw...gelinks.sql.gz (Dump d'une des tables du Wikipédia Anglais)

[Artemus24]

Salut PoelQ.

Dans la création et le chargement de votre table, avez-vous essayé de supprimer les index, ainsi que la clef primaire ?
A la fin du chargement, vous pouvez les créer à nouveau. Il se peut que le problème vienne de là.

@+

[PoelQ]

Bonsoir Artemus,

Si je supprime les index, ne vais-je pas avoir des problèmes avec MySQL qui m'indiquera des doublons, à la fin de l'import ?

Voici les lignes que j'ai supprimé :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
UNIQUE KEY `pl_from` (`pl_from`,`pl_namespace`,`pl_title`),
KEY `pl_namespace` (`pl_namespace`,`pl_title`,`pl_from`),
KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)

[Artemus24]

Salut PoelQ.

Normalement, c'est un dump d'une base de données que vous installez, donc le problème des doublons ne se pose pas.

Et que donne votre problème de performance en ce qui concerne le chargement ?
Et quand vous faites la création des index et de la primary key, est-ce que cela prend beaucoup de temps ?

@+

[PoelQ]

Bonsoir Artemus,

J'ai lancé l'import du dump il y a environ une heure et c'est déjà à 20%. Pour l'instant il n'y a pas l'air d'avoir de problème de performance.
J'essayerai de créer les index quand l'import sera terminé.

[PoelQ]

Bonjour Artemus,

L'import des plus de 1 milliard de lignes s'est terminé en moins de 3 heures ! Je viens de lancer la requête pour créer le premier index. J'espère que celui-ci ne va pas durer trop longtemps.

Merci Beaucoup Artemus.

[Artemus24]

Salut PoelQ.

Trois heures pour le chargement de tes 1 milliard de lignes. C'est bien mieux qu'avant, n'est-ce pas !
Et quand est-il de la création de la primary key et de tes index ?

Ce qui est emmerdant avec MySql, c'est qu'il y a plein de contrôle qui sont activés et non nécessaire pour le chargement d'une base de données.
C'est cela qui fait que le chargement prend beaucoup de temps.

Il y a un test qu'un membre de ce forum avant fait avec je crois que 1 million de lignes. Il mettais environ 45 minutes !
J'ai désactivé les contrôles inutiles, organisé le chargement afin d'avoir les lignes dans l'ordre de la clef primaire, désactivé les index ...
Quand j'ai refais le même test, j'ai obtenu environ 1 minute 30 secondes au total, c'est-à-dire chargement et recréation des index.

@+

[PoelQ]

Bonsoir Artemus,

En effet c'est beaucoup mieux qu'avant.
En ce qui concerne les index après plus de 7 heures j'ai décidé d'arrêter et de créer les miens (car les index qui étaient présent ne m'était pas utiles). Pour créer deux des quatre index que je souhaite il m'a fallu environ 3 heures.
C'est beaucoup mieux que les premiers tests que j'avais effectués.
En ce qui concerne MySQL c'est vrai que plus je l'utilise et plus je me rends compte qu'il n'est vraiment pas top "out of the box". Il y a beaucoup de paramètres qu'il faut prendre soin de calculer et de changer pour gagner en performance.
J'ai beaucoup réfléchi à changer de RDBMS mais la conversion des dumps vers MSSQL ou une base NoSQL était trop compliqué et coûteux en temps.

[SQLpro]

En ce qui concerne MySQL c'est vrai que plus je l'utilise et plus je me rends compte qu'il n'est vraiment pas top "out of the box". Il y a beaucoup de paramètres qu'il faut prendre soin de calculer et de changer pour gagner en performance.
J'ai beaucoup réfléchi à changer de RDBMS mais la conversion des dumps vers MSSQL ou une base NoSQL était trop compliqué et coûteux en temps.

Dans ce cas optez pour MS SQL Server qui est autoparamétré et auto tunable et vient d'apparaître sous Linux en version beta (la version Linux de prod sera disponible vers septembre).

Ca gère de très fortes volumétrie sans aucun problème. Faites un test comparatif et téléchargeant la version developpeur de SQL Server 2016 si vous êtes sous Windows ou sinon la version 2017 beta si vous êtes sous Linux.

Je pense que vous serez surpris des performances...

Quelques exemples de benchmarks :
https://blog.developpez.com/sqlpro/p...alles_en_sql_1
https://blog.developpez.com/sqlpro/p...-sql-norme/755

A +

[Artemus24]

Salut Poelq.

Et avec MariaDB, avez-vous fait des tests de performances ?

En effet, le choix du SGBDR est crucial pour les performances et la sécurité.
Microsoft SQL Server est un bon choix, mais cela nécessite de connaitre ce type de SGBDR afin de ne pas faire du copier/coller.

@+

[PoelQ]

Bonsoir Artemus,

Je n'ai pas encore essayé d'autre RDBMS mais je pensais plutôt m'orienté vers MongoDB ou CockRoachDB si je devais changer un jour.

[SQLpro]

Bonsoir Artemus,

Je n'ai pas encore essayé d'autre RDBMS mais je pensais plutôt m'orienté vers MongoDB ou CockRoachDB si je devais changer un jour.

MongoDB n'est absolument pas un SGBD Relationnel. Certaines opérations sont donc impossible, comme le dédoublonnement (unicité).

A +

[Artemus24]

Salut PoelQ.

Pour obtenir un SGBDR performant, il faut encore que celui-ci puisse gérer tous les plus que le matériel peut offrir.
Ce n'est pas le cas de MySql et de beaucoup d'autres SGBDR.

je pensais plutôt m'orienté vers MongoDB ou CockRoachDB si je devais changer un jour.

Je ne les connais pas, mais si je devrai faire un choix celui-ci se porterait sur un SGBDR qui respecte les normes.
J'ai plusieurs SGBDR d'installé sur mon ordinateur (quatre qui sont Microsoft SQL Server, MySql, MariaDB et FireBird).
Je n'ai pas fait des tests de comparaisons mais chacun à des avantages et des défauts.
Le mieux d'après ce que j'ai pu voir, reste Microsoft SQL Server version 2014.

Mais bon, tout dépend de ce que vous faites avec votre SGBDR et du choix du matériel et des moyens financiers que vous disposez.

@+