Performance Issues, long loading times

[JayVii]

Since upgrading to 2.0.0-8, I have tremendous performance issues, when loading the page. I.e.loading my timeline usually takes around 30 seconds, sub-pages typically are relatively quick.

Here's a measure via curl in 2.0.0-7:

time_namelookup: 0.013596
time_connect: 0.045928
time_appconnect: 0.103918
time_pretransfer: 0.104011
time_redirect: 0.000000
time_starttransfer: 0.134354
———
time_total: 0.134512

Following is a measure in 2.0.0-8:

time_namelookup: 0.010660
time_connect: 0.040303
time_appconnect: 0.088064
time_pretransfer: 0.088108
time_redirect: 0.000000
time_starttransfer: 38.087822
———
time_total: 38.088007

I tried tocurl all other 2.0.0-8 servers I could find on fediverse observer, but those do not appear to have the same performance issues as far as I can tell.

Downgrading to 2.0.0-7 seems to work flawlessly and the performance issues go away immediately.

Any ideas how to debug this issue?

[toddsundsted]

@JayVii i think this is related to sqlite3 version. i ran into it with a statically built binary i dropped a week ago to test some code, and it went away when i built normally. i can't check the version right at the moment, but you might try to upgrade (or at least change) your sqlite3 version if you can. i'll try to get more information when i can get access to my build system.

[toddsundsted]

fwiw i'm linking to 3.31.1 right now. do you know what version you're linking to?

[JayVii]

I built a docker image directly from dist without further changes (except for shard update so it would run through cleanly), so whatever version is used there. I'll check again when I get home.

[JayVii]

The builder docker image seems to use 3.32.1-r1, if I am not mistaken (alpine 3.12):

docker run crystallang/crystal:1.2.2-alpine apk add sqlite-static
fetch http://dl-cdn.alpinelinux.org/alpine/v3.12/main/x86_64/APKINDEX.tar.gz
fetch http://dl-cdn.alpinelinux.org/alpine/v3.12/community/x86_64/APKINDEX.tar.gz
(1/1) Installing sqlite-static (3.32.1-r1)
OK: 195 MiB in 53 packages

[JayVii]

The final image (alpine 3.16) uses 3.38.5-r0:

docker run alpine:latest apk add sqlite-static
fetch https://dl-cdn.alpinelinux.org/alpine/v3.16/main/x86_64/APKINDEX.tar.gz
fetch https://dl-cdn.alpinelinux.org/alpine/v3.16/community/x86_64/APKINDEX.tar.gz
(1/1) Installing sqlite-static (3.38.5-r0)
OK: 7 MiB in 15 packages

[toddsundsted]

thanks!

[JayVii]

Are there any other Logs i could provide?

[toddsundsted]

the other possible problem that just occurred to me is that your instance of the database is missing an index—it would be a little surprising in this case, but it's worth ruling out. if you open your database with sqlite3 and execute .schema that should dump the database schema. post that here and i can do a quite scan to make sure the right indexes are in place.

[JayVii]

.schema of the 2.0.0-8 database:

CREATE TABLE options (key TEXT PRIMARY KEY, value TEXT);
CREATE TABLE migrations (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE accounts (
    id integer PRIMARY KEY AUTOINCREMENT,
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL,
    username varchar(255) NOT NULL,
    encrypted_password varchar(255) NOT NULL,
    iri varchar(255) NOT NULL COLLATE NOCASE
  , timezone varchar(244) NOT NULL DEFAULT "", state text);
CREATE TABLE sqlite_sequence(name,seq);
CREATE UNIQUE INDEX idx_accounts_username
    ON accounts (username ASC);
CREATE TABLE sessions (
    id integer PRIMARY KEY AUTOINCREMENT,
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL,
    body_json text NOT NULL,
    session_key varchar(22) NOT NULL,
    account_id integer
  );
CREATE UNIQUE INDEX idx_sessions_session_key
    ON sessions (session_key ASC);
CREATE INDEX idx_sessions_account_id
    ON sessions (account_id ASC);
CREATE TABLE actors (
    "id" integer PRIMARY KEY AUTOINCREMENT,
    "created_at" datetime NOT NULL,
    "updated_at" datetime NOT NULL,
    "type" varchar(63) NOT NULL,
    "iri" varchar(255) NOT NULL COLLATE NOCASE,
    "username" varchar(255),
    "pem_public_key" text,
    "pem_private_key" text,
    "inbox" text,
    "outbox" text,
    "following" text,
    "followers" text,
    "name" text,
    "summary" text,
    "icon" text,
    "image" text,
    "urls" text,
    "deleted_at" datetime
  , blocked_at datetime, attachments text);
CREATE INDEX idx_actors_username
    ON actors (username ASC);
CREATE TABLE relationships (
    id integer PRIMARY KEY AUTOINCREMENT,
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL,
    type varchar(63) NOT NULL,
    from_iri varchar(255) NOT NULL COLLATE NOCASE,
    to_iri varchar(255) NOT NULL COLLATE NOCASE,
    confirmed boolean,
    visible boolean
  );
CREATE INDEX idx_relationships_from_iri_type_created_at
    ON relationships (from_iri ASC, type ASC, created_at DESC);
CREATE INDEX idx_relationships_to_iri_type
    ON relationships (to_iri ASC, type ASC);
CREATE TABLE collections (
    id integer PRIMARY KEY AUTOINCREMENT,
    created_at datetime NOT NULL,
    updated_at datetime NOT NULL,
    iri varchar(255) NOT NULL COLLATE NOCASE,
    items_iris text,
    total_items integer,
    first varchar(255),
    last varchar(255),
    prev varchar(255),
    next varchar(255),
    current varchar(255)
  );
CREATE UNIQUE INDEX idx_collections_iri
    ON collections (iri ASC);
CREATE TABLE objects (
    "id" integer PRIMARY KEY AUTOINCREMENT,
    "created_at" datetime NOT NULL,
    "updated_at" datetime NOT NULL,
    "type" varchar(63) NOT NULL,
    "iri" varchar(255) NOT NULL COLLATE NOCASE,
    "visible" boolean,
    "published" datetime,
    "attributed_to_iri" text COLLATE NOCASE,
    "in_reply_to_iri" text COLLATE NOCASE,
    "replies" text,
    "to" text,
    "cc" text,
    "summary" text,
    "content" text,
    "media_type" text,
    "source" text,
    "attachments" text,
    "urls" text,
    "deleted_at" datetime
  , blocked_at datetime, name text);
CREATE INDEX idx_objects_in_reply_to_iri
    ON objects (in_reply_to_iri ASC);
CREATE INDEX idx_objects_published
    ON objects (published ASC);
CREATE TABLE activities (
    "id" integer PRIMARY KEY AUTOINCREMENT,
    "created_at" datetime NOT NULL,
    "updated_at" datetime NOT NULL,
    "type" varchar(63) NOT NULL,
    "iri" varchar(255) NOT NULL COLLATE NOCASE,
    "visible" boolean,
    "published" datetime,
    "actor_iri" text COLLATE NOCASE,
    "object_iri" text COLLATE NOCASE,
    "target_iri" text COLLATE NOCASE,
    "to" text,
    "cc" text,
    "summary" text
  , undone_at datetime);
CREATE UNIQUE INDEX idx_activities_iri
    ON activities (iri ASC);
CREATE INDEX idx_activities_object_iri
    ON activities (object_iri ASC);
CREATE TABLE tasks (
    "id" integer PRIMARY KEY AUTOINCREMENT,
    "created_at" datetime NOT NULL,
    "updated_at" datetime NOT NULL,
    "type" varchar(63) NOT NULL,
    "source_iri" text COLLATE NOCASE,
    "subject_iri" text COLLATE NOCASE,
    "failures" text,
    "running" boolean DEFAULT 0,
    "complete" boolean DEFAULT 0,
    "backtrace" text,
    "next_attempt_at" datetime,
    "last_attempt_at" datetime,
    "state" text
  );
CREATE INDEX idx_tasks_type
    ON tasks (type ASC);
CREATE INDEX idx_tasks_type_running_complete_backtrace_next_attempt_at_created_at
    ON tasks (type ASC, running ASC, complete ASC, backtrace ASC, next_attempt_at ASC, created_at ASC);
CREATE TABLE tags (
    "id" integer PRIMARY KEY AUTOINCREMENT,
    "created_at" datetime NOT NULL,
    "updated_at" datetime NOT NULL,
    "subject_iri" text NOT NULL COLLATE NOCASE,
    "type" varchar(99) NOT NULL,
    "name" varchar(99) NOT NULL COLLATE NOCASE,
    "href" text
  );
CREATE INDEX idx_tags_type_subject_iri
    ON tags (type ASC, subject_iri ASC);
CREATE INDEX idx_tags_type_name
    ON tags (type ASC, name ASC);
CREATE TABLE tag_statistics (
    "type" varchar(99) NOT NULL,
    "name" varchar(99) NOT NULL COLLATE NOCASE,
    "count" integer,
    PRIMARY KEY("type", "name")
  ) WITHOUT ROWID;
CREATE TABLE points (
    "id" integer PRIMARY KEY AUTOINCREMENT,
    "chart" varchar(63) NOT NULL,
    "timestamp" datetime NOT NULL,
    "value" integer NOT NULL
  );
CREATE INDEX idx_points_chart_timestamp
    ON points (chart ASC, timestamp ASC);
CREATE INDEX idx_objects_attributed_to_iri
    ON objects (attributed_to_iri ASC);
CREATE INDEX idx_activities_actor_iri
    ON activities (actor_iri ASC);
CREATE INDEX idx_activities_target_iri
    ON activities (target_iri ASC);
CREATE UNIQUE INDEX idx_actors_iri
    ON actors (iri ASC);
CREATE UNIQUE INDEX idx_objects_iri
    ON objects (iri ASC);
CREATE TABLE filter_terms (
    "id" integer PRIMARY KEY AUTOINCREMENT,
    "created_at" datetime NOT NULL,
    "updated_at" datetime NOT NULL,
    "actor_id" integer,
    "term" text NOT NULL
  );
CREATE INDEX idx_filter_terms_actor_id
    ON filter_terms (actor_id ASC);

[toddsundsted]

i've now been able to duplicate this although i don't have a confirmed fix. it seems like with some versions of sqlite and on some builds, the query planner just makes terrible decisions about the best query plan to run. in production right now it takes 2-3 seconds minimum to load a page on a server that was statically built against 3.38.5. when i run the queries themselves in production in the cli version 3.31.1 they perform as expected.

[toddsundsted]

@JayVii e37c8af4 may fix this. i'd be interested in your experience. it works, on average, better on the versions of SQLite i have access to.

[JayVii]

@toddsundsted I just built 8dd791f6b06f753a4e32873e7ff5a14068ea767e with following dockerfile:

FROM crystallang/crystal:1.2.2-alpine AS builder
RUN apk update && apk upgrade && apk add sqlite-static
WORKDIR /build/
ARG version
RUN git clone --branch ${version:-dist} https://github.com/toddsundsted/ktistec . # change here
RUN git config user.email "you@example.com" # change here
RUN git config user.name "Your Name" # change here
RUN git merge 8dd791f6b06f753a4e32873e7ff5a14068ea767e # change here
RUN shards update # change here
RUN shards install --production
RUN crystal build src/ktistec/server.cr --static --no-debug --release

FROM alpine:latest AS server
RUN apk --no-cache add tzdata
WORKDIR /app
COPY --from=builder /build/etc /app/etc
COPY --from=builder /build/public /app/public
COPY --from=builder /build/server /bin/server
RUN mkdir /db
RUN ln -s /app/public/uploads /uploads
ENV KTISTEC_DB=/db/ktistec.db
CMD ["/bin/server"]
VOLUME /db /uploads
EXPOSE 3000

The issue is indeed gone and your most recent changes are very well appreciated too!

Thanks a lot!