Consejos para mejorar el rendimiento de tu bases de datos MySql
Estos consejos para mejorar el rendimiento de tu base de datos MySql te ayudarán a mejorar el rendimiento de tu web y por tanto la usabilidad de tu web. A día de hoy todo el mundo navega desde su smartphone, por lo que éste tema es de suma importancia.
En este artículo trataremos la importancia del rendimiento web, esta vez desde el lado del almacenamiento de los datos, lo que se conoce como sistema gestor de base de datos. Todo sistema necesita almacenar información, la forma en la que ésta se almacena es crucial para el rendimiento. A día de hoy una gran parte de las webs de internet usan MySql como sistema gestor de base de datos.
Sorpresa al final -->
¿Qué es MySql?
MySql es el sistema gestor de bases de datos más usado en internet. La mayoría de webs de internet usan este sistema, por ejemplo, cualquier web hecha con Wordpress usa MySql para funcionar. Es un sistema de bases de datos relacional y de código abierto.
¿Por qué la mayor parte de webs usan MySql?
Sobre todo por su compatibilidad con la mayoría de hosting de internet y sobre todo por su gran rendimiento. Aunque desde que aparecieron los dispositivos móviles vemos que el rendimiento web sigue siendo algo insuficiente para una óptima usabilidad web desde el smartphone. Y por tanto, tenemos que optimizar al máximo los recursos para aumentar la velocidad de navegación.
MySql y sus motores de almacenamiento
MySql a su vez contienen diferentes motores de almacenamiento. A continuación hablaremos sobre algunos problemas de rendimiento en MySql y consejos para mejorarlos, así como elegir el mejor motor de base de datos.
Posibles problemas de rendimiento de bases de datos MySql
Información extraída de PhpMyAdmin. Gestor web visual de Mysql.
| Problema | Recomendación |
|---|---|
| {long_query_time} está configurado a 10 segundos o más, por lo que sólo aquellas consultas que tomen más 10 segundos serán registradas. | Se sugiere configurar «long_query_time» a un valor menor dependiendo de su entorno. Usualmente, un valor entre 1 y 5 segundos es el sugerido. |
| El registro de consultas lentas está desactivado. | Active el registro de consultas lenta configurando «log_slow_queries» a 'ON'. Esto ayudará a analizar consultas con mala performance. |
| El caché de consultas no está habilitado. | Se sabe que el caché de consultas puede mejorar la performance enormemente si está correctamente configurado. Actívelo definiendo «query_cache_size» a un valor en MiB de 2 dígitos y definiendo «query_cache_type» a 'ON'. Notar que: si está utilizando memcached ignore esta recomendación. |
| Hay demasiadas filas siendo ordenadas. | Si bien no hay nada de malo en ordenar una gran cantidad de filas, es probable que desee asegurarse que las consultas que requieren gran cantidad de ordenación utilicen campos indexados en la cláusula «ORDER BY», lo que resultará en una ordenación más rápida. |
| Hay demasiadas uniones («JOIN») sin índices. | Esto significa que las uniones («JOIN») están realizando escrutinios completos sobre tablas. Agregar índices a los campos utilizados en las condiciones de la unión las acelerarán en gran medida. |
| La tasa de lectura del primer índice es alta. | Esto normalmente indica escruitinios completos de índices. Éstos son más rápidos que escrutinios de tablas pero requieren gran cantidad de clicos de CPU en tablas grandes. Si dichas tablas tienen o han tenido una gran cantidad de actualizaciones («UPDATE» o «DELETE»), ejecutar «OPTIMIZE TABLE» podría reducir dicha cantidad y/o acelerar los escrutinios completos de índices. De otra forma, la cantidad de escrutinios completos de índices sólo puede ser reducida re-escribiendo las consultas. |
| La tasa de lecutura de datos de una posición fija es alta. | Esto indica que muchas consultas necesitan ordenar resultados y/o realizar un escrutinio completo de tablas, incluyendo consultas con uniones («JOIN») que no utilizan índices. Agregue índices donde sea aplicable. |
| La tasa de lecutra de la siguiente fila de una tabla es alta. | Esto indica que muchas consultas están realizando escrutinios completos de tablas. Agregue índices donde sea aplicable. |
| Muchas tablas temporales están siendo escritas la disco en lugar de ser mantenidas en memoria. | Aumentar «max_heap_table_size» y «tmp_table_size» podría ayudar. Sin embargo, algunas tablas temporales son siempre escritas a disco independientemente del valor de estas variables. Para eliminarlas deberá re-escribir las consultas para evitar estas condiciones (en una tabla temporal: la presencia de una columna «BLOB» o «TEXT», o la presencia de una columna mayor a 512 bytes) como se menciona al comienzo del artículo de Pythian Group |
| El porcentaje del búfer de claves MyISAM (caché de índices) es bajo. | Podría necesitar aumentar el valor de «key_buffer_size», examine sus tablas nuevamente para ver si se han eliminado índices o sus consultas y las expectativas de uso de los índices. |
| La tasa de apertura de tablas es alta. | Abrir tablas necesita E/S en disco, lo cual es costoso. Aumentar «table_open_cache» podría evitarlo. |
| El caché de hilos está desactivado, esto resulta en mayor sobrecarga en las nuevas conexiones a MySQL. | Active el caché de hilos configurado «thread_cache_size» a un valor mayor a 0. |
| El tamaño del archivo de registro InnoDB no parece ser apropiado en relación a la reserva de búfers InnoDB. | Especialmente en un sistema con gran cantidad de escrituras a tablas InnoDB, debería de configurar «innodb_log_file_size» a aproximadamente 25% de innodb_buffer_pool_size. Sin embargo, mientras mayor sea este valor, mayor será el tiempo de recuperación luego de una caída de la base de datos, por lo que este valor no debería ser mucho mayor a 256 MiB. Note, además, que no puede simplemente cambiar el valor de esta variable. Necesitará cerrar el servidor, eliminar los archivos de registro InnoDB, definir el nuevo valor en el archivo my.cnf, iniciar el servidor y luego revisar los mensajes de error para asegurarse que no hubo inconvenientens. Revise también este artículo |
El motor de almacenamiento de nuestra base de datos es importante
Ahora hablaremos de los diferentes de motores de almacenamiento. Hay varios, pero sobretodo hablaremos de los dos más populares en MySql. Es muy importante elegir el motor de almacenamiento adecuado tanto de nuestra base de datos como en cada una de las tablas que necesitemos. Actualmente se suelen usar sobre todo MyISAM y InnoDB.
Imagen sacada de PhpMyAdmin.
Razones para usar MyISAM como motor de almacenamiento
Si nuestra versión de MySQL es previa a la 5.5, dado que entonces InnoDB no soportaba particionar tablas ni crear índices FULLTEXT.
La razón de peso más importante para usar MyISAM es que es más rápido el acceso para lectura y ligeramente más rápido insertar (en algunos casos).
Si se va a tener múltiples actualizaciones de datos o inserciones concurrentes, por ejemplo, un bloqueo a nivel de tabla es muy ineficiente, versus un update sobre una tabla InnoDB que utiliza bloqueo a nivel de fila, y por tanto permite operaciones concurrentes.
Razones para usar InnoDB como motor de almacenamiento
InnoDB permite el uso de llaves foráneas, muy ncesarias para mantener la integridad referencial, lo que ayuda muchísimo para evitar la redundancia y concordancia de los datos, en nuestro modelo entidad relación.
- Soporte de transacciones
- Cuenta con un gran rendimiento y fiabilidad.
- Permite hacer búsquedas full-text en versiones de MySql mayores a la 5.6.
- InnoDB permite el uso de llaves foráneas que son indispensables para mantener la integridad referencial de un modelo de datos, el soporte para transacciones.Bloqueo de registros
Permite tener las características ACID (Atomicity, Consistency, Isolation and Durability: Atomicidad, Consistencia, Aislamiento y Durabilidad en español).
InnoDB se recupera de errores o reinicios no esperados del sistema a partir de sus logs, mientras que MyISAM requiere una exploración, reparación y reconstrucción de índices de los datos de las tablas que aún no habían sido volcadas a disco.
InnoDB muestra un rendimiento mayor si nuestra aplicación necesita ejecutar INSERT y UPDATE en cualquier momento con respecto al motor de almacenamiento MyISAM.
Aquí viene la sorpresa que al principio del artículo te prometía. Un enlace que te llevará al famoso libro "SQL - Notes for Professionals" con más de 100 sugerencias y trucos profesionales para trabajar con MySql, no te lo pierdas:
https://goalkicker.com/SQLBook/