search

toddsundsted / ktistec

Single user ActivityPub (https://www.w3.org/TR/activitypub/) server.
GNU Affero General Public License v3.0
366 stars 20 forks source link

Loading times increasingly becoming slower #95

Closed JayVii closed 7 months ago

JayVii commented 7 months ago

Recently my ktistec instance increasingly becomes slower and slower when loading the timeline or taking any action: favoriting, sharing, posting, etc typically takes 20+ seconds. Loading subpages (search, metrics, etc) sometimes times out due to loading times.

In the logs I see a lot of warnings about slow query times. Is there anything in particular I can do about this?

felixkrohn commented 7 months ago

That sounds similar to what I experienced in #75 , maybe you can try the fix related to sqlite mentioned by Todd there - It did wonders for my installation.

toddsundsted commented 7 months ago

sqlite sometimes does a poor job of picking which index to use when creating a query plan. with a large database that can amount to terrible query performance. ANALYZE makes sqlite update some internal statistics that help it make the correct choice.

unfortunately, sometimes even that isn't enough, so i recently added hints to many of the queries (see 0d9002cb) that help it avoid making bad assumptions about the data, which should help even if the statistics aren't available.

toddsundsted commented 7 months ago

with that said, i believe the fix in #75 is the thing to do, so i'd like to close this one as a duplicate.