Lemmy.world

I’ve talked a lot about drastic solutions.

I would seriously ponder: 1) copying the table of read_post and purging the live one. Re-integrate the data later. 2) copy out community table and purge any that aren’t active in past 3 weeks, re-integrate data later. 3) add index on post_aggregates community_id, 4) if that doesn’t work… put a limit of 1 month on the PostView query cut-off as an emergency performance measure.

ok, enough of running a detuned database to reproduce problems. Just configured PostgreSQL 15 with:

# DB Version: 15
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 24 GB
# CPUs num: 4
# Connections num: 500
# Data Storage: hdd

max_connections = 500
shared_buffers = 6GB
effective_cache_size = 18GB
maintenance_work_mem = 1536MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 4
effective_io_concurrency = 2
work_mem = 6291kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

Profile generating this error when other things work

 2023-08-09T23:26:33.273105Z  WARN lemmy_server::root_span_builder: Unknown: Error occurred while creating a new object: the database system is not yet accepting connections
Aug 09 23:26:33 instance-20230117-wakereality0 lemmy_server[378512]:    0: lemmy_api_common::utils::local_user_view_from_jwt
Aug 09 23:26:33 instance-20230117-wakereality0 lemmy_server[378512]:              at crates/api_common/src/utils.rs:135
Aug 09 23:26:33 instance-20230117-wakereality0 lemmy_server[378512]:    1: lemmy_api::local_user::report_count::perform
Aug 09 23:26:33 instance-20230117-wakereality0 lemmy_server[378512]:            with self=GetReportCount { community_id: None, auth: Sensitive }
Aug 09 23:26:33 instance-20230117-wakereality0 lemmy_server[378512]:              at crates/api/src/local_user/report_count.rs:15
Aug 09 23:26:33 instance-20230117-wakereality0 lemmy_server[378512]:    2: lemmy_server::root_span_builder::HTTP request
Aug 09 23:26:33 instance-20230117-wakereality0 lemmy_server[378512]:            with http.method=GET http.scheme="http" http.host=bulletintree.com http.target=/api/v3/user/report_count otel.kind="server" request_id=3af045bf-c4d3-43a2-86d4-79802c915e20
Aug 09 23:26:33 instance-20230117-wakereality0 lemmy_server[378512]:              at src/root_span_builder.rs:16
Aug 09 23:26:33 instance-20230117-wakereality0 lemmy_server[378512]: LemmyError { message: Unknown("Error occurred while creating a new object: the database system is not yet accepting connections"), inner: Error occurred while creating a new object: the database system is not yet accepting connections, context: SpanTrace [{ target: "lemmy_api_common::utils", name: "local_user_view_from_jwt", file: "crates/api_common/src/utils.rs", line: 135 }, { target: "lemmy_api::local_user::report_count", name: "perform", fields: "self=GetReportCount { community_id: None, auth: Sensitive }", file: "crates/api/src/local_user/report_count.rs", line: 15 }, { target: "lemmy_server::root_span_builder", name: "HTTP request", fields: "http.method=GET http.scheme=\"http\" http.host=bulletintree.com http.target=/api/v3/user/report_count otel.kind=\"server\" request_id=3af045bf-c4d3-43a2-86d4-79802c915e20", file: "src/root_span_builder.rs", line: 16 }] }

test code crawl API crawl data

1. anonymous
2. exercise every sort order
3. exercise limit max, vs. 1
4. nsfw on/off
5. read posts on/off WITH data in table (artificially generate)
6. local users, lots vs. few
7. block list empty vs. big
8. deleted communities large number vs. empty
9. saved posts only, large number vs. empty
10. moderator only, large number vs. empty