Closed Valodim closed 11 months ago
Ah, one more thing: The device_inbox table currently has ~4M values (which seems huge for ~130 active users, btw). And yesterday it was ~8.5M. Due to the timeouts happening, I increased the postgres connection limit. possibly because of this the delete statements started working and entries were actually deleted.
I'll keep investigating that query. It seems that postgres just isn't using the index to run it :thinking:
I noticed that postgres is spending a large amount of time in DELETE FROM statements on the device_inbox table
https://github.com/matrix-org/synapse/pull/16492 may help with this. That's in 1.96.0rc1.
ah, the issue described here goes back to https://github.com/matrix-org/synapse/issues/16479#issuecomment-1760363608. This issue caused many terabytes of useless IO for my relatively small instance and severely impacted responsiveness for some users, so glad it got sorted out quick.
nothing to see here, then. closing issue :)
Description
Some users have been complaining about slowness on my homeserver lately. Looking into it for a bit, I noticed that postgres is spending a large amount of time in DELETE FROM statements on the device_inbox table, and performs a huge amount of IO on these DELETE FROM statements:
This behavior is currently consistent: every minute or so, a bunch of postgres workers run these DELETE FROM statements, reading gigabytes of data.
This has caused consistent large amount of load since roughly the 12th, so two days after the v1.94.0 release. I don't precisely remember when I performed that update, typically I do them relatively quickly though.
This is a VM running only postgres, synapse, and a few related services. No major changes in config were made around the 12th, the last thing that happened was addition of mjolnir on august 25th. Synapse has had constant load of these DELETE FROM queries since then.
Steps to reproduce
sorry, no idea how to reproduce. if I can give any more insight I'll try to provide it, up to a call to inspect the server if that helps.
Homeserver
matrix.stratum0.org
Synapse Version
1.95.1
Installation Method
Debian packages from packages.matrix.org
Database
postgres, single server, never ported
Workers
Single process
Platform
Debian in a VM
Configuration
Presence is on :shrug:
Relevant log output
There are also errors like this in the log, relating to this statement: