Статьи

Написание MySQL Purge Jobs на Ruby с помощью самоцвета Cleansweep

Январь 25, 2018

Первоначально Написано Биллом Кайзером

Самое простое, что вы можете сделать с реляционной базой данных, — сказать ей удалить некоторые данные:

delete from accounts where accounts.cancelled = true

Вам не нужно быть администратором, чтобы понять, что это делает. Если бы все было так просто!

РубинЕсли вы используете MySQL , так как вы начинаете собирать все больше и больше данных, проблемы удаления данных медленно раскрываются. Операции удаления могут быть очень дорогими и вызвать конфликт. Удаление слишком большого количества записей за раз может повлиять на другие сеансы, пытающиеся изменить таблицу.

Иногда вы можете обойти это, удалив кусками:

repeat until done:
delete from accounts where accounts.cancelled = true limit 500

Но такой подход может привести к ненужному повторному сканированию строк и блокировке строк в длительных транзакциях. И это только усложняется оттуда:

  • Как вы отслеживаете прогресс и настраиваете свой сценарий?
  • Что делать, если вы перемещаете данные?
  • Как избежать задержки репликации при удалении строк с высокой скоростью?

Наиболее распространенные шаблоны, с которыми мы сталкиваемся при очистке данных, это удаление по временным меткам и удаление потерянных данных. Там отличная утилита в свободном Percona Toolkit под названием PT-архиватор , который предоставляет множество функций , которые помогут вам писать сценарии продувочных эффективно и без влияния на приложения в производстве. В частности, он позволяет вам явно идентифицировать индекс для обхода при выполнении удалений.

Вот простой пример удаления строк таблицы ошибок, которые старше одной недели и не помечены для архивирования:

pt-archiver \
--source u=dbuser,D=production,t=errors,i=index_on_timestamp \
--purge \
--limit 1000 \
--commit-each \
--bulk-delete \
--where "timestamp < now() - INTERVAL 1 WEEK AND archive = 0"

Это указывает, что строки должны удаляться по 1000 раз за раз, уменьшая индекс метки времени, гарантируя, что каждая итерация начинает сканирование с того места, где была удалена последняя строка. Он делает это, сначала запрашивая коды ошибок для очистки, а затем удаляя их отдельно. В этом случае это позволяет избежать повторного сканирования строк, для которых архив не равен 0.

Построение драгоценного камня Rubyweep

В то время как pt-archiver отлично подходит для очистки при сканировании одной таблицы, инженеры New Relic не смогли использовать его для более сложных операций, удаляя потерянные данные с помощью объединений вместо подзапросов. Мы также пропустили все инструменты для сценариев, планирования, мониторинга и уведомлений, которые мы используем для наших задач Ruby on Rails.

I decided to build a Ruby gem—called cleansweep—to give us many of pt-archiver’s benefits while also leveraging features of ActiveRecord that simplified the construction of purge jobs, allowed us to easily handle purging orphan data, and was more easily integrated into our existing Ruby tools.

Using the cleansweep gem, you specify a purge in Ruby like this:

copier = CleanSweep::PurgeRunner.new \
model: Error,
index: 'index_on_timestamp' do | model |
model.where(timestamp < ? and archive = ?', 1.week.ago, false)
end
copier.execute_in_batches

This creates a PurgeRunner instance and yields to an Arel scope you can use to specify the where clause. It then iterates through the table deleting in chunks, traversing the timestamp index.

We delete orphan rows by joining with tables that reference the data we want to delete:

copier = CleanSweep::PurgeRunner.new model: Error do | model |
model.joins('left join applications app on errors.app_id = app.id')
.where('app.id is null’)
end

In this version I don’t specify an index. By default, the cleansweep gem will use the primary key or the first unique index, and traverse that index in one direction. It does this in chunks, first querying for the rows and then deleting them. You can preview the exact queries by specifying:

puts copier.print_queries

You can use this output to examine query plans and tune the criteria:

Initial Query:
SELECT  `errors`.`id`
FROM `errors` FORCE INDEX(primary)
left join applications on errors.app_id = app_id
WHERE (app.id IS NULL)
ORDER BY `errors`.`id` ASC
LIMIT 500
Chunk Query:
SELECT  `errors`.`id`
FROM `errors` FORCE INDEX(primary)
left join applications on errors.app_id = app_id
WHERE (app.id IS NULL) AND (`errors`.`id` > 1001)
ORDER BY `errors`.`id` ASC
LIMIT 500
Delete Statement:
DELETE
FROM `errors`
WHERE (`errors`.`id` in ( 1, 3, 4, … 1001))

Being able to specify the scope becomes especially helpful as the criteria get more complex. For instance, perhaps you want to delete by a timestamp, but only on certain accounts. Or maybe you want to delete orphan data, but you only need to look at a certain type of error.

More bells and whistles

The cleansweep gem also offers a number of other bells and whistles:

  • Option to throttle rate of deletes by sleeping between chunks
  • Built-in custom instrumentation for monitoring with New Relic
  • Option for printing out progress statistics at an interval you specify
  • Ability to copy rows into another table instead of deleting them
  • Ability to purge rows in one table using IDs in another table. At New Relic, we use this to purge satellite tables by building a temporary table of IDs and creating cleansweep instances for each table we need to purge that references the IDs in the temp table
  • Ability to suspend when the replication lag exceeds a certain time threshold
  • Ability to suspend when the history list size exceeds a certain threshold
  • Ability to traverse an index in reverse order, or not traverse at all
  • Accept a logger instance to use for logging. We use this to pass in a facade to our remote logger

This only scratches the surface of all the things the cleansweep gem can do. You can find details, documentation, and more examples at http://bkayser.github.com/cleansweep.

30
Share: