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;
mysql> USE frag_test;
mysql> CREATE TABLE frag_test (c1 VARCHAR(64)) ENGINE=MYISAM;
mysql> INSERT INTO frag_test VALUES ('fila 1');
mysql> INSERT INTO frag_test VALUES ('fila 2');
mysql> INSERT INTO frag_test VALUES ('fila 3');
mysql> 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

Tratamiento de los errores del módulo DBI de Perl

Cuando utilizamos el módulo DBI para conectarnos a una base de datos en Perl, existen algunos atributos que definen cómo tratar las alertas y errores que sucedan:

PrintWarn

Valor por defecto: Si los warnings de Perl están activados, es 1. De lo contrario, 0.

Controla la impresión de los warnings que determina el driver. Utiliza la función warn.

Como los drivers lo utilizan poco, considero que se puede dejar en el valor por defecto (sea cual fuere)

PrintError

Valor por defecto: 1

Además de devolver los errores de la forma normal, fuerza warnings. Utiliza la función warn.

Considero que es correcto generar los warnings adicionales, manteniendo el valor por defecto, para que se alerten todos los errores.

RaiseError

Valor por defecto: 0

En lugar de devolver los errores de la forma normal, fuerza una excepción. Utiliza la función die. Si está activado, por lo general se desactiva PrintError. Pero si ambos estuvieran activados, se procesa primero PrintError (warning) y luego RaiseError (excepción). En el momento de la conexión, curiosamente, se procesan al revés!

Considero que debe activarse cuando el uso de la base de datos sea esencial para la aplicación (lo más común), de lo contrario la ejecución continúa aún cuando el módulo DBI detecte un error.

 

Los valores por defecto de estos atributos se establecen cuando se realiza la conexión:

my $dbh = DBI->connect("<DBI:CONNECTION>", "<DB_USER>", "<DB_PASS>");

En este caso: PrintWarn depende de si los warnings están activados, PrintError será 1 y RaiseError tendrá el valor 0.

 

Los atributos se pueden redefinir en el momento de la conexión:

my $dbh = DBI->connect("<DBI:CONNECTION>", "<DB_USER>", "<DB_PASS>",{RaiseError => <BOOL>, PrintError => <BOOL>, PrintWarn => <BOOL>});

Ejemplo:

my $dbh = DBI->connect("dbi:mysql:test:localhost:3306", "test", "secure",{RaiseError => 1, PrintError => 0});

En este caso determinamos el forzar una excepción ante un error, desactivando el warning que su hubiera generado en su lugar.

 

También se pueden modificar en cualquier momento posterior para realizar alguna acción en particular:

Ejemplo:

$dbh->{RaiseError} = 0;

 

Se puede agregar código Perl para manejar los warnings y las excepciones generadas en cada caso.

 

Basado en:

http://search.cpan.org/~timb/DBI-1.631/DBI.pm

http://oreilly.com/catalog/perldbi/chapter/ch04.html

Usar GROUP_CONCAT ordenando los registros

La función GROUP_CONCAT es muy útil para juntar los valores de otros campos u expresiones habiendo realizado previamente un agrupamiento por ciertos campos. Entonces vamos a presentar un ejemplo que iremos refinando basado en ciertas necesidades:

Suponiendo que tenemos una tabla que contiene las  etiquetas de los usuario para que marquen sus mails, y deseamos obtener el listado de los nombres de etiquetas que cada uno posee:

SELECT usuario, GROUP_CONCAT( etiqueta ) as etiquetas
FROM etiquetas_usuario
GROUP BY usuario;

Obs: recuerden que implícitamente se está realizando un orden por GROUP BY.

Ahora necesitamos que si algún usuario hubiera definido más de una etiqueta con el mismo nombre, aparezca en el listado solo una vez:

SELECT usuario, GROUP_CONCAT( DISTINCT etiqueta ) as etiquetas
FROM etiquetas_usuario
GROUP BY usuario;

Para ser más prolijos vamos a ordenar los nombres de etiquetas alfabéticamente:

SELECT usuario, GROUP_CONCAT( DISTINCT etiqueta ORDER BY etiqueta ASC ) as etiquetas
FROM etiquetas_usuario
GROUP BY usuario;

Obs: podemos omitir ASC, dado que es el sentido de ordenamiento por defecto. Podemos utilizar en su lugar DESC, y hasta ordenar por otras columnas o expresiones!

Por último, vamos a separar con un guíon medio las distintas etiquetas en el listado  (dado que por defecto se utiliza una coma):

SELECT usuario, GROUP_CONCAT( DISTINCT etiqueta ORDER BY etiqueta ASC SEPARATOR ' - ' ) as etiquetas
FROM etiquetas_usuario
GROUP BY usuario;

Para tener en cuenta:

  • los valores NULL, no se concatenan
  • se puede omitir el separador indicando SEPARATOR ''
  • el resultado de la concatenación se trunca al valor máximo indicado por la variable group_concat_max (pueden consultar el valor de esta variable en su Mysql)

Inserciones concurrentes en tablas MyISAM de MySQL

Mysql soporta la inserción de registros en una tabla con engine MyISAM al mismo tiempo que se realizan consultas sobre la misma. Es decir, se pueden ejecutar sentencias SELECT mientras se están concretando sentencias INSERT. En un principio esto parece ser muy bueno pero tenemos que tener algunas consideraciones.

Existe una variable de configuración que controla este comportamiento: concurrent_insert. La misma puede tomar los siguientes valores a partir de Mysql 5.5.3 (entre paréntesis se indica el valor correspondiente en versiones previas):

  • NEVER (0): desactiva los INSERTs concurrentes
  • AUTO (1): activa los INSERTs concurrentes solamente para las tablas MyISAM no tengan huecos.
    • Cuando los huecos de una tabla se completan, se vuelven a activar las inserciones concurrentes en esa tabla.
    • Es el valor por defecto.
  • ALWAYS (2): activa los INSERTs concurrentes para TODAS las tablas MyISAM (aunque tengan huecos).
    • Si la tabla tiene huecos y está siendo utilizada por otro proceso (thread), las filas nuevas se agregan al final de la tabla.
    • De lo contrario, se lockea la tabla y se intenta llenar los huecos.

Ejemplo

mysql> SELECT @@GLOBAL.concurrent_insert;
AUTO

Además, los resultados de las inserciones podrían no ser visibles instantáneamente, dado que si existen muchas se encolan y se tratan secuencialmente, en simultaneidad con las consultas.

Basado en:

http://dev.mysql.com/doc/refman/5.5/en/concurrent-inserts.htm

Consideraciones para limitar los resultados de una consulta en MySQL

Les comento brevemente algunas consideraciones para el uso de LIMIT en un SELECT en Mysql. El formato es el siguiente:

... LIMIT <NRO-FILAS>|<POSICION>,<CANTIDAD>

Si se utiliza un único parámetro NRO-FILAS, entonces se obtiene esa cantidad de registros como máximo en el resultado.

Si se utiliza la forma con los 2 parámetros, POSICION se refiere a un offset en los resultados obtenidos y CANTIDAD al número de registros a considerar desde POSICION. Muy importante: la primer POSICION es 0!

Por compatibilidad con PostgreSQL, existe también la forma alternativa:

... LIMIT <CANTIDAD> OFFSET <POSICION>

Por lo general, cuando se limite la cantidad de resultados, los mismos se habrán solicitado en un orden, por lo que la cláusula ORDER BY se indica siempre antes que LIMIT.

Ejemplo:

En todas las sentencias indicadas a contiuación, se obtienen las 5 notas más recientes:

SELECT id, texto, fecha FROM notas ORDER BY fecha DESC LIMIT 5;

SELECT id, texto, fecha FROM notas ORDER BY fecha DESC LIMIT 0,5; #Empieza en 0!

SELECT id, texto, fecha FROM notas ORDER BY fecha DESC LIMIT 4 OFFSET 0; #Compatibilidad con PostgreSQL

Obtener detalles sobre un código de error de MySQL

Cuando ocurre un error de Mysql por lo general se indica el código numérico y su descripción. Cuando solamente indica el código, podemos obtener su descripción ingresando en la línea de comandos:

perror <CODIGO_ERROR>

Obs: Tengan en cuenta que en Windows para poder hacer esto, debemos agregar a la variable Path la ruta al directorio bin de Mysql.

Ejemplo:

Intentamos borrar una base de datos que no existe:

mysql> drop database `db_inexistente`;
ERROR 1008 (HY000): Can't drop database 'db_inexistente'; database doesn't exist

Vamos a la línea de comandos e ingresamos:

perror 1008
MySQL error code 1008 (ER_DB_DROP_EXISTS): Can't drop database '%-.192s'; database doesn't exist

También podemos probarlo con el error al exceder el max_allowed_packet.

 

Es importante saber que perror nos puede dar información sobre códigos de error del sistema operativo, además de aquellos particulares de Myqsl.

Ejemplo:

Si intentamos importar el dump de una base de datos grande, podríamos obtener el siguiente error:

ERROR 1030 (HY000): Got error 28 from storage engine

Entonces, podemos consultar qué significa el código de error 28 (para el sistema operativo) mediante:

perror 28
OS error code  28:  No space left on device

lo cual nos indica que nos hemos quedado sin espacio en disco.

 

Tip adicional: En http://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html pueden encontrar el listado de códigos de error de Myqsl 5.5