How to find database sizes in MySQL/MariaDB Database Server

Cpanel/Whm License $3/mo Plesk License $10/mo Cloudlinux License $5/mo

(: November 28, 2018)

Are you trying to find which databases in your MySQL/MariaDB database server has large tablespace?. People have varying reasons for finding database sizes in MySQL/MariaDB. It could be for data truncation, Archiving, optimizations e.t.c.

In this how-to guide, I’ll share with you a query you can use in your MySQL/MariaDB database server to find the size of each Database.

SELECT
	COUNT(*) AS Total_Table_Count
	,table_schema
	,CONCAT(ROUND(SUM(table_rows)/1000000,2),'M') AS Total_Row_Count
	,CONCAT(ROUND(SUM(data_length)/(1024*1024*1024),2),'G') AS Total_Table_Size
	,CONCAT(ROUND(SUM(index_length)/(1024*1024*1024),2),'G') AS Total_Table_Index
	,CONCAT(ROUND(SUM(data_length+index_length)/(1024*1024*1024),2),'G') Total_Size
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY SUM(data_length+index_length)
DESC LIMIT 10;

This query will:

.td_uid_2_5d907dc7b9a48_rand.td-a-rec-img{text-align:left}.td_uid_2_5d907dc7b9a48_rand.td-a-rec-img img{margin:0 auto 0 0}
  • Find the number of tables in each database
  • Total number of rows in a database
  • Total table size and Index
  • Total table size in GB

Below is a sample output:

+-------------------+--------------------+-----------------+------------------+-------------------+------------+
| Total_Table_Count | table_schema       | Total_Row_Count | Total_Table_Size | Total_Table_Index | Total_Size |
+-------------------+--------------------+-----------------+------------------+-------------------+------------+
|               124 | b4gaags_db         | 1.72M           | 0.10G            | 0.06G             | 0.16G      |
|                33 | mutima             | 0.44M           | 0.10G            | 0.03G             | 0.13G      |
|                55 | gitea              | 0.00M           | 0.00G            | 0.00G             | 0.00G      |
|                48 | kanboard           | 0.00M           | 0.00G            | 0.00G             | 0.00G      |
|                31 | mysql              | 0.00M           | 0.00G            | 0.00G             | 0.00G      |
|                76 | information_schema | NULL            | 0.00G            | 0.00G             | 0.00G      |
|                 3 | zourfs             | 0.00M           | 0.00G            | 0.00G             | 0.00G      |
|                52 | performance_schema | 0.07M           | 0.00G            | 0.00G             | 0.00G      |
+-------------------+--------------------+-----------------+------------------+-------------------+------------+
8 rows in set (0.027 sec)

The ORDER BY DESC LIMIT 10 will show the database sizes from the largest to smallest. Adjust the LIMIT 10 to print more records.

.td_uid_4_5d907dc7b9c3e_rand.td-a-rec-img{text-align:left}.td_uid_4_5d907dc7b9c3e_rand.td-a-rec-img img{margin:0 auto 0 0}

Related posts

AriseBank ICO Fraud Case May Ensnare Additional Parties

SXI ADMIN

BTC China in Discussion With Regulators Over Bitcoin Recognition

SXI ADMIN

Bitcoin is Still Volatile, But That Doesn’t Mean It’s Not Viable

SXI ADMIN

How to install Docker on Fedora 30/29/28

SXI ADMIN

Why Brave’s $35 Million ICO May Not Be Enough for a High-Tech Hiring Spree

SXI ADMIN

Bearish Undertone: OMG Token Flirts With Fibonacci Support

SXI ADMIN

Ethereum Developers Publish Roadmap for EVM Upgrade

SXI ADMIN

What happened at the US regulators’ bitcoin meeting?

SXI ADMIN

How to Install lsyncd on Fedora 22

SXI ADMIN

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More