Improve performance of background update `populate_user_directory_process_rooms`

[H-Shay]

Relevant code begins here:

synapse/synapse/storage/databases/main/user_directory.py

Line 87 in 335f52d

self.db_pool.updates.register_background_update_handler(

This process can cause excess load on the DB, perhaps need to reconfigure the batch size to make it less resource-intensive.

[clokep]

The main issue (from my understanding) is that it reports based on the number of rooms which are processed, but rooms vary in how "expensive" they are. Maybe using something like rooms * users_in_room would be better?

That might allow the background update algorithm to process it a bit more reasonably.

[richvdh]

For background: we saw a significant slowdown on matrix.org :

This appears to have aligned with some work done by the populate_user_directory_process_rooms background database update. In particular, the update starts with a big delete:

synapse/synapse/storage/databases/main/user_directory.py

Lines 583 to 595 in 335f52d

	async def delete_all_from_user_dir(self) -> None:
	"""Delete the entire user directory"""
	def _delete_all_from_user_dir_txn(txn: LoggingTransaction) -> None:
	txn.execute("DELETE FROM user_directory")
	txn.execute("DELETE FROM user_directory_search")
	txn.execute("DELETE FROM users_in_public_rooms")
	txn.execute("DELETE FROM users_who_share_private_rooms")
	txn.call_after(self.get_user_in_directory.invalidate_all)
	await self.db_pool.runInteraction(
	"delete_all_from_user_dir", _delete_all_from_user_dir_txn
	)

... which, based on postgres's slow query logs, completed at 21:12 UTC on 2023-03-13, exactly coinciding with the first spike in the above graph.

The main issue (from my understanding) is that it reports based on the number of rooms which are processed, but rooms vary in how "expensive" they are. Maybe using something like rooms * users_in_room would be better?

I'm not convinced that's the main issue. Certainly the updates are quite expensive, but empirically the delete caused a problem too - and it's much harder to break down into small steps.

To be determined here:

• why does a delete in users_who_share_private_rooms cause a significant slowdown in event persistence
• this was far from the only spike in event persistence times over the course of the evening: there were others at 20:15 and a long slowdown between 18:00 and 18:25. It is currently unknown what caused them.

[reivilibre]

We should use TRUNCATE instead of unconditional DELETE FROM, which is faster on Postgres (no table scan).

[clokep]

Do we think #15316 is enough here or is there more to do?

[reivilibre]

I don't know of anything more to do here for now — do we still see trouble on Matrix.org?

[erikjohnston]

Empirically its doing ~1Hz of room processing on matrix.org, which is going to take months?

We should have a look at what queries its doing etc and see if we can speed it up. We can just turn on DEBUG logging on the background worker to see what its doing.