aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
-rw-r--r--ansible/playbook.yml1
-rw-r--r--ansible/roles/rrdstats/defaults/main.yml6
-rw-r--r--ansible/roles/rrdstats/handlers/main.yml7
-rw-r--r--ansible/roles/rrdstats/meta/main.yml4
-rw-r--r--ansible/roles/rrdstats/tasks/main.yml99
-rw-r--r--ansible/roles/rrdstats/templates/generate-rrd-stats.sh.j2146
-rw-r--r--ansible/roles/rrdstats/templates/generate-rrdtool-stats.service.j226
-rw-r--r--ansible/roles/rrdstats/templates/generate-rrdtool-stats.timer.j212
-rw-r--r--ansible/roles/rrdstats/vars/main.yml9
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