Forum Linux.général MariaDB (MySQL) MDB Tools → trés nombreux insert, extrêmement lent :( ? [résolu]

Posté par  (site web personnel) . Licence CC By‑SA.
Étiquettes :
1
9
oct.
2015

Je dois remplacer l'usage d'Access de Microsoft par une application web sur MariaDB (alias MySQL).

Pour se faire, je développe l'application web sur une copie de la base de donnée Access.

La partie consultation est déjà opérationnelle ! Mais durant le développement de la partie insertion et modification, l'encodage se fait toujours sur la base de donnée Access. Je dois donc régulièrement "synchroniser", de la bd Access vers la bd MariaDB.

Pour se faire, j'utilise MDB Tools

Ça fonctionne bien…

Sauf que, je devais exécuter mes scripts sur un poste de travail (GNU/Linux) car le système GNU/Linux du serveur n'avait pas la version de MDB Tools suffisamment récente.

Depuis une semaine, le serveur à été mis à jour et je peux donc envisager d'y faire tourner mes scripts utilisant MDB Tools. L'intérêt sera une automatisation…

(le serveur sert également de NAS, donc le fichier Access s'y trouve, le serveur web intranet aussi)

Malheureusement, je constate que sur le serveur, c'est extrèmement plus lent que sur mon poste de travail !

Le fait est que la commande mdb-export génère un script SQL constitué d'autant d'INSERT que d'enregistrements. Je pense que c'est de là que ça vient…

Mon poste de travail à peut-être une autre configuration par défaut… C'est là que je commence à perdre pied…

Dois-je chercher à modifier les scripts SQL au passage ?
Dois-je changer de moteur (engine en anglais) pour les tables (actuellement InnoDB) ?
Dois-je chercher à régler finement (tune) le disque dur (en RAID 1 sur le serveur) ?
.. ?

  • # I/O des disques dur et usage CPU

    Posté par  . Évalué à 2.

    ta machine de bureau à quoi comme config (CPU/RAM)
    et ton NAS ?

    rien que là, deja ca peut jouer,
    d'autres qu'actuellement c'est une machine qui genere la conversion MDB=>SQL
    et une autre qui mange le SQL

    alors que dans le nouveau scenario, c'est la meme machine, aux perfs surement moindre que la machine de bureau, qui fait MDB=>SQL=>Injection en base.

    • [^] # Re: I/O des disques dur et usage CPU

      Posté par  (site web personnel) . Évalué à 1.

      Merci beaucoup de me répondre. Je me sens déjà moins seul.

      Côté poste de travail :
      AMD Athlon II X3 450
      Bench : 2637 multi / 1063 single
      RAM: 4 Gio

      Côté serveur (NAS +…) :
      Il s'agit d'une machine virtuel via Xen, mais la majorité des ressource lui sont dédiés.
      AMD Athlon 64 X2 Dual Core 4200+
      Bench : 1102 multi / 667 single
      RAM: ± 1,2 Gio (partie des 2 Gio physique)

      Mais cela fait plusieurs heures que je cherche à comprendre, et j'ai déjà observé, à coups de free, top, iotop, htop, etc. qu'il n'y a pas de problème de mémoire, ni de problème CPU. C'est un véritable mystère :(

      C'est comme s'il y avait des temps d'attente, ou des sync entre chaque INSERT… Est-ce possible ?

      Ce que j'envisage actuellement, c'est de placer les tables de la base de donnée d'importation (temporaire) en mémoire vive, avec le moteur MEMORY à coup de sed sur le script issu de mdb-schema (création des tables).

      Mais je suis presque sûr qu'il dois y avoir une autre alternative moins "radicale"…

      Par ailleurs, non, c'est pas un problème de « MDB=>SQL=>Injection en base »… La méthode n'a pas changé… L'opération qui prenait 3 minutes sur poste de travail prend 37 minutes sur le serveur.

      La base de données n'est même pas très grande. Le fichier Access pèse 18 Mo. Il y a un total (20 table confondues) 75000 enregistrements (→ autant d'INSERT). Le script SQL contenant les INSERT fait également 18 Mo. C'est le traitement de ce dernier qui prend 37 minutes.

      • [^] # Re: I/O des disques dur et usage CPU

        Posté par  . Évalué à 2.

        4Go de RAM d'un coté,
        1.2Go de RAM de l'autre

        pour moi le souci vient de là, avec le moteur INNODB qui essaie de verifier la transaction à chaque fois qu'il fait un insert

        si l'insert contient des clefs dsitantes, des references à d'autres objets,
        il simule l'insertion et verifie qu'il n'y a pas de probleme de transaction avant de vraiment faire l'insert.

        si tu n'en est encore qu'aux tests de migration,
        regardes si tu peux passer en moteur MyISAM qui ne verifie alors pas les transactions,
        puis quand la migration sera faites definitivement, stopper la base, dumper,
        changer MyISAM en INNODB, reimporter la base, redemarrer la base.

        • [^] # Re: I/O des disques dur et usage CPU

          Posté par  (site web personnel) . Évalué à 1.

          Avec la commande htop, je ne vois rien qui correspondrait à ce problème.

          Je ne vois quasi aucune activité. C'est très etrange, je vous l'ai écrits, c'est comme s'il y avait des "sleep"… Le CPU reste à 5% et la mémoire vive reste avec ± 400 Mo de libre.

          La bd ne contient que des tables, non-lié entre-elles.

          Néanmoins, avec MyISAM, ça va beaucoup plus vite ! :)
          30 secondes auront suffis ! :)

      • [^] # Re: I/O des disques dur et usage CPU

        Posté par  . Évalué à 2.

        Si tu es sur une VM, c'est peut-être au niveau des io que ça pêche (tu partages la même bande passante pour plein de VMs alors que sur ton laptop tu as toute la bande passante pour toi).

        Essaie un iotop par exemple. Sinon dans le vmstat il doit y avoir moyen de voir les process en attente d'IO.

  • # créer une transaction ?

    Posté par  (site web personnel) . Évalué à 1.

    Un truc qui accélère normalement les mise à jour d'une base de donnée est d'effectuer les insertion au sein d'une transaction.
    pour cela, comme documenté ici il faut faire un START TRANSACTION; au début du script et un COMMIT; à la fin (une alternative est de grouper les INSERT par 1 000 ou 10 000 au sein de différente transaction, permettant de reprendre en cas d'erreur sans devoir tout recommencer)

    Par contre je ne comprends pas pourquoi c'est sensiblement plus rapide lorsque ton script est exécuté sur le serveur plutôt que sur le client (en partant de l'hypothèse que c'est le même script et que dans les deux cas ta base de donnée est sur le serveur)

    ATTENTION, si tu ne fais pas de COMMIT à la fin, aucune donnée n'est enregistrée.

    • [^] # Re: créer une transaction ?

      Posté par  (site web personnel) . Évalué à 1.

      Merci pour cette idée. Je viens de trouver une autre solution (via MyISAM) après plusieurs heures supplémentaires à domicile ;)

      À l'occasion, j'essayerai la transaction car je serais curieux de comparer les deux solutions.

      Pour ce qui est de l'explication de la différence entre le script exécuté sur mon poste de travail et l'exécution sur le serveur, nous devrons attendre mardi. Car le poste de travail en question ne se trouve pas chez moi. Mes hypothèses sont : soit niveau io, soit c'est MyISAM par défaut sur le poste de travail.

      • [^] # Re: créer une transaction ?

        Posté par  (site web personnel) . Évalué à 1.

        A mon avis, c'est parce que le poste de travail est en MyISAM. Une des différence principale entre MyISAM et InnoDB est que InnoDB supporte les transactions. Mais une transaction a un cout relatif. Si tu fait plusieurs insertion/updates au sein d'une même transaction tu réparti le cout de la transaction.

  • # SOLUTION ! passer par MyISAM

    Posté par  (site web personnel) . Évalué à 3. Dernière modification le 09 octobre 2015 à 16:18.

    Par défaut sur le serveur, c'est le moteur InnoDB qui est utilisé.
    Le passage du fichier Access vers MariaDB se fait dans une base de donnée temporaire (MyISAM). Un dump est fait à partir de là et réinjecté dans la base de donnée "en production" (InnoDB).

    Voici un résumé de mon script :

    $mdbfile="db_MSAccess.mdb"

echo "SET storage_engine=MyISAM;" >db_schema.sql
mdb-schema --drop-table "$mdbfile" mysql |grep -v "^COMMENT ON COLUMN" >>db_schema.sql

mysql -u theuser -pthepass tempdb <db_drop_tables.sql

mysql -u theuser -pthepass tempdb <db_schema.sql

# Oui, dans la base de donnée Access,
# les noms de table sont parfois "horribles" :(
mdb-export -D %F -I mysql "$mdbfile" "Première Table" >db_data.sql

mdb-export -D %F -I mysql "$mdbfile" "Deuxième Table" >>db_data.sql

# ... les autres tables...

mysql -u theuser -pthepass tempdb <db_data.sql

# Oui, je corrige cela...
mysql -u theuser -pthepass tempdb <db_rename.sql

# et encore d'autres aberrations...
mysql -u theuser -pthepass tempdb <db_cleaning.sql

mysqldump -u theuser -pthepass tempdb >tempdb_dump.sql

sed -i 's|ENGINE=MyISAM|ENGINE=InnoDB|g' tempdb_dump.sql

mysql -u theuser -pthepass proddb <tempdb_dump.sql

    Résultat, ce qui prenait 37 minutes prend maintenant 37 secondes :)

  • # Dump

    Posté par  (site web personnel) . Évalué à 2.

    Est-ce que tu ne peux pas utiliser mdbtools sur ton poste de travail pour générer un dump MySQL et exécuter celui-ci directement sur le serveur ?

    Au pire si c'est toujours lent tu pourras poster un extrait du dump et les gens sauront sans doute expliquer ton problème.

    • [^] # Re: Dump

      Posté par  (site web personnel) . Évalué à 1.

      Merci pour ton commentaire tardif.

      C'est exactement ce que je faisais depuis des mois, jusqu'à cette mise à jour du serveur qui me permet de le faire directement sur le serveur et donc de pouvoir automatiser l'opération.

      Par ailleurs, comme tu auras pu le voir dans mon précédent commentaire, nous avons trouver la solution, qui passe par l'utilisation de MyISAM moins pénalisé par les multiple INSERT (un par enregistrement) généré par mdb-export. InnoDB est ensuite utilisé "en prod". Entre les deux, le mysqldump génère quant à lui des INSERT beaucoup moins nombreux car groupant plusieurs enregistrements.

      • [^] # Re: Dump

        Posté par  (site web personnel) . Évalué à 1.

        le fait que ce soit les INSERT moins nombreux qui accélèrent le processus, me fait clairement penser que c'est le manque de transaction qui pénalise la solution originale. En effet, chaque INSERT correspond a une transaction par défaut, si tu groupe plusieurs enregistrements dans un INSERT, c'est quasi identique a grouper plusieurs INSERT d'un enregistrement dans une transaction.

Suivre le flux des commentaires

Note : les commentaires appartiennent à celles et ceux qui les ont postés. Nous n’en sommes pas responsables.