Feature request: Drop old lhma_ tables

[sroysen]

Since LHM tasks are the ones creating lhma_ tables when renaming the old tables on an online schema change, it would be nice that the same code provides an option to drop those that were created 'n' days ago.

Something that could be run like:

 bundle exec rake lhm:clean --older_than=10

[arthurnn]

@sroysen as you bringing up the point, is there any way to know if those tables are still hot or not, so we can safely drop them without locking?
I talked to @caueguerra, and they are slowly emptying the table before dropping it, to be safe and not having any contention. But I am wondering if we could get that info somehow.

Thanks

[shrirambalakrishnan]

@arthurnn I saw the note in the usage section of gem stating that Lhm won't delete the old, leftover table. This is on purpose, in order to prevent accidental data loss.

But I don't have any use case for retaining the lhma table. Can we have another parameter to the LHM.change_table method, which if set, will delete the lhma table. This will also prevent the work of manually deleting the tables or running a rake task in every staging and production environment.

[avit]

I use this in my Rakefile:

namespace :db do
  namespace :migrate do

    desc "Remove temporary tables after migration"
    task :cleanup => :environment do
      require 'lhm'
      Lhm.cleanup true
      Rake::Task['db:schema:dump'].invoke
    end

  end
end

I agree that this should not be an automatic step, but performed manually after everything is validated.

[edmund-huber]

Just dropping lhma tables might impact your availability, as at least on MySQL 5.x, and seemingly on some versions of MySQL 8.x as well, see: github/gh-ost#307 (comment)

We built a Sidekiq job that implements this protocol:

• if an lhma table, based on the datetime in the table name, is too young, then leave it alone.
• if an lhma table is not empty, then delete 1000 rows per second in (just DELETE .. LIMIT 1000; per second) until it is empty.
• if an lhma table is empty, make a note of the time (or look up said note). If it's been a few days since the table was drained, then drop the table.