Laravel - Actualización demasiado lenta (SQLSRV - MYSQL)

Question

Tengo una función en el controlador que selecciona toda la tabla Clientes de una base de datos SQL Server (remote, con menos de 1 ms de conexión), y actualiza la tabla Clientes en mi MySQL DB, hay aprox. 15000 filas. Cuando estaba en desarrollo en mi computadora (Windows 10), solía tomar 2426 segundos para completar la actualización. Ahora en el servidor de producción, se tarda entre 8 y 10 minutos. Probé la conexión entre mi servidor y el SQL Server y es muy rápido, así que el problema no es el controlador SQL sino MySQL. Servidor: Ubuntu 18.04 LTS, uso de CPU2.13% de 4 CPU(s), Uso de memoria 79.13% (6.26 GiB de 7.91 GiB) MySQL Ver 5.7.32 Mytop: 50 qps (ahora) al ejecutar la fucción, Slow qps: 0.0, Eficiencia clave 50% (fue el 97% antes de reiniciar el servicio MYSQL) Código ClienteAsesorSql::where('Concepto', 'NOT LIKE', '%RESERVADO%') ->chunk(500, function ($clientes_asesor) { foreach ($clientes_asesor as $clientes) { $cliente_opyme = Cliente::updateOrCreate(['cli_id' => $clientes->ClienteId],[ 'cli_id' => $clientes->ClienteId, 'cli_nombre' => $clientes->Concepto, 'cli_email' => $clientes['e-mail'], 'cli_telefono' => $clientes->Telefono, 'cli_movil' => $clientes->SMS, 'cli_domicilio' => $clientes->DomicilioNoFiscal, 'created_at' => $clientes->FechaEstado ]); $cliente_opyme ->save(); } });

Gpt · Accepted Answer

Para optimizar el proceso de actualización de los registros en MySQL, te recomendaría considerar las siguientes sugerencias:

Utilizar transacciones: Envuelve tu código dentro de una transacción para mejorar el rendimiento en la actualización de múltiples filas.

php
DB::beginTransaction();
try {
    ClienteAsesorSql::where('Concepto', 'NOT LIKE', '%RESERVADO%')->chunk(500, function ($clientes_asesor) {
        foreach ($clientes_asesor as $clientes) {
            $cliente_opyme = Cliente::updateOrCreate(['cli_id' => $clientes->ClienteId],[
                'cli_id' => $clientes->ClienteId,
                'cli_nombre' => $clientes->Concepto,
                'cli_email' => $clientes['e-mail'],
                'cli_telefono' => $clientes->Telefono,
                'cli_movil' => $clientes->SMS,
                'cli_domicilio' => $clientes->DomicilioNoFiscal,
                'created_at' => $clientes->FechaEstado
            ]);
            $cliente_opyme->save();
        }
    });
    DB::commit();
} catch (Exception $e) {
    DB::rollBack();
    // Manejo de error
}

Optimizar las consultas: Verifica que tus consultas sean eficientes, considera agregar índices a las columnas que utilizas en las condiciones de búsqueda.

Batch updates: En lugar de actualizar filas una por una, considera agrupar las actualizaciones en lotes para reducir la cantidad de consultas a la base de datos.

Ejecución en segundo plano: Si la actualización de los registros no necesita ser inmediata, podrías considerar ejecutar esta tarea en segundo plano a través de colas (por ejemplo, usando Laravel Queue).

Revisar configuración de MySQL: Puedes revisar la configuración de tu servidor MySQL, como el tamaño del buffer de consultas, el límite de conexiones, entre otros, para optimizar el rendimiento.

Implementando estas sugerencias, deberías poder mejorar el rendimiento de tu actualización de registros en MySQL.

Respondida el Dec 18, 2020 a las 14:57 - por Gpt

Gemini · Answer

Existen varias causas potenciales para el rendimiento lento que estás experimentando al actualizar una tabla MySQL desde una fuente de datos SQL Server remota:

Configuración de MySQL: Verifica la configuración de MySQL para optimizar el rendimiento, como aumentar el tamaño del búfer de consultas, habilitar la indexación y optimizar la configuración de memoria. Consultas lentas: Revisa las consultas que se ejecutan durante la actualización. Identifica y optimiza cualquier consulta lenta utilizando EXPLAIN o un analizador de consultas. Índices faltantes: Asegúrate de que las tablas involucradas en la actualización tengan índices adecuados para acelerar las búsquedas. Conexión de red: Verifica la conexión de red entre el servidor MySQL y la fuente de datos SQL Server remota. Cualquier latencia o pérdida de paquetes puede afectar el rendimiento. Fragmentación de la tabla: Si la tabla MySQL donde se están actualizando los datos está fragmentada, esto puede provocar un rendimiento lento. Realiza una operación OPTIMIZE TABLE para desfragmentar la tabla. Bloqueos: Identifica cualquier bloqueo que pueda estar ocurriendo durante la actualización utilizando herramientas como MySQLtuner o pt-query-digest. Recursos del sistema: Supervisa los recursos del sistema, como la CPU, la memoria y el espacio en disco, para asegurarte de que el servidor MySQL tenga suficientes recursos para manejar la carga de trabajo. Versión de MySQL: Considera actualizar a una versión más reciente de MySQL que pueda tener mejoras de rendimiento o características de optimización.

Además de estos factores, aquí hay algunas recomendaciones específicas basadas en el código proporcionado:

Tamaño de lote: Ajusta el tamaño del lote utilizado en el método chunk() para encontrar un equilibrio entre el rendimiento y el uso de memoria. Paralelismo: Explora el uso de múltiples procesos o subprocesos para ejecutar la actualización en paralelo, lo que puede mejorar el rendimiento. Almacenamiento en caché:* Almacena en caché los resultados de la consulta para las filas de SQL Server que se utilizan en la actualización. Esto puede reducir el tiempo de acceso a los datos.

Sigue estos pasos para identificar y resolver el problema de rendimiento:

Revisa la configuración de MySQL y optimízala para el rendimiento.

Analiza las consultas y optimízalas para reducir el tiempo de ejecución.

Verifica los índices y crea índices adicionales si es necesario.

Comprueba la conexión de red y resuelve cualquier problema de latencia o pérdida de paquetes.

Desfragmenta las tablas si es necesario.

Supervisa los bloqueos y resuélvelos si es necesario.

Actualiza a una versión más reciente de MySQL si es posible.

Ajusta el tamaño del lote y considera el paralelismo.

Implementa el almacenamiento en caché de los resultados de la consulta.

Una vez que hayas implementado estas medidas, vuelve a ejecutar la función de actualización y monitorea el rendimiento para verificar si hay mejoras.

Respondida el Dec 18, 2020 a las 15:10 - por Gemini

Laravel - Actualización demasiado lenta (SQLSRV - MYSQL)

2 Respuestas: