.::: Database size & Table Capacity in MySQL MariaDB :::.

https://teguhth.blogspot.com/2020/12/database-size-table-capacity-in-mysql.html
http://teguhth.blogspot.com/2022/08/create-store-procedure-for-database.html
https://teguhth.blogspot.com/2022/08/create-event-scheduler-to-run-query.htm

A. Create database nms tools ;

create database teguhthtools;
use teguhthtools;

MariaDB [(none)]> use teguhthtools;
Database changed
MariaDB [teguhthtools]>

B. table Database size capacity

1. query database size capacity
 SELECT @@hostname as ServerName, current_timestamp() as DateTime,s.schema_name as SchemaName,
CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00)) TotalSizeMB,
CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00)) DataUsedMB,
CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00)) DataFreeMB  
FROM INFORMATION_SCHEMA.SCHEMATA s, INFORMATION_SCHEMA.TABLES t
WHERE s.schema_name = t.table_schema GROUP BY s.schema_name ORDER BY SchemaName ;

 

2. create table for save database info
CREATE TABLE `tbl_dbsize` (
  `ServerName` varchar(15) DEFAULT NULL,
  `DateTime` datetime NOT NULL,
  `SchemaName` varchar(64) NOT NULL,
  `TotalSizeMB` varchar(49) DEFAULT NULL,
  `DataUsedMB` varchar(50) DEFAULT NULL,
  `DataFreeMB` varchar(48) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

3. insert data to table tbl_dbsize

insert into teguhthtools.tbl_dbsize
SELECT @@hostname as ServerName, current_timestamp() as DateTime,s.schema_name as SchemaName,
CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00)) TotalSizeMB,
CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00)) DataUsedMB,
CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00)) DataFreeMB  
FROM INFORMATION_SCHEMA.SCHEMATA s, INFORMATION_SCHEMA.TABLES t
WHERE s.schema_name = t.table_schema GROUP BY s.schema_name ORDER BY SchemaName ;
 

4. check data tbl_dbsize
use teguhthtools;
select * from tbl_dbsize order by DateTime desc;
select * from teguhthtools.tbl_dbsize order by DateTime desc;
 

5. delete row table if you need
delete from teguhthtools.tbl_dbsize order by DateTime desc;
 

C. table_info_size capacity

1. query table size capacity

SELECT @@hostname as ServerName,current_timestamp() as DateTime,
    table_schema,
    table_name,
    table_type,
    row_format,
    table_rows,
    round(1.0*data_length/1024/1024, 2) as DataSizeMB,
    round(index_length/1024/1024, 2) as IndexSizeMB,
    round(data_free/1024/1024, 2) as FreeSizeMB,
    round((data_length + index_length)/1024/1024, 2) as TotalSizeMB
FROM
    information_schema.tables
WHERE
 table_type ='base table' and table_schema not in('performance_schema','mysql','information_schema')
ORDER BY table_name;
 

2. create table for save table info

CREATE TABLE `tbl_tablesizeinfo` (
  `ServerName` varchar(8) DEFAULT NULL,
  `DateTime` datetime NOT NULL,
  `table_schema` varchar(64) NOT NULL,
  `table_name` varchar(64) NOT NULL,
  `table_type` varchar(64) NOT NULL,
  `row_format` varchar(10) DEFAULT NULL,
  `table_rows` bigint(20) DEFAULT NULL,
  `DataSizeMB` decimal(25,2) DEFAULT NULL,
  `IndexSizeMB` decimal(25,2) DEFAULT NULL,
  `FreeSizeMB` decimal(25,2) DEFAULT NULL,
  `TotalSizeMB` decimal(25,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;


3. insert data to table tbl_tablesizeinfo

insert into teguhthtools.tbl_tablesizeinfo
SELECT @@hostname as ServerName,current_timestamp() as DateTime,
    table_schema,
    table_name,
    table_type,
    row_format,
    table_rows,
    round(1.0*data_length/1024/1024, 2) as DataSizeMB,
    round(index_length/1024/1024, 2) as IndexSizeMB,
    round(data_free/1024/1024, 2) as FreeSizeMB,
    round((data_length + index_length)/1024/1024, 2) as TotalSizeMB
FROM
    information_schema.tables
WHERE
 table_type ='base table' and table_schema not in('performance_schema','mysql','information_schema')
ORDER BY table_name;

4. check data tbl_tablesizeinfo
use teguhthtools;
select * from tbl_tablesizeinfo order by DateTime desc;
select * from teguhthtools.tbl_tablesizeinfo order by DateTime desc;
 

5. delete row table if you need
delete from teguhthtools.tbl_tablesizeinfo order by DateTime desc;