Prune web hook logs older than one monthprune-web-hook-logs

This adds a recurring Sidekiq job that removes up to 50 000 old web hook
logs per hour. This will prevent the web_hook_logs table from growing
indefinitely.

Fixes https://gitlab.com/gitlab-org/gitlab-ce/issues/46120

6 files changed, 77 insertions, 1 deletions

diff --git a/app/workers/prune_web_hook_logs_worker.rb b/app/workers/prune_web_hook_logs_worker.rb
new file mode 100644
index 00000000000..76aa6e5ee20
--- /dev/null
+++ b/app/workers/prune_web_hook_logs_worker.rb
@@ -0,0 +1,26 @@
+# frozen_string_literal: true
+
+# Worker that deletes a fixed number of outdated rows from the "web_hook_logs"
+# table.
+class PruneWebHookLogsWorker
+  include ApplicationWorker
+  include CronjobQueue
+
+  # The maximum number of rows to remove in a single job.
+  DELETE_LIMIT = 50_000
+
+  def perform
+    # MySQL doesn't allow "DELETE FROM ... WHERE id IN ( ... )" if the inner
+    # query refers to the same table. To work around this we wrap the IN body in
+    # another sub query.
+    WebHookLog
+      .where(
+        'id IN (SELECT id FROM (?) ids_to_remove)',
+        WebHookLog
+          .select(:id)
+          .where('created_at < ?', 1.month.ago.beginning_of_day)
+          .limit(DELETE_LIMIT)
+      )
+      .delete_all
+  end
+end
diff --git a/changelogs/unreleased/prune-web-hook-logs.yml b/changelogs/unreleased/prune-web-hook-logs.yml
new file mode 100644
index 00000000000..433a890a000
--- /dev/null
+++ b/changelogs/unreleased/prune-web-hook-logs.yml
@@ -0,0 +1,5 @@
+---
+title: Prune web hook logs older than one month
+merge_request:
+author:
+type: added
diff --git a/config/initializers/1_settings.rb b/config/initializers/1_settings.rb
index 3d3448cb4d6..c0e735e1c5c 100644
--- a/config/initializers/1_settings.rb
+++ b/config/initializers/1_settings.rb
@@ -338,6 +338,10 @@ Settings.cron_jobs['issue_due_scheduler_worker'] ||= Settingslogic.new({})
 Settings.cron_jobs['issue_due_scheduler_worker']['cron'] ||= '50 00 * * *'
 Settings.cron_jobs['issue_due_scheduler_worker']['job_class'] = 'IssueDueSchedulerWorker'
 
+Settings.cron_jobs['prune_web_hook_logs_worker'] ||= Settingslogic.new({})
+Settings.cron_jobs['prune_web_hook_logs_worker']['cron'] ||= '0 */1 * * *'
+Settings.cron_jobs['prune_web_hook_logs_worker']['job_class'] = 'PruneWebHookLogsWorker'
+
 #
 # Sidekiq
 #
diff --git a/db/migrate/20180626161807_index_web_hook_logs_created_at.rb b/db/migrate/20180626161807_index_web_hook_logs_created_at.rb
new file mode 100644
index 00000000000..1de9486b00e
--- /dev/null
+++ b/db/migrate/20180626161807_index_web_hook_logs_created_at.rb
@@ -0,0 +1,18 @@
+# See http://doc.gitlab.com/ce/development/migration_style_guide.html
+# for more information on how to write migrations for GitLab.
+
+class IndexWebHookLogsCreatedAt < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  disable_ddl_transaction!
+
+  def up
+    add_concurrent_index(:web_hook_logs, :created_at)
+  end
+
+  def down
+    remove_concurrent_index(:web_hook_logs, :created_at)
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
index 0112fc726d4..9e82047675d 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -11,7 +11,7 @@
 #
 # It's strongly recommended that you check this file into your version control system.
 
-ActiveRecord::Schema.define(version: 20180626125654) do
+ActiveRecord::Schema.define(version: 20180626161807) do
 
   # These are extensions that must be enabled in order to support this database
   enable_extension "plpgsql"
@@ -2144,6 +2144,7 @@ ActiveRecord::Schema.define(version: 20180626125654) do
     t.datetime "updated_at", null: false
   end
 
+  add_index "web_hook_logs", ["created_at"], name: "index_web_hook_logs_on_created_at", using: :btree
   add_index "web_hook_logs", ["web_hook_id"], name: "index_web_hook_logs_on_web_hook_id", using: :btree
 
   create_table "web_hooks", force: :cascade do |t|
diff --git a/spec/workers/prune_web_hook_logs_worker_spec.rb b/spec/workers/prune_web_hook_logs_worker_spec.rb
new file mode 100644
index 00000000000..47feb960c66
--- /dev/null
+++ b/spec/workers/prune_web_hook_logs_worker_spec.rb
@@ -0,0 +1,22 @@
+require 'spec_helper'
+
+describe PruneWebHookLogsWorker do
+  describe '#perform' do
+    before do
+      hook = create(:project_hook)
+
+      5.times do
+        create(:web_hook_log, web_hook: hook, created_at: 2.months.ago)
+      end
+
+      create(:web_hook_log, web_hook: hook, response_status: '404')
+    end
+
+    it 'removes all web hook logs older than one month' do
+      described_class.new.perform
+
+      expect(WebHookLog.count).to eq(1)
+      expect(WebHookLog.first.response_status).to eq('404')
+    end
+  end
+end