MySQL very slow on docker

November 11, 2018 4.1k views
Docker MySQL MariaDB Ubuntu 18.04

Hi,
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.

3 Answers

Hey friend!

That’s rough. I’d like to check and make sure that the hypervisor is performing at it’s best, just to be sure. Can you open a ticket with our support team and reference this post?

Jarland

Hey jarland,
Thanks for your answer! I’ve just created a ticket. But I’m pretty sure the problem is created by me, not a problem of the server. Using a few MySQL services on docker does not seem to be a rare configuration. ;)

Hey, it’s me again.
It seems like the basic configuration ob MySql and MariaDB is not optimised for performance. I’ve just tweaked some some configuration, e.g. innodbbufferpool_size and the performance just got a giant boost. Another really great tool for getting some ideas for optimising ist the mysqltuner.pl script. So, if anybody encounters these problems just take a look at that.

Have another answer? Share your knowledge.