Deploy rrdtool-based statistics

9 files changed, 310 insertions, 0 deletions

diff --git a/ansible/playbook.yml b/ansible/playbook.yml
index 17f71b2..aa9a336 100644
--- a/ansible/playbook.yml
+++ b/ansible/playbook.yml
@@ -28,6 +28,7 @@
 - name: Deploy our monitoring stack
   hosts: monitoring
   roles:
+    - rrdstats
     - prometheus
     - prometheus-blackbox-exporter
 
diff --git a/ansible/roles/rrdstats/defaults/main.yml b/ansible/roles/rrdstats/defaults/main.yml
new file mode 100644
index 0000000..4a6266b
--- /dev/null
+++ b/ansible/roles/rrdstats/defaults/main.yml
@@ -0,0 +1,6 @@
+---
+rrdstats_script_path: /opt/pydis/generate-rrdtool-stats.sh
+rrdstats_env_path: /etc/opt/pydis-rrdstats.env
+
+rrdstats_pg_username: rrdtool
+rrdstats_pg_password: "{{ vault_rrdstats_pg_password }}"
diff --git a/ansible/roles/rrdstats/handlers/main.yml b/ansible/roles/rrdstats/handlers/main.yml
new file mode 100644
index 0000000..40f0095
--- /dev/null
+++ b/ansible/roles/rrdstats/handlers/main.yml
@@ -0,0 +1,7 @@
+---
+- name: Restart the rrdstats timer
+  ansible.builtin.service:
+    name: generate-rrdtool-stats.timer
+    state: restarted
+  tags:
+    - role::rrdstats
diff --git a/ansible/roles/rrdstats/meta/main.yml b/ansible/roles/rrdstats/meta/main.yml
new file mode 100644
index 0000000..a7a62e2
--- /dev/null
+++ b/ansible/roles/rrdstats/meta/main.yml
@@ -0,0 +1,4 @@
+---
+dependencies:
+  - postgres
+  - systemd
diff --git a/ansible/roles/rrdstats/tasks/main.yml b/ansible/roles/rrdstats/tasks/main.yml
new file mode 100644
index 0000000..9fe4571
--- /dev/null
+++ b/ansible/roles/rrdstats/tasks/main.yml
@@ -0,0 +1,99 @@
+---
+- name: Install rrdtool
+  ansible.builtin.package:
+    name: rrdtool
+    state: present
+  tags:
+    - role::rrdstats
+
+- name: Template the statistics script
+  ansible.builtin.template:
+    src: generate-rrd-stats.sh.j2
+    dest: "{{ rrdstats_script_path }}"
+    owner: root
+    group: root
+    mode: "0555"
+  tags:
+    - role::rrdstats
+
+- name: Configure PostgreSQL user
+  become: true
+  become_user: postgres
+  community.postgresql.postgresql_user:
+    comment: Python Discord RRDTool-based longterm statistic gatherer
+    conn_limit: 2
+    name: "{{ rrdstats_pg_username }}"
+    password: "{{ rrdstats_pg_password }}"
+    state: present
+  tags:
+    - role::rrdstats
+
+- name: Configure PostgreSQL privileges to connect to database
+  become: true
+  become_user: postgres
+  community.postgresql.postgresql_privs:
+    database: metricity
+    objs: metricity
+    privs: CONNECT
+    role: "{{ rrdstats_pg_username }}"
+    type: database
+  tags:
+    - role::rrdstats
+
+- name: Configure PostgreSQL privileges to read tables
+  become: true
+  become_user: postgres
+  community.postgresql.postgresql_privs:
+    database: metricity
+    role: "{{ rrdstats_pg_username }}"
+    # Unfortunately we cannot be more granular here.
+    # https://github.com/ansible/ansible-modules-core/issues/1529
+    # -> https://github.com/ansible/ansible/issues/18955
+    # -> https://github.com/ansible-collections/community.general/issues/118
+    # -> https://github.com/ansible-collections/community.postgresql/issues/15
+    # Otherwise, we would need:
+    # - messages: id, channel_id, created_at
+    # - threads: id, archived
+    # - users: id, in_guild
+    # The `id` columns are almost purely needed for counting.
+    privs: SELECT
+    objs: messages,threads,users
+  tags:
+    - role::rrdstats
+
+- name: Template environment variables
+  ansible.builtin.copy:
+    content: |
+      DB_DSN="postgresql://{{ rrdstats_pg_username }}:{{ rrdstats_pg_password }}@localhost:5432/metricity?application_name=rrdstats"
+    dest: "{{ rrdstats_env_path }}"
+    # This must only be readable by root because systemd will read it and
+    # inject the environment variables into the target process.
+    owner: root
+    group: root
+    mode: "0400"
+  tags:
+    - role::rrdstats
+
+- name: Template systemd units
+  ansible.builtin.template:
+    src: generate-rrdtool-stats.{{ item }}.j2
+    dest: /etc/systemd/system/generate-rrdtool-stats.{{ item }}
+    owner: root
+    group: root
+    mode: "0444"
+  tags:
+    - role::rrdstats
+  loop:
+    - service
+    - timer
+  notify:
+    - Reload the systemd daemon
+    - Restart the rrdstats timer
+
+- name: Start and enable the timer
+  ansible.builtin.service:
+    name: generate-rrdtool-stats.timer
+    enabled: true
+    state: started
+  tags:
+    - role::rrdstats
diff --git a/ansible/roles/rrdstats/templates/generate-rrd-stats.sh.j2 b/ansible/roles/rrdstats/templates/generate-rrd-stats.sh.j2
new file mode 100644
index 0000000..e26966d
--- /dev/null
+++ b/ansible/roles/rrdstats/templates/generate-rrd-stats.sh.j2
@@ -0,0 +1,146 @@
+#!/bin/sh
+
+set -eu
+
+HERE="$(dirname "$0")"
+DATA="$HERE/data"
+
+MESSAGES_RRD="$DATA/messages.rrd"
+THREADS_RRD="$DATA/threads.rrd"
+USERS_RRD="$DATA/users.rrd"
+LAST_RUN_FILE="$DATA/last_run"
+
+if [ "$DB_DSN" = "" ]; then
+    echo "The \$DB_DSN parameter must be set"
+    exit 1
+fi
+
+if [ ! -d "$DATA" ]; then
+    mkdir "$DATA"
+fi
+
+#  critter crunch crumbling
+if [ ! -f "$MESSAGES_RRD" ]; then
+    rrdtool create "$MESSAGES_RRD" \
+        --step 5m \
+        DS:python_general:ABSOLUTE:5m:0:U \
+        DS:python_help:ABSOLUTE:5m:0:U \
+        DS:off_topic_0:ABSOLUTE:5m:0:U \
+        DS:off_topic_1:ABSOLUTE:5m:0:U \
+        DS:off_topic_2:ABSOLUTE:5m:0:U \
+        DS:staff_social:ABSOLUTE:5m:0:U \
+        DS:staff_log:ABSOLUTE:5m:0:U \
+        DS:others:ABSOLUTE:5m:0:U \
+        DS:off_topic:COMPUTE:off_topic_0,off_topic_1,off_topic_2,+,+ \
+        DS:total:COMPUTE:python_help,off_topic,+,staff_social,staff_log,+,others,python_general,+,+,+ \
+        RRA:AVERAGE:0.5:5m:3d \
+        RRA:AVERAGE:0.5:1h:1M \
+        RRA:AVERAGE:0.5:6h:6M \
+        RRA:AVERAGE:0.5:1d:15y
+fi
+
+if [ ! -f "$THREADS_RRD" ]; then
+    rrdtool create "$THREADS_RRD" \
+        --step 5m \
+        DS:archived:GAUGE:5m:0:U \
+        DS:unarchived:GAUGE:5m:0:U \
+        RRA:LAST:0.5:5m:3d \
+        RRA:LAST:0.5:1h:1M \
+        RRA:LAST:0.5:6h:6M \
+        RRA:LAST:0.5:1d:15y
+fi
+
+if [ ! -f "$USERS_RRD" ]; then
+    rrdtool create "$USERS_RRD" \
+        --step 5m \
+        DS:users:GAUGE:5m:0:U \
+        RRA:LAST:0.5:5m:3d \
+        RRA:LAST:0.5:1h:1M \
+        RRA:LAST:0.5:6h:6M \
+        RRA:LAST:0.5:1d:15y
+fi
+
+now="$(date +%s)"
+if [ -f "$LAST_RUN_FILE" ]; then
+    last_run="$(cat "$LAST_RUN_FILE")"
+else
+    last_run="$(( $now - 60 * 1 ))"
+fi
+
+# Messages
+# At this point I would like to mention that Snowflake and its complete
+# inability to handle big integer IDs is a really annoying waste of memory. Oh
+# well.
+#
+# There is some way to make this SQL command directly output things in the rrdtool update
+# format, but I'm not there yet. Chris could probably do it. I still have to learn some SQL.
+psql --tuples-only --csv "$DB_DSN" -c "
+    WITH messages_by_category AS (
+        SELECT
+            id,
+    	   CASE WHEN channel_id = '267624335836053506' THEN 'python_general'
+    	        WHEN channel_id = '1035199133436354600' THEN 'python_help'
+    	        WHEN channel_id = '291284109232308226' THEN 'off_topic_0'
+                 WHEN channel_id = '463035241142026251' THEN 'off_topic_1'
+                 WHEN channel_id = '463035268514185226' THEN 'off_topic_2'
+    	        WHEN channel_id IN ('365960823622991872', '563594791770914816', '464469101889454091', '385474242440986624', '714214212200562749', '720668923636351037', '473092532147060736', '775412552795947058', '305126844661760000', '822920136150745168', '798959130634747914', '822853512709931008', '551789653284356126') THEN 'staff_social'
+    	        WHEN channel_id IN ('649243850006855680', '1014943924185473094', '467752170159079424', '282638479504965634', '833371042046148738', '528976905546760203', '640292421988646961') THEN 'staff_log'
+    	        ELSE 'others'
+    	    END AS category
+        FROM messages
+        WHERE created_at > to_timestamp($last_run)
+    )
+    SELECT COUNT(id), category FROM messages_by_category GROUP BY category
+" | awk -F, '
+  BEGIN {
+    counts["python_general"]=0
+    counts["python_help"]=0
+    counts["off_topic_0"]=0
+    counts["off_topic_1"]=0
+    counts["off_topic_2"]=0
+    counts["staff_social"]=0
+    counts["staff_log"]=0
+    counts["others"]=0
+  }
+
+  {
+    counts[$2]=$1
+  }
+
+  END {
+    print "N:" counts["python_general"] ":" counts["python_help"] ":" counts["off_topic_0"] ":" counts["off_topic_1"] ":" counts["off_topic_2"] ":" counts["staff_social"] ":" counts["staff_log"] ":" counts["others"]
+  }
+' | xargs -t rrdtool update --template "python_general:python_help:off_topic_0:off_topic_1:off_topic_2:staff_social:staff_log:others" "$MESSAGES_RRD" --
+
+# Threads
+psql --tuples-only --csv "$DB_DSN" -c "
+  WITH threads_by_archived AS (
+    SELECT CASE WHEN archived THEN 'archived' 
+                ELSE 'unarchived' 
+           END AS archived 
+    FROM threads
+  ) 
+  SELECT COUNT(*), archived FROM threads_by_archived GROUP BY archived
+" | awk -F, '
+  BEGIN {
+    counts["archived"]=0
+    counts["unarchived"]=0
+  }
+  {
+    counts[$2]=$1
+  }
+
+  END {
+    print "N:" counts["archived"] ":" counts["unarchived"]
+  }
+' | xargs -t rrdtool update --template "archived:unarchived" "$THREADS_RRD" --
+
+# Users
+# The sed call works around python-discord/metricity#157 by explicitly telling rrdtool 
+# that the current amount of users is unknown. It may be removed once this is fixed upstream.
+(
+ echo -n 'N:' &&
+ psql --tuples-only --csv "$DB_DSN" -c 'SELECT COUNT(*) FROM users WHERE in_guild = true'
+) | sed -e 's/^N:0$/N:U/' | xargs -t rrdtool update "$USERS_RRD" --
+
+echo -n "$now" > "$LAST_RUN_FILE"
diff --git a/ansible/roles/rrdstats/templates/generate-rrdtool-stats.service.j2 b/ansible/roles/rrdstats/templates/generate-rrdtool-stats.service.j2
new file mode 100644
index 0000000..6528600
--- /dev/null
+++ b/ansible/roles/rrdstats/templates/generate-rrdtool-stats.service.j2
@@ -0,0 +1,26 @@
+# Ansible managed
+[Unit]
+Description = Generate Python Discord statistics via rrdtool
+After = postgresql.service
+Requires = postgresql.service
+
+[Service]
+ExecStart = {{ rrdstats_script_path }}
+DynamicUser = true
+User = pydis-rrdstats
+EnvironmentFile = {{ rrdstats_env_path }}
+
+# Sandboxing
+NoNewPrivileges = true
+
+# Resource control
+# Most of the resource expenditure of this unit will be dished out in the psql connection.
+# The rest here is just to ensure it doesn't impede the server's stability.
+Nice = 10
+# Default of 6
+IOSchedulingClass = 5
+CPUQuota = 10%
+MemoryMax = 100M
+TasksMax = 20
+
+# vim: ft=dosini.jinja2:
diff --git a/ansible/roles/rrdstats/templates/generate-rrdtool-stats.timer.j2 b/ansible/roles/rrdstats/templates/generate-rrdtool-stats.timer.j2
new file mode 100644
index 0000000..92e6bab
--- /dev/null
+++ b/ansible/roles/rrdstats/templates/generate-rrdtool-stats.timer.j2
@@ -0,0 +1,12 @@
+# Ansible managed
+
+[Unit]
+Description = Generate rrdtool stats minutely
+
+[Timer]
+OnCalendar = minutely
+
+[Install]
+WantedBy = timers.target
+
+# vim: ft=dosini.j2:
diff --git a/ansible/roles/rrdstats/vars/main.yml b/ansible/roles/rrdstats/vars/main.yml
new file mode 100644
index 0000000..8322e1e
--- /dev/null
+++ b/ansible/roles/rrdstats/vars/main.yml
@@ -0,0 +1,9 @@
+$ANSIBLE_VAULT;1.1;AES256
+66633334396438376638326638613331643830613237366237393239346535623930326165393537
+3637353837613463303963323134333035383034353632380a636666383737353735313434396639
+39383564393731316335653435326635326262306237653064643364366535303663633463333563
+3361636332626264630a353436336162313937656233636135356264653638666566346663623439
+37613832326332383163396537376363616466353165353132303665633232636536623731653238
+66353463383164623061613738653162316234393564373864346661383962613764656264393063
+62303530393862306632323464316635393261333138333233613333626331346337383233663034
+35393162326639656361