842

How can I get the size of a MySQL database?

Suppose the target database is called "v3".

Improve this question
0

13 Answers 13

Reset to default
1794

Run this query, and you'll probably get what you're looking for:

SELECT table_schema "DB Name", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema;

This query comes from the MySQL forums, where there are more comprehensive instructions available.

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

Even after I delete most of the data from the tables in the database, the size remains the same
@Vidz are you using InnoDB engine. If you do, you can free space unless you use file_per_table and alter tables.
Please keep in mind that this method will not return any of the databases that are completely empty, at least a single table must exist for the database to appear in the result.
To select from a single database, add this between the FROM and GROUP line: where table_schema='DATABASE_NAME' - replacing DATABASE_NAME with your database.
Note: MySQL Workbench will spit out a Syntax error: {column title} (double quoted text) is not valid input here. error. The column titles should be wrapped in tick marks. I.e. Database Name.
|
214

It can be determined by using following MySQL command

SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema

Result

Database Size (MB) db1 11.75678253 db2 9.53125000 test 50.78547382

Get result in GB

SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 / 1024 AS "Size (GB)" FROM information_schema.TABLES GROUP BY table_schema
Improve this answer

1 Comment

Better Performance : SELECT table_schema AS "Database", (SUM(data_length)+SUM(index_length)) / 1024 / 1024 / 1024 AS "Size (GB)" FROM information_schema.TABLES GROUP BY table_schema
46

Alternatively, if you are using phpMyAdmin, you can take a look at the sum of the table sizes in the footer of your database structure tab. The actual database size may be slightly over this size. However, it appears to be consistent with the table_schema method mentioned above.

Screenshot:

Enter image description here

Improve this answer

Comments

31

To get a result in MB:

SELECT SUM(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2)) AS "SIZE IN MB" FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = "SCHEMA-NAME";

To get a result in GB:

SELECT SUM(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024), 2)) AS "SIZE IN GB" FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = "SCHEMA-NAME";
Improve this answer

Comments

26

Alternatively, you can directly jump into the data directory and check for the combined size of v3.myd, v3.myi and v3.frm files (for MyISAM), or v3.idb & v3.frm (for InnoDB).

Improve this answer

3 Comments

Note: ibd files only exist if using innodb_file_per_table
This answer is very specific to storage engine. Answer by @brian-willis is more appropriate.
This will not work if you don't have access (using a cloud service), for example, you are using AWS RDS...
24

If you want the list of all database sizes sorted, you can use:

SELECT * FROM (SELECT table_schema AS `DB Name`, ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS `DB Size in MB` FROM information_schema.tables GROUP BY `DB Name`) AS tmp_table ORDER BY `DB Size in MB` DESC;
Improve this answer

Comments

21 
mysqldiskusage --server=root:MyPassword@localhost pics

Output:

+----------+----------------+ | db_name | total | +----------+----------------+ | pics | 1,179,131,029 | +----------+----------------+

If not installed, this can be installed by installing the mysql-utils package which should be packaged by most major distributions.

Alas, they got rid of that package. If you are on Linux (or similar),

du -m /var/lib/mysql/*

will list the size, in megabytes, for each database you have. (Caveats: You probably need to be root to run the command, and the path may not be what I provided there.) This lists the 20 biggest:

du -m /var/lib/mysql/* | sort -nb | tail
Improve this answer

7 Comments

On Debian 10 this package is called mysql-utilities
@angristan - Thanks. I think Oracle is tossing the package. Even an old copy of the utilities will be useful in most situations.
CAUTION: mysqldiskusage requires use of unencripted password in command line. Make sure to delete it from history after use.
In some shells, putting a space in front of the command avoids saving the line in 'history'.
@stan mysql-utilities package does not exist anymore on Debian 11. Where can we get the mysqldiskusage command then?
|
21

First log in to MySQL using:

mysql -u username -p

Command to Display the size of a single Database along with its table in MB.

SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = "database_name" ORDER BY (data_length + index_length) DESC;

Change database_name to your Database

Command to Display all the Databases with its size in MB.

SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
Improve this answer

Comments

8

To get the list of all the databases sorted by their size in descending order without using any sub-query, you can use the below query:

SELECT table_schema AS Database_Name, SUM(data_length + index_length) Size_In_Bytes, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) Size_In_MB, ROUND(SUM(data_length + index_length) / 1024 / 1024/ 1024, 2) Size_In_GB FROM information_schema.tables GROUP BY table_schema ORDER BY Size_In_Bytes DESC;

You will get the size of all the databases in Bytes, MB and GB as shown in the example below:

enter image description here

As asked by OP, to get the size on any particular database like "v3" WHERE can be used in the query as given below:

SELECT table_schema AS Database_Name, SUM(data_length + index_length) Size_In_Bytes, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) Size_In_MB, ROUND(SUM(data_length + index_length) / 1024 / 1024/ 1024, 2) Size_In_GB FROM information_schema.tables WHERE table_schema = 'v3' GROUP BY table_schema ORDER BY Size_In_Bytes DESC;
Improve this answer

Comments

6

Go into the mysql data directory and run du -h --max-depth=1 | grep databasename

Improve this answer

4 Comments

ok. but for cloud database servers like RDS, GCP we don't have access to server file system.
The file size does not reflect the real database size. In fact, after deleting entries from a table, the file is not shrunk; instead, it contains unallocated space that the engine will reuse by the next occasion.
On many shared hostings this is exactly what they use for calculation.
It looks like we don't need to use grep and we don't need the --max-depth=1 parameter. In my case the result was the same with the command du -h databasename.
2

In addition: If someone wants to get the size of a single table please use the following codes:

SELECT TABLE_NAME AS `Table Name`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size ( in MB)` FROM information_schema.TABLES WHERE TABLE_SCHEMA = "your_db_name" AND TABLE_NAME = "your_single_table_name" ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

Note: It won't show the fraction numbers for using the ROUND() method.

Improve this answer

1 Comment

Hi, the question is about how to calculate the whole size of a db, not a single table.. Please edit your answer in order to reach that result, or explain what your answer is doing more than the accepted answer. Cheers
0

If you are using MySQL Workbench

If you use MySQL Workbench, you can check the database size as follows:

  • Open MySQL Workbench and connect to your MySQL server.
  • Go to the "Navigator" pane on the left.
  • Right-click on the database you want to check.
  • Select "Schema Inspector."
  • The "Schema Inspector" window will show various details, including the estimated size of the database. Data may be showed GiB. 1 GB = 0.93132257461548 GiB

Using ubuntu file system. Usually mysql resides inside /var/lib du -sh var/lib/mysql

-s: Display only total. If you do not use s it will show breakdown of subfolder. -h: Print sizes in human-readable format (e.g., 1K, 234M, 2G).
Improve this answer

Comments

0

Not a single answer or comment even mentions a possible overhead of the database tables and doesn't take this into account in the SQL query.

This is the most common mistake when somebody asks: "How big is my database X?". And after getting the wrong! total size, asks: "But then why there's so little space left on the partition/disk where database data is located?".

This is how you get actual total size of the database, replace [DATEBASE_NAME] with your database name:

SELECT `TABLE_SCHEMA` AS "Database", FORMAT_BYTES(SUM(`DATA_LENGTH` + `INDEX_LENGTH` + `DATA_FREE`)) AS "Total Size" FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = '[DATEBASE_NAME]';

* when using MariaDB version <11.8, then prepend function name FORMAT_BYTES with "sys.": sys.format_bytes .

Result:

+----------------+------------+ | Database | Total Size | +----------------+------------+ | v3 | 3.56 GiB | +----------------+------------+
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.