Using Capistrano and Rake for DB Maintenance

I know that periodically it's a good idea to run analyze table on tables in your mysql database so they have updated statistics for the query optimizer to use.

Until now, I've generally done it manually by shelling into a machine, connecting to mysql at the command line and running the commands. Not a big deal, or a big time sink, but it seemed ripe for some automation.

#lib/tasks/database.rake
namespace :db do
desc "Runs analyze table on each of the tables in the DB so that queries can be optimized correctly"
task :analyze_tables => :environment do
cmd = build_db_command('mysql')

conn = ActiveRecord::Base.connection
tables = conn.tables.reject {|i| i == 'schema_info'}

db_command = "echo '"
db_command += tables.collect {|table| "analyze table #{table};"}.join(' ')
db_command += "' | " + cmd

puts "Running analyze tables on: #{tables.join(', ')}"
puts %x[#{db_command}]
end
end


the cool thing here is using ActiveRecord::Base.connection.tables to get a list of all the tables in your database. Once I have those, I turn them into a string of commands that I use echo to pipe to mysql.

echo 'analyze table posts; analyze table comments; analyze table users; | mysql -u deploy -psecret blog_production '


since I have other commands that interact with mysql, I created a method

build_db_command

that builds a mysql command using the configuration in your database.yml file.

#lib/tasks/database.rake

def build_db_command(executable)
abc = ActiveRecord::Base.configurations[RAILS_ENV]
case abc['adapter']
when 'mysql'
cmd = [executable]
cmd << "--host='#{abc['host']}'" unless abc['host'].blank?
cmd << "--user='#{abc['username'].blank? ? 'root' : abc['username']}'"
cmd << "--password='#{abc['password']}'" unless abc['password'].blank?
cmd << abc['database']

cmd.flatten.join(' ')
else
raise "Task not supported by '#{abc['adapter']}'."
end
end


In order to make this rake task accessible through capistrano, I used rubaidh's approach:

#config/recipies.rb
def rubaidh_run_rake(*tasks)
rake = fetch(:rake, '/usr/local/bin/rake')
rails_env = fetch(:rails_env, 'production')

tasks.each do |task|
run "cd #{latest_release}; #{rake} RAILS_ENV=#{rails_env} #{task}"
end
end

namespace :db do
desc "Run through all the tables in the DB and run analyze_table on them"
task :analyze_tables, :roles => :db, :only => { :primary => true } do
rubaidh_run_rake "db:analyze_tables"
end
end


voila! Now I can just

cap db:analyze_tables