Sqlite import sql script is very slow #8598

New Issue

GiteaMirror · 2025-11-02T08:12:03-06:00

GiteaMirror commented

2025-11-02 08:12:03 -06:00

Originally created by @rcdevice on GitHub (Feb 22, 2022).

Feature Description

When I use cmd [ sqlite3 gitea.db < gitea-db.sql ] to restore the Sqlite database from gitea-db.sql , It takes about 5-6 minutes, unbearably slow, and the data that I recovered was only a few hundred records .

Here's my solution,add two command sets at the beginning and end of the gitea-db.sql file：

PRAGMA journal_mode = MEMORY;
PRAGMA synchronous = OFF;
PRAGMA foreign_keys = OFF;
PRAGMA ignore_check_constraints = OFF;
PRAGMA auto_vacuum = NONE;
PRAGMA secure_delete = OFF;
BEGIN TRANSACTION;

<gitea-db.sql CONTENT>

COMMIT;
PRAGMA ignore_check_constraints = ON;
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;

After the above modification, run same again, less than 1 second, very fast.

I think install Gitea with SQLite very slow to initialize is the same reason.

Screenshots

No response

Originally created by @rcdevice on GitHub (Feb 22, 2022). ### Feature Description When I use cmd [ sqlite3 gitea.db < gitea-db.sql ] to restore the Sqlite database from gitea-db.sql , It takes about 5-6 minutes, unbearably slow, and the data that I recovered was only a few hundred records . Here's my solution,add two command sets at the beginning and end of the gitea-db.sql file： ---------------------------------- PRAGMA journal_mode = MEMORY; PRAGMA synchronous = OFF; PRAGMA foreign_keys = OFF; PRAGMA ignore_check_constraints = OFF; PRAGMA auto_vacuum = NONE; PRAGMA secure_delete = OFF; BEGIN TRANSACTION; <gitea-db.sql CONTENT> COMMIT; PRAGMA ignore_check_constraints = ON; PRAGMA foreign_keys = ON; PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; --------------------------------------- After the above modification, run same again, less than 1 second, very fast. I think install Gitea with SQLite very slow to initialize is the same reason. ### Screenshots _No response_

GiteaMirror added the type/docs issue/workaround labels 2025-11-02 08:12:03 -06:00

GiteaMirror commented

2025-11-02 08:12:04 -06:00

@seedform commented on GitHub (Oct 20, 2022):

As a temporary workaround for my small (less than 20mb) sqlite database, I was able to get away by simply adding BEGIN; to the top of the script (this disables auto commits in sqlite) and COMMIT; at the end.

This made it easy to automate restoration via shell script and Ansible:

cat <(echo 'BEGIN;') gitea-db.sql <(echo 'COMMIT;') | sqlite3 gitea.db

Runtimes

Unfortunately, this never finished in a reasonable amount of time on an HDD due to the sheer number writes to disk, so I ran it on an SSD instead:

# Default gitea script

$ time sh -c 'cat gitea-db.sql | sqlite3 gitea.db; sync'

real	7m10.470s
user	0m6.704s
sys	0m24.534s

# With BEGIN + COMMIT to disable auto commit

$ time sh -c 'cat <(echo "BEGIN;") gitea-db.sql <(echo "COMMIT;") | sqlite3 gitea.db'

real	0m0.882s
user	0m0.174s
sys	0m0.020s

@seedform commented on GitHub (Oct 20, 2022): As a temporary workaround for my small (less than 20mb) sqlite database, I was able to get away by simply adding `BEGIN;` to the top of the script (this disables auto commits in sqlite) and `COMMIT;` at the end. This made it easy to automate restoration via shell script and Ansible: ```sh cat <(echo 'BEGIN;') gitea-db.sql <(echo 'COMMIT;') | sqlite3 gitea.db ``` ### Runtimes Unfortunately, this never finished in a reasonable amount of time on an HDD due to the sheer number writes to disk, so I ran it on an SSD instead: ```sh # Default gitea script $ time sh -c 'cat gitea-db.sql | sqlite3 gitea.db; sync' real 7m10.470s user 0m6.704s sys 0m24.534s ``` ```sh # With BEGIN + COMMIT to disable auto commit $ time sh -c 'cat <(echo "BEGIN;") gitea-db.sql <(echo "COMMIT;") | sqlite3 gitea.db' real 0m0.882s user 0m0.174s sys 0m0.020s ```

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github-starred/gitea#8598