IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Administration MySQL Discussion :

Chute de performance après ~ 400 Millions de lignes insérées [MySQL-5.7]


Sujet :

Administration MySQL

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre averti Avatar de PoelQ
    Homme Profil pro
    Étudiant Exia.Cesi
    Inscrit en
    Janvier 2015
    Messages
    22
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 27
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Étudiant Exia.Cesi
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2015
    Messages : 22
    Par défaut Chute de performance après ~ 400 Millions de lignes insérées
    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)

  2. #2
    Membre prolifique Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    Février 2011
    Messages
    6 973
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : Février 2011
    Messages : 6 973
    Par défaut
    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à.

    @+

  3. #3
    Membre averti Avatar de PoelQ
    Homme Profil pro
    Étudiant Exia.Cesi
    Inscrit en
    Janvier 2015
    Messages
    22
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 27
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Étudiant Exia.Cesi
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2015
    Messages : 22
    Par défaut
    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`)

  4. #4
    Membre prolifique Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    Février 2011
    Messages
    6 973
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : Février 2011
    Messages : 6 973
    Par défaut
    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 ?

    @+

  5. #5
    Membre averti Avatar de PoelQ
    Homme Profil pro
    Étudiant Exia.Cesi
    Inscrit en
    Janvier 2015
    Messages
    22
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 27
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Étudiant Exia.Cesi
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2015
    Messages : 22
    Par défaut
    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é.

  6. #6
    Membre averti Avatar de PoelQ
    Homme Profil pro
    Étudiant Exia.Cesi
    Inscrit en
    Janvier 2015
    Messages
    22
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 27
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Étudiant Exia.Cesi
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2015
    Messages : 22
    Par défaut
    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.

  7. #7
    Membre prolifique Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    Février 2011
    Messages
    6 973
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : Février 2011
    Messages : 6 973
    Par défaut
    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.

    @+

  8. #8
    Membre averti Avatar de PoelQ
    Homme Profil pro
    Étudiant Exia.Cesi
    Inscrit en
    Janvier 2015
    Messages
    22
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 27
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Étudiant Exia.Cesi
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2015
    Messages : 22
    Par défaut
    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.

  9. #9
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    22 010
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 22 010
    Billets dans le blog
    6
    Par défaut
    Citation Envoyé par PoelQ Voir le message
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  10. #10
    Membre prolifique Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    Février 2011
    Messages
    6 973
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : Février 2011
    Messages : 6 973
    Par défaut
    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.

    @+

  11. #11
    Membre averti Avatar de PoelQ
    Homme Profil pro
    Étudiant Exia.Cesi
    Inscrit en
    Janvier 2015
    Messages
    22
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 27
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Étudiant Exia.Cesi
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2015
    Messages : 22
    Par défaut
    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.

  12. #12
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    22 010
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 22 010
    Billets dans le blog
    6
    Par défaut
    Citation Envoyé par PoelQ Voir le message
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  13. #13
    Membre prolifique Avatar de Artemus24
    Homme Profil pro
    Agent secret au service du président Ulysses S. Grant !
    Inscrit en
    Février 2011
    Messages
    6 973
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Agent secret au service du président Ulysses S. Grant !
    Secteur : Finance

    Informations forums :
    Inscription : Février 2011
    Messages : 6 973
    Par défaut
    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.

    Citation Envoyé par PoelQ
    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.

    @+

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [2012] Perte de performance après ajout de millions de lignes
    Par Ornitho76 dans le forum MS SQL Server
    Réponses: 14
    Dernier message: 12/09/2014, 08h36
  2. Problème de performance Update de 60 mille lignes.
    Par ludvax dans le forum Oracle
    Réponses: 15
    Dernier message: 03/07/2006, 10h41
  3. Transferer 500 millions de lignes
    Par Kikoune dans le forum Oracle
    Réponses: 4
    Dernier message: 26/05/2006, 17h09
  4. [JTable] problème après suppression d'une ligne
    Par fredo3500 dans le forum Composants
    Réponses: 7
    Dernier message: 17/03/2005, 10h01
  5. [SELECT sur 16 millions de lignes] délai très grand
    Par localhost dans le forum Requêtes
    Réponses: 6
    Dernier message: 22/11/2004, 17h04

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo