10/11: Add Prometheus metrics for indexes specifically

[Christopher Baines]

cbaines pushed a commit to branch master
in repository data-service.

commit 5267bde60327a90e841056a548002e372885fcbc
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Fri Oct 9 19:42:14 2020 +0100

    Add Prometheus metrics for indexes specifically
---
 guix-data-service/metrics.scm        | 37 +++++++++++++++++++++++---
 guix-data-service/web/controller.scm | 51 +++++++++++++++++++++++++++++++-----
 2 files changed, 78 insertions(+), 10 deletions(-)

diff --git a/guix-data-service/metrics.scm b/guix-data-service/metrics.scm
index 50be722..16d7fb9 100644
--- a/guix-data-service/metrics.scm
+++ b/guix-data-service/metrics.scm
@@ -19,7 +19,8 @@
   #:use-module (ice-9 match)
   #:use-module (squee)
   #:export (fetch-high-level-table-size-metrics
-            fetch-pg-stat-user-tables-metrics))
+            fetch-pg-stat-user-tables-metrics
+            fetch-pg-stat-user-indexes-metrics))
 
 (define (fetch-high-level-table-size-metrics conn)
   ;; Adapted from https://wiki.postgresql.org/wiki/Disk_Usage
@@ -41,7 +42,6 @@ SELECT table_name,
        COALESCE(pg_tablespace.spcname,'default') AS tablespace,
        row_estimate,
        table_bytes,
-       index_bytes,
        toast_bytes
 FROM (
   SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes
@@ -75,12 +75,11 @@ FROM (
 LEFT JOIN pg_tablespace ON tablespace_id = pg_tablespace.oid")
 
   (map (match-lambda
-         ((name tablespace row-estimate table-bytes index-bytes toast-bytes)
+         ((name tablespace row-estimate table-bytes toast-bytes)
           (list name
                 tablespace
                 (or (string->number row-estimate) 0)
                 (or (string->number table-bytes) 0)
-                (or (string->number index-bytes) 0)
                 (or (string->number toast-bytes) 0))))
        (exec-query conn query)))
 
@@ -126,3 +125,33 @@ SELECT relname, seq_scan, seq_tup_read,
             (analyze-count       . ,analyze-count)
             (autoanalyze-count   . ,autoanalyze-count))))
        (exec-query conn query)))
+
+(define (fetch-pg-stat-user-indexes-metrics conn)
+  (define query
+    "
+SELECT pg_indexes.indexname,
+       pg_indexes.tablename,
+       COALESCE(pg_indexes.tablespace,'default') AS tablespace,
+       pg_stat_user_indexes.idx_scan,
+       pg_stat_user_indexes.idx_tup_read,
+       pg_stat_user_indexes.idx_tup_fetch,
+       pg_relation_size(indexrelid) AS size_in_bytes
+FROM pg_stat_user_indexes
+LEFT JOIN pg_indexes
+  ON pg_stat_user_indexes.indexrelname = pg_indexes.indexname
+ AND pg_stat_user_indexes.schemaname = pg_indexes.schemaname
+WHERE pg_stat_user_indexes.schemaname = 'guix_data_service'")
+
+  (map
+   (match-lambda
+     ((indexname tablename tablespace
+                 idx_scan idx_tup_read idx_tup_fetch
+                 size_in_bytes)
+      `((name          . ,indexname)
+        (table-name    . ,tablename)
+        (tablespace    . ,tablespace)
+        (idx-scan      . ,idx_scan)
+        (idx-tup-read  . ,idx_tup_read)
+        (idx-tup-fetch . ,idx_tup_fetch)
+        (bytes         . ,size_in_bytes))))
+   (exec-query conn query)))
diff --git a/guix-data-service/web/controller.scm 
b/guix-data-service/web/controller.scm
index a650889..4727771 100644
--- a/guix-data-service/web/controller.scm
+++ b/guix-data-service/web/controller.scm
@@ -130,7 +130,24 @@
                                                    "-"
                                                    "_"))
                                    #:labels '(name))))
-                               pg-stat-fields)))
+                               pg-stat-fields))
+
+         (pg-stat-indexes-fields '(idx-scan idx-tup-read
+                                   idx-tup-fetch bytes))
+
+         (pg-stat-indexes-metrics (map (lambda (field)
+                                         (cons
+                                          field
+                                          (make-gauge-metric
+                                           registry
+                                           (string-append
+                                            "pg_stat_indexes_"
+                                            (string-replace-substring
+                                             (symbol->string field)
+                                             "-"
+                                             "_"))
+                                           #:labels '(name))))
+                                       pg-stat-indexes-fields)))
     (lambda ()
       (letpar& ((metric-values
                  (with-thread-postgresql-connection
@@ -141,13 +158,16 @@
                 (pg-stat-user-tables-metrics
                  (with-thread-postgresql-connection
                   fetch-pg-stat-user-tables-metrics))
+                (pg-stat-user-indexes-metrics
+                 (with-thread-postgresql-connection
+                  fetch-pg-stat-user-indexes-metrics))
                 (load-new-guix-revision-job-metrics
                  (with-thread-postgresql-connection
                   select-load-new-guix-revision-job-metrics)))
 
         (for-each (match-lambda
                     ((name tablespace row-estimate
-                           table-bytes index-bytes toast-bytes)
+                           table-bytes toast-bytes)
 
                      (metric-set table-row-estimate-metric
                                  row-estimate
@@ -156,10 +176,6 @@
                                  table-bytes
                                  #:label-values `((name       . ,name)
                                                   (tablespace . ,tablespace)))
-                     (metric-set table-index-bytes-metric
-                                 index-bytes
-                                 #:label-values `((name       . ,name)
-                                                  (tablespace . ,tablespace)))
                      (metric-set table-toast-bytes-metric
                                  toast-bytes
                                  #:label-values `((name       . ,name)
@@ -183,6 +199,29 @@
                   field-values)))
              pg-stat-user-tables-metrics)
 
+        (map (lambda (field-values)
+               (let ((name (assq-ref field-values 'name))
+                     (table-name (assq-ref field-values 'table-name))
+                     (tablespace (assq-ref field-values 'tablespace)))
+                 (for-each
+                  (match-lambda
+                    (('name . _) #f)
+                    (('table-name . _) #f)
+                    (('tablespace . _) #f)
+                    ((field . value)
+                     (let ((metric (or (assq-ref pg-stat-indexes-metrics field)
+                                       (error field))))
+                       (metric-set metric
+                                   value
+                                   #:label-values
+                                   `((name       . ,name)
+                                     (table      . ,table-name)
+                                     ,@(if (eq? field 'bytes)
+                                           `((tablespace . ,tablespace))
+                                           '()))))))
+                  field-values)))
+             pg-stat-user-indexes-metrics)
+
         (for-each (match-lambda
                     ((repository-label completed count)
                      (metric-set