Docker and MariaDB, really very slow query

Question

I rarely come here to ask questions, since I usually find answers by digging hard, but this time I didn't see solutions and I'm really very frustrated.

I am setting up a docker container with MariaDB, with this Dockerfile:

FROM mariadb:latest
WORKDIR /app

I compile this Dockerfile with: docker build --rm -t mysql_mariadb .

And I have this docker-compose.yml:

name: mariadb_app
services:
  app:
    image: mysql_mariadb
    environment:
      MARIADB_ROOT_PASSWORD: "test"
    ports:
      - "3307:3307"
    networks:
      - nets
    volumes:
      - ./Config/mariadb.cnf:/etc/mysql/mariadb.cnf:ro
      - C:/Docker/Data/MariaDB_DATA:/var/lib/mysql
networks:
  nets:
    external: true

And I use this to start container: docker-compose -f docker-compose.yml up --build -d

And finally this is "mariadb.cnf" file:

[mysqld]
wait_timeout = 28800
interactive_timeout = 28800
max_allowed_packet = 256M
port = 3307

I access it from PHPMyAdmin, and everything seems fine. It connects well and the database works. Everything was going well until I migrated a significant amount of data, and ran a query that involved several tables.. here began a problem for which I still have not found any solution.

The query:

SELECT a.id, a.name AS name, a.label AS label, c.name AS category, a.plays,
(SELECT COUNT(*) FROM user_favorites WHERE audio_id = a.id) AS favs
FROM audios AS a
INNER JOIN audio_categories AS c ON a.category_id = c.id
ORDER BY c.datetime ASC, a.datetime ASC;

(Please don't comment about possible improvements to the query, that's not why I'm here)

I have same MariaDB database with wamp64 (Windows server), and it have exactly same tables and data, and I ran exactly same query. In PHPMyAdmin, the result was this:

Showing rows 0 - 24 (total of 1745, The query took 0.6545 seconds.)

When I run the query in the container database, this is what I get:

Showing rows 0 - 24 (total of 1745, The query took 31.6956 seconds.)

0.6 secs vs 31.69 secs. How is this possible?

I tried everything, I changed all settings in the mariadb.cnf file, I checked resource usage (I already allocated more cpus and ram in the yml), this also happens by executing the query locally in the container so it is not a network problem. I even tried not using mariadb:latest image, using a debian and installing mariadb manually. Same results... I don't know what else to do and I don't know why there is such a difference in time if it is the same database, with the same configuration, the same data and the same query.

I read an answer that suggested modifying the "innodb_flush_log_at_trx_commit" setting to 2 or 0. Also no results..

Everything indicates that it is a docker and resource allocation problem, but nothing I have tried worked for me.

I've been very stuck with this for days, and I really need help.

Thanks.

Answer 1

Solved. The problem was the WSL in Windows, I replaced it with Hyper V (at Docker Desktop install setup) and the performance improved significantly. When I can, I will install everything on Linux to stop having problems. Thank you all for your comments.