Check MySql DB size through Rails

I have a Rails 2.3.8 application running over a MySql DB. Is it possible to check the actual size of the DB (in megabytes) through the Rails application? If so, can i 开发者_Python百科get a detailed view such as how many megabytes does a certain table takes?

Any thoughts?

---

If you just want the results back into a Hash, then this works pretty well:

db_name = Rails.configuration.database_configuration[Rails.env]["database"]

sql = "SELECT table_name AS name, table_rows, data_length, index_length FROM information_schema.TABLES  WHERE table_schema = '#{db_name}' ORDER BY (data_length + index_length) DESC;"

table_data = ActiveRecord::Base.connection.execute(sql).map{|r| {name: r[0], rows: r[1], data_size: r[2], index_size: r[3]}}

And, then if you're in Rails console and want to dump out the results:

c,d,i=0,0,0;table_data.each {|r| print r[:name].ljust(30),helper.number_with_delimiter(r[:rows]).rjust(16)," rows", helper.number_to_human_size(r[:data_size]).rjust(12), helper.number_to_human_size(r[:index_size]).rjust(12), helper.number_to_human_size(r[:data_size] + r[:index_size]).rjust(12),"\n";c+=r[:rows];d+=r[:data_size];i+=r[:index_size]};print "-"*90,"\n","Total".ljust(30),helper.number_with_delimiter(c).rjust(16)," rows", helper.number_to_human_size(d).rjust(12), helper.number_to_human_size(i).rjust(12), helper.number_to_human_size(d+i).rjust(12),"\n"

---

I created a gem a while back

gem 'mysql_rake_tasks', '~> 0.1.0'
bundle install

rake db:mysql:stats
+--------------------------------+---------------+-----------+----------+------------+
| Table Name                     |          Rows | Data Size | IDX Size | Total Size |
+--------------------------------+---------------+-----------+----------+------------+
| fish                           |  1.41 Million |   93.6 MB |  41.1 MB |     135 MB |
| birds                          |             0 |     16 KB |  0 Bytes |      16 KB |
| cats                           |            14 |     16 KB |  0 Bytes |      16 KB |
| schema_migrations              |             7 |     16 KB |  0 Bytes |      16 KB |
| users                          |             5 |     16 KB |    32 KB |      48 KB |
+--------------------------------+---------------+-----------+----------+------------+
|                                                                       |     135 MB |
+--------------------------------+---------------+-----------+----------+------------+
Database: mydb_development  MySQL Server Version: 5.1.58

---

You can fetch all you need from INFORMATION_SCHEMA

Some query from that page:

SELECT table_schema 'database',
concat( round( sum( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) size
FROM information_schema.TABLES
WHERE ENGINE=('MyISAM' || 'InnoDB' )
GROUP BY table_schema;