Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
 

Here is a handy little script to get the top 20 MySQL tables based on size

I was trying to free some space up on my zabbix server; the database had grown to a 6G within a few months. I needed to get the top offending table to see what kind of data was in there I could get rid of. Since I only use Zabbix mostly for at the moment monitoring and no so much reporting on things over time. I went with the history table.

TABLEROWSDataIdxTotal SizeIDX-Frac
zabbix.history_uint 36.95M1.81G0.97G2.78G0.53
zabbix.history6.59M0.32G0.17G0.50G0.53
zabbix.trends_uint4.23M0.29G0.00G0.29G0.00
SELECT CONCAT(table_schema, '.', table_name),
       CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
       CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
       CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
       CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
       ROUND(index_length / data_length, 2)                                           idxfrac
FROM   information_schema.TABLES
ORDER  BY data_length + index_length DESC
LIMIT  20;