Skip to content

Instantly share code, notes, and snippets.

@vasilii-b

vasilii-b/check-db-size.sh

Last active March 3, 2023 07:56
Show Gist options
  • Save vasilii-b/b0457bf8beeded0c51a24bff99d54b6c to your computer and use it in GitHub Desktop.
Save vasilii-b/b0457bf8beeded0c51a24bff99d54b6c to your computer and use it in GitHub Desktop.
Download ZIP
Check database size on disk and see the table sizes with SQL
Raw
check-db-size.sh
df -h | grep 'mysql\|Filesystem'
Raw
check-db-size.sql
# Show the DB size overall
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;
# show the largest database tables (top 30):
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 / 1024), 2) AS 'TableSize (Gb)', 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 5 DESC LIMIT 30;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment