Skip to content
This repository has been archived by the owner on Apr 26, 2024. It is now read-only.

devices: use combined ANY clause for faster cleanup #15861

Merged
merged 1 commit into from Jul 3, 2023
Merged

devices: use combined ANY clause for faster cleanup #15861

merged 1 commit into from Jul 3, 2023

Conversation

pacien
Copy link
Contributor

@pacien pacien commented Jul 2, 2023

Old device entries for the same user were being removed in individual
SQL commands, making the batch take way longer than necessary.

This combines the commands into a single one with a IN/ANY clause.

Example of log entry before the change, regularly observed with
"log_min_duration_statement = 10000" in PostgreSQL's config:

LOG:  duration: 42538.282 ms  statement:
DELETE FROM device_lists_stream
WHERE user_id = '@someone' AND device_id = 'someid1'
AND stream_id < 123456789
;
DELETE FROM device_lists_stream
WHERE user_id = '@someone' AND device_id = 'someid2'
AND stream_id < 123456789
;
[repeated for each device ID of that user, potentially a lot...]

With the patch applied on my instance for the past couple of days, I
no longer notice overly long statements of that particular kind.

@pacien pacien requested a review from a team as a code owner July 2, 2023 13:23
@pacien
devices: use combined ANY clause for faster cleanup
b0c614e 
Old device entries for the same user were being removed in individual
SQL commands, making the batch take way longer than necessary.

This combines the commands into a single one with a IN/ANY clause.

Example of log entry before the change, regularly observed with
"log_min_duration_statement = 10000" in PostgreSQL's config:

    LOG:  duration: 42538.282 ms  statement:
    DELETE FROM device_lists_stream
    WHERE user_id = '@Someone' AND device_id = 'someid1'
    AND stream_id < 123456789
    ;
    DELETE FROM device_lists_stream
    WHERE user_id = '@Someone' AND device_id = 'someid2'
    AND stream_id < 123456789
    ;
    [repeated for each device ID of that user, potentially a lot...]

With the patch applied on my instance for the past couple of days, I
no longer notice overly long statements of that particular kind.

Signed-off-by: pacien <pacien.trangirard@pacien.net>
MatMaul
MatMaul approved these changes Jul 3, 2023
View reviewed changes
Copy link
Contributor

@MatMaul MatMaul left a comment •
edited

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

LGTM, this looks equivalent to me. Thanks for your contribution !

@MatMaul MatMaul merged commit 07d7cbf into matrix-org:develop Jul 3, 2023
37 checks passed
yingziwu added a commit to yingziwu/synapse that referenced this pull request Jul 19, 2023
@yingziwu
Merge tag 'v1.88.0' into develop
55a9f8a 
This release
 - raises the minimum supported version of Python to 3.8, as Python 3.7 is now [end-of-life](https://devguide.python.org/versions/), and
 - removes deprecated config options related to worker deployment.

See [the upgrade notes](https://github.com/matrix-org/synapse/blob/release-v1.88/docs/upgrade.md#upgrading-to-v1880) for more information.

- Revert "Stop writing to column `user_id` of tables `profiles` and `user_filters`", which was introduced in Synapse 1.88.0rc1. ([\matrix-org#15953](matrix-org#15953))

- Add `not_user_type` param to the [list accounts admin API](https://matrix-org.github.io/synapse/v1.88/admin_api/user_admin_api.html#list-accounts). ([\matrix-org#15844](matrix-org#15844))

- Pin `pydantic` to `^=1.7.4` to avoid backwards-incompatible API changes from the 2.0.0 release.
  Contributed by @PaarthShah. ([\matrix-org#15862](matrix-org#15862))
- Correctly resize thumbnails with pillow version >=10. ([\matrix-org#15876](matrix-org#15876))

- Fixed header levels on the [Admin API "Users"](https://matrix-org.github.io/synapse/v1.87/admin_api/user_admin_api.html) documentation page. Contributed by @sumnerevans at @beeper. ([\matrix-org#15852](matrix-org#15852))
- Remove deprecated `worker_replication_host`, `worker_replication_http_port` and `worker_replication_http_tls` configuration options. ([\matrix-org#15872](matrix-org#15872))

- **Remove deprecated `worker_replication_host`, `worker_replication_http_port` and `worker_replication_http_tls` configuration options.** See the [upgrade notes](https://github.com/matrix-org/synapse/blob/release-v1.88/docs/upgrade.md#removal-of-worker_replication_-settings) for more details. ([\matrix-org#15860](matrix-org#15860))
- Remove support for Python 3.7 and hence for Debian Buster. ([\matrix-org#15851](matrix-org#15851), [\matrix-org#15892](matrix-org#15892), [\matrix-org#15893](matrix-org#15893), [\matrix-org#15917](matrix-org#15917))

- Add foreign key constraint to `event_forward_extremities`. ([\matrix-org#15751](matrix-org#15751), [\matrix-org#15907](matrix-org#15907))
- Add read/write style cross-worker locks. ([\matrix-org#15782](matrix-org#15782))
- Stop writing to column `user_id` of tables `profiles` and `user_filters`. ([\matrix-org#15787](matrix-org#15787))
- Use lower isolation level when cleaning old presence stream data to avoid serialization errors. ([\matrix-org#15826](matrix-org#15826))
- Add tracing to media `/upload` code paths. ([\matrix-org#15850](matrix-org#15850), [\matrix-org#15888](matrix-org#15888))
- Add a timeout that aborts any Postgres statement taking more than 1 hour. ([\matrix-org#15853](matrix-org#15853))
- Fix the `devenv up` configuration which was ignoring the config overrides. ([\matrix-org#15854](matrix-org#15854))
- Optimised cleanup of old entries in `device_lists_stream`. ([\matrix-org#15861](matrix-org#15861))
- Update the Matrix clients link in the _It works! Synapse is running_ landing page. ([\matrix-org#15874](matrix-org#15874))
- Fix building Synapse with the nightly Rust compiler. ([\matrix-org#15906](matrix-org#15906))
- Add `Server` to Access-Control-Expose-Headers header. ([\matrix-org#15908](matrix-org#15908))

* Bump authlib from 1.2.0 to 1.2.1. ([\matrix-org#15864](matrix-org#15864))
* Bump importlib-metadata from 6.6.0 to 6.7.0. ([\matrix-org#15865](matrix-org#15865))
* Bump lxml from 4.9.2 to 4.9.3. ([\matrix-org#15897](matrix-org#15897))
* Bump regex from 1.8.4 to 1.9.1. ([\matrix-org#15902](matrix-org#15902))
* Bump ruff from 0.0.275 to 0.0.277. ([\matrix-org#15900](matrix-org#15900))
* Bump sentry-sdk from 1.25.1 to 1.26.0. ([\matrix-org#15867](matrix-org#15867))
* Bump serde_json from 1.0.99 to 1.0.100. ([\matrix-org#15901](matrix-org#15901))
* Bump types-pyopenssl from 23.2.0.0 to 23.2.0.1. ([\matrix-org#15866](matrix-org#15866))
Fizzadar added a commit to beeper/synapse-legacy-fork that referenced this pull request Jul 27, 2023
@Fizzadar
Merge tag 'v1.88.0' into merge-1.88
f2d2258 
This release
 - raises the minimum supported version of Python to 3.8, as Python 3.7 is now [end-of-life](https://devguide.python.org/versions/), and
 - removes deprecated config options related to worker deployment.

See [the upgrade notes](https://github.com/matrix-org/synapse/blob/release-v1.88/docs/upgrade.md#upgrading-to-v1880) for more information.

- Revert "Stop writing to column `user_id` of tables `profiles` and `user_filters`", which was introduced in Synapse 1.88.0rc1. ([\matrix-org#15953](matrix-org#15953))

- Add `not_user_type` param to the [list accounts admin API](https://matrix-org.github.io/synapse/v1.88/admin_api/user_admin_api.html#list-accounts). ([\matrix-org#15844](matrix-org#15844))

- Pin `pydantic` to `^=1.7.4` to avoid backwards-incompatible API changes from the 2.0.0 release.
  Contributed by @PaarthShah. ([\matrix-org#15862](matrix-org#15862))
- Correctly resize thumbnails with pillow version >=10. ([\matrix-org#15876](matrix-org#15876))

- Fixed header levels on the [Admin API "Users"](https://matrix-org.github.io/synapse/v1.87/admin_api/user_admin_api.html) documentation page. Contributed by @sumnerevans at @beeper. ([\matrix-org#15852](matrix-org#15852))
- Remove deprecated `worker_replication_host`, `worker_replication_http_port` and `worker_replication_http_tls` configuration options. ([\matrix-org#15872](matrix-org#15872))

- **Remove deprecated `worker_replication_host`, `worker_replication_http_port` and `worker_replication_http_tls` configuration options.** See the [upgrade notes](https://github.com/matrix-org/synapse/blob/release-v1.88/docs/upgrade.md#removal-of-worker_replication_-settings) for more details. ([\matrix-org#15860](matrix-org#15860))
- Remove support for Python 3.7 and hence for Debian Buster. ([\matrix-org#15851](matrix-org#15851), [\matrix-org#15892](matrix-org#15892), [\matrix-org#15893](matrix-org#15893), [\matrix-org#15917](matrix-org#15917))

- Add foreign key constraint to `event_forward_extremities`. ([\matrix-org#15751](matrix-org#15751), [\matrix-org#15907](matrix-org#15907))
- Add read/write style cross-worker locks. ([\matrix-org#15782](matrix-org#15782))
- Stop writing to column `user_id` of tables `profiles` and `user_filters`. ([\matrix-org#15787](matrix-org#15787))
- Use lower isolation level when cleaning old presence stream data to avoid serialization errors. ([\matrix-org#15826](matrix-org#15826))
- Add tracing to media `/upload` code paths. ([\matrix-org#15850](matrix-org#15850), [\matrix-org#15888](matrix-org#15888))
- Add a timeout that aborts any Postgres statement taking more than 1 hour. ([\matrix-org#15853](matrix-org#15853))
- Fix the `devenv up` configuration which was ignoring the config overrides. ([\matrix-org#15854](matrix-org#15854))
- Optimised cleanup of old entries in `device_lists_stream`. ([\matrix-org#15861](matrix-org#15861))
- Update the Matrix clients link in the _It works! Synapse is running_ landing page. ([\matrix-org#15874](matrix-org#15874))
- Fix building Synapse with the nightly Rust compiler. ([\matrix-org#15906](matrix-org#15906))
- Add `Server` to Access-Control-Expose-Headers header. ([\matrix-org#15908](matrix-org#15908))

* Bump authlib from 1.2.0 to 1.2.1. ([\matrix-org#15864](matrix-org#15864))
* Bump importlib-metadata from 6.6.0 to 6.7.0. ([\matrix-org#15865](matrix-org#15865))
* Bump lxml from 4.9.2 to 4.9.3. ([\matrix-org#15897](matrix-org#15897))
* Bump regex from 1.8.4 to 1.9.1. ([\matrix-org#15902](matrix-org#15902))
* Bump ruff from 0.0.275 to 0.0.277. ([\matrix-org#15900](matrix-org#15900))
* Bump sentry-sdk from 1.25.1 to 1.26.0. ([\matrix-org#15867](matrix-org#15867))
* Bump serde_json from 1.0.99 to 1.0.100. ([\matrix-org#15901](matrix-org#15901))
* Bump types-pyopenssl from 23.2.0.0 to 23.2.0.1. ([\matrix-org#15866](matrix-org#15866))

# -----BEGIN PGP SIGNATURE-----
#
# iQIzBAABCAAdFiEE8SRSDO7gYkSP4chELS76LzL74EcFAmS2ncYACgkQLS76LzL7
# 4EfaiA/9HUdk1tIlnQyIZDAT0d9RhmaL+KL1Fkk4wpi16QrtJ7gqLTrq1BXDxOYM
# 2bycL0yHN9gPu3f7TI0ic4p17T/vud8Fd7FoPJTkUZ7dFHsEPICtNmIp6ZkpuDYW
# Sv8QKuEeMxe98XCKxiI+zctu8wtNsrnu2RECD0zUqf5rMgbabuYnpSge2CqKftuf
# CfmYN161QjnONavQTk4iYSFmJpRZwvwoAlpMPsqkMIrhId2ko2SkPj1HBPrAFrFs
# Fq/PaVZQRZk15wnQzrLrlRAEfq/quoOSDnJSlUvMPWjsQUVP8Ug149L9oUIiwhqq
# zQtuL9dl5xGoUWMiWOP8937gVeA/lsJpcVPka3G0g3mIR8ukbHUOm2fZReV30xp8
# 81xpu8KwzDR+/Oo3INYsqoOiQ/t7Myg8sTgiJBMParuRmfqnsbdUWG8pEeUMzDYY
# 4+yzRrHo9KnHcAMEFT94rqVhgl7OQh2Fx9zduW7YOVk0wo0EBMl/rcfEsvvl//l0
# sSykqGx1XIr3Cdynp1PjCYJsYdLJzG73aSVh5qPY5sftsHIQ8DiiKX+UlSqlguMW
# 1ndkCuz3fK/+9CFGbBiixe/RKFxn0CVp3cBU6cCVzyLJerZpXrOkyMmSuZtOuIaH
# cLLGK2bQSbOegPtg4qjpL537xmk94tUiSGhEdt7M4wsHm4uRv0M=
# =QYwZ
# -----END PGP SIGNATURE-----
# gpg: Signature made Tue Jul 18 15:12:22 2023 BST
# gpg:                using RSA key F124520CEEE062448FE1C8442D2EFA2F32FBE047
# gpg: Can't check signature: No public key

# Conflicts:
#	.github/workflows/latest_deps.yml
#	.github/workflows/release-artifacts.yml
#	.github/workflows/tests.yml
#	.github/workflows/twisted_trunk.yml
#	docker/Dockerfile
#	poetry.lock
#	synapse/api/auth/base.py
#	synapse/config/experimental.py
#	synapse/handlers/pagination.py
#	synapse/handlers/room_batch.py
#	synapse/push/bulk_push_rule_evaluator.py
#	synapse/rest/admin/users.py
#	synapse/rest/client/room_batch.py
#	synapse/storage/databases/main/__init__.py
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Reviewers

@MatMaul MatMaul MatMaul approved these changes

Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.

None yet
2 participants
@pacien @MatMaul