Database Size

What Makes Up The Size of a Database?

The total size of a database is the sum of the sizes of its tables.

The table’s size is not only the size of the rows that can be seen in the table, but it also includes table’s additional functions (different types of keys and indexes) and a unused space left as a result of requests and actions.

The size of the database table consists of three parts:

• data
• indexes
• overhead

The data for each data type is stored on the database server in files that take up free disk space on the server.

How To View The Size of a Database And Its Tables?

The size of the database and its tables can be viewed via various client programs and phpMyAdmin or directly from the SSH terminal.

We will use phpMyAdmin for this here.

After logging into phpMyAdmin, select the required database from the left column for which all the tables are displayed on the first Structure tab and at the very end, on the last line of the table, the total size of the tables is displayed. However, this does not include the overhead of the tables:

In order to see the actual size of the table (data + indexes + overhead), you need to go to the Structure tab of the table and look for the Space usage table in the Information subsection. The Effective line in it is the actual size of the table on the database server:

What Is Table’s Overhead?

Overhead is the unreleased space of a MySQL / MariaDB database table that is allocated to the table, but not in use.

This can be seen inside of the table information_schema.TABLES in the Data_free column. Size is displayed in bytes.

Data_free is caused by unused and fragmented space due to deletion of rows from the table (DELETE), updating of rows (UPDATE) with larger values, reduction and rearrangement of table space, reservation of space for future queries (INSERT, UPDATE).

How To Find Tables With Large Overhead?

If database has a large number of tables and you don’t know which of them might have a large overhead, instead of looking at the Structure tab for each table, a quicker way to find the problematic table, is to use the following SQL query.

SHOW TABLE STATUS WHERE `Data_free` > 0;

The Data_free column of the query result table shows the overhead of the table, the sizes are displayed in bytes:

A more detailed SQL query that shows the sizes in megabytes of each of the three data types separately and the total actual size of the table (data + indexes + overhead).
Replace [DATABASE_NAME] with the actual database name in the command:

SELECT `TABLE_SCHEMA` AS "Database", `TABLE_NAME` AS "Table", `ENGINE`, `ROW_FORMAT`, `TABLE_ROWS`, ROUND(((`DATA_LENGTH`) / 1024 / 1024), 2) AS "Data Size (MiB)", ROUND(((`INDEX_LENGTH`) / 1024 / 1024), 2) AS "Index Size (MiB)", ROUND(((`DATA_FREE`) / 1024 / 1024), 2) AS "Overhead (MiB)", ROUND(((`DATA_LENGTH` + `INDEX_LENGTH` + `DATA_FREE`) / 1024 / 1024), 2) AS "Total Size (MiB)" FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = '[DATABASE_NAME]' AND `DATA_FREE` > 0 ORDER BY `DATA_FREE` DESC;

It can be seen that the size of this WordPress’ options table is 113.3 MiB, but under the unreleased space is stuck 8337 MiB or 8.1 GiB – that’s more than 70 times the size of the table’s data!

Freeing Up Unused Space – Optimizing The Table

In order to free up the unused space of the table, an optimization must be performed on the table.

To do the optimization of a table, there is a link named Optimize table in the table Space usage on the Structure tab’s page and also on the Operations tab’s page.

Alternatively a SQL query can be made for optimizing a table (replace [DATABASE_NAME] and [TABLE_NAME] in the command with the name of the database and table):

OPTIMIZE TABLE `[DATABASE_NAME]`.`[TABLE_NAME]`;