Computing MySQL database size can provide you useful informations : disk usage, index size, number of rows, lack of index, collations and many more.
Calculating these values use the information database : INFORMATION_SCHEMA, the place that stores information about all the other databases that the MySQL server maintains.
Simple Query to show MySQL Database Usage
This query will show the index and data size in Mb, number of rows per table with database type, partitions and collation.
|
1 2 3 4 5 6 7 8 |
SELECT TABLE_NAME AS "Table", TABLE_ROWS AS "Rows", CONCAT((FORMAT((DATA_LENGTH) / POWER(1024,2),2)), ' Mb') AS "Data Size", CONCAT((FORMAT((INDEX_LENGTH) / POWER(1024,2),2)), ' Mb') AS "Index Size", CONCAT((FORMAT((DATA_LENGTH+ INDEX_LENGTH) / POWER(1024,2),2)), ' Mb') AS "Total Size", TRIM(TRAILING ', ' FROM CONCAT_WS(', ', ENGINE, TABLE_COLLATION, CREATE_OPTIONS)) AS "Type" FROM information_schema.TABLES WHERE information_schema.TABLES.table_schema = "${Your Database Name}" |
This MySQL query execution will output something like this :
|
1 2 3 4 5 6 |
Table | Rows | Data Size|Index Size|Total Size| Type MySQL_TABLE_A | 342| 0.19 Mb| 0.00 Mb| 0.19 Mb| InnoDB, utf8_general_ci MySQL_TABLE_B | 8747| 6.52 Mb| 0.70 Mb| 7.22 Mb| InnoDB, utf8_general_ci MySQL_TABLE_C | 0| 0.02 Mb| 0.00 Mb| 0.02 Mb| InnoDB, latin1_swedish_ci MySQL_TABLE_D | 4265| 1.52 Mb| 0.42 Mb| 1.94 Mb| InnoDB, utf8_general_ci MySQL_TABLE_E | 1934| 0.44 Mb| 0.16 Mb| 0.59 Mb| InnoDB, utf8_general_ci |
This is also a good example why I also added database engine and MySQL collation in this query, one table is not in utf8_general_ci but in latin1_swedish_ci.
Another way to show MySQL Database Usage
This query use a MySQL function to compute size in more readable number by changing size numbers units.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
DELIMITER $ DROP FUNCTION IF EXISTS byteResize$ CREATE FUNCTION byteResize(bytes FLOAT(9)) RETURNS VARCHAR(50) BEGIN # Unit list DECLARE unit INTEGER UNSIGNED DEFAULT 1; # Resizing WHILE bytes > 1024 DO SET bytes = bytes / 1024; SET unit = unit + 1; END WHILE; RETURN CONCAT(ROUND(bytes, 2), ' ', ELT(unit, '', 'K', 'M', 'G', 'T'), 'b'); END$ DELIMITER ; SELECT TABLE_NAME AS "Table", TABLE_ROWS AS "Rows", byteResize(DATA_LENGTH) AS "Data Size", byteResize(INDEX_LENGTH) AS "Index Size", byteResize(DATA_LENGTH+ INDEX_LENGTH) AS "Total Size", TRIM(TRAILING ', ' FROM CONCAT_WS(', ', ENGINE, TABLE_COLLATION, CREATE_OPTIONS)) AS "Type" FROM information_schema.TABLES WHERE information_schema.TABLES.table_schema = "${Your Database Name}" |
This MySQL query will output :
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Table | Rows | Data Size|Index Size|Total Size| Type MySQL_TABLE_A | 40119| 3.52 Mb| 4.03 Mb| 7.55 Mb| InnoDB, utf8_general_ci MySQL_TABLE_B | 1042959| 757.45 Mb| 37.28 Mb| 794.73 Mb| InnoDB, utf8_general_ci, partitioned MySQL_TABLE_C | 36788508| 2.43 Gb| 3.10 Gb| 5.53 Gb| InnoDB, utf8_general_ci, partitioned MySQL_TABLE_D | 1876| 112.00 Kb| 0.00 b| 112.00 Kb| InnoDB, utf8_general_ci MySQL_TABLE_E | 0| 16.00 Kb| 16.00 Kb| 32.00 Kb| InnoDB, utf8_general_ci MySQL_TABLE_F | 1925| 480.00 Kb| 144.00 Kb| 624.00 Kb| InnoDB, utf8_general_ci MySQL_TABLE_G | 1210839| 70.78 Mb| 124.14 Mb| 194.92 Mb| InnoDB, utf8_general_ci, partitioned MySQL_TABLE_H | 1762| 96.00 Kb| 64.00 Kb| 160.00 Kb| InnoDB, utf8_general_ci MySQL_TABLE_I | 44384| 10.52 Mb| 7.55 Mb| 18.06 Mb| InnoDB, utf8_general_ci MySQL_TABLE_J | 1536| 176.00 Kb| 0.00 b| 176.00 Kb| InnoDB, utf8_general_ci MySQL_TABLE_K | 10849| 1.52 Mb| 0.00 b| 1.52 Mb| InnoDB, utf8_general_ci |
This will make the MySQL table size values more readable as the first query returned 5,711.73 Mb as Table B total size.
Hope this will help you.
Very nice. I added these to my list of useful queries.
Thanks