The user_ips table can grow too much and cause performance problems

[spantaleev]

Description

On a server of mine, due to using matrix-corporal and another tool which logs in via matrix-synapse-shared-secret-auth, the server has seen quite a lot of logins (1.7 million records in user_ips for < 100 users).

It has taken a few months to accumulate so many records, but nevertheless, the user_ips table is growing continuously.

Due to that, API requests for /_matrix/client/unstable/devices become incredibly slow (taking around ~30 seconds). Perhaps other Synapse code which touches user_ips is slowed down in a similar way.

I realize that my usage is a little odd (lots of automated user logins), but still:

• maybe the user_ips table shouldn't grow indefinitely?

• maybe logging out cleanly should purge user_ips for that token automatically?

• maybe the /_matrix/client/unstable/devices API endpoint can be more efficient in how it retrieves data from user_ips? Or maybe user_ips can benefit from some additional index (possibly not -- indexes looked okay to me)?

Version information

• Version: 0.34.0 (Python 3)

• Install method: Official Docker image

[neilisfragile]

Is the slow call to devices definitely due to user_ips? I'm surprised that you would see 30s queries from a table of 1.7M unless the indexes really are incorrect.

#4115 relates to some ideas on general perf for device management - could it be related for you?

[spantaleev]

Looks like #4115 has the possibility to cause performance trouble, but not on login, and not in a synchronous manner.

After cleaning up the user_ips table, the /_matrix/client/unstable/devices API endpoint became fast again.

[neilisfragile]

Okay, I think we can spend some time on this. The idea being to prune inactive entries after a configurable time frame.

[richvdh]

This should have been fixed by #4370, which landed in 0.99.0.