Hago una copia de seguridad de mi servidor Live usando el comando mysqldump a través del trabajo CRON en mi servidor Ubuntu a través de Bash Shell Script y el mismo script también carga la copia de seguridad en mi servidor de copia de seguridad. Anteriormente, esto funcionaba bien, pero ahora me enfrento a un problema de lentitud (la copia de seguridad y la carga en el servidor de copia de seguridad tardan 1 hora), ya que el tamaño de una de las tablas de la base de datos ha aumentado a 5 GB y consta de 10 millones de registros. Vi en un hilo que podemos ajustar la inserción de SQL a través de la ejecución masiva/grupal de SQL.¿Cómo puede mysql insertar millones de registros más rápido?
Pero en mi caso, no estoy seguro de cómo puedo crear un Shell Script para realizar lo mismo.
El requisito es que quiero exportar todas mis tablas de SQL Database en grupos de un máximo de 10k para que la ejecución sea más rápida mientras se importa en el servidor.
He escrito este código en el script bash de mi servidor:
#!/bin/bash cd /tmp file=$(date +%F-%T).sql mysqldump \ --host ${MYSQL_HOST} \ --port ${MYSQL_PORT} \ -u ${MYSQL_USER} \ --password="${MYSQL_PASS}" \ ${MYSQL_DB} > ${file} if [ "${?}" -eq 0 ]; then mysql -umyuser -pmypassword -h 198.168.1.3 -e "show databases" mysql -umyuser -pmypassword -h 198.168.1.3 -D backup_db -e "drop database backup_db" mysql -umyuser -pmypassword -h 198.168.1.3 -e "create database backup_db" mysql -umyuser -pmypassword -h 198.168.1.3 backup_db < ${file} gzip ${file} aws s3 cp ${file}.gz s3://${S3_BUCKET}/live_db/ rm ${file}.gz else echo "Error backing up mysql" exit 255 fiEl servidor de respaldo y el servidor en vivo comparten la misma configuración de hardware de AWS: 16 GB de RAM, 4 CPU, 100 GB de SSD.
Estas son las capturas de pantalla y los datos:
Captura de pantalla y consultas para la depuración en el servidor en vivo: tablas de esquema de información:
https://i.imgur.com/RnjQwbP.pngMOSTRAR ESTADO GLOBAL:
https://pastebin.com/raw/MuJYwnsmMOSTRAR VARIABLES GLOBALES:
https://pastebin.com/raw/wdvn97XPCaptura de pantalla y consultas para la depuración en el servidor de copia de seguridad:
https://i.imgur.com/rB7qcYU.png https://pastebin.com/raw/K7vHXqWi https://pastebin.com/raw/PR2gWpqeLa carga de trabajo del servidor es casi insignificante. No hay carga todo el tiempo, también he monitoreado a través del Panel de monitoreo de AWS, y esa es la única razón para tomar más del servidor de recursos requerido para que nunca se agote. Me he llevado 16 GB de RAM y 4 de CPU que son más que suficientes. El panel de monitoreo de AWS mostró un uso máximo del 6 % en raras ocasiones y el máximo de veces es de alrededor del 1 %.
Analysis of GLOBAL STATUS and VARIABLES:Observaciones:
Los asuntos más importantes:
Algunas sugerencias de configuración para una mejor utilización de la memoria:
key_buffer_size = 20M innodb_buffer_pool_size = 8G table_open_cache = 300 innodb_open_files = 1000 query_cache_type = OFF query_cache_size = 0Algunas sugerencias de configuración por otras razones:
eq_range_index_dive_limit = 20 log_queries_not_using_indexes = OFFRecomiende usar el registro lento (con long_query_time = 1) para localizar las consultas traviesas. Entonces podemos discutir cómo mejorarlos. http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
Detalles y otras observaciones:
( (key_buffer_size - 1.2 * Key_blocks_used * 1024) ) = ((512M - 1.2 * 8 * 1024)) / 16384M = 3.1% -- Porcentaje de RAM desperdiciada en key_buffer. -- Disminuir key_buffer_size (ahora 536870912).
( Key_blocks_used * 1024 / key_buffer_size ) = 8 * 1024 / 512M = 0.00% -- Porcentaje de key_buffer utilizado. Alta marca de agua. -- Baje key_buffer_size (ahora 536870912) para evitar el uso innecesario de la memoria.
( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((512M / 0.20 + 128M / 0.70)) / 16384M = 16.7% -- La mayor parte de la RAM disponible debería estar disponible para el almacenamiento en caché. -- http://mysql.rjweb.org/doc.php/memory
( table_open_cache ) = 16,293 -- Número de descriptores de tabla para almacenar en caché -- Varios cientos suele ser bueno.
( innodb_buffer_pool_size ) = 128M -- InnoDB Data + Index cache -- 128M (un antiguo valor predeterminado) es lamentablemente pequeño.
( innodb_buffer_pool_size ) = 128 / 16384M = 0.78% -- % de RAM utilizada para InnoDB buffer_pool -- Establecido en aproximadamente el 70 % de la RAM disponible. (Muy bajo es menos eficiente; demasiado alto corre el riesgo de intercambiar).
( innodb_lru_scan_depth ) = 1,024 -- "InnoDB: page_cleaner: 1000ms el ciclo previsto tomó..." puede arreglarse bajando lru_scan_ depth
( innodb_io_capacity ) = 200 -- Al vaciar, use esta cantidad de IOP. -- Las lecturas pueden ser lentas o puntiagudas.
( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10 -- Capacidad: max/plain -- Recomendado 2. Max debe ser aproximadamente igual a los IOP que su subsistema de E/S puede manejar. (Si se desconoce el tipo de unidad, 2000/200 puede ser un par razonable).
( Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests ) = 3,565,561,783 / 162903322931 = 2.2% -- Solicitudes de lectura que tenían que llegar al disco -- Aumente innodb_buffer_pool_size (ahora 134217728) si tiene suficiente RAM.
( Innodb_pages_read / Innodb_buffer_pool_read_requests ) = 3,602,479,499 / 162903322931 = 2.2% -- Solicitudes de lectura que tenían que llegar al disco -- Aumente innodb_buffer_pool_size (ahora 134217728) si tiene suficiente RAM.
( Innodb_buffer_pool_reads ) = 3,565,561,783 / 279710 = 12747 /sec -- Falta el caché en buffer_pool. -- ¿Aumentar innodb_buffer_pool_size (ahora 134217728)? (~100 es el límite para HDD, ~1000 es el límite para SSD).
( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((3565561783 + 1105583) ) / 279710 = 12751 /sec -- InnoDB I/O -- ¿Aumentar innodb_buffer_pool_size (ahora 134217728)?
( Innodb_buffer_pool_read_ahead_evicted ) = 5,386,209 / 279710 = 19 /sec
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 1,564,913,152 / (279710 / 3600) / 2 / 48M = 0.2 -- Proporción -- (ver minutos)
( innodb_flush_method ) = innodb_flush_method = fsync -- Cómo InnoDB debería pedirle al sistema operativo que escriba bloques. Sugiera O_DIRECT o O_ALL_DIRECT (Percona) para evitar el doble almacenamiento en búfer. (Al menos para Unix). Consulte a chrischandler para obtener una advertencia sobre O_ALL_DIRECT
( default_tmp_storage_engine ) = default_tmp_storage_engine =
( innodb_flush_neighbors ) = 1 -- Una optimización menor al escribir bloques en el disco. -- Utilice 0 para unidades SSD; 1 para disco duro.
( ( Innodb_pages_read + Innodb_pages_written ) / Uptime / innodb_io_capacity ) = ( 3602479499 + 1115984 ) / 279710 / 200 = 6441.7% -- Si > 100 %, necesita más io_capacity. -- Aumente innodb_io_capacity (ahora 200) si las unidades pueden manejarlo.
( innodb_io_capacity ) = 200 -- Capacidad de operaciones de E / S por segundo en disco . 100 para unidades lentas; 200 para unidades giratorias; 1000-2000 para SSD; multiplique por el factor RAID.
( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Si se debe usar el hash adaptativo (AHI). -- ON para la mayoría de solo lectura; APAGADO para DDL-pesado
( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Normalmente debería estar ON. -- Hay casos en los que es mejor APAGADO. Ver también innodb_adaptive_hash_index_parts (ahora 8) (después de 5.7.9) e innodb_adaptive_hash_index_partitions (MariaDB y Percona). ON ha estado implicado en fallas raras (error 73890). 10.5.0 decidió desactivarse por defecto.
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Indica si se registran todos los Interbloqueos. -- Si está plagado de interbloqueos, enciéndalo. Precaución: si tiene muchos interbloqueos, esto puede escribir mucho en el disco.
( innodb_ft_result_cache_limit ) = 2,000,000,000 / 16384M = 11.6% -- Límite de bytes en el conjunto de resultados de TEXTO COMPLETO. (Posiblemente no preasignado, pero ¿crece?) -- Baje la configuración.
( local_infile ) = local_infile = ON -- local_infile (ahora ON) = ON es un posible problema de seguridad
( Qcache_lowmem_prunes ) = 6,329,393 / 279710 = 23 /sec -- Quedándose sin espacio en QC -- aumente query_cache_size (ahora 16777216)
( Qcache_lowmem_prunes/Qcache_inserts ) = 6,329,393/7792821 = 81.2% -- Tasa de eliminación (frecuencia de necesidad de podar debido a que no hay suficiente memoria)
( Qcache_hits / Qcache_inserts ) = 1,619,341 / 7792821 = 0.208 -- Proporción de aciertos e inserciones -- alta es buena -- Considere desactivar la caché de consultas.
( Qcache_hits / (Qcache_hits + Com_select) ) = 1,619,341 / (1619341 + 9691638) = 14.3% -- Proporción de aciertos -- SELECCIONES que usaron QC -- Considere desactivar la caché de consultas.
( Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached) ) = 1,619,341 / (1619341 + 7792821 + 278272) = 16.7% -- Tasa de aciertos de caché de consultas -- Probablemente sea mejor desactivar el control de calidad.
( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (16M - 1272984) / 3058 / 16384 = 0.309 0.309 -- query_alloc_block_size vs formula -- Ajustar query_alloc_block_size (ahora 16384)
( Created_tmp_disk_tables ) = 1,667,989 / 279710 = 6 /sec -- Frecuencia de creación de tablas "temporales" de disco como parte de SELECCIONES complejas -- aumente tmp_table_size (ahora 16777216) y max_heap_table_size (ahora 16777216). Consulte las reglas de las tablas temporales sobre cuándo se usa MEMORY en lugar de MyISAM. Tal vez los cambios menores en el esquema o la consulta puedan evitar MyISAM. Es más probable que ayuden mejores índices y la reformulación de las consultas.
( Created_tmp_disk_tables / Questions ) = 1,667,989 / 11788712 = 14.1% : porcentaje de consultas que necesitaban una tabla tmp en disco. -- Mejores índices / Sin blobs / etc.
( Created_tmp_disk_tables / Created_tmp_tables ) = 1,667,989 / 4165525 = 40.0% -- Porcentaje de tablas temporales que se derramaron en el disco -- Tal vez aumente tmp_table_size (ahora 16777216) y max_heap_table_size (ahora 16777216); mejorar índices; evitar manchas, etc.
( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 473 - 0 ) / ( 473 + 0 ) = 100.0% -- ¿Está cerrando sus declaraciones preparadas? -- Agregar cierres.
( Com_stmt_close / Com_stmt_prepare ) = 0 / 473 = 0 -- Las sentencias preparadas deben estar cerradas. -- Compruebe si todas las declaraciones preparadas están "cerradas".
( binlog_format ) = binlog_format = MIXED -- DECLARACIÓN/FILA/MIXTO. -- ROW es preferido por 5.7 (10.3)
( long_query_time ) = 5 -- Límite (segundos) para definir una consulta "lenta". -- Sugerir 2
( Subquery_cache_hit / ( Subquery_cache_hit + Subquery_cache_miss ) ) = 0 / ( 0 + 1800 ) = 0 -- Tasa de aciertos de caché de subconsulta
( log_queries_not_using_indexes ) = log_queries_not_using_indexes = ON -- Ya sea para incluirlos en slowlog. -- Esto abarrota el registro lento; apáguelo para que pueda ver las consultas realmente lentas. Y disminuya long_query_time (ahora 5) para captar las consultas más interesantes.
( back_log ) = 80 -- (Tamaño automático a partir de 5.6.6; basado en max_connections) -- Aumentar a min(150, max_connections (ahora 151)) puede ayudar cuando se realizan muchas conexiones.
Anormalmente pequeño:
Delete_scan = 0.039 /HR Handler_read_rnd_next / Handler_read_rnd = 2.06 Handler_write = 0.059 /sec Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ) = 97.9% Table_locks_immediate = 2.6 /HR eq_range_index_dive_limit = 0Anormalmente grande:
( Innodb_pages_read + Innodb_pages_written ) / Uptime = 12,883 Com_release_savepoint = 5.5 /HR Com_savepoint = 5.5 /HR Handler_icp_attempts = 110666 /sec Handler_icp_match = 110663 /sec Handler_read_key = 62677 /sec Handler_savepoint = 5.5 /HR Handler_tmp_update = 1026 /sec Handler_tmp_write = 40335 /sec Innodb_buffer_pool_read_ahead = 131 /sec Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 43524924.1% Innodb_data_read = 211015030 /sec Innodb_data_reads = 12879 /sec Innodb_pages_read = 12879 /sec Innodb_pages_read + Innodb_pages_written = 12883 /sec Select_full_range_join = 1.1 /sec Select_full_range_join / Com_select = 3.0% Tc_log_page_size = 4,096 innodb_open_files = 16,293 log_slow_rate_limit = 1,000 query_cache_limit = 3.36e+7 table_open_cache / max_connections = 107Cuerdas anormales:
Innodb_have_snappy = ON Slave_heartbeat_period = 0 Slave_received_heartbeats = 0 aria_recover_options = BACKUP,QUICK innodb_fast_shutdown = 1 log_output = FILE,TABLE log_slow_admin_statements = ON myisam_stats_method = NULLS_UNEQUAL old_alter_table = DEFAULT sql_slave_skip_counter = 0 time_zone = +05:30Tarifa por segundo = RPS
Sugerencias a tener en cuenta para el grupo de parámetros de su instancia de 'Copia de seguridad' de AWS
innodb_buffer_pool_size=10G # from 128M to reduce innodb_data_reads RPS of 16 innodb_change_buffer_max_size=50 # from 25 percent to speed up INSERT completion innodb_buffer_pool_instances=3 # from 1 to reduce mutex contention innodb_write_io_threads=16 # from 4 for your intense data INSERT operations innodb_buffer_pool_dump_pct=90 # from 25 percent to reduce WARM UP delays innodb_fast_shutdown=0 # from 1 to help avoid RECOVERY on instance STARTDebería encontrar que estos cambios reducen el tiempo de actualización de DATOS requerido en su instancia de BACKUP. Su instancia LIVE tiene diferentes características operativas, pero debe tener todas estas sugerencias aplicadas, así como otras. Consulte el perfil para obtener información de contacto y comuníquese para obtener asistencia adicional.
Su mención de REPLICACIÓN probablemente debería ignorarse ya que no puede tener SERVER_ID de 1 tanto en el MAESTRO como en el ESCLAVO con replicación para sobrevivir. Su servidor LIVE no puede ser MAESTRO porque LOG_BIN está APAGADO por la primera razón.
Observaciones EN VIVO, el recuento de com_begin fue 30, com_commit fue 0 después de más de 3 días de tiempo de actividad. Por lo general, encontramos que commit es lo mismo que com_begin. ¿Alguien se olvidó de COMMIT los datos?
com_savepoint reportó 430 operaciones. com_rollback_to_savepoint informó 430 operaciones. Normalmente no vemos una reversión para cada punto de guardado en 3 dqys.
com_stmt_prepare informó 473 operaciones. com_stmt_execute informó 1211 operaciones. com_stmt_close informó 0 operaciones. Olvidarse de CERRAR declaraciones preparadas cuando se hace deja recursos en uso que podrían haberse liberado.
handler_rollback counter 961 en 3 días. Parece inusual para 3 días de tiempo de actividad.
slow_queries contó 87338 en 3 días excediendo los 5 segundos para completarse. log_slow_verbosity=query_plan,explain ayudaría a su equipo a identificar la causa de la lentitud. Sus registros de consultas lentas ya están ACTIVADOS.
Kerry, LO MEJOR para ti y tu equipo.