files/scripts/optimize_tables.rb


1
2
3
4
5
6
7
8
9
10
11
12
13

#!/usr/bin/env ruby

# set home as we runit as weekly cron, where HOME is /
ENV['HOME'] = '/root'
tables = %x(mysql -Bse "SELECT TABLE_SCHEMA,TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0 AND ENGINE IN ('MyISAM','InnoDB','ARCHIVE')")
tables = tables.split(/\n/)
tables.each { |table|
    tableitems = table.chomp.split(/\t/)
    system "mysql #{tableitems[0]} -Bse \"OPTIMIZE TABLE \\`#{tableitems[0]}\\`.\\`#{tableitems[1]}\\`\" | grep -q OK"
    if $?.to_i > 0 then
        puts "error while optimizing #{tableitems[0]}.#{tableitems[1]}"
    end
}