How to Get Size of MaiaDB Database?

MariaDB is an open-source relational database management system that is a subset of MySQL which is based on SQL(Structured query language). It is an improved version of MySQL and has various features, security, and performance when compared to MySQL.

To get details about tables we can use information_schmea.TABLES. The information_schema is a built-in database that consists of many views that contain information about the objects defined in the current database. You can add data_length and index_legth to get the total size of both the data and any index that we created in the database.

• data_length: It provides the space occupied by the actual data stored in your tables.
• index_legth: It provides the space occupied by the indexes you may have created on your tables.

How to Get the Size of MariaDB Database

Getting the size of the MariaDB Database understood with the For all Databases of the User and Individual Database Tables Size. Let’s understand below.

For all Databases of the User

Syntax:

SELECT table_schema AS Databases_list,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS Size_in_MiB
FROM information_schema.TABLES
GROUP BY table_schema;

For Individual Database Tables Size

Syntax:

SELECT table_schema AS Databases_list, table_name AS Tables_list,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS Size_in_MiB
FROM information_schema.TABLES
WHERE table_schema = 'your_database_name';

To get the database list we use the table_schema column and to get each individual table of the database we use the table_name. The SUM() integrate the size of each individual tables. The ROUND() is used here to round the size to 2 decimal places after converting it to MiB.

Examples of How to Get the Size of MariaDB Database

In below examples two databases are created with respective tables along with values and then there size is displayed.

Databases Creation

The below command creates the first database.

CREATE DATABASE database1;

The below command creates the second database.

CREATE DATABASE database2;

Output:

Creation of Database 1 and 2

Table Creation in Database1

The below command switch to database 1.

 USE database1;

The below command creates the first table in the Database 1.

CREATE TABLE users 
(
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL
);

Output:

Creation of Table 1 in Database 1

The below command creates the second table in the Database1.

CREATE TABLE posts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Output:

Creation of Table 2 in Database 1

Insertion of Values in Tables in Database1

The below command inserts the respective values to table 1.

INSERT INTO users (username, email) VALUES
  ('john_doe', 'john.doe@example.com'),
  ('jane_smith', 'jane.smith@example.com');

Output:

Insertion of Values in Table 1 in Database 1

The below command inserts the respective values to table 2.

INSERT INTO posts (user_id, title, content) VALUES
  (1, 'My first post', 'This is my first blog post!'),
  (2, 'A cool article', 'I found this interesting article and wanted to share it.');

Output:

Insertion of Values in Table 2 in Database 1

Table Creation in Database2

The below command switch to database 2.

USE database2;

The below command creates the first table in the Database2.

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  description TEXT
);

Output:

Creation of Table 1 in Database 2

The below command creates the second table in the Database2.

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL
);

Output:

Creation of Table 2 in Database 2

Insertion of Values in Tables in Database2

The below command inserts the respective values to table 1.

INSERT INTO products (name, price, description) VALUES
  ('T-shirt', 19.99, 'Comfortable and stylish T-shirt'),
  ('Mug', 12.95, 'The perfect mug for your coffee or tea');

Output:

Insertion of Values in Table 1 in Database 2

The below command inserts the respective values to table 2.

INSERT INTO orders (user_id, product_id, quantity) VALUES
  (1, 1, 2),
  (2, 2, 1);

Output:

Insertion of Values in Table 2 in Database 2

List of All the Databases Size

The below command displays table_schema as Databases_list and also the sum of data_length and index_length as Size_in_MB from the information_schema.TABLES and its is grouped by the table_schema.

SELECT table_schema AS Databases_list,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS Size_in_MB
FROM information_schema.TABLES
GROUP BY table_schema;

Output:

List of all the Databases Size

List of all the Table Sizes of the Databases

The below command displays table_schema as Databases_list , table_name as Tables_list and also the sum of data_length and index_length as Size_in_MB from the information_schema.TABLES and these all information are displayed from the respective databases name added under WHERE condition.

SELECT table_schema AS Databases_list, table_name AS Tables_list,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS Size_in_MB
FROM information_schema.TABLES
WHERE table_schema IN ('database1', 'database2');

Output:

List of all the table sizes of the databases

Conclusion

You can easily find the databases size and there table size by adding data_length and index_length columns from the information_schema.TABLES of the respective table. This size is default in bytes but you can be rounded or converted to any unit of size such as MB,GB,TB.