Здравствуйте Гость ( Вход | Регистрация ) | Форум в сети 4599-й день

> Помогите с mysql, замедляется импорт *.sql дампа

Neri Karra
Oct 9 2018, 21:45
  
Пост #1



Репутация:   7  
Мембер
*

Группа: Пользователи
Сообщений: 116
С нами с: 15-April 10


Постараюсь коротко изложить суть проблеммы.

Конфиг железа, как говорится, что было под рукой - E6600/2048DDR2/SATA2
ПО - Ubuntu 16.04.5 x64 + все остальное (по сути только apache2 и mysql) с оф. репозиториев.
Винт - исправный, сектора, скорость, в целом смарт ок.

Задача - залить дамп БД сайта размером в десятки миллионов строк, размер дампа около 10 ГБ.

Пробовал как через консоль, так и (сейчас вот) через HeidiSQL.

Все начинается шикарно, скорость более полумиллиона обработанных строк в минуту, НО со временем скорость падает и падает. Для наглядности - за первый час обработано около 30 млн, но за практически целые сутки только 55-60 млн и сейчас импорт движется очень медленно.

swap убунты - в зашкал (но не сразу, точно не в перые пару часов работы)

скрин htop:

IPB ImageIPB Image

конфиг-файл мускула mysql.cnf

» Нажмите, чтобы показать спойлер - нажмите опять, чтобы скрыть... «


понимаю, что где-то накосячил с конфигом
подскажите где
и как сделать что бы весь импорт пролетал весь со стартовой скоростью.

не судите строго, ковыряюсь чисто в общеобразовательных целях, но все же... wink.gif
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
 
Reply to this topicStart new topic
Ответов(1 - 18)
Allex
Oct 9 2018, 22:00
  
Пост #2

Благодарности: 341

Репутация:   1371  
Sphynx in Mirror
Group Icon

Группа: Модеры
Сообщений: 22 480
С нами с: 2-February 07


swap убунты - в зашкал (но не сразу, точно не в перые пару часов работы)

Собственно, вот и ответ.

Тупо не хватает памяти, как начало свопиться - все уперлось в диск.
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
Beat
Oct 9 2018, 22:01
  
Пост #3



Репутация:   15  
Мембер
*

Группа: Пользователи
Сообщений: 125
С нами с: 15-February 08


а если выключить создание/оновление индексов на время импорта ?

Сообщение отредактировал Beat - Oct 9 2018, 22:01
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
Neri Karra
Oct 9 2018, 22:12
  
Пост #4



Репутация:   7  
Мембер
*

Группа: Пользователи
Сообщений: 116
С нами с: 15-April 10


(Allex @ Oct 9 2018, 23:00) *

Собственно, вот и ответ.

Тупо не хватает памяти, как начало свопиться - все уперлось в диск.


в конфиге квоты по памяти уменьшить просто ?
а до каких параметров? как правильно расчитать с учетом скромного объема озу в 2 гб?
может своп сделать больше? или только во вред будет?

(Beat @ Oct 9 2018, 23:01) *

а если выключить создание/оновление индексов на время импорта ?


а как именно это сделать? dash2.gif



да и дело в том, что все получалось немного ранее уже, за пол дня через консоль
но там железки были посвежее (амд4*3,2/4096ддз/ssd)
софт и конфиг тот же, только памяти везде в 2 раза больше отрезал
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
-13-
Oct 10 2018, 8:27
  
Пост #5

Благодарности: 5711

Репутация:   1202  
Флудер
Group Icon

Группа: Модеры
Сообщений: 5 784
С нами с: 17-October 06


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

попробуйте урезать память мускула и мониторить загрузку диска iotop
а еще в мускуле есть параметр innodb_read_io_threads \ innodb_write_io_threads , если это конечно к вашей БД относится

в моем случае 7Гб БД на ВМ с х8 ЦП 3,7ГГц, 16Гб памяти и nvme ssd заливался около 10 минут

Сообщение отредактировал -13- - Oct 10 2018, 11:44
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
Koka-ftp
Oct 10 2018, 9:50
  
Пост #6



Репутация:   817  
Голден Мембер
*****

Группа: Пользователи
Сообщений: 2 960
С нами с: 10-April 08


когда-то переливал большие базы банальным переносом /var/lib/mysql...но там есть свои "нюансы" smile.gif

Сообщение отредактировал Koka-ftp - Oct 10 2018, 11:46
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
nerve
Oct 10 2018, 16:29
  
Пост #7



Репутация:   276  
Голден Мембер
*****

Группа: Пользователи
Сообщений: 1 713
С нами с: 5-January 07


(Neri Karra @ Oct 9 2018, 22:45) *


Пробовал как через консоль,


какой командой делали через консоль?
как насчет:

cd /folder/with/database/file
mysql -p
set global net_buffer_length=1000000;
set global max_allowed_packet=1000000000;
set foreign_key_checks = 0;
source database.sql
set foreign_key_checks = 1;

User is offlineProfile CardPM
Go to the top of the page
+Quote Post
Neri Karra
Oct 11 2018, 0:26
  
Пост #8



Репутация:   7  
Мембер
*

Группа: Пользователи
Сообщений: 116
С нами с: 15-April 10


(nerve @ Oct 10 2018, 17:29) *

какой командой делали через консоль?

без выделенных строк
с use db_name вначале


cd /folder/with/database/file
mysql -p
set global net_buffer_length=1000000;
set global max_allowed_packet=1000000000;
set foreign_key_checks = 0;

source database.sql
set foreign_key_checks = 1;


UPD
оставлю наночь по вашей подсказке, посмотрим что выйдет с foreign_key_checks = 0; и пр.
Open in new windowIPB Image
UPD2

за 11 минут - 6 млн строк. своп пока что на уровне 10-20 мб
посмотрим...

(Koka-ftp @ Oct 10 2018, 10:50) *

когда-то переливал большие базы банальным переносом /var/lib/mysql...но там есть свои "нюансы" smile.gif


вся проблема в том, что пробовал перенести развернутую базу (папку с файлами) с пк (с wamp*a) примонтировав раздел, но phpmyadmin не видит таблиц в базе, ошибку выдает\

хотя если с одного вампа на другой переносили на флешке - все ок.
не стал углубляться в проблемму - решил разобраться и сделать по нормальному. и сделал. но на другой (в 3 раза шустрее машине) и точно не помню при каком mysql.cnf pardon.gif

а вот про "ньюансы" хотелось бы поподробней

(-13- @ Oct 10 2018, 9:27) *

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

попробуйте урезать память мускула и мониторить загрузку диска iotop
а еще в мускуле есть параметр innodb_read_io_threads \ innodb_write_io_threads , если это конечно к вашей БД относится

в моем случае 7Гб БД на ВМ с х8 ЦП 3,7ГГц, 16Гб памяти и nvme ssd заливался около 10 минут


спасибо за подсказку, завтра буду экспериментировать с памятью и io_threads

Сообщение отредактировал Neri Karra - Oct 11 2018, 0:29
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
Neri Karra
Oct 11 2018, 9:44
  
Пост #9



Репутация:   7  
Мембер
*

Группа: Пользователи
Сообщений: 116
С нами с: 15-April 10


В общем, таже фигня.
Все подвисает страшно, даже апач пхпинфо полминуты "отдает"
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
nerve
Oct 11 2018, 10:26
  
Пост #10



Репутация:   276  
Голден Мембер
*****

Группа: Пользователи
Сообщений: 1 713
С нами с: 5-January 07


попробуй это
https://www.ozerov.de/bigdump/usage/
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
Neri Karra
Oct 11 2018, 11:50
  
Пост #11



Репутация:   7  
Мембер
*

Группа: Пользователи
Сообщений: 116
С нами с: 15-April 10


заюзал iotop - подтвердила что мускул упирается головой в своп

вопрос - как заставить его работать медленнее, но стабильнее (без зависаний)

попробовать дать памяти раза в два меньше?

(nerve @ Oct 11 2018, 11:26) *

попробуй это
https://www.ozerov.de/bigdump/usage/


буду сейчас пробовать, спасибо
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
Neri Karra
Oct 11 2018, 21:27
  
Пост #12



Репутация:   7  
Мембер
*

Группа: Пользователи
Сообщений: 116
С нами с: 15-April 10


(Neri Karra @ Oct 11 2018, 12:50) *

заюзал iotop - подтвердила что мускул упирается головой в своп

вопрос - как заставить его работать медленнее, но стабильнее (без зависаний)

попробовать дать памяти раза в два меньше?
буду сейчас пробовать, спасибо

И тут не проканало

bigdump не может справиться с моим файлом

Stopped at the line 394.

At this place the current query includes more than 300 dump lines. That can happen if your dump file was created by some tool which doesn't place a semicolon followed by a linebreak at the end of each query, or if your dump contains extended inserts or very long procedure definitions. Please read the BigDump usage notes for more infos. Ask for our support services in order to handle dump files containing extended inserts.


Advanced notes

Note 1: BigDump will fail processing large tables containing extended inserts. An extended insert contains all table entries within one SQL query. BigDump isn’t able to split such SQL queries. In most cases BigDump will stop if some query includes to many lines. But if PHP complains that allowed memory size exhausted or MySQL server has gone away your dump probably also contains extended inserts. Please turn off extended inserts when exporting database from phpMyAdmin. If you only have a dump file with extended inserts please ask for our support service in order to convert it into a file usable by BigDump.
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
phpkiev
Oct 12 2018, 11:15
  
Пост #13



Репутация:   17  
Новачек


Группа: Пользователи
Сообщений: 95
С нами с: 24-November 07


Что-то странное. У нас ежедневно бекапится несколько баз суммарно за 20гиг и периодически разворачиваются дампы, это всё занимает минуты, не дни. Правда сервера на ДО, не локальные.

Ну если проблема с SQL дампами, попробуйте скопировать файлами. Правда при этом надо помнить, что если в базах есть таблицы innoDB и предварительно при первичной настройке не включалась опция innodb_file_per_table - то все данные из таких таблиц будут храниться в одном файле (обычно это что-то типа /var/lib/mysql/ibdata1) и его надо переносить отдельно (иначе MySQL не увидит innoDB таблиц)
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
-13-
Oct 14 2018, 13:34
  
Пост #14

Благодарности: 5711

Репутация:   1202  
Флудер
Group Icon

Группа: Модеры
Сообщений: 5 784
С нами с: 17-October 06


xtrabackup же для innodb + binlog
а для myisam и rsync хватит
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
Neri Karra
Oct 14 2018, 22:34
  
Пост #15



Репутация:   7  
Мембер
*

Группа: Пользователи
Сообщений: 116
С нами с: 15-April 10


Купил вчера SSD 240
тестрирую на нем...
может действительно дело в "медленном диске"
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
baltazor
Oct 15 2018, 17:11
  
Пост #16

Благодарности: 26419

Репутация:   526  
Смерть - это лишь начало
Group Icon

Группа: Модеры
Сообщений: 3 603
С нами с: 5-September 07


Если у вас innodb таблицы:

1) попробуйте сделать dump через mysqldump с опциями --quick --single-transaction за счет первой опции просто бэкап будет быстрее за счет второй опции быстрее будет разворачиваться (на сколько быстрее сложно сказать, замеров не делал, но по ощущениям было быстрее)
2) если у вас всего 2гб памяти на сервер, я бы мускулу выделил бы 1-1.2гб памяти через innodb_buffer_pool_size
3) для новой базы я бы еще выставил опцию innodb_file_per_table=1 , но тут будьте осторожны и сперва прочитайте документацию как правильно это делать.

То что в начале идет быстрая вставка данных а потом замедляется совершенно логично, ведь сперва идет вставка записей в кэш памяти а потом идет сброс на диск. Еще момент то что у вас Sata2 и нужно понимать что из-за этого интерфейса SSD диск будет работать не во всю силу.

P.S. Ну и как посоветовал -13- попробуйте использовать xtrabackup но опять же, сперва вдумчиво почитайте документацию к этому инструменту.
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
Neri Karra
Вчера, 0:04
  
Пост #17



Репутация:   7  
Мембер
*

Группа: Пользователи
Сообщений: 116
С нами с: 15-April 10


да, движек иннодб)

innodb_buffer_pool_size выделил 1024М
innodb_file_per_table=1
и на SSD диске все прошло за часов 6.

да,SATA2 , чтение запись в раене 200мб/сек макс (линейная запись, при крупных файлах)

Всем спасибо за советы и подсказки, получил много полезной информации.
Тему закрывать не буду, может кто-то еще чего то захочет добавить smile.gif
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
-13-
Вчера, 10:24
  
Пост #18

Благодарности: 5711

Репутация:   1202  
Флудер
Group Icon

Группа: Модеры
Сообщений: 5 784
С нами с: 17-October 06


скорее всего "часов за 6" потому что не все ssd одинаково полезны
обычный ширпотреб не намного отличается от hdd в сценариях с БД
а enterprise ssd показывают производительность на порядок выше

я это прочувствовал на UA-Хостингах где ставят абышо и Hetzner, где ставят то что указано в тарифе

Сообщение отредактировал -13- - Вчера, 10:25
User is offlineProfile CardPM
Go to the top of the page
+Quote Post
Neri Karra
Вчера, 21:23
  
Пост #19



Репутация:   7  
Мембер
*

Группа: Пользователи
Сообщений: 116
С нами с: 15-April 10


купленый мной SSD дествительно из категории "ширпотреб"
а именно Kingston SSDNow A400 240 GB (SA400S37/240G) (1.25k)

тем не менее, для моих експериментов достаточно smile.gif
User is offlineProfile CardPM
Go to the top of the page
+Quote Post

Reply to this topicStart new topic

 



- Упрощённая версия
Сейчас: 17th October 2018 - 19:54
Сайт не предоставляет электронные версии произведений, а занимается лишь коллекционированием и каталогизацией ссылок, присылаемых и публикуемых на форуме нашими читателями. Если вы являетесь правообладателем какого-либо представленного материала и не желаете чтобы ссылка на него находилась в нашем каталоге, свяжитесь с нами и мы удалим её. Файлы для обмена предоставлены пользователями сайта и администрация не несёт ответственности за их содержание.