Каков самый быстрый способ загрузки 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.