Para saber todo lo que pesan nuestras base de datos seria esta consulta

 

 

SELECT
count(*) TABLES
,concat(round(sum(table_rows)/1000000,2),’K’) rows
,concat(round(sum(data_length)/(1024*1024*1024),2),’G’) DATA
,concat(round(sum(index_length)/(1024*1024*1024),2),’G’) idx
,concat(round(sum(data_length+index_length)/(1024*1024*1024),2),’G’) total_size
,round(sum(index_length)/sum(data_length),2) idxfrac
FROM
information_schema.TABLES;

SELECTcount(*) TABLES,concat(round(sum(table_rows)/1000000,2),’K’) rows,concat(round(sum(data_length)/(1024*1024*1024),2),’G’) DATA,concat(round(sum(index_length)/(1024*1024*1024),2),’G’) idx,concat(round(sum(data_length+index_length)/(1024*1024*1024),2),’G’) total_size,round(sum(index_length)/sum(data_length),2) idxfracFROMinformation_schema.TABLES;

 

Con esta otra consulta sacamos el top 10 de las tablas mas pesadas en nuesto servidor MySQL

 

SELECT
count(*) TABLES
,table_schema,concat(round(sum(table_rows)/1000000,2),'M') rows
,concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA
,concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx
,concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size
,round(sum(index_length)/sum(data_length),2) idxfrac
FROM
information_schema.TABLES
GROUP BY
table_schema
ORDER BY
sum(data_length+index_length) DESC LIMIT 10;

 

 

Y si queremos verlo sobre una base de datos en concreto y todas sus tablas sería algo como

 

select table_name,table_rows, data_length,index_length,round((data_length/(1024)),2) as Kb

from  information_schema.TABLES where table_schema = «tablename» order by data_length desc;

 

  

Por admin

Deja una respuesta