Skip to content

Instantly share code, notes, and snippets.

@vasilii-b

vasilii-b/Show database size.sql

Last active November 8, 2022 09:51
Show Gist options
  • Save vasilii-b/83b0dbbcc5f36072ba2c161358e789b2 to your computer and use it in GitHub Desktop.
Save vasilii-b/83b0dbbcc5f36072ba2c161358e789b2 to your computer and use it in GitHub Desktop.
Download ZIP
Magento 2 - Check database size
Raw
check-biggest-db-tables.sql
# Shows which datbase tables are the biggest ones and the most dead space
SELECT
table_schema,
table_name AS 'Table',
ROUND(
(
(data_length) / 1024 / 1024
),
2
) AS 'DataSpace',
ROUND(
(
(index_length) / 1024 / 1024
),
2
) AS 'IndexSpace',
ROUND(
(
(data_length + index_length) / 1024 / 1024
),
2
) AS 'TableSize',
ROUND(
(
(data_free) / 1024 / 1024
),
2
) AS 'DeadSpace',
ROUND(
(
(
data_length + index_length + data_free
) / 1024 / 1024
),
2
) AS 'DiskSpace',
ROUND(
(
(data_free) / 1024 / 1024
)/(
(
data_length + index_length + data_free
) / 1024 / 1024
),
2
) * 10 as 'WastedPercent'
FROM
information_schema.TABLES
WHERE
table_schema != 'jmc'
union
SELECT
table_schema,
'all',
SUM(
ROUND(
(
(data_length) / 1024 / 1024
),
2
)
) AS 'DataSpace',
SUM(
ROUND(
(
(index_length) / 1024 / 1024
),
2
)
) as 'IndexSpace',
SUM(
ROUND(
(
(data_length + index_length) / 1024 / 1024
),
2
)
) as 'TableSize',
SUM(
ROUND(
(
(data_free) / 1024 / 1024
),
2
)
) as 'DeadSpace',
SUM(
ROUND(
(
(
data_length + index_length + data_free
) / 1024 / 1024
),
2
)
) as 'DiskSpace',
(
SUM(
ROUND(
(
(data_length + index_length) / 1024 / 1024
),
2
)
)
)/(
SUM(
ROUND(
(
(
data_length + index_length + data_free
) / 1024 / 1024
),
2
)
)
) * 10 as 'WastedPercent'
FROM
information_schema.TABLES
WHERE
table_schema = 'jmc'
GROUP BY
table_schema
ORDER BY
7 DESC
LIMIT
30;
Raw
check-db-disk-size.sh
# Looking for how much disk space takes the MySQL instance
df -h | grep 'mysql\|Filesystem'
Raw
Show database size.sql
SELECT table_schema "Data Base Name",
SUM( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
SUM( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment