@ubirust

Как увеличить скорость загрузки данных в Mysql?

Приветствую всех! Есть базы данных, весит примерно 40 гб. Нужно его восстановить на свой сервер. Проблема в скорости. Там более 100 млн. строк. Сейчас 1000 строк загружается за 3-4 минуты, что очень долго. На другом форуме нашел решение, мол добавить эти значения:
innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0

Куда я только не пробовал добавлять их, начал с пути /etc/mysql/@my.cnf (тут мне не нравится то, что у меня стоит @ перед my.cnf) просто дописал в конце этого файла [mysqld] и внизу параметры, не сработало. В этом файле содержится текст:
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

Далее прочитал на других форумах, что нужно в этом случае данные записывать в /etc/mysql/mysql.conf.d/mysqld.cnf
Также туда записал, понятное дело, что в других местах я это предварительно удалил и перезапустил службы mysql командой: sudo service mysql restart --innodb-doublewrite=0, как и предлагали на форуме, где это было решением.

Но скорость не увеличивается, так и на 1000 строк затрачивается 3-4 минуты.

Пробовал эти настройки вставлять и по пути /etc/mysql/conf.d/mysqldump.cnf, тоже ничего не происходит.
Хотя у других людей в десятки раз сокращалась время загрузки:
6412eaf5aa23f906348528.png
То есть этот способ работает, но видимо что-то я не то делаю. Тем более я знаю, что и 1 тб дампы восстанавливают, что уж говорить о 40 гб, задача решаемая.

Вопрос: куда эти параметры в итоге вставлять? Что я делаю не то?

Характеристика моей вирт. машины:
ssd - 100 гб
vcpu - 4
ram - 32

UPDATE:
Был вопрос почему именно 1000 строк:
Ответ: Я скопировал кусок дампа на 1000 строк. Там внутри команда вставки:
INSERT INTO `orders` VALUES'(что нужно вставить)
INSERT INTO `orders` VALUES'(что нужно вставить)
INSERT INTO `orders` VALUES'(что нужно вставить)
И так далее. Весь дамп это и содержит. Сами значения правильно вставляются, с этим проблем нет. Проблема только в скорости.
  • Вопрос задан
  • 784 просмотра
Решения вопроса 1
dimonchik2013
@dimonchik2013
non progredi est regredi
самое быстрое - чтенгие из файла, csv в MyISAM потом замена на innodb или другой движ
медленнее - убивание индексов из SQL сценария и ручное создание после
ну и самое медленное AS IS

а тае настройки до Ж все, в пределах 50% идеале процентов прирост
Ответ написан
Пригласить эксперта
Ответы на вопрос 5
Stalker_RED
@Stalker_RED
1000 строк загружается за 3-4 минуты
интересно в каком виде у вас данные и как вы их загружаете, вероятно вы делаете что-то сильно не так.
Откуда у вас вообще цифра 1000, вы построчно вставляете?
Ответ написан
ThunderCat
@ThunderCat Куратор тега MySQL
{PHP, MySql, HTML, JS, CSS} developer
1) Как загружаются данные? Из консоли импортом дампа? Из пхпмайадмина? Из своего кода?
2) Закомментируйте все строки создания индексов, это сильно ускорит загрузку. Естественно в конце надо будет пройтись и ручками добавить индексы в нужные места.
3) про конфиг
To check which configuration files are processed by the server, just execute
$ /path_to_mysqld/mysqld --help --verbose
At the beginning of output you will find information about configuration files, e.g.
Ответ написан
Комментировать
@Akina
Сетевой и системный админ, SQL-программист.
оффтоп
Загрузка большого объёма данных через штатные средства резервного копирования - вообще занятие для записных мазохистов. Впрочем, 40 гигов в .SQL - это немного...


Если база, которую надо скопировать/переместить на другой хост, сейчас жива, доступна, и допускает некоторый даунтайм (необходимый на создание её полной копии в файловой системе), а также версия исходного и целевого серверов совпадает, то следует использовать именно путь переноса файлов данных.

Если даунтайм недоступен или версии серверов различаются, но исходная база доступна, надо сделать бэкап структуры, и отдельно бэкап данных в формат CSV. Структуру поделить на две части - собственно таблицы и всё остальное. Затем восстановить таблицы, в них скопировать данные, затем наслоить все остальные объекты.

Если же исходная база недоступна - то запускай развёртывание бэкапа и сиди-жди, другого пути нет.

На другом форуме нашел решение, мол добавить эти значения:

Эти значения критично зависят от железа хоста. И на твоём хосте запросто вместо ускорения можно поиметь изрядное замедление.

Опять же - корректируя innodb_buffer_pool_size, нужно корректировать innodb_buffer_pool_chunk_size и/или innodb_buffer_pool_instances. И вообще - см. https://dev.mysql.com/doc/refman/5.7/en/innodb-buf... (перейди на справку для своей версии, если нужно).
Ответ написан
Комментировать
@rPman
Отключить/удалить индексы, вернуть после полного импорта. Это самое большое ускорение.

Ещё можно изменить стратегию кеширования данных, дело в том что сервер базы данных слишком щепитильно относится к корректности загружаемых данных, выдавая частый fflush, т.е. принудительную запись данных с ожиданием ее окончания. Это актуально для режима работы с базой но первоначальное наполнение пустой базы можно не боятся сбоев, ведь попытку можно перезапустить с нуля. Например можно изменить режим записи в журнал ext4 на data writeback или лучше средствами виртуализации настроить кеширования диска на -device cache=unsafe для qemu.
Ещё круче можно настроить bcache с кеширующим диском на ramdisk....

В общем есть где творчески разгуляться, в итоге можно поднять скорость импорта на порядок, особенно если хватает оперативной памяти
Ответ написан
Комментировать
mayton2019
@mayton2019
Bigdata Engineer
Есть базы данных, весит примерно 40 гб. Нужно его восстановить на свой сервер

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

(я достигал резкого увеличения скорости загрузки когда переключал engine с InnoDb на MyISAM.
Это было давно. И как щас я не знаю. Может щас появились новые engines которые лучше чем
MyISAM. Надо читать. Но на тот момент мне транзакции были безразличны. Нужно было просто
хоть как-то загрузить данные и MyISAM помог).

Очень хорошо грузится CSV в пустую свеже-созданную таблицу без ничего. Даже не надо объявлять PK.. Лучше потом его активировать и также добавить констрейнты в режиме no check.

UPD:
Ответ написан
Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Похожие вопросы