Need help optimizing Vikunja performance - Slow API responses

Support
1

Hello Vikunja community,

I’m running into some performance issues with my Vikunja installation, and I’m hoping someone can help me troubleshoot and optimize it. Here are the details:

Setup:

  • Vikunja version: v0.24.2
  • Running in Docker containers (vikunja-vikunja-1 and vikunja-db-1)
  • MariaDB version: 10.11.9-MariaDB-ubu2204-log
  • Host system: Ubuntu 22.04
  • Server specs: 11.4 GiB RAM (total for the system)

Issue:
The main problem I’m facing is slow API responses, particularly when fetching all tasks. For example:

GET /api/v1/tasks/all?sort_by=due_date&sort_by=id&order_by=asc&order_by=desc&filter=done+%3D+false&filter_include_nulls=false&s=&filter_timezone=GMT&page=1

This request is taking over 2 seconds to complete, which seems quite slow.

Current status:

  1. Resource usage is generally low:

    • Vikunja container: CPU spikes up to 2.46%, Memory stable around 12-13 MiB
    • Database container: CPU spikes up to 4.64%, Memory stable around 106-107 MiB

  2. Database indexes:
    tasks table:
    ±------±-----------±-----------------------±-------------±-------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------+
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
    ±------±-----------±-----------------------±-------------±-------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------+
    | tasks | 0 | PRIMARY | 1 | id | A | 14 | NULL | NULL | | BTREE | | | NO |
    | tasks | 0 | UQE_tasks_id | 1 | id | A | 14 | NULL | NULL | | BTREE | | | NO |
    | tasks | 1 | IDX_tasks_due_date | 1 | due_date | A | 4 | NULL | NULL | YES | BTREE | | | NO |
    | tasks | 1 | IDX_tasks_project_id | 1 | project_id | A | 14 | NULL | NULL | | BTREE | | | NO |
    | tasks | 1 | IDX_tasks_repeat_after | 1 | repeat_after | A | 4 | NULL | NULL | YES | BTREE | | | NO |
    | tasks | 1 | IDX_tasks_start_date | 1 | start_date | A | 4 | NULL | NULL | YES | BTREE | | | NO |
    | tasks | 1 | IDX_tasks_end_date | 1 | end_date | A | 4 | NULL | NULL | YES | BTREE | | | NO |
    | tasks | 1 | IDX_tasks_done | 1 | done | A | 4 | NULL | NULL | YES | BTREE | | | NO |
    | tasks | 1 | IDX_tasks_done_at | 1 | done_at | A | 7 | NULL | NULL | YES | BTREE | | | NO |
    ±------±-----------±-----------------------±-------------±-------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------+

labels table:
±-------±-----------±--------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
±-------±-----------±--------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------+
| labels | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | | NO |
| labels | 0 | UQE_labels_id | 1 | id | A | 4 | NULL | NULL | | BTREE | | | NO |
±-------±-----------±--------------±-------------±------------±----------±------------±---------±-------±-----±-----------±--------±--------------±--------+

  1. I’ve enabled debug logging and query logging

  2. Docker Compose configuration (sensitive info redacted):
    version: ‘3’
    services:
    vikunja:
    image: vikunja/vikunja:latest
    environment:
    VIKUNJA_DATABASE_HOST: db
    VIKUNJA_DATABASE_PASSWORD: [REDACTED]
    VIKUNJA_DATABASE_TYPE: mysql
    VIKUNJA_DATABASE_USER: vikunja
    VIKUNJA_DATABASE_DATABASE: vikunja
    VIKUNJA_SERVICE_JWTSECRET: [REDACTED]
    VIKUNJA_DATABASE_AUTOMIGRATE: 1
    VIKUNJA_LOG_LEVEL: DEBUG
    VIKUNJA_LOG_DATABASE_QUERIES: 1
    ports:

    • 3456:3456
      volumes:
    • ./files:/app/vikunja/files
      depends_on:
      db:
      condition: service_healthy
      restart: unless-stopped

db:
image: mariadb:10
command: >
–character-set-server=utf8mb4
–collation-server=utf8mb4_unicode_ci
–innodb-buffer-pool-size=512M
–innodb-log-file-size=128M
–innodb-flush-log-at-trx-commit=2
–max-connections=250
–query-cache-type=1
–query-cache-size=32M
–slow-query-log=1
–slow-query-log-file=/var/log/mysql/slow-query.log
–long-query-time=2
environment:
MYSQL_ROOT_PASSWORD: [REDACTED]
MYSQL_USER: vikunja
MYSQL_PASSWORD: [REDACTED]
MYSQL_DATABASE: vikunja
volumes:
- ./db:/var/lib/mysql
restart: unless-stopped
healthcheck:
test: [“CMD”, “mysqladmin”, “ping”, “-h”, “localhost”, “-u”, “vikunja”, “–password=[REDACTED]”]
interval: 10s
timeout: 5s
retries: 5
start_period: 40s

What I’ve tried:

  • Analyzed the database indexes
  • Monitored resource usage with docker stats
  • Checked slow query logs
  • Enabled debug logging and database query logging

Questions:

  1. Are there any recommended optimizations for the “fetch all tasks” query?
  2. Should I add more indexes to the labels table? If so, which columns?
  3. Are there any Vikunja-specific settings I should adjust for better performance?
  4. Any recommendations for implementing caching to improve response times?
  5. Is my MariaDB configuration optimal for this setup?

I’d greatly appreciate any insights or suggestions to improve the performance of my Vikunja instance. Let me know if you need any additional information!

Thank you in advance for your help!

2

anyone? please help me with this?

3

I run a few Vikunja instances on systems with similar or lower specs, try being one of them. The response times are typically much lower without any optimization on the system. This makes me think the issue is caused by something on your system.

How many users / tasks / projects do you have on the instance?

Which queries do you see in the slow query log?

4

Actually, I solved this issue by moving to postgres & all things are working now

1 Like