Quel est le moyen le plus rapide de vider un gros fichier XML (> 1 Go) dans une base de données MySQL?
Les données en question sont le Dumpoverflow Creative Commons Data Dump.
Ceci sera utilisé dans une visionneuse hors ligne Stackoverflow i Bâtiment, car je cherche à faire des études / codés dans des endroits où je n'aurai pas accès à Internet.
J'aimerais libérer cela au reste de l'adhésion à Stackoverflow pour leur propre usage lorsque le projet est terminé.
À l'origine, je lisais XML / écrivant à DB un enregistrement à la fois. Cela a pris environ 10 heures pour courir sur ma machine. Le code Hacktastic que j'utilise maintenant jette de 500 enregistrements dans un tableau, crée une requête d'insertion pour charger les 500 à la fois (par exemple, " Merci pour tous les pointeurs que vous pouvez fournir!
idées jusqu'à présent
procédure stockée qui charge un fichier XML entier dans une colonne, puis l'analyse à l'aide de XPATH
insérer dans les valeurs des poteaux (...), (...), (...), ( ...) ...; "). Bien que cela soit plus rapide, cela prend toujours des heures à courir. Il est clair que ce n'est pas la meilleure façon d'y aller, alors j'espère que le grand cerveau sur ce site saura d'une meilleure façon.
Contraintes
charger xml infile nomame.xml " ne sont pas utilisables dans ce projet, car cette fonctionnalité n'est disponible que dans MySQL 5.4 et ci-dessus. Cette contrainte est essentiellement due à mon espoir que le projet serait utile aux personnes autres que moi, et je préfère ne pas forcer les gens à utiliser des versions bêta de MySQL.
8 Réponses :
fait Ceci aide du tout? C'est une procédure stockée qui charge un fichier XML entier dans une colonne, puis l'analyse à l'aide de XPath et crée une table / insère les données de là. Semble une sorte de fou, mais cela pourrait fonctionner.
J'ai essayé, mais j'ai rencontré des problèmes pour que cela fonctionne correctement. Je pense que le vrai dresseur de l'affaire est le max_allowed_packet, qui est défini sur 1 Mo par défaut. Comme il lisait tout le fichier XML, qui correspond à environ 1,2 Go pour le fichier POSTS.XML, je pense que cela est inutilisable.
Je vois ce que vous voulez dire - il semble que la valeur maximale du max_allowed_packet est de 1 Go, ce qui n'est toujours pas assez grand. Serait-il possible de rompre le document XML aux partitions logiques (disons, un document par table) dans plusieurs documents XML avant de les nourrir à MySQL?
Le problème persiste même si nous mappons les fichiers 1-à-1 avec les tables. Le fichier POSTS.XML dans le décharge de données est de 1,19 Go pour septembre, il suffit donc de casser au moins ce fichier à part. Je ne suis pas tout simplement pas à l'aise d'exiger un utilisateur potentiel de modifier leur installation MySQL pour permettre au paquet de 1 Go. J'aimerais garder cela aussi vanille que possible afin de ne pas causer de problèmes. par exemple. "Donnez-moi votre adresse et vos informations d'identification du serveur, et je vais faire le reste ... Oh, et cela vous dérangerait-il de vous assurer que tout est transmis dans UTF8 car ce n'est pas aussi défaut que les docs disent? Merci .."
Pas la réponse que vous voulez, mais l'API MySQL C a le mysql_stmt_send_long_data fonction.
Il y a 2 parties à ceci:
Pour lire le fichier XML, ce lien http: // csharptubutoriel. blogspot.com/2006/10/10/reading-xml-fast.html montre que 1 Mo peut être lu dans 2,4 secondes à l'aide de lecteur de flux, ce qui serait 2400 secondes ou 40 minutes (si mes mathématiques fonctionnent en retard ) pour 1 gb fichier.
De ce que j'ai lu le moyen le plus rapide d'obtenir des données dans MySQL consiste à utiliser les données de charge.
http://dev.mysql.com/doc/ Refman / 5.1 / FR / LOAD-DATA.HTML
Par conséquent, si vous pouvez lire les données XML, écrivez-le aux fichiers pouvant être utilisés par des données de charge, puis exécutez les données de charge. Le temps total peut être inférieur aux heures que vous êtes expérimentant.
C'est en fait beaucoup plus rapide de lire le fichier XML que vous ne le pensez. J'utilise un xmlreader pour lire le fichier et j'ai rapidement vérifié le temps nécessaire pour lire le fichier et préparer les requêtes. En lisant uniquement Posttypeid et Corps (les seuls deux champs que je joue avec) dans les chaînes (pour vous assurer qu'ils sont lus, car je ne sais pas quand les requêtes paramétrées tirent leurs données) et commentant le cmd.executenonquery () déclaration, le processus a pris 1 minute, 12 secondes. Le goulot d'étranglement est très dans la partie de base de données, pas la lecture des données. Je vais chercher à recréer des fichiers lisibles de la charge de la charge, merci.
Vous pourrez peut-être améliorer encore les performances en divisant d'abord l'entrée XML dans plusieurs fichiers XML afin qu'ils puissent être traités en parallèle.
Bonjour Shiraz, je viens d'essayer de tenter votre suggestion concernant la création de fichiers temporaires et d'utiliser des données de charge pour les traiter. Il a fallu 2m22 pour créer le fichier et 2m35s pour charger le fichier dans la base de données. Il y avait un certain nombre d'avertissements que je dois examiner, mais je suis sûr que ceux-ci seront résolus avec quelques modifications mineures. 5 minutes pour charger 1,2 million d'enregistrements vont bien au-delà de mes attentes. Profitez de votre prime, c'était bien gagné!
Les erreurs étaient exactement ce que je pensais être. Je suppose à tort qu'il n'y aurait pas de guillemets dans le champ du corps, car cela fermerait l'attribut XML dans le fichier d'origine et rendu donc le XML invalide. Ce que j'ai omis de considérer, c'est que la xmlreader que j'utilisais était de décoder des entités comme elle est allée, il y avait donc des guillemets doubles présents, ce qui vissait le fichier CSV. J'ai échappé à toutes les barres obliques, puis a également échappé aux citations et tout était génial. 3M34S Pour créer le fichier, puis 2m43s pour effectuer la charge MySQL - Total de 6M17S pour charger les champs PostTypeid et Corps!
J'ai remarqué dans l'un de vos commentaires ci-dessus que vous envisagez MSSQL, alors je pensais poster cela. SQL Server dispose d'un utilitaire appelé SQMLXMLBulkload, conçu pour importer de grandes quantités de données XML dans une base de données SQL Server. Voici la documentation de la version SQL Sever 2008:
http://msdn.microsoft.com/en-us/library /ms171993.aspx
Les versions antérieures de SQL Server ont également cet utilitaire
Ceci est partiellement pourquoi j'ai posé cette question, en fait. J'ai été déçu de l'heure de chargement de 10 heures, mais quelque peu les épaules comme "Eh bien, c'est beaucoup de données .. En outre, je vais avoir beaucoup de temps pour accélérer plus tard" mais j'ai vu un post sur Meta Se plaindre d'un temps de chargement de 8 minutes à MSSQL et j'ai été énervé. Le gars a mis son code sur Github et c'était ce qu'il utilisait. Eh bien, sqlbulkcopy, pas la version XML dont vous parlez. Merci pour les informations, je vais certainement garder à l'esprit lorsque je commence à jouer avec d'autres bases de données (c'est un projet de mine jouet, à des fins d'apprentissage / portefeuille). Merci!
OK, je vais être un idiot ici et répondre à votre question avec une question.
pourquoi le mettre dans une base de données?
Et si ... Juste a what-si ... vous avez écrit le XML aux fichiers XML sur le lecteur local et, si nécessaire, écrivez des informations d'indexation dans la base de données. Cela devrait fonctionner de manière significative plus rapide que d'essayer de charger une base de données et serait beaucoup plus portable. Tout ce dont vous auriez besoin de plus en plus est un moyen de rechercher et d'indexer des références relationnelles. Il devrait y avoir beaucoup d'aide pour la recherche et l'aspect relationnel devrait être suffisamment facile à construire? Vous pourriez même envisager de réécrire les informations afin que chaque fichier contienne un seul poste avec toutes les réponses et toutes les commentaires là-bas.
Quoi qu'il en soit, mes deux cents (et cela ne vaut pas un centime).
Que diriez-vous de quelque chose comme le incubateur.apache.org/lucene.net Port de lucene.apache.org
J'aime l'idée de Lucene, mais j'attendrais honnêtement m'attendre à tirer les données à Lucene pour prendre plus longtemps que de l'obtenir dans une base de données
J'ai quelques pensées pour vous aider à accélérer ...
La taille de la requête peut avoir besoin d'être modifiée, il y a souvent un point où la grande déclaration coûte plus de temps à analyser et devient donc plus lente. Le 500 peut être optimal, mais ce n'est peut-être pas et vous pourriez modifier un peu (cela pourrait être plus, cela pourrait être moins).
aller multithreadisé. En supposant que votre système ne soit pas déjà plat sur le traitement, vous pouvez faire des gains en supprimant les données dans des morceaux et que les threads leur traitent. Encore une fois, c'est une chose d'expérimentation de trouver le nombre optimal de threads, mais beaucoup de gens utilisent des machines multicœurs et ont des cycles de processeur à reculer.
sur le front de la base de données, assurez-vous que la table est aussi nue que possible. Éteignez les index et chargez les données avant d'indexer.
roches SQLBULKCOPY. Je l'ai utilisé pour transformer une fonction de 30 minutes à 4 secondes. Cependant, cela s'applique uniquement à MS SQL Server .
Pourrais-je vous suggérer de regarder les contraintes de votre table que vous avez créées? Si vous déposez toutes les clés de la base de données, des contraintes, etc., la base de données fera moins de travail sur vos insertions et un travail moins récursif.
Deuxièmement, configurez les tables avec de grosses tailles initiales pour empêcher votre redîne si vous insérez dans une base de données vierge.
Enfin, voyez s'il y a une API de style de copie en vrac pour MySQL. SQL Server format essentiellement les données car il irait sur le disque et le serveur SQL relie le flux jusqu'au disque et vous pompe dans les données. Il effectue ensuite une vérification de cohérence pour toutes les données au lieu d'un par insertion, améliorant considérablement vos performances. Bonne chance;)
Avez-vous besoin de MySQL? SQL Server facilite votre vie si vous utilisez Visual Studio et votre base de données est basse performance / taille.
Je n'ai pas besoin mysql; C'est simplement ce que j'ai déjà installé. Ceci est, après tout, principalement un projet personnel / portefeuille. N'ayant jamais travaillé avec MSSQL, ce sera une fonctionnalité secondaire une fois le projet principal terminé. J'utiliserai certainement SQLBulkCopy lorsque le moment vient. La chose la plus proche pour MySQL semble être une couverture pour la syntaxe de données de charge. En ce qui concerne les index, etc. Le tableau est nu car les enregistrements ne seront ajoutés qu'une seule fois. Je vais vérifier dans votre suggestion de «grandes tailles initiales». Merci pour les idées.
dans PostgreSQL , le moyen absolu le plus rapide d'obtenir des données en vrac consiste à supprimer tous les index et déclenche, utilisez l'équivalent de MySQL's Load Data puis recréez vos index / déclencheurs. J'utilise cette technique pour tirer 5 Go de données de forum dans une base de données PostgreSQL dans environ 10 minutes.
accordé, cela peut ne pas s'appliquer à MySQL, mais cela vaut la peine d'être coupé. Aussi, La réponse de cette question suggère que c'est en fait une stratégie viable pour MySQL.
Un rapide Google a changé quelques conseils sur Augmenter les performances de Les données de charge de MySQL .
Pourquoi mysql? Si vous construisez un spectateur hors ligne, quelque chose comme Sqlite semble beaucoup plus approprié. En tant que bonus, ce sera beaucoup, beaucoup plus rapide que MySQL.
Honnêtement, Mark, j'étais sous l'impression erronée Sqlite était destinée à des bases de données plus petites. Je viens de jeter un coup d'œil sur le site et j'ai remarqué un commentaire que ce n'était pas pour "très grandes jeux de données", je relisons le commentaire de cette section et remarqua à quel point la limite est élevée. Peut-être que je vais y jeter un coup d'œil comme une autre base de données. J'ai déjà des projets pour permettre à MSSQL d'être utilisé à la place.