MySQL Calculate How Much Disk Space You Wasted
Its not the new term for DBAs. MySQL has an awesome parameter
innodb-file-per-tables allows MySQL to create separate files for each tables. This helped a lot to manage the disk space in more efficient way. But when we perform a large batch job for delete or update the data in MySQL tables, you may face this fragmentation issue. Comparing with SQL server, MySQL’s fragmentation is not high. I had a similar situation where my Disk space was consuming 80% and when I check the huge files in OS, one table’s
idb file consumed 300GB+. I know it has some wasted blocks(but not actually wasted, MySQL will use this space, it’ll not return this to OS) Then I checked the
information schema to find out the data size and its index size. It was 27GB only. Then I realize, we did a batch operation to delete many billions of records in that table.
Thanks to Rolando - MySQL DBA:
When I searched the similar issue on dba stackexchange, I found this great script by Rolando. He had given this script to calculate the wasted size for a single table. I just add some South Indian Masala on top of it.(just for fun). You can use the below script to identify the wasted space/fragmented space in GB for all tables in a database.
- DB - Your Database Name
- MYSQL_DATA_DIR - Your Data directory for MySQL
- MYSQL_USER - MySQL user to query the
- MYSQL_PASS - Password for the MySQL user.
Its better to create the script as a shell file and print the output in a file.