Check the table capacity and database capacity with MariaDB

How to check table capacity and database capacity in MariaDB.

If you operate a database with MariaDB, the capacity of the table to which the log is written will increase steadily.

It is useful to be able to check the capacity of the table, such as when you want to delete unnecessary past log data on a regular basis.

We will also introduce commands that can check the capacity of the entire database.

Check the capacity of the table

SQL to check the capacity of the table. The data capacity, index capacity, and total capacity obtained by adding them are displayed. The number of rows in the table (table_rows) and the average size of the columns (avg_row_length) are also displayed. Sorted in descending order of total size.

# SQL to check the capacity of the table
select
  table_name,
  floor((data_length + index_length) / 1024/1024) as total_size_MB,
  floor((data_length) / 1024/1024) as data_size_MB,
  floor((index_length) / 1024/1024) as index_size_MB,
  table_rows,
  avg_row_length
from
  information_schema.tables
where
  table_schema = database ()
order by
  (data_length + index_length) desc
;

This is a sample SQL output to check the capacity of the table.

+ ---------------------------- + ----------------- + ---- ---------- + --------------- + ------------ + ---------- ------ +
table_name | total_size_MB | data_size_MB | index_size_MB | table_rows | avg_row_length |
+ ---------------------------- + ----------------- + ---- ---------- + --------------- + ------------ + ---------- ------ +
book | 304 | 179 | 125 | 344669 | 547 |
| author | 157 | 104 | 52 | 1212336 | 90 |
Publisher | 138 | 138 | 0 | 2467485 | 58 |
+ ---------------------------- + ----------------- + ---- ---------- + --------------- + ------------ + ---------- ------ +

Check the capacity of the database

SQL to check the capacity of the database. It is calculated by summing the capacity of the table.

# SQLDB size to check database capacity
select
  table_schema, floor(sum (data_length) /1024/1024) as database_size_MB
from
  information_schema.tables
group by group by
  table_schema
order by
  sum (data_length + index_length) desc
;

This is a sample output result to check the capacity of the database.

+ -------------------- + ------------------ +
| table_schema | database_size_MB |
+ -------------------- + ------------------ +
| perlzemi | 660 |
| kimoto_system | 50 |
+ -------------------- + ------------------ +

MariaDB Tutorial