Я хотел бы создать копию базы данных примерно с 40 таблицами InnoDB и около 1,5 ГБ данных с помощью mysqldump и MySQL 5.1.
Каковы наилучшие параметры (т.е. --single-transaction), которые обеспечат самый быстрый сброс и загрузку данных?
Кроме того, при загрузке данных во вторую БД быстрее:
1) передать результаты непосредственно на второй экземпляр сервера MySQL и использовать параметр --compress
or
2) загрузите его из текстового файла (например: mysql ‹ my_sql_dump.sql)
Направьте его напрямую в другой экземпляр, чтобы избежать накладных расходов на диск. Не беспокойтесь о --compress, если только вы не работаете в медленной сети, поскольку в быстрой локальной сети или обратной связи сетевые накладные расходы не имеют значения.
Использование параметра "-T" с mysqldump приводит к большому количеству файлов .sql и .txt в указанном каталоге. Это примерно на 50 % быстрее для дампа больших таблиц, чем один файл .sql с инструкциями INSERT (занимает на 1/3 меньше времени настенных часов).
Кроме того, при восстановлении есть огромное преимущество, если вы можете загружать несколько таблиц параллельно и нагружать несколько ядер. На 8-ядерном компьютере это может достигать 8-кратной разницы во времени настенных часов для восстановления дампа, помимо повышения эффективности, обеспечиваемого «-T». Поскольку "-T" заставляет каждую таблицу храниться в отдельном файле, загружать их параллельно проще, чем разбивать большой файл .sql.
Доведя описанные выше стратегии до их логической крайности, можно создать сценарий для параллельного создания большого дампа базы данных. Ну, это именно то, что Maakit mk-parallel-dump (см. http://www.maatkit.org/doc/mk-parallel-dump.html) и инструменты mk-parallel-restore; Perl-скрипты, которые делают несколько вызовов базовой программы mysqldump. Однако, когда я пытался использовать их, у меня возникли проблемы с завершением восстановления без повторяющихся ошибок ключа, которые не возникали при ванильных дампах, поэтому имейте в виду, что ваш пробег может отличаться.
Переключатель --single-transaction очень полезен для создания дампа активной базы данных без ее приостановки или для создания дампа подчиненной базы данных без остановки подчиненной базы данных.
К сожалению, -T не совместим с --single-transaction, поэтому вы получите только одну.
Обычно снятие дампа происходит намного быстрее, чем его восстановление. Еще есть место для инструмента, который берет входящий монолитный файл дампа и разбивает его на несколько частей для параллельной загрузки. Насколько мне известно, такого инструмента еще не существует.
Чтобы прослушать входящий дамп на одном хосте, выполните:
nc -l 7878 > mysql-dump.sql
Затем на хосте БД запустите
mysqldump $OPTS | nc myhost.mydomain.com 7878
Это снижает конкуренцию за дисковые шпиндели на мастере из-за записи дампа на диск, немного ускоряя ваш дамп (при условии, что сеть достаточно быстра, чтобы не отставать, что является довольно безопасным предположением для двух хостов в одном центре обработки данных). Кроме того, если вы создаете новый ведомый сервер, это избавляет от необходимости передавать файл дампа после его завершения.
Предостережения — очевидно, вам нужно иметь достаточную пропускную способность сети, чтобы невыносимо не замедлять работу, и в случае обрыва TCP-сессии вам придется начинать все сначала, но для большинства дампов это не является серьезной проблемой.
Наконец, я хочу прояснить один пункт общей путаницы.
Несмотря на то, как часто вы видите эти флаги в примерах и руководствах mysqldump, они излишни, потому что они включены по умолчанию:
--opt--add-drop-table--add-locks--create-options--disable-keys--extended-insert--lock-tables--quick--set-charset. Из http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html:
Использование --opt аналогично указанию --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, -- быстро и --set-charset. Все параметры, обозначаемые --opt, также включены по умолчанию, потому что --opt включен по умолчанию.
Из этих поведений «--quick» является одним из наиболее важных (пропускает кэширование всего набора результатов в mysqld перед передачей первой строки) и может быть с «mysql» (который НЕ включает --quick по умолчанию) для значительного ускорения запросов, которые возвращают большой набор результатов (например, вывод всех строк большой таблицы).
я думаю, что это будет намного быстрее и сэкономит место на диске, если вы попытаетесь репликация базы данных вместо использования mysqldump. лично я использую sqlyog enterprise для моей действительно тяжелой работы, но есть также ряд другие инструменты, которые могут предоставлять те же услуги. если, конечно, вы не хотите использовать только mysqldump.
Для innodb --order-by-primary --extended-insert обычно является лучшей комбинацией. Если вы стремитесь к максимальной производительности, а целевой блок имеет много ядер ЦП, вы можете разделить полученный файл дампа и выполнить параллельные вставки во многих потоках, вплоть до innodb_thread_concurrency/2.
Кроме того, настройте innodb_buffer_pool_size на цели до максимального значения, которое вы можете себе позволить, и увеличьте innodb_log_file_size до 128 или 256 МБ (осторожно с этим, вам нужно удалить старые файлы журналов перед перезапуском демона mysql, иначе он не перезапустится)
Используйте инструмент mk-parallel-dump от Maatkit.
По крайней мере, это, вероятно, будет быстрее. Я бы больше доверял mysqldump.
Как часто вы это делаете? Действительно ли это проблема производительности приложения? Возможно, вам следует разработать способ сделать это, при котором не нужно сбрасывать все данные (репликация?)
С другой стороны, 1.5G — довольно маленькая база данных, поэтому, вероятно, это не будет большой проблемой.
