MariaDB / Eligiendo el formato del log binario

Bienvenidos sean a este post, hoy hablaremos sobre la importancia del formato de este log.

En posts anteriores hemos mencionado los formatos que dispone, tambien hemos mencionado algunas de las limitaciones de dichos formatos por eso es fundamental saber cual de los tres se ajusta mejor a nuestras necesidades, para establecer el formato se usa la variable binlog_format en el archivo de configuraciones o la opcion –binlog-format en el inicio, si no se especifica un valor el predeterminado es STATEMENT, formato basado en instrucciones, tambien podemos saber su formato actual viendo la variable @@binlog_format, por ultimo esta variable es dinamica por lo tanto podemos modificarla en el amo mientras esta funcionando, tambien es de tipo global o para la conexion actual, aunque modiicarla cuando esta activo no es recomendable porque puede hacer que la replicacion falle o tenga conductas inesperadas.

Formato basado en instrucciones

El formato de instrucciones (STATEMENT) es el mas antiguo formato de log binario, dado que este almacena todos los queries que modificaron los datos, algunas veces las instrucciones UPDATE, DELETE o REPLACE con el condicional WHERE no escriben ninguna modificacion pero estas igualmente seran almacenadas en el log, en cambio la instruccion SELECT nunca se almacena en este log porque no modifica ningun dato, aunque mencionamos este formato tiene una limitacion muy importante: solo se deben enviar instrucciones deterministas al servidor, pero que significa determinista?

Si se ejecutan dos veces en bases de datos idénticas, necesariamente deben tener los mismos efectos

El Tinchicus

Esto no aplica al SELECT porque ni siquiera se almacena en el log pero con instrucciones que almacenan el tiempo actual, NOW() por ejemplo, o un numero aleatorio hara que el valor almacenado en los esclavos sea diferente al amo, mariadb intentara detectar instrucciones o declaraciones inseguras y en caso de detectarlas producira un error semejante a este:

Note (Code 1592): Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.

Pero mariadb no chequea si los valores almacenados en variables que son deterministas, por lo tanto por medio de estas podemos ingresar valores no deterministas:

MariaDB [test]> SET @a = RAND();
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> CREATE TABLE example ENGINE = InnoDB SELECT @a;
Query OK, 1 row affected (0.50 sec)
Records: 1  Duplicates: 0  Warnings: 0

Nota: 
Esta es una buena practica para evitar inconvenientes con programas almacenados

Dado que el algoritmo encargada de determinar cuando las instrucciones son deterministas puede ser bastante pesimista haciendo que se genere errores aun con instrucciones que cumplen todas las condiciones, esto produce que devuelve mas seguido la nota de codigo 1592, lo cual puede hacer que nuestro log de errores crezca de manera exponencial, con este motivo solo puede hacernos migrar a un formato mixto pero no nos apresuremos, veamos algunas posibles declaraciones no deterministas:

• Funciones no deterministas, RAND() y NOW() pero tambien incluyen instrucciones como FOUND_ROWS(), ROW_COUNT(), y LOAD_FILE()
• Funciones definidas por el usuario, funciones escritas en C e instaladas en el servidor
• Referencias a las variables del servidor, dado que estos valores pueden diferir en los esclavos, hay algunas excepciones pero deberiamos tratar de usar nuestra logica para encontrarlos o simplemente evitar usarlos
• Una instruccion UPDATE con una opcion de LIMIT, aunque tenga la opcion de ORDER BY, este es un bug documentado
• Se modifica una columna AUTO_INCREMENT y la instruccion causa que se ejecute un trigger o llame una funcion almacenada
• Un valor AUTO_INCREMENT es automaticamente generado y no es la primera columna en la clave primaria (muy inusual pero seria poco eficiente)
• La instruccion LOAD DATA INFILE era considerada no segura en versiones anteriores a la 10.0
• El comando INSERT DELAYED en las tablas MyIsam, otros motores ignoran este comando
• Una tabla de sistema esta involucrada en la instruccion, esto es debido a que las tablas de sistema (mysql, information_schema, etc) almacenan informacion en base al servidor que estan usando y esta puede diferir una de la otra a pesar de usar las mismas instrucciones

Podemos agregar que las instrucciones READ COMMITTED y READ UNCOMMITTED pueden ser usada con el formato STATEMENT, en cambio no podemos usar REPETEABLE READ y SERIALIZABLE, la razon? Es simple, el orden de la ejecucion de las instrucciones en algunas ocasiones puede depender del tiempo de ejecucion de las mismas, y esto ocurre porque una instruccion que usa bloqueos puede demorar la ejecucion de otras instrucciones, aunque algunas instrucciones no deterministas son seguras y pueden usarse dado que el log binario contiene la informacion necesaria para poder replicarlo correctamente, veamos algunas de estas funciones:

• DATABASE(), SCHEMA()
• CONNECTION_ID()
• LAST_INSERT_ID()
• CURDATE(), CURRENT_DATE()
• CURTIME(), CURRENT_TIME()
• CURRENT_TIMESTAMP(), UNIX_TIMESTAMP(), NOW()
• UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP()
• LOCALTIME(), LOCALTIMESTAMP()

Si observan tambien incluimos a NOW() como segura, a pesar de haber estado usandola como ejemplo de no determinista, y esto es debido a que en realidad de todas las instrucciones de tiempo la unicamente realmente no segura es SYSDATE(), esto es debido a que devuelve el tiempo exacto de la llamada de la funcion y no el tiempo de ejecucion de la instruccion, provocando que multiples llamadas a esta funcion en el mismo query pueda devolver distintos valores, por ultimo observen que todas estas aclaraciones no aplican solamente a la replicacion sino al log binario en si mismo porque teniendo valores tan dispares tambien dificultaria la restauracion de un backup, pasemos al siguiente tema.

Formato basado en filas

Tal como hemos hecho con el formato de instrucciones tambien hemos mencionado en algunos posts anteriores que este formato almacene los cambios a los datos, aunque tenemos algunas excepciones dado que algunas operaciones son escritas al log en una forma diferente, veamos cuales son:

• Instrucciones DDL tales como CREATE TABLE o DROP TABLE
• Instrucciones que implicitan modifican la base mysql, tales como CREATE USER o GRANT pero no las que lo hacen explicitamente como INSERT o UPDATE
• Instrucciones que envuelven tablas temporales

Todas estas excepciones se almacenan como instrucciones, tal como si el formato STATEMENT estuviera en uso, por ejemplo si utilizaramos un CREATE con la opcion de SELECT para pasar la informacion, al momento de almacenarlo en el log la parte de la instruccion CREATE TABLE se almacena como una instruccion en el log pero la informacion obtenida por el SELECT se almacena como filas, su principal ventaja es que no tiene las limitaciones del otro formato, dado que ahora todas las instrucciones son seguras, porque se almacenan todas las modificaciones de los datos y esto hace que sea irrelevante si la instruccion que lo produce es determinista o no, tambien podemos usar cualquier nivel de aislamiento, incluyendo a READ COMMITTED and READ UNCOMMITTED, asi como los esclavos tampoco necesitan ejecutar instrucciones SQL y simplemente deben aplicar los cambios a las filas, dejando que todas las operaciones complejas se ejecuten en el amo.

Pero no todo es felicidad, la principal desventaja de este formato es que si la instruccion modifica muchas filas se va a necesitar escribir muchos mas datos en el log, lo cual puede ocasionar que se agrande en demasia el log, y si usamos replicacion el amo va a necesitar enviar mucha mas informacion a los esclavos pero los datos en este formato son mas compactos y estos al momento de ser escritos en una base es por medio de iNSERT o UPDATE, imaginemos que tenemos mucha informacion que ingresar por medio de INSERT, el formato por instrucciones analizado anteriormente podria resultar en archivo de log muchisimo mas grande, dado que no solo almacena los datos sino la instruccion tambien, y podemos agregar que el formato por instrucciones es muy poco eficiente cuando las cargas de trabajo consisten de muchas instrucciones que solo modifican una fila.

Formato mixto

Hasta aqui vimos que el formato basado en instrucciones es muy interesante pero tiene sus limitaciones, despues vimos que el formato de filas tiene menos limitaciones pero es menos eficiente para la mayoria de las cargas de trabajo, por estas razones y otras podemos considerar usar el formato mixto (MIXED) en la mayoria de los casos, este formato almacena las mayoria de las instrucciones en el archivo de log con el formato de archivo (STATEMENT) para las funciones deterministas, pero en el caso de detectar una funcion no determinista la almacena con el formato de fila (ROW), el almacenamiento de informacion en el log binario por medio de este formato es llamado inyeccion binaria (binary injection), al igual que sucede con el formato de instruccion los unicos niveles de aislacion que podemos usar son READ COMMITTED y READ UNCOMMITTED, lo cual comentamos cuando hablamos del primer formato.

Para ir finalizando vamos a hablar sobre el almacenamiento del log binario para programas almacenados, al igual que vimos con los formatos las rutinas almacenadas (procedimientos y funciones) se consideran seguros si estos son deterministas y no modifican ningun dato, por lo tanto si el log binario esta activo mariadb evita la creacion de funciones almacenadas si no son declaradas con la opcion NOT DETERMINISTIC, o una de las opciones READS SQL DATA, CONTAINS SQL, o NO SQL, aunque se puede forzar su creacion dado que es posible establecer a la variable @@log_bin_trust_function_creators sin embargo tales funciones no seran seguras con el formato de instrucciones, los triggers no pueden ser declarados como DETERMINISTIC o NOT DETERMINISTIC aunque si contiene instrucciones no deterministas pueden ser no seguros, y no se olviden que las instrucciones SQL se pueden comportar de diferentes formas dependiendo de los permisos, es decir que si nosotros estamos seguro que los programas almacenados siempre tienen los mismos permisos para ejecutar todas las operaciones que realiza en el amo estos no deberian ser almacenados en el log con el formato de instrucciones.

En resumen, hoy hemos visto los formatos del log binario, instrucciones, filas y mixtos, como son, como trabajan, como nos pueden beneficiar, algunas desventajas, porque existe el mixto y como almacenar los programas almacenados, espero les haya sido util sigueme en tumblr, Twitter o Facebook para recibir una notificacion cada vez que subo un nuevo post en este blog, nos vemos en el proximo post.