Improve resiliency of database background index operations

Database::Reindexing.invoke has too many responsibilities that are started with one rake task:

  • looping though databases
  • creating indexes
  • removing indexes
  • reindexing manually queued indexes
  • reindexing bloated indexes

We've put this to test with CI data partitioning work and identified a few problems:

  • it first executes the create index tasks and then the removal ones. This is not great because if the index creation times out, the index will be present in the database and to re-attempt creation we need to use a different name because we can't just enqueue a new command for destruction and recreation since they will not be executed in this order.
  • it tries to create the same index without any backoff mechanism, blocking everything if exceptions are raised, requiring manual intervention to unblock it: gitlab-com/gl-infra/production#8312 (closed)
  • it wastes precious time because for each invocation it loops though the databases, executes 2 index creation tasks and 2 reindexing tasks. Reindexing should not be executed as long as there are indexes creation in the queue. Also because there's so much going on in one invocation, if the task is called late on Sunday it possible for it to complete Monday evening.
  • manual reindexing tasks are lost if the exclusive lock is not obtained: #386243 (closed)

And there's also some tech debt because we know that the task is enqueued during the weekend and we've added some checks in the code for different behavior depending on the day of the week. This should not be hardcoded in the app, but passed in when invoked.

Proposal

Break the rake task into multiple Thor tasks(has a nicer CLI):

  • rails gitlab:database:process_async_index --database main/ci --pick 2
    This should execute the removal and creation of indexes on the selected database.

  • rails gitlab:database:reindex --database main/ci --no-large-tables

    • this should be added in the cron schedule < 10 minutes after the process_async_index task because creating an index will take > 10 minutes, otherwise the index would have been created with a post deployment migration.
    • the task should specify if it wants to reindex large tables or not, this way we can add a cron schedule for Saturdays that includes large tables and for Sundays we can execute it with the --no-large-tables option.

Index creation improvements

  • Add attempts=0 and last_error=text columns to postgres_async_indexes
  • rescue errors and increase the attempts number
  • change the scheduling algorithm to PostgresAsyncIndex.to_create.order(attempts: :asc, id: :asc), this will back off the failing index and allow others to be created
  • save the exception backtrace on the last_error column since the 1 week retention in ES is way too small for debugging around holidays

With these in place the cron schedule from deploy-01-sv-gprd.c.gitlab-production.internal:/var/opt/gitlab/crond/database-reindexing would become something like this:

# Saturdays execute two index creation tasks for each invocation
12 * * * 6 rails gitlab:database:process_async_index --database main --pick 2
12 * * * 6 rails gitlab:database:process_async_index --database ci --pick 2

# Sunday execute only one instruction to reduce the risk of it spanning into Monday
12 * * * 0 rails gitlab:database:process_async_index --database main --pick 1
12 * * * 0 rails gitlab:database:process_async_index --database ci --pick 1

# 2 minutes after indexing actions start reindexing, this will be a no-op if index creation is in progress
14 * * * 6 rails gitlab:database:reindex --database main --pick 2
14 * * * 6 rails gitlab:database:reindex --database ci --pick 2

# Sunday exclude large tables from processing
14 * * * 0 rails gitlab:database:reindex --database main --pick 1 --no-large-tables
14 * * * 0 rails gitlab:database:reindex --database ci --pick 1 --no-large-tables
Edited by Marius Bobin