Ускорение процесса записи данных в mysql?

Question

[Mo1she]

Здравствуйте. Я, используя php XmlReader, распаршиваю большой xml файл и записываю в mysql. Но получается довольно медленно (60 тыс строк за 7 мин). То есть с такой скоростью мне понадобится несколько дней. Может можно расположить mysql сервер в облаке, так будет побыстрее? И как-нибудь распараллелить этот процесс. Подскажите пожалуйста способы ускорить всё это. И расскажите, пожалуйста, про то, какие именно параметры (CPU, RAM, DISK) в приоритете влияют на быстроту записи в mysql?

P.S. Мой вопрос не касается того как оптимизировать sql запрос.

Answer 1

[Decadal]

вы можете сделать параметры в своем скрипте вроде --offset 0 --max 10000
потом запустить несколько скриптов параллельно с разными параметрами, чтобы они не пересекались (т.е. чтобы скрипты работали с разными данными)
вот вам бюджетный параллелизм

Comments

[Mo1she]

пробовал так разделить и запустить два скрипта. Это не увеличивает скорость, т.к. каждый скрипт после этого записывает в mysql данные в два раза медленнее.

[Decadal]

тогда хочется узнать не последовательно ли он случаем пишет данные? Вы делаете вставку одним инсертом для сотни записей, или сотней инсертов?

[Mo1she]

$post = new \XMLReader();

        $post->open('Posts.xml');
        
        while($post->read()) {
            if ($post->nodeType == \XMLReader::ELEMENT && $post->name == 'post') {
                $post = new Post;
                $post->fill(['post_id' => $post->getAttribute('Id'), 'content'=>html_entity_decode($post->getAttribute('Content'))]);
                $post->save();
            }
        }

        $post->close();

[Decadal]

Mo1she, а потом вы говорите "ваш вопрос не касается оптимизации запросов". Еще как касается.
Угадываю, что вы в цикле генерируете инсерт запросы и тут же их выполняете. Из-за этого создается равномерная постоянная нагрузка на базу данных.
Включение другого скрипта создает такую же нагрузку и производительность падает.

Просто перепишите процесс вставки нормально и будет вам быстродействие без облачных серверов и всего такого. Аппаратные улучшения имеют смысл тогда когда программные средства оптимизации уже исчерпаны. Это не ваш случай.

[Mo1she]

Спасибо. Вместо того, чтобы делать вставку в таблицу на каждой итерации, теперь я собираю в кучу и делаю вставку по 1000 строк за раз. Это ускорило работу в 5 раз. Но это всё равно медленно. Однако запуск в таком виде нескольких скриптов одновременно опять же не ускоряет работу, а всё так же как было раньше время каждого скрипта увеличивается.

[SagePtr]

Рекомендации из документации MySQL перепробовали? Некоторые из них тоже могут ускорить вставку

[Decadal]

Mo1she, время неизбежно увеличивается, но попробуйте засечь, сколько записей вставит один скрипт за минуту, а сколько - два параллельных скрипта.

Answer 2

[serginhold]

есть такое https://dev.mysql.com/doc/refman/5.7/en/load-xml.html

или можно на два этапа разделить
1. конвертнуть xml в csv, заодно отбросить лишний поля
2. https://dev.mysql.com/doc/refman/5.7/en/load-data.html

Answer 3

[vovik64]

часто проблема в индексах таблички.....убери индексы и создай их заново после импорта

потому что после каждой вставки переиндексация идет и если индексов несколько то соответственно и времени больше