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

Anuncios

Especificar columnas en el comando SHOW TABLE STATUS

El comando SHOW TABLE STATUS muestra para cada tabla de la base de datos en uso los siguientes datos:

Name, Engine, Version, Row_format, Rows, Avg_row_length, Data_length, Max_data_length, Index_length, Data_free, Auto_increment, Create_time, Update_time, Check_time, Collation, Checksum, Create_options, Comment

Podemos limitar mediante:

  • FROM <BASE_DE_DATOS>: la base de datos sobre la cual obtener la información
  • LIKE '<PATRON>‘: aquellas tablas que coincidan con el patrón (utilizando caracteres y comodines)

El problema es que, en realidad, no hay forma de poder especificar el subconjunto de campos que realmente necesitamos.

A partir de Mysql 5.0.2 tenemos las posibilidad de hacer algo similar a partir de la base de datos information_schema mediante la siguiente consulta:

mysql> SELECT Table_name, Engine, Version, Row_format, Table_rows, Avg_row_length, Data_length, Max_data_length, Index_length, Data_free, Auto_increment, Create_time, Update_time, Check_time, Table_collation, Checksum, Create_options, Table_comment
FROM information_schema.tables
WHERE table_schema = DATABASE();

Obs: La función DATABASE() retorna la base de datos actual (en uso). Si no hay ninguna base de datos seleccionada, devuelve NULL.

Más aún, esta forma es mucho más potente dado que permite aplicar y combinar ciertas restricciones mediante:

  • table_schema = <BASE_DE_DATOS>: la base de datos sobre la cual obtener la información
    • se pueden indicar varias uniendo esta sintaxis mediante un OR
    • es más podemos usar table_schema LIKE '<PATRON>' para considerar las bases que coincidan con el patrón (utilizando caracteres y comodines)
  • table_name = <TABLA>: la tabla sobre la cual obtener la información
    • se pueden indicar varias uniendo esta sintaxis mediante un OR
    • es más podemos usar table_name LIKE '<PATRON>' para considerar las tablas que coincidan con el patrón (utilizando caracteres y comodines)

De esta manera podemos especificar cada uno de los campos deseados (o incluso aplicar funciones sobre ellos), las bases de datos y las tablas a consultar.

Ejemplo:

mysql> SELECT table_name AS Name,Engine,Row_format,table_rows as Rows,Data_free FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'mysql' AND table_name LIKE 'help%';

Name           Engine  Row_format    Rows  Data_free
-------------  ------  ----------  ------  ---------
help_category  MyISAM  Dynamic         40          0
help_keyword   MyISAM  Fixed          485          0
help_relation  MyISAM  Fixed         1090          0
help_topic     MyISAM  Dynamic        534          0