martes, 18 de agosto de 2009

Estadisticas sobre tu set de datos en MySQL

No se a ti pero a mi muchas veces me paso que en alguna junta me preguntaran "cuanto mide la base de datos??... y cuanto aumenta por dia/semana/mes?". Casi siempre la pregunta planteada por alguien que quiere saber mas que nada por curiosidad pero que te mete en un problema... y que tal si te preguntan sobre tablas especificas??!!

Contar numero de registros es facil pero que tal espacio utilizado en disco? si usas InnoDB entonces lo siguiente te servira.

Con el siguiente query puedes ver el numero de registros en TODA una base de datos, espacio en disco ocupado por datos, espacio ocupado por indices, espacio total y la relacion indices vs. datos.

Ejemplo usando la base de datos 'mysql'. Para usar la tuya solo cambia el final despues de TABLE_SCHEMA:

SELECT count(*) TABLES,
concat(round(sum(table_rows)/1000000,4),'M') rows,
concat(round(sum(data_length)/(1024*1024*1024),4),'G') DATA,
concat(round(sum(index_length)/(1024*1024*1024),4),'G') idx,
concat(round(sum(data_length+index_length)/(1024*1024*1024),4),'G') total_size,
round(sum(index_length)/sum(data_length),4) idxfrac
FROM information_schema.TABLES where TABLE_SCHEMA='mysql';

El query te va a regresar algo similar a:
+--------+---------+---------+---------+------------+---------+
| TABLES | rows | DATA | idx | total_size | idxfrac |+--------+---------+---------+---------+------------+---------+
| 17 | 0.0018M | 0.0004G | 0.0001G | 0.0005G | 0.1562 | +--------+---------+---------+---------+------------+---------+


Que tal si solo quieres el dato para una tabla en particular? Entonces usa esto:
SELECT count(*) TABLES, concat(round(sum(table_rows)/1000000,4),'M') rows, concat(round(sum(data_length)/(1024*1024*1024),4),'G') DATA, concat(round(sum(index_length)/(1024*1024*1024),4),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),4),'G') total_size, round(sum(index_length)/sum(data_length),4) idxfrac FROM information_schema.TABLES where TABLE_SCHEMA='pm_beta' and TABLE_NAME='nombre_de_mi_tabla';

Y la salida sera:
+--------+---------+---------+---------+------------+---------+
| TABLES | rows | DATA | idx | total_size | idxfrac |+--------+---------+---------+---------+------------+---------+
| 1 | 8.9598M | 0.7090G | 1.0834G | 1.7923G | 1.5281 | +--------+---------+---------+---------+------------+---------+


Notese que cambie a una tabla que tuviera mas datos.

Ahora, vamos a ver como sacar el desgloze por cada base de datos limitando a 10 max:
SELECT count(*) TABLES, table_schema,concat(round(sum(table_rows)/1000000,4),'M') rows, concat(round(sum(data_length)/(1024*1024*1024),4),'G') DATA, concat(round(sum(index_length)/(1024*1024*1024),4),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),4),'G') total_size, round(sum(index_length)/sum(data_length),4) idxfrac FROM information_schema.TABLES GROUP BY table_schema ORDER BY sum(data_length+index_length) DESC LIMIT 10;

Salida:
+--------+--------------------+------------+-----------+-----------+------------+---------+
| TABLES | table_schema | rows | DATA | idx | total_size | idxfrac |+--------+--------------------+------------+-----------+-----------+------------+---------+
| 358 | produccion | 7722.5668M | 411.1646G | 870.1474G | 1281.3120G | 2.1163 |
| 17 | mysql | 0.0018M | 0.0004G | 0.0001G | 0.0005G | 0.1562 |
| 6 | test | 0.0000M | 0.0000G | 0.0000G | 0.0000G | 4.0716 |
| 17 | information_schema | NULL | 0.0000G | 0.0000G | 0.0000G | NULL |
| 1 | maatkit | 0.0000M | 0.0000G | 0.0000G | 0.0000G | NULL |
|+--------+--------------------+------------+-----------+-----------+------------+---------+


Por ultimo vamos a ver como hacerlo para una sola tabla:
SELECT count(*) TABLES, TABLE_NAME, table_rows, concat(round(sum(data_length)/(1024*1024*1024),4),'G') DATA, concat(round(sum(index_length)/(1024*1024*1024),4),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),4),'G') total_size, round(sum(index_length)/sum(data_length),4) idxfrac FROM information_schema.TABLES WHERE TABLE_SCHEMA='mi_base_de_datos' AND table_name = 'mi_tabla' group by TABLE_NAME;

Salida:
+--------+------------------------+------------+----------+-----------+------------+---------+
| TABLES | TABLE_NAME | table_rows | DATA | idx | total_size | idxfrac |+--------+------------------------+------------+----------+-----------+------------+---------+
| 1 | mi_tabla | 1209954640 | 75.8538G | 176.0356G | 251.8894G | 2.3207 |+--------+------------------------+------------+----------+-----------+------------+---------+

Si quieres ver todas las tablas o un grupo de tablas solo cambia lo siguiente:
table_name = 'mi_tabla'
por...
table_name like '%substring_en_mis_tablas%'

Con esto podras sacar muy buena informacion.... el formato de salida puede ser manipulado facilmente con php o perl y puedes correr tus comandos usando cron de manera que acumules nueva informacion cada dia o semana o mes segun prefieras.

aburrrrrrrrr........

No hay comentarios.:

Publicar un comentario