Etiqueta: MySQL

Redimensionar innodb_log_file_size de MySQL

Tras realizar un análisis del estado del servidor de MySQL con MySQL Tunner, mostraba que deberíamos cambiar el valor del parámetro innodb_log_file_size.

Así que nos vamos al fichero de configuración (my.cnf) y, como no existe, añado innodb_log_file_size=16M. Reinicio el servicio de base de datos y… no arranca el servicio. En el log veo el motivo, sólo se permiten valores entre 0 y 5M.

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 1073741824 bytes!
InnoDB: Possible causes for this error:
 (a) Incorrect log file is used or log file size is changed
 (b) In case default size is used this log file is from 10.0
 (c) Log file is corrupted or there was not enough disk space
 In case (b) you need to set innodb_log_file_size = 48M

Tras realizar una búsqueda por Google, encuentro la solución en una entrada de Andy Hayes en DBA Diaries, donde se explica detalladamente que hay dos motivos por los que se bloquea esa modificación: por un lado el valor de innodb_fast_shutdown (no era nuestro caso) y por otro los errores de los ficheros de log.

1.- Cambiar el valor de innodb_fast_shutdown a 0 o 1
2.- Para el servidor de MySQL y revisar que no existan errores
3.- Mover los ficheros de log fuera del directorio de datos (ib_logfile0, ib_logfile1, etc)
4.- Cambiar el valor de innodb_log_file_size en my.cnf
5.- Iniciar el servicio de MySQL

HeiddiSQL 7.0

Ya está disponible la versión 7.0 de este magnífico gestor de bases de datos. Entre los cambios más notables destaca:

  • Soporte básico para Microsoft SQL Server
  • Botón para detener consultas largas
  • Gestión de usuarios: propuesta de password aleatorio y soporte SSL
  • Soporte para autenticación en MariaDB 5.2, con indicación con iconos de MariaDB y Percona Server
  • Correcciones varias en el uso con Wine
  • Recuperación el SQL original en VIEW editor

Puedes consultar el resto de cambios de la versión 7.0 aquí. Descargar HeiddiSQL 7.0

Tipos de índices en MySQL

Los índices son un grupo de datos vinculado a una o varias columnas que almacena una relación entre el contenido y la fila en la que se encuentra. Con esto se agilizan las búsquedas en una tabla al evitar que MySQL tenga que recorrer toda la tabla para obtener los datos solicitados.

Por tanto la creación de índices es de vital importancia para acelerar las consultas a base de datos, sobre todo cuando las tablas presentan un tamaño considerable. Pero los índices no son el remedio a todos los males, puesto que estos cambian cada vez que la columna asociada se modifica, no se deberían crear indices sobre columnas en las que son frecuentes las operaciones de escritura. También habrá que tener en cuenta que los índices ocupan espacio, en algunos caso más que la tabla a la que hacen referencia.

En MySQL hay cinco tipos de índices:

  • PRIMARY KEY: Este índice se ha creado para generar consultas especialmente rápidas, debe ser único y no se admite el almacenamiento de NULL.
  • KEY o INDEX: Son usados indistintamente por MySQL, permite crear indices sobre una columna, sobre varias columnas o sobre partes de una columna.
  • UNIQUE: Este tipo de índice no permite el almacenamiento de valores iguales.
  • FULLTEXT: Permiten realizar búsquedas de palabras. Sólo pueden usarse sobre columnas CHAR, VARCHAR o TEXT
  • SPATIAL: Este tipo de índices solo puede usarse sobre columnas de datos geométricos (spatial) y en el motor MyISAM

Funcionamiento del proceso de consulta en MySQL

Cuando una consulta, del tipo que sea (INSERT, SELECT, GRANT, …) es enviada a MySQL se ejecuta un procedimiento que consta de cuatro pasos: la caché de consultas, el parseado, la planificación y la ejecución.

      1.- La caché de consultas: Cuando se recibe una consulta de tipo SELECT se ejecuta un algoritmo de hash y comprueba si tiene alguna consulta idéntica almacenada, en caso afirmativo devolverá el resultado cacheado. La caché de consultas es muy útil en tablas que no cambian frecuentemente y donde se realizan muchas peticiones idénticas, suele ser el caso de páginas web dinámicas que muestran el resultado almacenado en base de datos. Se activa con la directiva query_cache_type en el fiche my.cnf (OFF o 0 desactiva la caché, ON o 1 activa excepto cuando se usa SQL_NO_CACHE y 2 solo guarda resultados en caché cuando se indica expresamente con SQL_CACHE).

      2.- Parseado: Si la consulta no se encuentra en caché se parsea, comprobando la sintaxis de la consulta y dividiéndola en sus elementos básicos. Se identifica el tipo de consulta, las tablas que están involucradas y se gestiona la cláusula WHERE para su ejecución.

      3.- Planificación: Una identificados todos los elementos que intervendrán en la consulta llega el momento de decidir como será ejecutada. Con EXPLAIN podemos ver el plan que seguirá MySQL para resolver una consulta, lo que nos ayudará a modificar las intenciones de resolución escogiendo la que más nos convenga.

      4.- Ejecución: Con todo decidido resta ejecutar la consulta y devolver el resultado.

Y hasta aquí todo el misterio que encierra la ejecución de una consulta sobre MySQL. El modo en el que ayudamos a que MySQL resuelva eficazmente las consultas realizadas determina en la mayoría de los casos el resultado final de rendimiento de una base de datos.

Habitualmente la mayor parte de las bases de datos están mal aprovechas, bien porque han crecido más de lo planteado o por un mal diseño del almacenamiento. Muchas veces se suele intentar solucionar a golpe de mejora de hardware, cuando en realidad una buena optimización ayudando a MySQL a ejecutar los procesos de consulta suele repercutir en una mejora mucho mayor que ofrecerle un hardware más potente.

MySQL: Mejorar las consultas a la base de datos

Como segunda parte de la optimización de MySQL podemos mejorar las consultas que realicemos sobre la base de datos. A pesar de ser una tarea tediosa, de vez en cuando es conveniente realizar una revisión de las consultas, comprobando que siguen siendo igual de eficaces que cuando las planteamos.

  • Como primera recomendación al hacer una consulta a una tabla es no usar «*» en el SELECT. ¿Si no se van a usar todos y cada uno de los campos definidos en esa tabla que sentido tiene que usemos toda la información? Ninguno, pues eso, definir la consulta con SELECT campo1, campo2, … FROM dará mejor resultado
  • Probar la consulta que vamos a implementar con EXPLAIN y analizar el resultado. Intentaremos que las consultas que realicemos no contengan ALL en la columna type, puesto que en ese caso se hará un escaneo completo de la tabla.
  • Los campos utilizados con la cláusula WHERE son buenos candidatos a ser índices, comprueba con EXPLAIN como los usaría MySQL para determinar si está aconsejado su uso.
  • Evitar el uso innecesario de LIKE y RLIKE, pues realizar comparaciones extensas llevará mucho más tiempo. Si se usan tablas MyISAM pueden utilizarse índices FULLTEXT sobre los campos de texto donde se realice la comparación.
  • Al insertar registros, resulta más eficiente la escritura de varias sentencias la mismo tiempo que varias por sepado. Con INSERT DELAYED puedes generar una cola que esperará hasta que la tabla no esté siendo utlizada (No puede usarse con todos los motores). Utilizando INSERT LOW_PRIORITY darás prioridad a las sentencias de escritura y con SELECT HIGH_PRIORITY darás prioridad a las sentencias de consulta.
  • Cuando la cantidad de resultados a devolver por MySQL no va a ser utilizada en su conjunto, como cuando se usa un paginador, devolver la cantidad apropiada utilizando la cláusula LIMIT.

Ante todo hay que mantener una premisa básica: no guardar datos innecesarios y no extraer información que no se va a utilizar.

Optimización de bases de datos MySQL

Mantener una base de datos optimizada es una de las claves para lograr una mejora importante en el rendimiento, tanto a nivel de base de datos como una mejora significativa en el rendimiento de las aplicaciones que hacen uso de ellas. Aunque voy a enfocarlo en MySQL, podría aplicarse a la mayoría de gestores de base de datos.

Podríamos centrarnos en tres escenarios a la hora de acometer una optimización de MySQL: la optimización de la base de datos, la mejora de las consultas que se realizan sobre ella y por último, pero no menos importante, el pulido de los programas que trabajan con los datos. Hoy vamos a centrarnos en la primera parte.

Optimizar la base de datos

  • El primer punto a tener en cuenta será el diseño de la base de datos en el momento de su creación. No hay mejor remedio que hacer bien las cosas desde un principio. Habrá que pararse a calcular la carga que va a soportar MySQL, tanto a nivel de accesos como la cantidad de datos de habrá que almacenar. También es importante pensar como se extraerán los datos a la hora de hacer las tablas y que relaciones mantendrán esos datos con otras tablas y campos.
  • Para el uso de cada tipo de campo sobre nuestras tablas debemos buscar siempre los más adecuados al tipo de dato que tendremos que almacenar. Un campo incorrecto forzará a trabajar más de lo necesario a nuestro motor de base de datos. Debemos evitar el uso de campos de longitud variable si no los necesitamos, intentaremos usar campos numéricos en lugar de campos de texto y evitaremos que los campos puedan contener NULL (ralentizará las consultas innecesariamente).
  • Una vez tenemos el modelo de nuestra base de datos, debemos pensar en como se consultarán los datos para generar los índices. Cuando mejores sean los indices que tengamos, con mayor rapidez realizará MySQL las consultas solicitadas. Para ello podemos hacer uso de EXPLAIN para que nos muestre como se harán las consultas y que índices utilizará MySQL. Como orientación será bueno tener índices sobre aquellos campos que sean utilizados en las clausulas WHERE o JOIN, y no crees índices innecesarios, se actualizarán con cada modificación haciendo un trabajo innecesario.
  • Otro punto importante y en el que la mayoría no se fijan, piensa si no será mejor que la base de datos viva por su cuenta y que las aplicaciones accedan a los datos a través de procedimientos almacenados y funciones. De este modo será posible realizar modificaciones no previstas sin tener que tocar nada en las aplicaciones que acceden a los datos.
  • Hasta aquí sobre la creación y diseño, ahora viene el apartado de mantenimiento de los datos. Primer consejo y el más importante: mantener la base de datos con la información necesaria. Para ello cada cierto tiempo hay que realizar limpieza y eliminar o archivar aquellos datos que ya no sean necesarios. Por otra parte, cuando se han realizado muchos cambios debemos volver a reorganizar la información, para ello ejecutamos OPTIMIZE TABLE, que se encargará de reparar y optimizar los datos e índices de las tablas.
  • Puede que en determinado momento se produzca una inserción masiva de datos por alguna circunstancia imprevista. Llegados a este punto, lo mejor es volver a analizar si el diseño de la base de datos se sigue correspondiendo con el objetivo definido durante su diseño inicial. Suele ser una de las cosas que más pereza da realizar, sobre todo porque tendrá incidencia sobre las aplicaciones que ya utilizan la base de datos, pero que mejor resultado puede dar cuando hablamos de optimización. En este apartado es cuando vienen muy bien tener procedimientos almacenados y funciones.

Mejorar las consultas a la base de datos

Liberada HeidiSQL 6.0

HeidiSQL es una interfaz para la administración de MySQL que permite gestionar las bases de datos a través de una intuitiva interfaz para Windows, disponible para 2000, XP, Vista y 7, aunque también se puede instalar en Linux con Wine.

Esta nueva versión de HeidiSQL [descarga] es el resultado de 331 cambios realizados sobre la versión 5.1 y que incluye algunas nuevas funcionalidades como:

  • Resultados por lotes en una misma ficha de consulta
  • Múltiples conexiones en una sola instancia de la aplicación
  • Resultados de la consulta editables
  • Administración de usuarios utilizando la sintaxis GRANT y REVOKE
  • Ejecución de la consulta actual con [Mayús + Ctrl + F9]
  • Vista previa de imágenes incluidas en campos BLOB

HeidiSQL es una buena alternativa a MySQL Workbench para la gestión de bases de datos, además incluye una versión «portable» para poder llevarlo siempre cargado en un USB y tenerlo disponible en cualquier momento, nunca se sabe cuando se va a necesitar acceder a una base de datos.