From d3d97882fa4b21d3d896f4362cc9cdf337981824 Mon Sep 17 00:00:00 2001
From: Tom Hughes <tom@compton.nu>
Date: Thu, 2 Nov 2023 18:40:08 +0000
Subject: [PATCH] Use sql_exporter to monitor rails queue lengths

---
 cookbooks/db/recipes/master.rb                | 12 +++++++++
 .../db/templates/default/sql_rails.yml.erb    | 25 +++++++++++++++++++
 roles/db-master.rb                            |  1 +
 3 files changed, 38 insertions(+)
 create mode 100644 cookbooks/db/templates/default/sql_rails.yml.erb

diff --git a/cookbooks/db/recipes/master.rb b/cookbooks/db/recipes/master.rb
index 636cd60dc..eafc7db13 100644
--- a/cookbooks/db/recipes/master.rb
+++ b/cookbooks/db/recipes/master.rb
@@ -140,6 +140,10 @@ PLANETDIFF_PERMISSIONS = {
   "ways" => :select
 }.freeze
 
+PROMETHEUS_PERMISSIONS = {
+  "delayed_jobs" => :select
+}.freeze
+
 %w[
   acls
   active_storage_attachments
@@ -205,6 +209,7 @@ PLANETDIFF_PERMISSIONS = {
                 "cgimap" => CGIMAP_PERMISSIONS[table],
                 "planetdump" => PLANETDUMP_PERMISSIONS[table],
                 "planetdiff" => PLANETDIFF_PERMISSIONS[table],
+                "prometheus" => PROMETHEUS_PERMISSIONS[table],
                 "backup" => [:select]
   end
 end
@@ -300,3 +305,10 @@ end
 service "yearly-reindex.timer" do
   action [:enable, :start]
 end
+
+template "/etc/prometheus/exporters/sql_rails.collector.yml" do
+  source "sql_rails.yml.erb"
+  owner "root"
+  group "root"
+  mode "0644"
+end
diff --git a/cookbooks/db/templates/default/sql_rails.yml.erb b/cookbooks/db/templates/default/sql_rails.yml.erb
new file mode 100644
index 000000000..8d4990d0f
--- /dev/null
+++ b/cookbooks/db/templates/default/sql_rails.yml.erb
@@ -0,0 +1,25 @@
+collector_name: sql_rails
+
+metrics:
+  - metric_name: rails_queue_length
+    type: gauge
+    help: Rails job queue length
+    key_labels:
+      - queue
+      - status
+    values: [length]
+    query: |
+      SELECT
+        queue,
+        CASE
+        WHEN failed_at IS NOT NULL THEN 'failed'
+        WHEN locked_at IS NOT NULL THEN 'running'
+        WHEN attempts > 0 THEN 'retry'
+        ELSE 'pending'
+        END AS status,
+        COUNT(*) AS length
+      FROM
+        delayed_jobs
+      GROUP BY
+        queue,
+        status
diff --git a/roles/db-master.rb b/roles/db-master.rb
index b7bc87f3b..36d70e902 100644
--- a/roles/db-master.rb
+++ b/roles/db-master.rb
@@ -3,6 +3,7 @@ description "Role applied to all the master database server"
 
 default_attributes(
   :postgresql => {
+    :monitor_queries => true,
     :settings => {
       :defaults => {
         :archive_mode => "on",
-- 
2.45.1