Каков самый быстрый способ загрузки XML файла в MySQL с помощью С#?

Вопрос

Каков самый быстрый способ сбрасывать большой ( > 1 ГБ) XML файл в базу данных MySQL?

Данные

Данные, о которых идет речь, - это дамп данных Creative Commons Data StackOverflow.

Цель

Это будет использоваться в автономном средстве просмотра StackOverflow, которое я создаю, так как я ищу для изучения/кодирования в тех местах, где у меня не будет доступа к Интернету.

Я хочу опубликовать это для остальных членов StackOverflow для их собственного использования, когда проект будет завершен.

Проблема

Первоначально я читал из XML/записи в DB по одной записи за раз. Это заняло около 10 часов, чтобы работать на моей машине. Хакстастический код, который я использую, теперь выдает 500 записей в массив, а затем создает запрос на вставку для загрузки всего 500 одновременно (например, "INSERT INTO posts VALUES (...), (...), (...) ... ;" ). Хотя это происходит быстрее, для выполнения все еще требуется несколько часов. Понятно, что это не лучший способ сделать это, поэтому я надеюсь, что большие мозги на этом сайте будут лучше знать.

Ограничения

  • Я создаю приложение с помощью С# в качестве настольного приложения (например, WinForms).
  • Я использую MySQL 5.1 в качестве моей базы данных. Это означает, что такие функции, как "LOAD XML INFILE filename.xml", не могут использоваться в этом проекте, так как эта функция доступна только в MySQL 5.4 и выше. Это ограничение во многом связано с моей надеждой на то, что проект будет полезен другим людям, кроме меня, и я бы предпочел не заставить людей использовать бета-версии MySQL.
  • Я хочу, чтобы загрузка данных была встроена в мое приложение (т.е. никаких инструкций "Загрузить дамп в MySQL с помощью" foo "перед запуском этого приложения." ).
  • Я использую MySQL Connector/Net, поэтому все в пространстве имен MySql.Data допустимо.

Спасибо за любые указатели, которые вы можете предоставить!

Идеи до сих пор

хранимую процедуру, которая загружает весь XML файл в столбец, затем анализирует его с помощью XPath

  • Это не сработало, так как размер файла зависит от ограничений переменной max_allowed_packet, которая по умолчанию установлена ​​в 1 МБ. Это намного меньше размера файлов дампов данных.

Ответы

Ответ 1

Есть две части:

  • чтение xml файла
  • запись в базу данных

Для чтения xml файла эта ссылка http://csharptutorial.blogspot.com/2006/10/reading-xml-fast.html показывает, что 1 Мб может быть прочитан в 2.4 с с помощью средства чтения потоков, что должно быть 2400 секунд или 40 минут (если мои математики работают так поздно) для 1 ГБ файла.

Из того, что я прочитал, самым быстрым способом получить данные в MySQL является использование LOAD DATA.

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Поэтому, если вы можете прочитать данные xml, напишите его в файлы, которые могут использоваться LOAD DATA, затем запустите LOAD DATA. Общее время может быть меньше, чем часы, которые вы испытываете.

Ответ 2

Хорошо, я собираюсь быть идиотом здесь и ответить на ваш вопрос вопросом.

Зачем ставить его в базу данных?

Что делать, если... просто что-то... если вы написали xml в файлы на локальном диске и, если нужно, напишите в базе данных некоторую информацию индексации. Это должно выполняться значительно быстрее, чем пытаться загрузить базу данных и будет гораздо более переносимым. Все, что вам нужно, это способ поиска и способ индексирования реляционных ссылок. Должна быть много помощи при поиске, а реляционный аспект должен быть достаточно прост в построении? Вы даже можете подумать о повторной записи информации, чтобы каждый файл содержал одну запись со всеми ответами и комментариями прямо там.

Во всяком случае, только мои двухценты (и это не стоит ни копейки).

Ответ 3

У меня есть несколько мыслей, чтобы помочь ускорить это...

  • Возможно, размер запроса может быть изменен, часто бывает точка, в которой большой оператор стоит больше времени разбора и поэтому становится медленнее. 500 может быть оптимальным, но, возможно, это не так, и вы можете немного подкорректировать его (это может быть больше, это может быть меньше).

  • Идите многопоточно. Предполагая, что ваша система еще не сглажена при обработке, вы можете добиться некоторой выгоды, разбив данные на куски и обработав их потоками. Опять же, это экспериментальная вещь, чтобы найти оптимальное количество потоков, но многие люди используют многоядерные машины и имеют запасные циклы процессора.

  • В передней панели базы данных убедитесь, что таблица такая же голая, как и может быть. Отключите все индексы и загрузите данные перед их индексированием.

Ответ 4

SqlBulkCopy ROCKS. Я использовал его, чтобы включить 30-минутную функцию до 4 секунд. Однако это применимо только к MS SQL Server.

Могу ли я предложить вам взглянуть на ограничения на созданную вами таблицу? Если вы отбросите все ключи в базе данных, ограничения и т.д., База данных будет делать меньше работы с вашими вставками и менее рекурсивной работой.

Во-вторых, настройте таблицы с большими начальными размерами, чтобы предотвратить изменение размера, если вы вставляете в пустую базу данных.

Наконец, посмотрите, есть ли API-интерфейс для массового копирования для MySQL. SQL Server в основном форматирует данные, поскольку он будет спускаться на диск, а SQL-сервер связывает поток с диском и накачивает данные. Затем он выполняет одну проверку согласованности для всех данных вместо одной вставки, что значительно повышает вашу производительность. Удачи;)

Вам нужен MySQL? SQL Server упрощает вашу жизнь, если вы используете Visual Studio, а ваша база данных имеет низкую производительность/размер.

Ответ 5

Помогает ли эта помощь? Это хранимая процедура, которая загружает весь XML файл в столбец, затем анализирует его с помощью XPath и создает таблицу/вставляет данные оттуда. Кажется сумасшедшим, но это может сработать.

Ответ 6

Не тот ответ, который вы хотите, но mysql c api имеет функцию mysql_stmt_send_long_data.

Ответ 7

Я заметил в одном из ваших комментариев выше, что вы рассматриваете MSSQL, поэтому я решил опубликовать это. SQL Server имеет утилиту SQMLXMLBulkLoad, которая предназначена для импорта больших объемов данных XML в базу данных SQL Server. Вот документация для версии SQL Sever 2008:

http://msdn.microsoft.com/en-us/library/ms171993.aspx

В более ранних версиях SQL Server также есть эта утилита

Ответ 8

В PostgreSQL абсолютным самым быстрым способом получения массивных данных является удаление всех индексов и триггеров, использование эквивалента MySQL LOAD DATA, а затем воссоздайте свои индексы/триггеры. Я использую эту технику, чтобы вывести 5 ГБ данных форума в базу данных PostgreSQL примерно через 10 минут.

Конечно, это может не относиться к MySQL, но это стоит того. Кроме того, этот ответ SO-ответа предполагает, что на самом деле это жизнеспособная стратегия для MySQL.

В быстрый google появилось несколько советов по увеличение производительности MySQL LOAD DATA.