Я хотел бы создать копию базы данных с приблизительно 40 таблицами InnoDB и около 1,5 ГБ данных с mysqldump и MySQL 5.1.
Каковы наилучшие параметры (например: --single-transaction), которые приведут к самому быстрому дампу и нагрузке данных?
Кроме того, при загрузке данных во второй БД ускоряется:
1) передайте результаты непосредственно во второй экземпляр сервера MySQL и используйте параметр -compress
или
2) загрузить его из текстового файла (то есть: mysql < my_sql_dump.sql)
Проведите его напрямую в другой экземпляр, чтобы избежать накладных расходов на диск. Не беспокойтесь о --compress, если вы не работаете по медленной сети, так как в быстрой локальной сети или loopback сетевые накладные расходы не имеют значения.
Использование опции "-T" с mysqldump приводит к большому количеству файлов .sql и .txt в указанном каталоге. Это на 50% быстрее, чем для хранения больших таблиц, чем один файл .sql с инструкциями INSERT (требуется меньше на 1/3 часа).
Кроме того, при восстановлении огромное преимущество, если вы можете параллельно загружать несколько таблиц и насыщать несколько ядер. На 8-ядерном ящике это может быть столько же, сколько разница в 8 раз в режиме настенных часов для восстановления дампа, помимо повышения эффективности, обеспечиваемого "-T" . Поскольку "-T" заставляет каждую таблицу хранить в отдельном файле, загрузка их параллельно проще, чем разделение массивного файла .sql.
Приведенные выше стратегии до логической точки зрения, можно создать script, чтобы сбрасывать базу данных широко параллельно. Ну, именно то, что Maakit mk-parallel-dump (см. http://www.maatkit.org/doc/mk-parallel-dump.html) и инструменты mk-parallel-restore; perl, которые выполняют множественные вызовы в основной программе mysqldump. Однако, когда я пытался их использовать, у меня возникли проблемы с тем, чтобы восстановление завершилось без дублирования ключевых ошибок, которые не возникали с дампами ванили, поэтому имейте в виду, что ваше перемещение может отличаться.
Переключатель --single-transaction очень полезен для взятия дампа в живой базе данных без необходимости откладывать его или сбрасывать базу подчиненных без остановки slave.
К сожалению, -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, --quick и --set-charset. Все параметры, которые обозначаются --opt, также включены по умолчанию, поскольку по умолчанию функция -opt включена.
Из этих поведений "-quick" является одним из самых важных (пропускает весь набор результатов в mysqld перед передачей первой строки) и может быть с "mysql" (который не поворачивается --quick on по умолчанию), чтобы значительно ускорить запросы, которые возвращают большой набор результатов (например, сброс всех строк большой таблицы).
Я думаю, что это будет намного быстрее и сэкономить дисковое пространство, если вы попытались репликацию базы данных в отличие от использования 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 - довольно небольшая база данных, поэтому, вероятно, это не будет проблемой.