Повысить скорость импорта mysql

У меня есть большая база данных 22GB. Я использовал резервную копию с помощью команды mysqldump в формате gzip.

Когда я извлекаю gz файл, он создает файл .sql 16.2GB

Когда я пытаюсь импортировать базу данных на моем локальном сервере, для импорта требуется около 48 часов. Есть ли способ увеличить скорость процесса импорта?

Также я хотел бы знать, нужно ли делать какие-либо изменения оборудования для повышения производительности.

Текущая конфигурация системы

 Processor: 4th Gen i5
 RAM: 8GB

#update

my.cnf выглядит следующим образом

#
# 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

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer      = 16M
max_allowed_packet  = 512M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 4M
query_cache_size        = 512M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet  = 512M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 512M

#
# * 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/

Он загружается в течение 3 дней, и сейчас импортировано 9,9 ГБ. База данных имеет таблицы MyISAM и InnoDB. Что я могу сделать для повышения эффективности импорта?

Я попытался экспортировать каждую таблицу отдельно в формате gz с помощью mysqldump и импортировать каждую таблицу через PHP скрипт, выполнив следующий код

$dir="./";
$files = scandir($dir, 1);
array_pop($files);
array_pop($files);
$tablecount=0;
foreach($files as $file){
    $tablecount++;
    echo $tablecount."     ";

    echo $file."\n";
    $command="gunzip < ".$file." | mysql -u root -pubuntu cms";

    echo exec($command);
}

Ответы

Ответ 1

Есть много параметров, которые отсутствуют, чтобы полностью понять причину проблемы. например:

Версия MySQL
Тип и скорость диска
Свободная память на сервере перед запуском сервера MySQL
вывод iostat до и во время mysqldump.
Каковы параметры, которые вы используете для создания файла дампа в первую очередь.

и многие другие.

Итак, я постараюсь угадать, что ваша проблема в дисках, потому что у меня есть 150 экземпляров MySQL, которыми я управляю с 3TB данных на одном из них, и обычно проблема с диском

Теперь к решению:

Прежде всего - ваш MySQL не настроен для лучшей производительности.

Вы можете прочитать о наиболее важных настройках для настройки в блоге Percona: http://www.percona.com/blog/2014/01/28/10-mysql-settings-to-tune-after-installation/

В частности, проверьте параметры:

innodb_buffer_pool_size 
innodb_flush_log_at_trx_commit
innodb_flush_method

Если ваша проблема - это диск - чтение файла с одного и того же диска - ухудшает ситуацию.

И если ваш MySQL-сервер начнет меняться, потому что у него недостаточно оперативной памяти, ваша проблема становится еще больше.

Вам необходимо запустить диагностику на вашем компьютере до и во время процедуры восстановления, чтобы понять это.

Кроме того, я могу предложить вам использовать другую технику для выполнения задачи перестройки, которая работает быстрее, чем mysqldump.

Это Percona Xtrabackup - http://www.percona.com/doc/percona-xtrabackup/2.2/

Вам нужно будет создать резервную копию с ней и восстановить ее или перестроить с запущенного сервера напрямую с помощью функции потоковой передачи.

Кроме того, версия MySQL начиная с 5.5 - InnoDB работает быстрее, чем MyISAM. Подумайте об изменении всех своих таблиц.

Ответ 2

Выполнение дампа и восстановление в описанном порядке означает, что MySQL должен полностью перестроить индексы при импорте данных. Он также должен анализировать данные каждый раз.

Было бы намного эффективнее, если бы вы могли копировать файлы данных в формате, который MySQL уже понимает. Хороший способ сделать это - использовать innobackupex из Percona

(Open Source и распространяется как часть XtraBackup, доступный для загрузки из здесь).

Это займет моментальный снимок таблиц MyISAM, а для таблиц InnoDB он скопирует базовые файлы, а затем воспроизведет журнал транзакций против них, чтобы обеспечить согласованное состояние. Он может делать это с живого сервера без простоя (я понятия не имею, является ли это вашим требованием?)

Я предлагаю вам ознакомиться с документацией, но взять в ней резервную копию простейшей формы:

$ innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/
$ innobackupex --apply-log /path/to/BACKUP-DIR/

Если данные находятся на одном компьютере, тогда у innobackupex даже есть простая команда восстановления:

$ innobackupex --copy-back /path/to/BACKUP-DIR

Существует гораздо больше вариантов и способов резервного копирования, поэтому я бы очень хотел, чтобы вы хорошо прочитали документацию, прежде чем начать.

Для ссылки на скорость наш медленный тестовый сервер, который выполняет около 600 операций ввода-вывода, может восстановить резервную копию на 500 ГБ примерно за 4 часа, используя этот метод.

Наконец: Вы упомянули, что можно сделать для ускорения импорта. В основном это зависит от того, что у бутылки. Как правило, операции импорта связаны с привязкой ввода/вывода (вы можете проверить это, проверив io ожидания), и способ ускорить это с более быстрой пропускной способностью диска - либо более быстрые диски, либо больше из них в унисон.

Ответ 3

Убедитесь, что вы увеличили переменную " max_allowed_packet" до достаточно большого размера. Это действительно поможет, если у вас много текстовых данных. Использование высокопроизводительного оборудования наверняка улучшит скорость импорта данных.

mysql --max_allowed_packet=256M -u root -p < "database-file.sql"

Ответ 4

Одна вещь, которую вы можете сделать, это

SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS=0

И вы также можете играть со значениями

innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_flush_method

в my.cnf, чтобы вы пошли, но в целом вы должны взглянуть на остальные параметры innodb, чтобы узнать, что лучше подходит вам.

Это проблема, с которой я столкнулся в прошлом, я не чувствую, что полностью занялся, но надеюсь, что я указал в этом направлении с самого начала. Скорее бы спасли меня некоторое время.

Ответ 5

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

Ответ 6

Способ 1: Отключить внешние ключи, как предполагалось.

SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS = 0

Способ 2: использовать BigDump, он будет разбивать ваш файл mysqldump и затем импортировать его. http://www.ozerov.de/bigdump/usage/

Вопрос: Вы сказали, что вы загружаете? как вы импортируете свой свалку? не напрямую с сервера/командной строки?

Ответ 7

Мне пришлось иметь дело с той же проблемой. Я нашел использование mysqldump для вывода в CSV файл (например:):

mysqldump -u [username] -p -t -T/path/to/db/directory [database] --fields-enclosed-by=\" --fields-terminated-by=,

а затем импортировать эти данные с помощью запроса LOAD DATA INFILE из клиента mysql (например:):

LOAD DATA FROM INFILE /path/to/db/directory/table.csv INTO TABLE FIELDS TERMINATED BY ',';

будет примерно на порядок быстрее, чем просто выполнять SQL-запросы, содержащие данные. Конечно, он также зависит от уже созданных таблиц (и пустых).

Конечно, вы можете это сделать, экспортируя и импортируя сначала свою пустую схему.

Ответ 8

Я не уверен, что это вариант для вас, но лучший способ сделать это - это то, что Tata и AndySavage уже сказали: взять снимок файлов данных с производственного сервера, а затем установить их в локальном поле используя Percona innobackupex. Он будет последовательно создавать резервные таблицы InnoDb и выполнять блокировку записи в таблицах MyISAM.

Подготовьте полную резервную копию на рабочей машине:

http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/preparing_a_backup_ibk.html

Скопируйте (или перейдите через SSH при создании резервной копии - подробнее здесь) резервные копии файлов на локальный компьютер и восстановите их:

Восстановить резервную копию:

http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/restoring_a_backup_ibk.html

Здесь вы можете найти полную документацию innobackupex: http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_script.html

Время восстановления будет намного быстрее, чем чтение дампа SQL.