How to get size of a mysql database?
Suppose the target database is called "v3".
11 Answers
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.
-
4Even after I delete most of the data from the tables in the database, the size remains the same– VidzCommented Aug 8, 2014 at 9:05
-
2@Vidz are you using InnoDB engine. If you do, you can free space unless you use file_per_table and alter tables.– mandzaCommented Nov 1, 2014 at 9:32
-
6Please 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.– v010dyaCommented Dec 29, 2014 at 19:39
-
17To select from a single database, add this between the
FROM
andGROUP
line:where table_schema='DATABASE_NAME'
- replacingDATABASE_NAME
with your database.– KJ PriceCommented Nov 9, 2016 at 13:37 -
4Note: 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
. Commented Jul 17, 2017 at 22:15
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
-
1Better 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– sonicliCommented Jun 10, 2022 at 4:42
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.
Screen-shot :
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";
Alternatively you can directly jump into data directory and check for combined size of v3.myd, v3. myi and v3. frm files (for myisam) or v3.idb & v3.frm (for innodb).
-
7Note: ibd files only exist if using innodb_file_per_table Commented Aug 1, 2014 at 21:45
-
2This answer is very specific to storage engine. Answer by @brian-willis is more appropriate. Commented Jun 23, 2017 at 9:03
-
This will not work if you don't have access (using a cloud service), for example, you are using AWS RDS... Commented Apr 8, 2022 at 14:38
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;
mysqldiskusage --server=root:MyPassword@localhost pics
+----------+----------------+
| 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.
Update
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
-
2
-
@angristan - Thanks. I think Oracle is tossing the package. Even an old copy of the utilities will be useful in most situations. Commented Jul 27, 2020 at 16:19
-
1CAUTION:
mysqldiskusage
requires use of unencripted password in command line. Make sure to delete it from history after use. Commented Nov 3, 2020 at 16:51 -
In some shells, putting a space in front of the command avoids saving the line in 'history'. Commented Nov 3, 2020 at 17:35
-
@stan
mysql-utilities
package does not exist anymore on Debian 11. Where can we get themysqldiskusage
command then?– baptxCommented Feb 24, 2023 at 23:28
First login 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;
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:
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;
Go into the mysql data directory and run du -h --max-depth=1 | grep databasename
-
5ok. but for cloud database servers like RDS, GCP we don't have access to server file system.– AkhilCommented Sep 21, 2021 at 6:12
-
1The 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.– ApuleiusCommented Apr 9, 2022 at 17:18
-
On many shared hostings this is exactly what they use for calculation.– jorCommented Aug 23, 2022 at 7:51
-
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 commanddu -h databasename
.– baptxCommented Feb 24, 2023 at 23:32
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.
Hope this will help many of us.
-
1Hi, 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– funder7Commented Jun 30, 2022 at 11:36