device_list_stream is stored inefficiently, leading to massive disk space use #1962

Activity

1. richvdh commented on Mar 2, 2017

   

   richvdh

   on Mar 2, 2017

   MemberAuthor

   More actions

   we also SELECT user_id FROM device_lists_stream WHERE stream_id > ?, rather than SELECT DISTINCT, so the query result is potentially huge, which can make synapse use all the memory.
2. richvdh commented on Mar 3, 2017

   

   richvdh

   on Mar 3, 2017

   Last edited by richvdh

   MemberAuthor

   More actions

   To check if you are being affected by this, connect to the database and:

   SELECT count(*) c,user_id from device_lists_stream group by user_id having count(*) > 1000000;
   

   if the above shows users with many entries, you can delete them with:

   DELETE from device_lists_stream WHERE user_id=<user_id>;
   

   (Note that this may harm your ability to communicate with that user using end-to-end-encryption).
3. richvdh commented on Mar 8, 2017

   

   richvdh

   on Mar 8, 2017

   MemberAuthor

   More actions

   fixed in #1964 / #1969, I think.
4. richvdh

   closed this as completedon Mar 8, 2017