--- title: "Database Size" canonical_url: "https://www.zone.lv/help/kb/database-size/" post_type: "ht_kb" published: "2024-11-13T13:32:23+00:00" modified: "2025-01-10T07:14:12+00:00" language: "en" author: "martti" taxonomies: ht_kb_category: - name: "MariaDB / MySQL" url: "https://www.zone.lv/help/kb-categories/mariadb-mysql-en/" - name: "Technical" url: "https://www.zone.lv/help/kb-categories/technical-en/" ht_kb_tag: - name: "mariadb" url: "https://www.zone.lv/help/kb-tags/mariadb-en/" - name: "mysql" url: "https://www.zone.lv/help/kb-tags/mysql-en/" --- # 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. Attention! Even if the count of the rows, size of the data in the table is small or the table is completely empty, it may still have unused space called overhead due to previous actions and thus the files of the table containing this unused space will still take up space on the database server’s disk. ## 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. Guide [How to access phpMyAdmin?](https://www.zone.lv/help/en/kb/how-to-access-phpmyadmin/) 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: ![phpMyAdmin - Database Tables](https://www.zone.lv/static/sites/5/phpmyadmin_database_tables-1.png) 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: ![phpMyAdmin - Structure of the table](https://www.zone.lv/static/sites/5/phpmyadmin_table_structure-1.png) ![phpMyAdmin - Space usage of the table](https://www.zone.lv/static/sites/5/phpmyadmin_space_usage-1.png) ## 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). Data\_free Although the Data\_free space is not in use and is not directly reflected in the database size, it still physically uses disk space on the database server as a database table file. ## 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. To make an SQL query in phpMyAdmin, you need to go to the `SQL` tab, write or paste the query into the text box and then press the `Go` button to make the 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: ![phpMyAdmin - SQL query: SHOW TABLE STATUS](https://www.zone.lv/static/sites/5/phpmyadmin_show_table_status-1.png) 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; ``` ![phpMyAdmin - SQL query: SELECT all sizes and total size results](https://www.zone.lv/static/sites/5/phpmyadmin_select_all_sizes_results-1.png) 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! ![phpMyAdmin - SQL query: SELECT al sizesl and total size](https://www.zone.lv/static/sites/5/phpmyadmin_select_all_sizes-1.png) ## 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]`; ``` Attention! For very large tables, optimization can take a long time, during which the table may be locked and therefore unusable by the application. It is therefore recommended that optimization is not performed during peak usage. Read more in the documentation of the corresponding database: [MariaDB: OPTIMIZE TABLE](https://mariadb.com/kb/en/optimize-table/) [MySQL: OPTIMIZE TABLE Statement](https://dev.mysql.com/doc/refman/en/optimize-table.html)