SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 01.12.2016 08:54:21

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Как ускорить импорт базы (база в развернутом виде весит 350гб, дамп весит 100 гб)

Доброго времени суток, коллеги!

Собственно, сабж. Ubuntu, MySQL 5.6, InnoDB, в базе около 10 табличек, некоторые связаны вторичными ключами,
в каждой табличке от 10 до 100 миллионов записей. везде есть первичные ключи, где-то есть уникальные индексы.

mysqldump на source-сервере сделал с ключами --quick и --disable-keys.

В получившемся sql-файле с дампом insert-строки разбиты по 20-30 тысяч values в каждой.
Думал добавить --single-transaction, но не стал в опасении, что destination-сервер просто не вытянет такую адскую транзакцию.

На destination-сервере выставил (для этой операции) innodb_flush_log_at_trx_commit в 0.

запустил mysql ... < dumpfile.sql, ibd-файл увеличивается раз в 4 секунды примерно на 4 мегабайта, такими темпами долго оно будет импортироваться...

Вопрос - можно ли еще как-то ускорить? Окромя непосредственного копирования файлов.

На src-сервере
mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.6.31-0ubuntu0.15.10.1 |
+-------------------------+
1 row in set (0.00 sec)


На dest-сервере
mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.6.30-0ubuntu0.15.10.1 |
+-------------------------+
1 row in set (0.00 sec)

Отличия вроде невелики.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#2 01.12.2016 09:40:34

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Как ускорить импорт базы (база в развернутом виде весит 350гб, дамп весит 100 гб)

Нужно попробовать такие варианты
I.
1. создать таблицы без индексов
2. LOAD DATA INFILE;
3. создать индексы

II.
То же, что I, но сначала создать таблицы MyISAM, а затем преобразовать в Innodb

Неактивен

 

#3 01.12.2016 09:55:42

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Как ускорить импорт базы (база в развернутом виде весит 350гб, дамп весит 100 гб)

rgbeast, спасибо за ответ. А что должен находится в том файле, который подаётся на вход LOAD DATA INFILE? mysqldump-выхлоп исходной таблицы или данные в преобразованном виде?

Пытаюсь пока все же копированием сделать, но пока налетаю на то, что исчезает tablespace sad.
Может быть существует инструкция для делания таких хаков?


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#4 01.12.2016 10:20:19

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Как ускорить импорт базы (база в развернутом виде весит 350гб, дамп весит 100 гб)

В файле должен быть CVS. Его можно сделать с помощью SELECT INTO OUTFILE.

Правильное копирование можно сделать с помощью Percona xtrabackup

Неактивен

 

#5 01.12.2016 11:40:04

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Как ускорить импорт базы (база в развернутом виде весит 350гб, дамп весит 100 гб)

Percona xtrabackup - да, судя по описанию то, что нужно.

Пока выкрутился через копирование на уровне ФС( если копирование файлов обернуть в

ALTER TABLE ТАБЛИЦА DISCARD TABLESPACE;
ALTER TABLE ТАБЛИЦА IMPORT TABLESPACE;

), то получается.

А в будущем обязательно перейду на xtrabackup.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#6 02.12.2016 12:03:33

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Как ускорить импорт базы (база в развернутом виде весит 350гб, дамп весит 100 гб)

После переноса посредством копирования файлов и импорта tablespace вылез интересный эффект (связываю его именно с тем, что таблицы переехали с сервера на другой "не по Фен-шую"):

оптимизатор стал предпочитать делать FTS вместо использования индекса.

mysql> explain SELECT o.`f1` FROM o WHERE o.`f2` = 180293302;
+----+-------------+-------+------+----------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys  | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+------+----------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | o     | ALL  | t_id | NULL | NULL    | NULL |    1 | Using where; |
+----+-------------+-------+------+----------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)


Индекс у поля f2, разумеется, есть.
Записей сотни миллионов, по ключу f2=180293302 выбирается максимум 10, и source-сервере такой запрос, разумеется, летает со свистом.

USE INDEX пробовал, игнорируется.
FORCE INDEX даёт нужный эффект, но по понятным причинам совершенно не хочется перелопачивать весь код бизнес-логики и прописывать FORCE INDEX на SELECT'ы.
При этом possible keys есть. Но ни один из них не выбирается, и ref = NULL

Гуглил, находил просто такие фразы как "иногда, чтобы заставить оптимизатор корректно подбирать индексы на таблицу, нужно перестроить индексы, в случае innodb это ALTER TABLE `t` ENGINE=InnoDB".
В моем случае так и вышло, альтернул табличку, explain стал работать корректно.

Как думаете, это вылезло именно из-за такого копирования, которое я использовал?
Ведь при xtrabackup такого не должно уже быть?


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#7 02.12.2016 12:06:02

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Как ускорить импорт базы (база в развернутом виде весит 350гб, дамп весит 100 гб)

Скорее всего так и есть. Неофициальный перенос таблиц не обновляет статистику и сервер думает, что таблицы пустые. При использовании xtrabackup подобного быть не должно.

Неактивен

 

#8 02.12.2016 12:10:31

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Как ускорить импорт базы (база в развернутом виде весит 350гб, дамп весит 100 гб)

Забавно, что про используемый мной способ переноса таблиц немало в принципе написано в сети и никто не упоминал про побочку, что я описал.

rgbeast, скажи, а  есть какая-то спецкоманда для обновления этой самой статистики? Или только ALTER TABLE `t` ENGINE=InnoDB? А то мне кажется, что есть в этом какой-то overhead.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#9 02.12.2016 12:14:45

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Как ускорить импорт базы (база в развернутом виде весит 350гб, дамп весит 100 гб)

Может быть поможет ANALYZE TABLE

Неактивен

 

#10 02.12.2016 12:16:34

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: Как ускорить импорт базы (база в развернутом виде весит 350гб, дамп весит 100 гб)

Ага, спасибо, похоже на то.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson