Open darkdrgn2k opened 4 years ago
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
Script to purge unused data
# 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
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
Vacuuming
vacuum full takes a long time
VACUUM ANALYZE
Should be enough
Status of vaccume
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;
Synapse Debugging
DB is big