MariaDB / Como crear tablas comprimidas de InnoDB

Bienvenidos sean a este post, hoy veremos como es crear una tabla de esta forma.

Antes de crear una tabla de esta forma debemos verificar que el modo estricto este activado, la razon porque se hace esto es debido a que InnoDB ejecuta mas chequeos al ejecutar CHECK TABLE cuando el modo esta activo, si algo va al momento de crearlo, la tabla no se creara y un error sera devuelto en su lugar, lo cual es una buena ayuda para evitar que las tablas sean diferentes a las que esperamos, aunque tenemos un inconveniente y este es que las aplicaciones se basan en las ejecuciones de sql y el modo estricto puede romperlas, por esta razon el modo estricto se desactiva de manera predeterminada y se puede activar durante la sesion, para ello ejecutemos el siguiente comando:

MariaDB [(none)]> set @@global.innodb_strict_mode = ON;

Nota: 
En versiones mas modernas viene activo de manera predeterminada pero no esta mas de activarlo o desactivarlo en caso de ser necesario como comentamos antes.

Una vez habilitado el modo estricto, la creacion de tablas es muy similar a como lo hicimos hasta ahora pero debemos especificar el formato de las filas, veamos un ejemplo:

MariaDB [base3]> create table tabla_comp(
    -> id INT UNSIGNED NOT NULL PRIMARY KEY,
    -> c1 VARCHAR(255),
    -> c2 VARCHAR(255)
    -> ) engine = innodb,
    -> row_format = compressed;

Pero no solamente podemos hacerlo de esta forma, dado que para las tablas comprimidas se puede configurar el tamaño de los bloques de indices (paginas), para esto podemos utilizar la opcion KEY_BLOCK_SIZE, como esta opcion es para las tablas comprimidas el servidor entiende que es de este tipo y hace innecesario el uso de row_format:

MariaDB [base3]> create table tabla_comp(
    -> id INT UNSIGNED NOT NULL PRIMARY KEY,
    -> c1 VARCHAR(255),
    -> c2 VARCHAR(255)
    -> ) engine = innodb,
    -> key_block_size = 8;

Si bien el tamaño de los bloques de indices no afecta el nivel de compresion, la cual no puede ser establecida por tabla, sin embargo cambiando el tamaño de la pagina determinamos cuantas filas entran por pagina, los tamaños permitidos son:

• 16
• 8 (predeterminado)
• 4
• 2
• 1
• 0

Todos los valores son en kb (KiloBytes), el valor de 0 especifica el valor predeterminado pero tambien desactiva la comprension de tablas, usualmente KEY_BLOCK_SIZE es mas chico que innodb_page_size (cuyo valor predeterminado es 16), un valor de 16 permite almacenar muchas filas pero siempre tengan en mente que las columnas BLOB y TEXT se almacenan por afuera (como hemos mencionado en posts anteriores), pero en ocasiones el valor que asignamos a KEY_BLOCK_SIZE no es adecuado para los indices por lo tanto el modo estricto es indispensable porque fuerza a mariadb a producir un error, en caso de no estar activado se creara la tabla pero el error nos sucedera cuando intentemos insertar o modificar una linea.

Una buena practica para determinar el mejor valor para KEY_BLOCK_SIZE es crear varias copias de la tabla, cada una con un tamaño diferente y probar su performance en una carga de trabajo realista, esto puede monitorearse haciendo queries en la base information_schema, tambien es posible cambiar el tamaño de KEY_BLOCK_SIZE de una tabla comprimida mediante el siguiente comando:

MariaDB [base3]> alter table tabla_comp key_block_size = 16;

Aunque tambien podemos hacerlo en tablas no comprimidas, por ejemplo vamos a ver las caracteristicas de una tabla mediante el siguiente comando:

MariaDB [base3]> select * from information_schema.INNODB_SYS_TABLES\G

Les muestro los datos que tenemos actualmente sobre la tabla que voy a trabajar:

*************************** 16. row ***************************
     TABLE_ID: 43
         NAME: base3/t3
         FLAG: 33
       N_COLS: 4
        SPACE: 27
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single

Observen que la tabla es de tipo Dynamic, nuestro siguiente paso sera modificar a KEY_BLOCK_SIZE tal como hicimos para la tabla comprimida:

MariaDB [base3]> alter table t3 key_block_size = 8;

Si todo sale bien no deberia devolver ningun error ni aviso y si volvemos a hacer la verificacion veremos la siguiente modificacion:

*************************** 16. row ***************************
     TABLE_ID: 46
         NAME: base3/t3
         FLAG: 41
       N_COLS: 4
        SPACE: 30
   ROW_FORMAT: Compressed
ZIP_PAGE_SIZE: 8192
   SPACE_TYPE: Single

Observen como ahora paso de Dynamic a Compressed dado que mariadb al hacer esa modificacion considera que se debe comprimir, tal como hablamos al comienzo del post, asi que tambien podemos comprimir alguna tabla vieja de nuestra base en caso de ser necesario.

En resumen, hoy hemos visto como crear tablas comprimidas, los requerimientos que debemos tener en cuenta antes de hacerlas, como se puede modificar despues de creada y como convertir una tabla existente en comprimida, espero les haya sido de utilidad 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.