Tablas fragmentadas MyISAM en Mysql

Las tablas fragmentadas, si bien no ocasionan problemas graves, pueden disminuir la velocidad de la lectura de los datos.

A continuación haremos una serie de pruebas para ver cómo responden las tablas MyISAM a la fragmentación.

En las consultas mostraremos solamente las columnas del estado de la tabla que necesitamos, sabiendo que no podemos especificar columnas en el comando SHOW TABLE STATUS.

Crear base de datos, tabla y datos de prueba

mysql> CREATE DATABASE frag_test;
USE frag_test;
CREATE TABLE frag_test (c1 VARCHAR(64)) ENGINE=MYISAM;
INSERT INTO frag_test VALUES ('fila 1');
INSERT INTO frag_test VALUES ('fila 2');
INSERT INTO frag_test VALUES ('fila 3');
INSERT INTO frag_test VALUES ('fila 4');

Verificar estado de la tabla

mysql> SELECT table_name AS Name,Engine,Row_format,table_rows as Rows,Data_free FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE();

Name       Engine  Row_format    Rows  Data_free
---------  ------  ----------  ------  ---------
frag_test  MyISAM  Dynamic          4          0

Presten atención a la columna Data_free que indica 0 bytes.

Borrar un registro y verificar estado de la tabla

mysql> DELETE FROM frag_test WHERE c1 = 'fila 2';

mysql> SELECT table_name AS Name,Engine,Row_format,table_rows as Rows,Data_free FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE();

Name       Engine  Row_format    Rows  Data_free
---------  ------  ----------  ------  ---------
frag_test  MyISAM  Dynamic          3         20

Ahora vemos que hay 20 bytes asignados pero sin uso en la tabla.

Realizar la inserción de un registro, verificar estado de la tabla y los datos

mysql> INSERT INTO frag_test VALUES ('fila 5');

mysql> SELECT table_name AS Name,Engine,Row_format,table_rows as Rows,Data_free FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE();

Name       Engine  Row_format    Rows  Data_free
---------  ------  ----------  ------  ---------
frag_test  MyISAM  Dynamic          4          0

Nuevamente volvemos a tener 0 bytes libres. Solo se utiliza el espacio fragmentado (hueco) si el formato de fila es fixed o el tamaño del registro a ingresar es menor o igual al hueco existente.

Al consultar el contenido de la tabla:

mysql> SELECT c1 FROM frag_test;

c1
------
fila 1
fila 5
fila 3
fila 4

la fila 5 se agregó entre la 1 y la 3, donde estaba antes la 2 (que fue borrada).

 

Ahora vamos a ver cómo podemos desfragmentar una tabla.

Borrar un registro y verificar estado de la tabla

mysql> DELETE FROM frag_test WHERE c1 = 'fila 3';

mysql> SELECT table_name AS Name,Engine,Row_format,table_rows as Rows,Data_free FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE();

Name       Engine  Row_format    Rows  Data_free
---------  ------  ----------  ------  ---------
frag_test  MyISAM  Dynamic          3         20

Tal como sucedió cuando borramos la fila 2, vemos que hay 20 bytes asignados pero sin uso en la tabla.

Optimizar la tabla y verificar el estado

Este proceso realiza un bloqueo (lock) de la tabla y puede demorar mucho tiempo si tenemos mucha fragmentación y datos!

mysql> OPTIMIZE TABLE frag_test;

Table                Op        Msg_type  Msg_text
-------------------  --------  --------  --------
frag_test.frag_test  optimize  status    OK

Si lo intentamos de nuevo (lo mismo que hacer este proceso sobre una tabla ya optimizada que no se haya modificado) obtendremos:

Table                Op        Msg_type  Msg_text
-------------------  --------  --------  ---------------------------
frag_test.frag_test  optimize  status    Table is already up to date

mysql> SELECT table_name AS Name,Engine,Row_format,table_rows as Rows,Data_free FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE();

Name       Engine  Row_format    Rows  Data_free
---------  ------  ----------  ------  ---------
frag_test  MyISAM  Dynamic          3          0

Como podemos ver, se ha quitado el espacio sin uso de la tabla.

Con una versión de Mysql posterior a 5.0.2 podemos obtener todas las tablas fragmentadas mediante la siguiente consulta

mysql> SELECT table_schema, table_name, data_free, engine
FROM information_schema.tables WHERE table_schema
NOT IN ('information_schema', 'mysql') AND data_free > 0;

Basado en:

http://www.databasejournal.com/features/mysql/article.php/3927871/MySQL-Data-Fragmentation—What-When-and-How.htm

Tagged with: , , ,
Publicado en Aplicaciones