create - mysql workbench

mejorar la velocidad de importación mysql

Question

Tengo una gran base de datos de 22GB . Solía ​​hacer una copia de seguridad con el comando mysqldump en formato gzip.

Cuando extraigo el archivo gz produce el archivo .sql de 16.2GB

Cuando trato de importar la base de datos en mi servidor local, me lleva aproximadamente 48 horas importar. ¿Hay alguna forma de aumentar la velocidad del proceso de importación?

También me gustaría saber si es necesario realizar algún cambio de hardware para mejorar el rendimiento.

Configuración actual del sistema

 Processor: 4th Gen i5
 RAM: 8GB

#actualizar

my.cnf es el siguiente

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

Se está cargando durante 3 días y en este momento se han importado 9,9 GB. La base de datos tiene tablas MyISAM e InnoDB . ¿Qué puedo hacer para mejorar el rendimiento de importación?

He intentado exportar cada tabla por separado en formato gz con mysqldump e importar cada tabla a través de un script PHP ejecutando el siguiente código

$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);
}

Answer 1

Asegúrese de aumentar su variable " max_allowed_packet " a un tamaño lo suficientemente grande. Esto realmente ayudará si tienes muchos datos de texto. El uso de hardware de alto rendimiento seguramente mejorará la velocidad de importación de datos.

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

Answer 2

Forma 1: deshabilite las claves foráneas como se sugirió fakedrake.

SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS = 0

Modo 2: use BigDump, dividirá su archivo mysqldump y luego lo importará. http://www.ozerov.de/bigdump/usage/

Pregunta: ¿Dijiste que estás subiendo? cómo está importando su vertedero? no directamente desde el servidor / línea de comando?

Answer 3

Hacer un volcado y restaurar de la manera descrita significará que MySQL tiene que reconstruir completamente los índices a medida que se importan los datos. También tiene que analizar los datos cada vez.

Sería mucho más eficiente si pudiera copiar archivos de datos en un formato que MySQL ya entiende. Una buena forma de hacerlo es usar el innobackupex de Percona

(Código abierto y distribuido como parte de XtraBackup disponible para descargar desde here ).

Esto tomará una instantánea de las tablas MyISAM, y para las tablas InnoDB copiará los archivos subyacentes, luego reproducirá el registro de transacciones contra ellos para garantizar un estado consistente. Puede hacerlo desde un servidor en vivo sin tiempo de inactividad (no tengo idea si es un requisito suyo).

Le sugiero que lea la documentación, pero para hacer una copia de seguridad en su forma más simple de usar:

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

Si los datos están en la misma máquina, entonces innobackupex incluso tiene un comando de restauración simple:

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

Hay muchas más opciones y diferentes formas de hacer la copia de seguridad, así que realmente le recomiendo que lea bien la documentación antes de comenzar.

Para referencia a la velocidad, nuestro servidor de prueba lento, que hace alrededor de 600 IOPS puede restaurar una copia de seguridad de 500 GB en aproximadamente 4 horas usando este método.

Por último: mencionó lo que podría hacerse para acelerar la importación. Va a depender principalmente de lo que sea el cuello de la botella. Normalmente, las operaciones de importación están vinculadas a E / S (puede probar esto verificando io espera) y la forma de acelerarlo es con un rendimiento de disco más rápido, ya sea discos más rápidos o más al unísono.

Answer 4

Hay muchos parámetros que faltan para comprender completamente el motivo del problema. como:

1. Versión de MySQL
2. Tipo de disco y velocidad
3. Memoria libre en el servidor antes de iniciar el servidor MySQL
4. salida de iostat antes y en el momento del mysqldump.
5. ¿Cuáles son los parámetros que utiliza para crear el archivo de volcado en primer lugar?

y muchos más.

Así que trataré de adivinar que su problema está en los discos porque tengo 150 instancias de MySQL que administro con 3 TB de datos en una de ellas, y generalmente el disco es el problema

Ahora a la solución:

En primer lugar, su MySQL no está configurado para un mejor rendimiento.

Puede leer sobre la configuración más importante para configurar en la publicación del blog de Percona: http://www.percona.com/blog/2014/01/28/10-mysql-settings-to-tune-after-installation/

Especialmente revisa los parámetros:

innodb_buffer_pool_size 
innodb_flush_log_at_trx_commit
innodb_flush_method

Si su problema es el disco, leer el archivo desde la misma unidad, empeora el problema.

Y si su servidor MySQL comienza a intercambiar porque no tiene suficiente memoria RAM disponible, su problema se agrava aún más.

Debe ejecutar los diagnósticos en su máquina antes y en el momento del procedimiento de restauración para resolverlo.

Además, puedo sugerirle que use otra técnica para realizar la tarea de reconstrucción, que funciona más rápido que mysqldump.

Es Percona Xtrabackup - http://www.percona.com/doc/percona-xtrabackup/2.2/

Tendrá que crear la copia de seguridad con ella y restaurar desde allí, o reconstruir desde el servidor en ejecución directamente con la opción de transmisión.

Además, la versión de MySQL a partir de 5.5 - InnoDB funciona más rápido que MyISAM. Considere cambiar todas sus tablas a la misma.

Answer 5

No estoy seguro de que sea una opción para ti, pero la mejor manera de hacerlo es lo que Tata y AndySavage ya dijeron: tomar una instantánea de los archivos de datos del servidor de producción y luego instalarlos en tu caja local usando Percona''s innobackupex. Hará una copia de seguridad de las tablas de InnoDb de forma consistente y realizará un bloqueo de escritura en las tablas de MyISAM.

Prepare una copia de seguridad completa en la máquina de producción:

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

Copie (o canalice a través de SSH mientras realiza la copia de seguridad - más información here ) los archivos respaldados en su máquina local y restáurelos:

Restaurar la copia de seguridad:

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

Puede encontrar la documentación completa de innobackupex aquí: http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_script.html

El tiempo de restauración será MUCHO más rápido que leer un volcado de SQL.

Answer 6

Obtenga más RAM, obtenga un procesador más rápido, obtenga un SSD para escribir más rápido. Lote los insertos para que funcionen más rápido que un montón de insertos individuales. Es un archivo enorme y llevará tiempo.

Answer 7

Tuve que lidiar con el mismo problema. He encontrado que usa mysqldump para generar un archivo CSV (como este):

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

y luego importar esos datos usando la consulta LOAD DATA INFILE desde el cliente mysql (como este):

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

ser un orden de magnitud más rápido que solo ejecutar las consultas SQL que contienen los datos. Por supuesto, también depende de que las tablas ya estén creadas (y vacías).

Por supuesto, también puede hacerlo exportando y luego importando su esquema vacío primero.

Answer 8

Una cosa que puedes hacer es

SET AUTOCOMMIT = 0; SET FOREIGN_KEY_CHECKS=0

Y también puedes jugar con los valores

innodb_buffer_pool_size
innodb_additional_mem_pool_size
innodb_flush_method

en my.cnf para que te vayas, pero en general deberías echarle un vistazo al resto de los parámetros innodb para ver qué es lo que más te conviene.

Este es un problema que tuve en el pasado. No creo que lo haya abordado por completo, pero espero haberme orientado en esta dirección desde el principio. Me hubiera ahorrado bastante tiempo.