Tareas típicas para el mantenimiento de MySQL

ByLuis

Tareas que se deberían ejecutar para el mantenimiento de la base de datos de MySQL:

  • Backup: hay que realizarlo de los ficheros de datos, ficheros de logs de transacciones, ficheros de configuración y logs binarios.
  • Limpiar los logs binarios: Se puede hacer mediante my.conf usando expire_logs_days o mediante la sentencia PURGE MASTER LOGS. Eso sí, no purgar los logs que necesiten los esclavos, ni borrarlos mediante un comando de un fichero (rm bin-log.*).
  • Optimizar las tablas: después de inserts y updates las tablas se llenan de espacio inútil, por lo que es necesario optimizar las tablas usando OPTIMIZE TABLE.
  • Vaciar la caché: cuando se ejecutan selects con tamaños diferentes de resultados, el cache se defragmenta, lo cual hace necesario que se vacie la cache (FLUSH QUERY CACHE).
  • Rotar los logs binarios: los logs binarios solo se rotan por tamaño, si quieres hacerlo en un determinado momento se debería usar la sentencia FLUSH LOGS.

Typical automated MySQL maintenance jobs

Similar Posts

MySQL Proxy: para optimizar tu base de datos

ByLuis

mysqlproxy.pngMySQL Proxy es una aplicación, aún en fase inicial, que se sitúa entre tu aplicación y MySQL. Permite monitorear, analizar y transformar las comunicaciones. Su flexibilidad permite un número ilimitado de usuarios, a parte de incluir balanceo de carga, failover, análisis de querys, filtrado y modificación de querys y mucho más.
Disponible para Linux, Mac OSX, FreeBSD, IBM AIX y Sun Solaris, mientras que en Windows aún no está disponible por haber problemas con la librería libevent, pero estará disponible en breve.
MySQL Proxy

Nueva version de SQLYog

ByLuis

Cuando SQLyog pasó de ser un producto únicamente comercial, a disponer de una versión gratuita, anunciamos esta gran noticia, y ahora un año después comentamos la salida de una nueva versión, la 6.12.
sqlyog.png
Desde entonces, esta aplicación es la que he estado usando para trabajar y he de decir que es la que más me ha gustado. Y a partir de la versión 6, mucho más, ya que uno de los inconvenientes que tenía era el uso de Unicode, no permitía escribir o mostrar texto en otros idiomas como el chino, japonés o árabe, por lo que aparecían las famosas interrogaciones cuando encontraba caracteres no reconocidos.
Además, ahora trata mucho mejor el encode y el collation, pudiendo elegirlos fácilmente en la creación de la base de datos y la creación / edición de tablas.
Un gran producto a tener en cuenta.
SQLYog 6.12

Flexviews: vistas materializadas en MySQL

ByLuis

Las vistas materializadas son aquellas vistas que se almacenan en una tabla de la base de datos. MySQL no provee de estas vistas, pero Flexviews lo simula mediante procedimientos almacenados que se ejecutan mediante triggers.
Flexviews

Consejos para optimizar MySQL

ByLuis

Buenos consejos para optimizar MySQL, ordenados por distintas categorías.

Queries

  • Usa EXPLAIN para comprender la ejecución de la query
  • Usa Slow Query Log
  • No uses DISNTINCT cuando usas o puedes usar GROUP BY
  • Crea INSERT o REPLACE en procesos batch y usa LOAD DATA antes que INSERT
  • LIMIT m,n no es tan rápido como parece
  • No uses ORDER BY RAND() si tienes más de dos mil registros
  • Usa SQL_NO_CACHE cuando selecciones datos que se actualizan con frecuencia o son abundantes
  • Evita usar caracteres especiales en los LIKE
  • Evita usar subconsultas correlacionadas en el SELECT y el WHERE
  • No hagas comparaciones calculadas
  • ORDER BY y LIMIT funcionan mejor con
  • No pongas text/blobs en los resultados si no vas a necesitarlos
  • Subqueries en el FROM pueden ser útiles para recuperar BLOBs sin necesidad de ordenarlos. Si haces un join de una tabla consigo misma, puede ser bueno si en la primera obtienes el indice y en segunda obtienes el resto de los datos.
  • ALTER TABLE … ORDER BY puede tomar los datos ordenados cronologicamente y reordenarlos mediante un campo diferente, esto podría hacer que las consultas vayan más rapidas por ese campo, aunque quizás fuera necesario que estuviera en el índice.
  • Es bueno saber cuando dividir una query compleja en la unión de otras más pequeñas
  • Elimina pequeñas cuentas si puedes
  • Haz queries similares para que el cache las use
  • Usa estándares
  • No uses características deprecated
  • Cambia a OR en índices de múltiples campos para que la UNION mejore la velocidad (> mysql 5.0)
  • No uses COUNT (*) en tablas Innodb para cada búsqueda. Para ello deberías usar SQL_CALC_FOUND_ROWS y SELECT FOUND_ROWS()
  • Usa INSERT … ON DUPLICATE KEY update (INSERT IGNORE) para evitar el SELECT

Escalado

  • Usa benchmarking
  • Aisla las cargas de trabajo, no dejes que las tareas administrativas interfieran en el rendimiento (por ejemplo, backups)
  • Testea en vez de hacer debug
  • A la vez que los datos crecen los índices cambian. Haz tu esquema tan modular como tu código.

Rendimiento de red

  • Minimiza el tráfico recuperando solo lo que necesitas.
  • Accede directamente a la paginación con LIMIT no recuperando los anteriores.
  • No uses SELECT *
  • No uses muchas queries rápidas y una algo más compleja puede ser más eficiente.

Mejoras en el sistema operativo

  • Usa particiones en los datos
  • Manten el servidor tan limpio como sea posible
  • Crea un entorno de test

Servidor MySQL

  • innodb_flush_commit=0 puede ayidar en el retraso del slave
  • Optimiza los tipos de datos, se consistente. Usa PROCEDURE ANALYSE() para ayudarte a determinar el tamaño de los datos que necesitas.
  • Si puedes comprime text y blobs
  • Comprime datos estáticos
  • No hagas backup de datos estáticos con frecuencia
  • Activa e incrementa el cache de las queries y el budder si es apropiado
  • Comprueba SHOW STATUS y SHOW VARIABLES
  • Defragmenta tablas, reconstruye índices, haz mantenimiento
  • Aumenta myisam_sort_buffer_size para optimizar inserta largos

Estos son los que me han parecido más interesantes, el resto los podéis encontrar en:

Top 1000 SQL Performance Tips

Gracias David por el aviso

Social Web: clon de Digg, Meneame o Reddit en PHP y MySQL

ByLuis

Social Web CMS es un sistema de noticias social al estilo de Digg o Menéame que está desarrollado en PHP y MySQL y que nos permite crear nuestro clon de forma sencilla.
Permite la inserción de artículos por parte de los usuarios, votar los artículos, realizar comentarios, valorar los comentarios, buscar contenidos, obtener feeds para secciones, categorías y usuarios, disponer de perfiles, mensajes privados … Además dispone de una administración que permite gestionar el anti-spam, bloquear URLs, importar RSS, …
Social Web CMS

Columnas virtuales en MySQL

ByLuis

MySQL está trabajando en un tipo nuevo de columnas las cuales pueden resultar muy útiles. Se trata de columnas virtuales, las cuales tienen un valor automático dependiente de una función.

Con un ejemplo se puede entender mejor:

create table t1 (
a enum('A','B','C'),
b int,
c virtual int as (if(a<>'A',null,b)) stored unique);

Como se puede ver, el campo c tendrá valor null si el campo a es distinto de ‘A’ y el valor del campo b en el resto de los casos.

¿Qué nos puede aportar esto?, pues mayor velocidad en queries que usen índices funcionales, simplicidad en queries usadas con frecuencia y el uso de índices basados en funciones.

También se podría hacer mediante el uso de triggers, pero si el dato se actualiza, este permanerá igual con triggers y cambiará automáticamente con el uso de las columnas virtuales. También se podrían usar vistas, pero es menos eficiente ya que se crean más objetos usando vistas.

Existen algunas restricciones como que solo se puede usar en MyISAM y InnoDB, pero aún así, estoy deseando poder echar mano a este tipo de columnas.

MySQL virtual columns preview