diff options
| -rw-r--r-- | ansible/playbook.yml | 1 | ||||
| -rw-r--r-- | ansible/roles/rrdstats/defaults/main.yml | 6 | ||||
| -rw-r--r-- | ansible/roles/rrdstats/handlers/main.yml | 7 | ||||
| -rw-r--r-- | ansible/roles/rrdstats/meta/main.yml | 4 | ||||
| -rw-r--r-- | ansible/roles/rrdstats/tasks/main.yml | 99 | ||||
| -rw-r--r-- | ansible/roles/rrdstats/templates/generate-rrd-stats.sh.j2 | 146 | ||||
| -rw-r--r-- | ansible/roles/rrdstats/templates/generate-rrdtool-stats.service.j2 | 26 | ||||
| -rw-r--r-- | ansible/roles/rrdstats/templates/generate-rrdtool-stats.timer.j2 | 12 | ||||
| -rw-r--r-- | ansible/roles/rrdstats/vars/main.yml | 9 |
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 |