Synapse DB Maintanace

[darkdrgn2k]

DB is big

 select schemaname as table_schema,
       relname as table_name,
       pg_size_pretty(pg_relation_size(relid)) as data_size
from pg_catalog.pg_statio_user_tables
order by pg_relation_size(relid) desc;

[darkdrgn2k]

Find which rooms have biggest state_groups_state

select count(*), room_id from state_groups_state group by room_id order by count(*) desc

count   |                      room_id
----------+---------------------------------------------------
 70989351 | !iEiJZbwrOzEkZNjsYf:matrix.org
 36962954 | !YYtOqtdMtFNanKzfuQ:matrix.org
 34975380 | !cURbafjkfsMDVwdRDQ:matrix.org

Run compression on the rooms
https://github.com/matrix-org/rust-synapse-compress-state

./synapse-compress-state_x86_64-unknown-linux -p "postgresql://synapse_user:PASSWORD@localhost/synapse" -r '!iEiJZbwrOzEkZNjsYf:matrix.org' -o out_big.sql -t
psql synapse < out_big.data

[darkdrgn2k]

Script to purge unused data

User MUST be server admin

# Get Token
USERNAME="@username:tomesh.net" 
TOKEN=$(sudo -u postgres -i psql -d synapse -A -t  -c "select token from access_tokens where user_id='$USERNAME' limit 1;")


# List all rooms
curl -k --header "Authorization: Bearer $TOKEN" \
    'https://localhost:8448/_synapse/admin/v1/rooms?limit=4000' > roomlist.json


# Find rooms with 0 local members in it 
jq '.rooms[] | select(.joined_local_members == 0) | .room_id' < roomlist.json > to_purge.txt

# Load them into a variable
rooms=$(cat to_purge.txt)

# Purge them all
for room_id in $rooms;
do
  echo Purging $room_id
   room_id=$(echo $room_id | tr -d \")
  curl -k --header "Authorization: Bearer $TOKEN" \
    -X DELETE -H "Content-Type: application/json" -d "{\"purge\": true}" \
    "https://localhost:8448/_synapse/admin/v1/rooms/$room_id"
done

[darkdrgn2k]

script to purge 3 years worth of data from all rooms

# Get Token
USERNAME="@username:tomesh.net" 
TOKEN=$(sudo -u postgres -i psql -d synapse -A -t  -c "select token from access_tokens where user_id='$USERNAME' limit 1;")


# List all rooms
curl -k --header "Authorization: Bearer $TOKEN" \
    'https://localhost:8448/_synapse/admin/v1/rooms?limit=4000' > roomlist.json


FROMDATE="-36 month"
TIME=$(date "+%s%N" -d "$FROMDATE" | cut -b1-13) 

jq '.rooms[] | select(.joined_local_members > 0) | .room_id' < roomlist.json > to_trim.txt
rooms=$(cat to_trim.txt)

for room_id in $rooms;
do
   room_id=$(echo $room_id | tr -d \")
curl -k --header "Authorization: Bearer $TOKEN" \
    -X POST -H "Content-Type: application/json" \
    -d '{ "delete_local_events": false, "purge_up_to_ts": $TIME}' \
    "https://localhost:8448/_synapse/admin/v1/purge_history/$room_id"

done

[darkdrgn2k]

Vacuuming

vacuum full takes a long time

VACUUM ANALYZE Should be enough

Status of vaccume

• Does not work on FULL backup

 select * from pg_stat_progress_vacuum;

List all processes (to get pid)

select * from pg_stat_activity ;

Find out what table is locked
PID is pid of vaccume

select relation::regclass from pg_locks where pid= 9543;

[darkdrgn2k]

Synapse Debugging

• Added promethus metrics
  https://github.com/matrix-org/synapse/blob/develop/docs/metrics-howto.md