Causas y soluciones para un rendimiento lento con eventos basados ​​en filas

Recientemente estuve trabajando en un problema con un cliente que describí como «rendimiento del esclavo demasiado lento». Durante un análisis rápido, descubrí que no se puede rastrear el subproceso de replicación del esclavo SQL mientras se procesan eventos basados ​​en filas del registro binario maestro.

Por ejemplo:

El estado de la lista de procesos para el subproceso SQL puede ser uno de los siguientes: Lectura del evento del registro del relé, o Bloqueo del sistema, o tal vez otro estado. En mi caso:

¿Que causas?

Echemos un vistazo a lo que podría causar tal comportamiento y a qué debemos prestar atención. Cuando el subproceso de SQL aplica el cambio de un evento basado en filas, debe ubicar la fila exacta que se actualizó. Con una clave principal, esto es trivial ya que una sola fila puede tener posiblemente el mismo valor que la clave principal.

Sin embargo, si no hay una clave principal en la tabla en el lado del esclavo de replicación, el subproceso de SQL debe buscar en toda la tabla para ubicar la fila para actualizar o eliminar. Repita la búsqueda para cada fila actualizada. Esta investigación requiere muchos recursos (el uso de la CPU puede ser de hasta el 100 %) y es lenta a medida que el esclavo se queda atrás.

Para las tablas de InnoDB, la clave «oculta» que se usa para los índices agrupados de las tablas sin una clave principal no se puede usar para evitar buscar filas en toda la tabla para actualizar o eliminar. Debemos tener en cuenta que la clave «oculta» es única solo para cada instancia de MySQL, por lo que el maestro de replicación y el esclavo de replicación generalmente no tienen los mismos valores que la clave «oculta» para la misma fila.

¿Qué podemos hacer para ayudar a resolver esto?

La mejor solución es asegurarse de que todas las tablas tengan una clave principal. Esto no solo garantiza que el subproceso SQL pueda ubicar fácilmente los archivos para actualizar o eliminar, sino que también se considera una práctica recomendada, ya que garantiza que todas las filas sean únicas.

Si no hay forma de agregar lógicamente una clave principal natural a la tabla, una posible solución es agregar una columna completa sin la firma de incremento automático como clave principal.

La siguiente pregunta lo ayuda a ubicar tablas sin una clave principal:

‘InnoDB’ ; Tenga en cuenta que para InnoDB, siempre debe ser único

NADA

clave para todas las tablas. Es necesario para el índice agrupado. Por lo tanto, agregar una columna «ficticia» explícita como se sugirió anteriormente no aumenta las necesidades generales de almacenamiento porque solo reemplaza la clave oculta.

No siempre es posible agregar inmediatamente una clave principal a la tabla si, por ejemplo, hay varias relaciones en el lado de la aplicación/heredado, falta de recursos, comportamiento desconocido de la aplicación después del cambio que necesita pruebas, etc.

En este caso, una solución a corto plazo es cambiar el algoritmo de búsqueda utilizado por el esclavo de replicación para ubicar filas modificadas por eventos basados ​​en filas.

El algoritmo de búsqueda se instala con la opción slave_rows_search_algorithms que está disponible en MySQL 5.6 y versiones posteriores. El valor predeterminado es usar un escaneo de índice si es posible, de lo contrario, un escaneo de tabla. https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html#option_mysqld_slave-rows-search-algorithmsSin embargo, para las tablas sin una clave principal, se usa un análisis de hash, lo que hace que el subproceso de SQL almacene en caché temporalmente los hash para reducir la sobrecarga de búsqueda de toda la tabla. El valor de Slave_rows_search_



‘INDEX_SCAN, HASH_SCAN’ ;

Solo tenga en cuenta INDEX_SCAN, HASH_SCAN

es el predeterminado en MySQL 8.0.Una cosa a tener en cuenta al usar escaneos hash es que los hash solo se reutilizan en un evento basado en filas. (Cada evento basado en filas puede tener cambios en varias filas de la misma tabla que se originan en la misma instrucción SQL).

  1. La opción binlog_row_event_max_size en los controles maestros de replicación t el tamaño máximo de un evento basado en filas. El tamaño máximo predeterminado del evento es de 8 kB. Esto significa que cambiar a escaneos hash solo mejorará el rendimiento del subproceso SQL cuando: Muchas filas caben en un evento basado en filas. Puede ayudar a aumentar el valor de binlog_row_event_max_sizeen el maestro de replicación, ya sea que esté actualizando o eliminando archivos grandes (por ejemplo, con datos de blob o de texto). Sólo puede configurar el binlog_row_event_max_
  2. cortar

en el archivo de configuración de MySQL, y restablecer este valor requiere un reinicio.

Una instrucción cambia varias filas.

https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#option_mysqld_binlog-row-event-max-size

Conclusiones

Aunque habilitar los escaneos hash mejorará el rendimiento lo suficiente como para que el esclavo de replicación lo mantenga, la solución permanente es agregar una clave primaria explícita a cada tabla. Esta debería ser la regla general en el diseño de esquemas para evitar y/o minimizar algunos problemas, como que el rendimiento del esclavo sea demasiado lento (como se describe en esta publicación). A continuación, voy a investigar cómo podemos averiguar el estado exacto del cable usando Performance Scheme para que la identificación del problema sea menos un juego de adivinanzas.

Author: Ing. Luis

A lo largo de conocer Windows y otros sistemas operativos me eh encontrado con diversos tipos de error, ahora brindo soluciones según mi experiencia-

Deja un comentario