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
Pingback: ¿Cómo obtener el tamaño de las tablas en MySQL? Y alguna idea para nuestros servidores | PlanetaLibre /
Thanks for a wonderful share. Here is the great example related to you blog. Mickey Mouse Varsity Jacket for adults
This is the one of the most important information for me. And I am feeling glad reading your article. The article is really excellent?
Jacket From Drive
Your passion for the biggest web slots shines through. What a fantastic resource. สล็อตเว็บใหญ่ สุด
I avoid slots that have a reputation for breaking easily. เว็บสล็อตแตกง่าย
I am keen on such themes so I will address page where it is cool depicted. สล็อตรวมทุกค่าย
Cool you record, the data is extremely salubrious further entrancing, I’ll give you an associate with my scene. เว็บสล็อตโรม่า
This is exceptionally engaging, however , it is vital that will mouse tap on the association: เกมส์ไพ่ป๊อกเด้ง
Online casinos have made me appreciate the art of bluffing and reading opponents. It’s a skill that comes in handy, especially in poker games. 우리카지노
I can suggest essentially not too bad and even dependable tips, accordingly see it: best concierge doctor