search

matrix-org / rust-synapse-compress-state

A tool to compress some state in a Synapse instance's database
https://pypi.org/project/synapse-auto-compressor/
Apache License 2.0
142 stars 32 forks source link

Tried to radically expand row count (spambots join) #89

Open grinapo opened 2 years ago

grinapo commented 2 years ago

Describe the bug

Processing room !NrRVEWbxnLuHdvlPhM:grin.hu... into c2022-05-27_16:04:49_compress.sql                                 
Fetching state from DB for room '!NrRVEWbxnLuHdvlPhM:grin.hu'...                                                      
  [20s] 1583038 rows retrieved                                                                                        
Got initial state from database. Checking for any missing state groups...
No missing state groups                                                                                               
Number of state groups: 17197                             
Number of rows in current table: 1583029             
Compressing state...                                                                                                  
[00:01:23] ____________________ 17197/17197 state groups 
[00:00:00] ____________________ 0/17197 state groups                                                                  
Number of rows after compression: 114710406 (7246.26%)
Compression Statistics:                           
  Number of forced resets due to lacking prev: 12690      
  Number of compressed rows caused by the above: 113861996
  Number of state groups changed: 14744                   
[01:03:19] ____________________ 17197/17197 state groups
Checking that state maps match...                                                                                     
[00:00:16] ____________________ 17197/17197 state groups  
New state map matches old one

Looking at the SQL it seems to be related to spambot activity there:

BEGIN;
DELETE FROM state_group_edges WHERE state_group = 2559052;
DELETE FROM state_groups_state WHERE state_group = 2559052;
INSERT INTO state_groups_state (state_group, room_id, type, state_key, event_id) VALUES
     (2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.power_levels$$, $$$$, $$$xIVAx9BlD21GEGxwFzAZSr3Z58pB3ZETFgGtEUIaASo$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.avatar$$, $$$$, $$$Eu352JMdRbmwC4I8wIOWtqXCGOnlhmfRqyO6cEfG8oc$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.history_visibility$$, $$$$, $$$H5u88bLynVJ-r7dSm4PKpZe9jljLzlSZVs0jOhjSXO0$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.canonical_alias$$, $$$$, $$$iLo9NBJwQleo5fVznsE5nDFG9xB_LvgybR1BgTLYyDA$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.create$$, $$$$, $$$VK7umGBPO8jCQfhiDixa0lTkD5QOwLZFCm2qRHevyJQ$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.name$$, $$$$, $$$cUHURYensmbiQqUsL6n15giZoFNsmLAcIa1oscs3DGo$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.join_rules$$, $$$$, $$$Oo6DH3LzHcLls_Dm9BhNiq2s5DM-9IHFnvMo6tIob9A$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.topic$$, $$$$, $$$C8kRrBjPaNLvf-8GamgPySDIXEf0qdNv-Qs9MY6J2rg$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.member$$, $$@id9q8f9ojo:matrix.litness.de$$, $$$sviZQHi-M0ga35zzvQ6RhlBwGavEKbTevW53Fhf3b4Q$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.member$$, $$@spamboti9mlixvpsm:shadowmind.de$$, $$$jRAy3wN65_l0GJs0cjnAq4YPd7Hzx-dKVa4OGN0lzio$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.member$$, $$@spambotlnm7qjmh8v:matrix.evildayz.com$$, $$$7MBcBsY3eN9k3ZcN80qojDAPmg0IOMKIpK0GKUIhtlQ$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.member$$, $$@spambotmp6z52lreo:ryne.moe$$, $$$Q-WQRfJrmdNfA1mUXS58UIOdcl23dZ7QaMwzXMIczrk$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.member$$, $$@spambotxyninzyatu:priv8.chat$$, $$$FDGQVo3SfkP6s_lyLkpPWueR3Ag9n0G1zRQUyolfwGU$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.member$$, $$@43xlg4kuxn:matrix.chernigiv-rada.gov.ua$$, $$$D7FkSkIEdBXR-6jllYhi7HdeHdzQeCaT8K2A9DTF4sE$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.member$$, $$@spambotw0mvrixaua:genericgaming.com$$, $$$eDoXmdMln0lW7SKLEaxwHFPHmCoIRYKSKuDq-ciKBZ4$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.member$$, $$@spambot79g0r0p0ky:sitp.happyforever.com$$, $$$30RXQ6qiAPEPV-67HtNJYtv1RxiohDvXZStLBdiFK78$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.member$$, $$@zxb2m32qes:firc.de$$, $$$N_Y68z5f3NGGJEq3o7JkF0BwTyTe8MZw9K-fTAJq7FU$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.member$$, $$@spambotkapzat9p0l:matrix.kyiv.dcomm.net.ua$$, $$$w3jGkXEkD4Dpnp6qyV-2HLc0htZrrPYX0qfRPjWEWA8$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.member$$, $$@s9aijn1a7r:agrobio-bretagne.org$$, $$$SmV9nFIvmKvm-DAEDbrnO0neE9dPgnEgUF_lbWfFWgA$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.member$$, $$@o3mmaht9mt:chat.xrzl.xyz$$, $$$nhXHq8ObVpt9hVcBtpfJ2p4tDBDl1OazrNv7N4vqKHA$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.member$$, $$@spambotxnuwwkkt8r:recosha.com$$, $$$5tPlCEsxuGLyq7qbnQgTE1XiBYZ3do26QcjatdwGxug$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.member$$, $$@spambotrhk8coifze:matrix.m4geek.com$$, $$$PKX9MNb2s4YUEVMjV_byLcZ_PlGz5R28Bf7vecDbKuo$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.member$$, $$@spambotium8q68vsr:gegen.chat$$, $$$_8J5UqtDo8dGldDpzYH6akllyRV4SOrFquOC2An-G_0$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.member$$, $$@spambot1gbe683jcx:matrix.techtino.co.uk$$, $$$DVbG7toEN7SA4m-_neRZQ5wNlUKacYni1jZ_lp0cnFg$$)
    ,(2559052, $$!NrRVEWbxnLuHdvlPhM:grin.hu$$, $$m.room.member$$, $$@6ody5v2wmu:matrix.palion.net$$, $$$853QGL8brWaYGyKYtIZR5csF2vQbITudXT0G0uFbA5k$$)

Maybe something worths looked at. :-) I do have the result SQL but it is huge.

grinapo commented 2 years ago

Do you need more data on this? It is probably not very beneficial to insert 70 times more rows as "compression".