Optimizando tablas InnoDB en MySQL

Cuando utilizamos MySQL es común optimizar tablas con muchos registros con cierta periodicidad, esto para solventar problemas de fragmentación, entre otros. La verdad esta es una de las cosas del modelo de PostgreSQL que echo en falta, quizás no es tan "amigable" pero todo queda claro desde el inicio.

En PostgreSQL hay un proceso de aspiradora (vacuum) que va eliminando periódicamente registros inutilizados en tablas, su configuración, pan nuestro de cada día para un admin de BBDD que debe ajustarlo con frecuencia.

Bueno.... Volviendo a MySQL, si necesita optimizar tablas InnoDB, lo mejor que puede utilizar son ALTER nulos, estas son instrucciones DDL de tipo ALTER sin parámetros que permiten seguir trabajando con las BBDD, porque realiza copias temporales en disco. La cuestión es que esta herramienta "reconstruye" la tabla y elimina, entre otros, los problemas de fragmentación.

Aquí les dejo un script para optimizar de "un sólo golpe" varias tablas InnoDB:

#!/bin/bash

if [ $# -lt 2 ]; then
        echo "You must specify database host"
        echo "Eg. script.sh MY_DATABSE 192.168.10.1"
        exit
fi

db="$1"
host="$2"
user="root"
declare -a tables=(Table1 Table2 Table3)

stty -echo
read -p "Enter MySQL's Admin password: " password
stty echo

for table in ${tables[@]}; do
        echo $table &&
        time mysql -u $user --password=$password -h $host $db -e "ALTER TABLE $table ENGINE=INNODB"
done

Básicamente optimizamos las tablas especificadas (en un arreglo) e imprimimos el tiempo que toma cada instrucción (time).

Si tiene la certeza de que todas las tablas de una BD son InnoDB y quiere optimizarlas todas aún más rápido, puede hacerlo valiéndose del comando "show tables"...

#!/bin/bash

if [ $# -lt 2 ]; then
        echo "You must specify database host"
        echo "Eg. script.sh MY_DATABSE 192.168.10.1"
        exit
fi

db="$1"
host="$2"
user="root"

stty -echo
read -p "Enter MySQL's Admin password: " password
stty echo

mysql -u $user --password=$password -h $host --batch --skip-column-names $db -e "SHOW TABLES" |
while read table; do
        echo $table &&
        time mysql -u $user --password=$password -h $host $db -e "ALTER TABLE $table ENGINE=INNODB"
done

La única diferencia es que las tablas ya no son especificadas a través de un arreglo (que recomiendo para BBDD grandes, donde optimizar todas las tablas podría demorar toda la vida), sino que se toman directamente del comando "SHOW TABLES" para una BD especificada.

Ejecutando lotes de modificaciones (INSERT, UPDATE, DELETE) en BBDD

Esta me salvó hace poco de un proceso de modificaciones (INSERT, UPDATE y DELETE) eterno!

Si necesita realizar muchas operaciones de las comentadas, por ejemplo un par de millones, resulta bastante útil y reduce considerablemente el tiempo de respuesta, incluir BEGIN y COMMIT al comienzo y final del lote.

Con esto eliminamos sobrecarga realizando una sola transacción y no una por instrucción. Espero sea de utilidad... Recuerde: BEGIN; INSERT...; COMMIT;

Barajita premiada (#21) - número de días en un mes

Esta es una barajita breve, pero no por ello menos útil. Se trata de un método sencillo en *nix de obtener el número de días que tiene un mes:

$ month=02; year=2011
$ cal $month $year | xargs | awk '{print $NF}'
28

Obtengo los días en formato calendario, los paso por xargs para quitar retornos de carro/tabulaciones y luego pido el último elemento con AWK.