Very slow docker-compose up

corinja · February 4, 2022, 12:57pm

Describe the issue/error/question

Trying to upgrade n8n via Docker-Compose. The process is taking an incredibly long time. Currently it has been running for about 16 hours and hasn’t completed. One factor that might be relevant is my SQLite db size of >30GB.

I want to know if I can extract the workflows created by the users and back them up and then rebuild my installation from scratch. Or if I can stop the installation safely and apply the database vars suggested here to shrink its size Why is my database so big? - #6 by jon

What is the error message (if any)?

No error but the logs show:

2022-02-03T20:51:00.898839004Z INFO: Started with migration for wait functionality.
2022-02-03T20:51:00.898843689Z       Depending on the number of saved executions, that may take a little bit.
2022-02-03T20:51:00.898848312Z 
2022-02-03T20:51:00.898852453Z 
2022-02-04T02:07:06.220215404Z query is slow: DROP TABLE IF EXISTS "temporary_execution_entity"
2022-02-04T02:07:06.287169871Z execution time: 18965220
2022-02-04T07:19:22.040508142Z query is slow: INSERT INTO "temporary_execution_entity"("id", "data", "finished", "mode", "retryOf", "retrySuccessId", "startedAt", "stoppedAt", "workflowData", "workflowId") SELECT "id", "data", "finished", "mode", "retryOf", "retrySuccessId", "startedAt", "stoppedAt", "workflowData", "workflowId" FROM "execution_entity"
2022-02-04T07:19:22.106938256Z execution time: 18735660
2022-02-04T12:20:08.399459586Z query is slow: DROP TABLE "execution_entity"
2022-02-04T12:20:08.471272580Z execution time: 18046298

Please share the workflow

n/a

Share the output returned by the last node

n/a

Information on your n8n setup

n8n version: The docker image that was pulled was originally latest but when this took a long time to deploy I scrapped that and went back to 0.149.0
Database you’re using (default: SQLite): SQLite. Database size is >30GB
Running n8n with the execution process [own(default), main]: Not sure
Running n8n via [Docker, npm, n8n.cloud, desktop app]: Docker

.env

# Folder where data should be saved

DATA_FOLDER=/root/n8n/

# The top level domain to serve from

DOMAIN_NAME=**********

# The subdomain to serve from

SUBDOMAIN=n8n

# DOMAIN_NAME and SUBDOMAIN combined decide where n8n will be reachable from

# above example would result in: https://n8n.example.com

# The user name to use for authentication - IMPORTANT ALWAYS CHANGE!

N8N_BASIC_AUTH_USER=******

# The password to use for authentication - IMPORTANT ALWAYS CHANGE!

N8N_BASIC_AUTH_PASSWORD=******

# Optional timezone to set which gets used by Cron-Node by default

# If not set New York time will be used

GENERIC_TIMEZONE=Europe/London

# The email address to use for the SSL certificate creation

SSL_EMAIL=*****.***@*****.com

docker-compose.yml

version: "3"

services:
  traefik:
    image: "traefik"
    restart: always
    command:
      - "--api=true"
      - "--api.insecure=true"
      - "--providers.docker=true"
      - "--providers.docker.exposedbydefault=false"
      - "--entrypoints.web.address=:80"
      - "--entrypoints.web.http.redirections.entryPoint.to=websecure"
      - "--entrypoints.web.http.redirections.entrypoint.scheme=https"
      - "--entrypoints.websecure.address=:443"
      - "--certificatesresolvers.mytlschallenge.acme.tlschallenge=true"
      - "--certificatesresolvers.mytlschallenge.acme.email=${SSL_EMAIL}"
      - "--certificatesresolvers.mytlschallenge.acme.storage=/letsencrypt/acme.json"
    ports:
      - "80:80"
      - "443:443"
    volumes:
      - ${DATA_FOLDER}/letsencrypt:/letsencrypt
      - /var/run/docker.sock:/var/run/docker.sock:ro

  n8n:
    image: n8nio/n8n
    restart: always
    ports:
      - "127.0.0.1:5678:5678"
    labels:
      - traefik.enable=true
      - traefik.http.routers.n8n.rule=Host(`${SUBDOMAIN}.${DOMAIN_NAME}`)
      - traefik.http.routers.n8n.tls=true
      - traefik.http.routers.n8n.entrypoints=web,websecure
      - traefik.http.routers.n8n.tls.certresolver=mytlschallenge
      - traefik.http.middlewares.n8n.headers.SSLRedirect=true

Thanks for any suggestions or help!

MutedJam · February 4, 2022, 1:49pm

Hi @corinja, sorry to hear you’re having a hard time here.

As you can see from your output the database queries are indeed what seems to be causing the slowness here. The post by @Jon you have linked would be a great step towards reducing the database size and increasing the speed by a lot here, but cancelling the migration could leave your database in a corrupt state so I’d suggest you avoid this.

Do you have any backups of your workflows? If so, you could just start over with a fresh database (which would be much faster) and then re-import your workflows (obviously you wouldn’t have past execution data in this case, but I suspect you might not need it).

corinja · February 4, 2022, 1:59pm

Thanks for the reply @MutedJam

I do not think we have any backups of our workflows. Is it not possible to export the workflows while the docker container is in this state?

MutedJam · February 4, 2022, 2:22pm

I have not tried this yet unfortunately, but have asked the team whether they might have any insights here and will of course confirm once I get any feedback here.

corinja · February 4, 2022, 3:20pm

Thanks.

So the latest condition of the container deployment is:

2022-02-04T13:01:38.518365320Z query is slow: CREATE INDEX "IDX_ca4a71b47f28ac6ea88293a8e2" ON "execution_entity" ("waitTill")

2022-02-04T13:01:38.518644548Z execution time: 139086

2022-02-04T14:21:05.683149168Z query is slow: VACUUM;

2022-02-04T14:21:05.777370574Z execution time: 4767153

2022-02-04T14:21:05.949788703Z There was an error initializing DB: "SQLITE_FULL: database or disk is full"
2022-02-04T14:21:05.961482712Z 
2022-02-04T14:21:05.961497862Z Stopping n8n...
2022-02-04T14:21:33.271054710Z Initializing n8n process
2022-02-04T14:21:34.378888022Z 
2022-02-04T14:21:34.378918303Z 
2022-02-04T14:21:34.378923709Z INFO: Started with migration for wait functionality.
2022-02-04T14:21:34.378928517Z       Depending on the number of saved executions, that may take a little bit.
2022-02-04T14:21:34.378933224Z 
2022-02-04T14:21:34.378937432Z 
2022-02-04T15:07:46.190495346Z query is slow: INSERT INTO "temporary_execution_entity"("id", "data", "finished", "mode", "retryOf", "retrySuccessId", "startedAt", "stoppedAt", "workflowData", "workflowId") SELECT "id", "data", "finished", "mode", "retryOf", "retrySuccessId", "startedAt", "stoppedAt", "workflowData", "workflowId" FROM "execution_entity"
2022-02-04T15:07:46.195179489Z execution time: 2771616

I wonder if I should stop the deployment at this point?

Jon · February 4, 2022, 5:27pm

Hey @corinja,

Out of interest how much space is available on the host? That line about the disk being full doesn’t look good. Could be worth running a docker images prune to clear up any older images and hopefully get some space back.

What sort of hardware are you running n8n on as well? As a possible solution you could stop the container, Copy the SQLite file somewhere else and then delete the current SQLite file so it starts again and use something to query the old SQLite database to pull out anything you might need.

I don’t have any detailed steps for this process but it might be something to think about.

corinja · February 4, 2022, 5:40pm

Thanks @Jon

The host has 60GB SSD (AWS Lightsail instance)

The SQLite file is just over 30GB, but I need to have a look at how the disk space is allocated. Could be that the db is on a partition with limited space.

I’ll consider your point about querying the db separately to extract our workflows. Is there any guidance about what tables that data would be in that you can point me to?

At the moment the process seems to have got further than it did previously so I’m going to let it run on for a while:

2022-02-04T14:21:33.271054710Z Initializing n8n process

2022-02-04T14:21:34.378888022Z

2022-02-04T14:21:34.378918303Z

2022-02-04T14:21:34.378923709Z INFO: Started with migration for wait functionality.

2022-02-04T14:21:34.378928517Z Depending on the number of saved executions, that may take a little bit.

2022-02-04T14:21:34.378933224Z

2022-02-04T14:21:34.378937432Z

2022-02-04T15:07:46.190495346Z query is slow: INSERT INTO "temporary_execution_entity"("id", "data", "finished", "mode", "retryOf", "retrySuccessId", "startedAt", "stoppedAt", "workflowData", "workflowId") SELECT "id", "data", "finished", "mode", "retryOf", "retrySuccessId", "startedAt", "stoppedAt", "workflowData", "workflowId" FROM "execution_entity"

2022-02-04T15:07:46.195179489Z execution time: 2771616

2022-02-04T15:47:08.627389259Z query is slow: DROP TABLE "execution_entity"

2022-02-04T15:47:08.627672378Z execution time: 2362420

2022-02-04T16:35:15.335473651Z query is slow: CREATE INDEX "IDX_cefb067df2402f6aed0638a6c1" ON "execution_entity" ("stoppedAt")

2022-02-04T16:35:15.335728814Z execution time: 2886637

2022-02-04T16:37:34.955354151Z query is slow: CREATE INDEX "IDX_ca4a71b47f28ac6ea88293a8e2" ON "execution_entity" ("waitTill")

2022-02-04T16:37:34.955620989Z execution time: 139619

corinja · February 7, 2022, 4:03pm

I fixed this with a combination of suggestions. I stopped the docker-compose up deployment as it was getting stuck on the size of the database file. The db was 30GB but in trying to complete some operation (possibly the vacuum step) it was attempting to create a temporary file the same size as the db on a 60GB partition, hence the error.

So I copied and removed the db onto a separate disk with more space. I added in to the docker-compose.yml the suggested database vars:

      - EXECUTIONS_PROCESS=main
      - EXECUTIONS_DATA_SAVE_ON_ERROR=all
      - EXECUTIONS_DATA_SAVE_ON_SUCCESS=none
      - EXECUTIONS_DATA_SAVE_ON_PROGRESS=true
      - EXECUTIONS_DATA_SAVE_MANUAL_EXECUTIONS=false
      - EXECUTIONS_DATA_PRUNE=true
      - EXECUTIONS_DATA_MAX_AGE=168
      - DB_SQLITE_VACUUM_ON_STARTUP=true

Then I ran docker-compose up -d and it started the container up and it created the database ok.

Finally, I opened up sqlite3 on the main db and attached the copy of the old db I had made. I inserted into the tables on the new db the data about our workflows and credentials:

sqlite> insert into main.credentials_entity select * from cr_db.credentials_entity;

sqlite> insert into main.tag_entity select * from cr_db.tag_entity;

sqlite> insert into main.workflow_entity select * from cr_db.workflow_entity;

sqlite> insert into main.workflows_tags select * from cr_db.workflows_tags;

Everything is back and working now