Technical
How To Calculate Total Database Size

Simingly simple task took me a litle bit of a research to go through. Finally I was able to discover this command show table status which is Mysql and specific provides statistics for the file size per table in the Data_length and Index_length. Browsing through many blogs I found this very nice ruby command to calculate total Data_length.

 mysqlshow -u <username> --password=<password> --status <database name> | ruby -e 'puts STDIN.readlines[4..-2].inject(0) {|s,e| s += e.split("|")[7].to_i}'

to get size in megabytes to result /1024 /1024

I decided to take it further and do the same calculations using rails from script/console do

 User.find_by_sql("show table status").inject(0){|result,row| result += (row.Data_length.to_i + row.Index_length.to_i)} / 1024 / 1024
=> 6460

so in my case total database size would be 6460 MB or about 6.4 GB.

NOTE: User is one of the models that I’ve used. It’s possible to use Active Record?::Base.connection.execute but it will return not active record class but rather pure Mysql::Result which does not support neither inject, nor collect so the code would be more cumbersome. In this case just replace User model with any of your models.

This is how the previously mentioned statment would look like:

>> ActiveRecord::Base.connection.execute("show table status").each{|row| total += (row[6].to_i + row[8].to_i)}
=> #<Mysql::Result:0xb787cae8>
>> total
=> 6774608546
>> total / 1024 / 1024
=> 6460