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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 dieciséis 17 18 19 |
mysql> SHOW ESCLAVO ESTADOG *************************** 1. póngase en fila *************************** … Archivo_de_registro_maestro: binlog.0000185 Read_Master_Log_Pos: 86698585 … Relé_maestro_archivo_de_registro: binlog.0000185 Slave_IO_Running: sí Esclavo_SQL_en ejecución: sí … Exec_Master_Log_Pos: 380 Relay_Log_Space: 85699128 … Maestro_UUID: 98974e7f–2fbc–18e9–72 discos compactos–07003817585c … Conjunto_Gtid_Recuperado: 98974e7f–2fbc–18e9–72 discos compactos–07003817585c: 1055–1057 Conjunto_Gtid_ejecutado: 7f42e2c5–3fbc–16e7–7fb8–05003715789a: 1–2, 98974e7f–2fbc–18e9–72 discos compactos–07003817585c: 1–1056 … |
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:
|
mysql> SHOW LISTA DE PROCESOS; + —- + —————– + —————– + —— + – ——– + —— + ———————————- + —————— + | Identificación | Usuario | Anfitrión | base de datos | Pedido | El clima | Expresar | Información | + —- + —————– + —————– + —— + – ——– + —— + ———————————- + —————— + … | 4 | sistema usuario | | NULO | Conectar | 268 | Leer evento sí Lu relé Iniciar sesión | NULO | … + —- + —————– + —————– + —— + – ——– + —— + ———————————- + —————— + |
¿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:
|
) puks ON tables.table_schema = puks.table_schema AND tables.table_name = puks.table_name DONDE puks.table_name ES NULO Y tables.table_schema NO EN (‘mysql’, ‘information_schema’, ‘performance_schema’, ‘sys’) Y tablas. table_type = «TABLA BASE» Y motor = «InnoDB»; SELECCIONEmesas .schema_table,mesas .nombre de la tabla,mesas .table_file SÍesquema_información. mesas UNIRSE A LA IZQUIERDA ( SELECCIONE esquema_tabla, nombre de la tabla SÍ esquema_información.estadísticas GRUPOS POR esquema_tabla, nombre de la tabla, nombre_índice AVVENÚSUMA ( CASO PARA EL MOMENTO no solo = 0 Y anulable ! = ‘SÍ’ Y qué 1 OTRO 0 TERMINAR ) =CONTAR
)pucchi SOBRE mesas .table_schema = puks.table_schemaY mesas .nombre de la tabla =puks.nombre_tabla INDUNI puks.nombre_tabla ESNULO Y mesas .table_schema NOEN( ‘mysql’, ‘información_esquema’, ‘esquema_rendimiento’, ‘sistema’) Y mesas .table_type = ‘TABLA BÁSICA’Ymotor= |
‘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.
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-algorithms
|
se puede cambiar dinámicamente usando:mysql> SET GLOBAL slave_rows_search_algorithms = «INDEX_SCAN, HASH_SCAN»; mysql > CONJUNTO GLOBAL Slave_rows_search_algorithms= |
‘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).
- 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_size
en 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_ - 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.
