MySQL very slow on docker
I’m trying to run a simple project with nginx, php and mysql on a droplet with 2 vCPU and 2 GB Ram using the Ubuntu-Docker-Image. The project runs on a really cheap host so far and collects about 1.5GB of data in a database so far and I’m trying to migrate this to a docker environment on Digital Ocean. Basically the main (InnoDB) tables are blogs (about 200 entries) and blog_posts (about 200000 entries).
My problem is, that a simple query is awful slow! Its basically like:
SELECT COUNT(p.id) FROM blog_posts AS p INNER JOIN blogs AS b ON (p.blog_id=b.id) WHERE b.active=1 AND p.active=1
So, nothing special. All used fields are indexed. On my local MacBook with the same docker-compose.yml the query takes about 400ms. On the droplet it takes more than 9 seconds!
So far i tried:
- use mysql 8
- use mysql 5.7
- use mysql 5.6
- use mariadb
- run Optimize-Table-Query
- additional indexed the PrimaryKey fields with a regular INDEX
- modified the fstab with barrier=0 for that volume
- changed the provider, Digital Ocean is the third now
- tried a larger configuration (more RAM)
- changed the innodb-default-row-format to Compact instead of dynamic
Nothing made this query noticeable faster.
When monitoring the server during those queries neither the CPU nor the RAM or the disk I/O seem to reach the limit.
When profiling the query the most time is used during “Send Data”
The best configuration so far is mariadb because it caches the query, but without caching or with changing/adding more parameter it is as slow as the other configurations.
I’m running all conatiner with a single docker-compose.yml and each component as a single service using:
- nginx (nginx:alpine)
- php-fpm (docker/php-fpm)
- mysql (mysql:5.7 / mysql:5.6 / mariadb)
The MySql Data is mounted as volume
... mysql: ... volumes: - ./docker/mysql/:/var/lib/mysql ...
So I’m really run out of Ideas! I’m struggling with this for 2 weeks now and really hope that someone can help me to find the bottleneck.
These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.