Thursday, June 02, 2011

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.

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}]

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
that builds a mysql command using the configuration in your database.yml file.


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(' ')
raise "Task not supported by '#{abc['adapter']}'."

In order to make this rake task accessible through capistrano, I used rubaidh's approach:
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}"

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"

voila! Now I can just
cap db:analyze_tables

Labels: , , , ,


At 10:45 AM , Blogger Konstantin Gredeskoul said...

What about:

mysqlcheck -Aa -uroot -p

to run analyze table for all databases and tables (including InnoDB) on a running server?

Available in MySQL 3.23.38 and later.

At 11:46 AM , Blogger Steve said...

That's a good alternative. You could just swap out the guts of the rake command with that.


Post a Comment

Subscribe to Post Comments [Atom]

<< Home