Optimizar base de datos MySQL

By: Javier on 5/31/2013

MySQL es el motor de base de datos por defecto de numerosas aplicaciones, entre ellas Bacula. Y como otros muchos motores de bases de datos, tiene su parte buena y su parte mala. La parte buena es la flexibilidad y potencia que nos ofrece. La parte mala es que en aplicaciones muy dinámicas, hablando de datos, MySQL tiende a fragmentar las tablas y por lo tanto, espacio en disco ocupado y rapidez de acceso a datos aumentan. Aunque este post lo relacionamos directamente con Bacula, este pequeño truco sirve para cualquier otras aplicación que emplee MySQL. Lo que vamos a explicar aquí, es como optimizar un poco las tablas, es decir, que tras realizar muchas escrituras y borrados y también updates, hacer que la estructura de la tabla vuelva a ser la óptima. Existen dos formas, la primera es la clausulo OPTIMIZE de mysql, y la segunda, un pequeño script, que aparece en la documentación de bacula. Ambas estrategias van a realizar algo parecido, desfragmentar la tabla, repararla y ordenar los índices. Vamos a ver cómo funcionan.

• OPTIMIZE
  
  Es una clausula de MySQL y sobre tablas MyISAM da muy buenos resultados. Por lo tanto, para aplicarlo a bacula, simplemente tendremos que ejecutar dicha clausula sobre todas las tablas, una a una. 

  mysql> OPTIMIZE LOCAL TABLE bacula.BaseFiles;
  +------------------+----------+----------+----------+
  | Table            | Op       | Msg_type | Msg_text |
  +------------------+----------+----------+----------+
  | bacula.BaseFiles | optimize | status   | OK       |
  +------------------+----------+----------+----------+
  1 row in set (0.00 sec)
  
  mysql> OPTIMIZE LOCAL TABLE bacula.CDImages;
  +-----------------+----------+----------+----------+
  | Table           | Op       | Msg_type | Msg_text |
  +-----------------+----------+----------+----------+
  | bacula.CDImages | optimize | status   | OK       |
  +-----------------+----------+----------+----------+
  1 row in set (0.00 sec)
  

  Según el grado de fragmentación que presente, el proceso puede llevar más o menos tiempo.
• script
  
  Esta forma es algo más elaborada y requiere escribir un poco de código, pero el resultado es mucho más óptimo y queda más logrado. Realizamos el siguiente script,

  #!/bin/bash
  
  inMB=$(du -ms $DIR | awk '{print $1}')
  echo "DB Bacula size: $inMB MB"
  echo "Optimize MySQL Database (I): dump"
  mysqldump -f -uroot -p --opt bacula > /tmp/bacula.sql
  echo "Optimize MySQL Database (II): write"
  mysql -u root -p bacula < /tmp/bacula.sql
  echo "Delete security copy"
  rm -f /tmp/bacula.sql
  newInMB=$(du -ms $DIR | awk '{print $1}')
  echo "New DB Bacula size: $newInMB MB"
  RATIO=`echo "scale=2; ($inMB-$newInMB)/$inMB*100" | bc`
  echo "$RATIO % compress ratio"
  

  Antes de ejecutarlo, intentamos comprender un poco lo que hace, que no es más que realizar un dump de la base de datos bacula para luego volver a insertarla. Al insertarla, genera el schema y borra los datos actuales para volver a meterlos, sacando toda fragmentación y ordenando los índices.
  Un ejemplo de ejecución sería,
  

  DB Bacula size: 1577 MB
  Optimize MySQL Database (I): dump
  Optimize MySQL Database (II): write
  Delete security copy
  New DB Bacula size: 897 MB
  43.00% compress ratio
  

Cualquier de las dos opciones es buena, y es aconsejable según el uso que se haga de bacula ejecutarla cada 3 o 6 meses. Si el equipo realiza muchas copias de seguridad, ejecutarlo una vez al mes, tampoco sobra. Realmente, lo importante es el porcentaje de espacio que se le gana. En esta ejecución, fue de un 43%, efectivamente lo necesitaba. Si la mejora es de un 3%, obviamente, no ;-)