Publi

¿Cómo obtener el tamaño de las tablas en MySQL? Y alguna idea para nuestros servidores

Hoy en día tenemos multitud de aplicaciones web instaladas en nuestros servidores. Algunas serán para gestionar nuestros servicios de forma más cómoda, también puede que tengamos varios WordPress instalados, Drupal, una tienda online, CRM, o algunas más específicas. El problema aquí es que como todo en esta vida digital, tiende a ocupar el máximo espacio posible. El caso es que a veces, es muy útil contar con un resumen del tamaño de todas las tablas de nuestro servidor. Cosa que a veces puede sorprendernos.

El código

Toda la información la tenemos en la tabla information_schema de nuestro servidor MySQL. Aquí encontraremos información sobre el tamaño, las filas, el motor de cada tabla, el comentario con el que se creó y algunas cosas más. También es verdad que el tamaño y las filas para tablas muy muy grandes puede ser aproximado, pero nos dará una idea.

Por cierto, aunque pongo muchas consultas, sólo hay que hacer una, pero a mí me gusta dar más posibilidades y rizar un poco el rizo.

La consulta es normal, un SELECT de toda la vida, por lo que podemos filtrar las tablas, hacer cálculos, etc. Por ejemplo:

1
> SELECT table_schema, TABLE_NAME, ROUND(((data_length + index_length) / 1024 / 1024), 2) `Size`  FROM information_schema.TABLES;

Aunque podemos ordenarlo con las tablas más grandes arriba del todo (orden DESCendente) o abajo (orden ASCendente):

1
> SELECT `table_schema`, `table_name`, ROUND(((data_length + index_length) / 1024 / 1024), 3) `Size`  FROM information_schema.TABLES  ORDER BY (`Size`) DESC;

Como tercer paso, vamos a añadir más información útil, cambiamos el nombre de las columnas, que siempre queda bien y eliminamos del listado las tablas que están en mysql e information_schema, porque suelen ser tablas pequeñas y harán crecer mucho el listado:

1
> SELECT `table_schema` AS `Schema`, `table_name` AS `Table`, `engine` AS `Engine`, `table_rows` AS `Rows`, ROUND(((data_length + index_length) / 1024 / 1024), 3) `Size`  FROM information_schema.TABLES WHERE table_schema NOT IN ('information_schema', 'mysql') ORDER BY (`Size`) DESC;

Como vemos, podemos filtrar en el WHERE, podríamos decirle que incluyera un solo schema o varios. Y, por supuesto, si queremos más precisión en el tamaño (ahora mismo estamos expresándolo en Mb, pero podemos hacerlo en Kb dividiendo sólo una vez por 1024.

Por último, vamos a filtrar por tamaño, porque muchas veces sólo nos interesará conocer las tablas que ocupan más de X Mb. Muchas veces sólo querremos saber las tablas más grandes porque serán las interesantes para nosotros, así que filtremos aceptando sólo las tablas que ocupan más de 100Mb, podríamos meterlo en WHERE (sumando data_length + index_length como hacemos para mostrar el campo), o con HAVING:

1
> SELECT `table_schema` AS `Schema`, `table_name` AS `Table`, `engine` AS `Engine`, `table_rows` AS `Rows`, ROUND(((data_length + index_length) / 1024 / 1024), 3) `Size in MB`  FROM information_schema.TABLES WHERE table_schema NOT IN ('information_schema', 'mysql') HAVING `Size in MB` > 100 ORDER BY (`Size in MB`) DESC;

Ideas para administrar nuestros servidores

Hay tablas en las que de verdad que no podemos hacer nada, van a crecer y ponerse gordas todo lo que puedan. Cuando son datos que nos sirven, por ejemplo una tabla de usuarios o de contenidos. Aunque muchas veces pueden ser tablas de sesión, de log, caché y otras cosas que podemos reducir. Por ejemplo, si nuestro sistema no ha borrado de la tabla algunas sesiones. Estoy de acuerdo en que estas sesiones deberían borrarse solas, pero a veces tenemos instalados scripts sin actualizar o que tienen fallos y nos la juegan.

Estaría bien hacer un script en Bash que tome decisiones:

1
2
3
4
5
TABLAS="$(mysql -N -n -uroot -pPASSWORD -e $'SELECT CONCAT_WS(\'.\', `table_schema`, `table_name`)  FROM information_schema.TABLES WHERE table_schema NOT IN (\'information_schema\', \'mysql\') AND (data_length + index_length) / 1024 / 1024 > 100 ORDER BY (data_length + index_length) DESC;')";

for t in $TABLAS; do
  echo $t
done

En lugar de echo, podemos aprovechar para enviar un mensaje al administrador o para ejecutar otra consulta SQL donde hagamos, por ejemplo:

1
> DELETE FROM $t WHERE Edad>100

o

1
> DELETE FROM $t WHERE Creation_time<NOW() - INTERVAL 3 MONTH

También podríamos ejecutar otro programa que se encargue, por ejemplo, de copiar estos datos a otro lado, por ejemplo, si son logs, y es información algo antigua viene muy bien sacarla a un archivo de texto y comprimirla, nos quitamos el peso de los índices (por ejemplo índice por tipo de elemento, fecha, script que lo genera, etc, que pueden ser muy pesados si están multiplicados por varios cientos de millones de entradas); además, la información de log es muy comprimible, y cuando tiene un tiempo, casi nunca vamos a tener que acceder a ella.

Foto principal: Markus Mit K

También podría interesarte...

Leave a Reply