Use sql_exporter to monitor rails queue lengths

diff --git a/cookbooks/db/recipes/master.rb b/cookbooks/db/recipes/master.rb
index 636cd60dc9507c8e5bc2d5cee672f0591fbc6b85..eafc7db133b70ffcfb98f86d284ad634f5123ee8 100644 (file)
--- 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 (file)
index 0000000..8d4990d
--- /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 b7bc87f3b373dc1b49abf04dc032a1595d480924..36d70e9023531d7fe6461a9b335f84a2c5b2c79d 100644 (file)
--- 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",