MySQL extremely slow just with docker-ce installed

[hcomnetworkers]

• This is a bug report
• This is a feature request
• I searched existing issues before opening this one

Expected behavior

MySQL fast with docker-ce installed as it is without docker-ce installed.

Actual behavior

MySQL on Ubuntu becomes extremely slow just by having docker-ce installed.

Steps to reproduce the behavior

• Create a database dump with any test data. E.g. use http://filldb.info to generate a dump. (Our dump is 9.15 MB and is called "fulldb.sql")
• Install blank new Ubuntu 16.04.04 Desktop edition and follow these steps:

sudo apt-get update
sudo apt-get install mysql-server
mysql -uroot -proot -e 'create database test;'
time mysql -uroot -proot test < fulldb.sql

real	0m5.534s
user	0m0.021s
sys	0m1.116s

sudo apt install curl
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
sudo apt-key fingerprint 0EBFCD88
sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"
sudo apt-get update
sudo apt-get install docker-ce
time mysql -uroot -proot test < fulldb.sql

real	3m39.500s
user	0m0.080s
sys	0m5.379s

sudo systemctl stop docker
sudo ifconfig docker0 down
--> still slow

sudo apt-get purge docker-ce
--> still slow

sudo reboot
--> fast again

We have also tried to use the mysql docker containers from https://hub.docker.com/r/mysql/mysql-server/ and https://hub.docker.com/_/mysql/ on a clean Ubuntu with the same slow result.

We have tried to find an explanation and found other people with a similar problem (e.g. https://forums.docker.com/t/mysql-slow-performance-in-docker/37179). However, we did not find any post describing the slowness already occurring outside the docker container as demonstrated by our reproduction guide above.
We hope that this might be the clue needed to figure out why this is happening. This obviously is not a problem from the containers.

Environment

• Ubuntu 16.04.04 LTS running in VMware® Workstation 14 Pro 14.1.1 build-7528167 on a Window 10 Pro host.
• mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using EditLine wrapper
• Docker version 17.12.1-ce, build 7390fc6

[xandriaw]

I'm also having this issue with Docker mysql containers on google container engine and on linux- Centos7. The original build of database is fast querying, the dump to a new one on my linux local machine is fast, but the docker container in either environment is 20+ times slower no matter if I load sql dump from docker-entrypoint-initdb.d or mysql -u root -p dbname < dbdump.sql

[peterforeman]

Same here. Running a PHP with Symfony app on "bare metal" is way faster than running the exact same in a docker container on the exact same host. The MySQL dir (/var/lib/mysql) is mounted via bind to a local directory. I'm experiencing this with MariaDB:10.1 as well as MySQL (latest) and on Linux (debian) and MacOS.

The performance difference is so dramatical that I'm unwilling to convert the app to a dockerized instance.

MacOS:
Client:
Version: 18.03.0-ce-rc1
API version: 1.37
Go version: go1.9.4
Git commit: c160c73
Built: Thu Feb 22 02:34:03 2018
OS/Arch: darwin/amd64
Experimental: true
Orchestrator: swarm

Server:
Engine:
Version: 18.03.0-ce-rc1
API version: 1.37 (minimum version 1.12)
Go version: go1.9.4
Git commit: c160c73
Built: Thu Feb 22 02:42:37 2018
OS/Arch: linux/amd64
Experimental: true

Linux:
Client:
Version: 18.02.0-ce
API version: 1.36
Go version: go1.9.3
Git commit: fc4de44
Built: Wed Feb 7 21:16:25 2018
OS/Arch: linux/amd64
Experimental: false
Orchestrator: swarm

Server:
Engine:
Version: 18.02.0-ce
API version: 1.36 (minimum version 1.12)
Go version: go1.9.3
Git commit: fc4de44
Built: Wed Feb 7 21:14:59 2018
OS/Arch: linux/amd64
Experimental: false

[schafdog]

I believe I am experiencing the same issue, even thought this sound really weird. Came from a Fedora 26 with older docker and upgraded to Fedora 27 installed docker-ce. So yes newer OS and other version of docker. I will try to reproduce by uninstalling . I am running btrfs, but since that wasn't an issue under F26...

Also tried the mysql on the raw F27 but this was also slow (while docker engine was still running)

[uncaught]

I've been trying to reproduce this problem with different distributions. using the OP's way:

• Fresh installation as a virtual machine
• installed mysql or mariadb
• tested speed
• installed docker-ce
• retested speed
• optionally switching mysql for mariadb and retested speed

So far i could...

• confirm the problem on:
  • Ubuntu 16.04 desktop, mysql 5.7
  • Ubuntu 17.10 desktop, mysql 5.7 + mariadb 10.1
• refute it on:
  • Centos 7 minimal edition, mariadb 5.5 + mysql 5.7
  • Centos 7 minimal edition + GNOME installation, mysql 5.7
  • Ubuntu 16.04 server, mysql 5.7

I thought there was a pattern emerging with the headless distributions until I installed gnome on Centos.

[uncaught]

When testing Fedora I've found something interesting: MariaDb was already slow on Fedora 27 Workstation without docker installed. But it was the same slowness (3m+) as I get on Ubuntu desktops with docker-ce installed.
Curiously I tried Fedora 27 Server edition to find that it is fast (~10s) as was the speed roughly about on Centos and Ubuntu server. And Fedora server already has docker installed.

So when one can find what Fedora Workstation and Ubuntu Desktop with docker have in common, this could be further triangulated.

[dypa]

I have the same issue on centos 7.4 with xfs and debian 8 with ext4. It looks like mysql bug #46959. The solution for me is set nobarrier for xfs and barrier=0 option for ext4 in /etc/fstab.

[Xplouder]

@dypa It did not work for me.

[schafdog]

Could this be related to cgroups settings for Desktop vs Server and know how to change this?

[DiegoFelipe]

I also got the same issue using docker in ubuntu server, idk if it works for you:
http://phpforus.com/how-to-make-mysql-run-fast-with-ext4-on-ubuntu/

[uncaught]

I've tried setting barrier=0 on the ext4 disk on Ubuntu Desktop and Fedora Workstation. Both without improvement of the mysql-speed.

[luozhouyang]

Same issue.
I tried to import data with this command:

mysql -h 127.0.0.1 -u user -p db < data.sql

In host machine, this just takes 5 mins, but in docker, it takes more than 30 mins and just inserted about 50,000 rows data.

[hadirsa]

I have the same issue on Ubuntu 16.04 and Docker 17.12.0-ce, build c97c6d6
Is there anyone found a solution?

[wollanup]

I have this issue since i updated ubuntu from 17.10 to 18.04. (desktop version)
I noticed it after restarting my dev machine and ran tests of a PHP app.
Before ubuntu update, DB populator script took around 0.8s, now it's around 17 seconds !

Dev machine : i7 7700k, 16GB RAM
I'm on docker 17.09.0-ce
My container uses MariaDB 10.2
/var/lib/docker is on an ext4 partition on a SSD

Edit:

Performances are back to normal, but I'm not sure how...

I tried to use a volume on another SSD for /var/lib/mysql in my compose file :

  mysql:
    build:
      context: ./docker/docker/mysql/
      dockerfile: MariaDB-10.Dockerfile
    volumes:     
      - ./mysql/:/var/lib/mysql

Deleted mysql container and up
20 seconds

I revert to default storage and applied advices from this thread :
moby/moby#21485 (comment)

echo 0 >/sys/block/sda/queue/iosched/slice_idle
echo 0 >/sys/block/sda/queue/iosched/group_idle

Deleted mysql container and up
0.8 second

I reverted back the IO schedulers and populate my DB (without deleting/restarting containers) and was expecting 20 seconds but no :

echo 1 >/sys/block/sda/queue/iosched/slice_idle
echo 1 >/sys/block/sda/queue/iosched/group_idle

2.2 seconds

EDIT : as noticed by @hriad, default value was 8, so when I set to 1, I didn't restore original value ;)

What change did restore performances ? I don't know, but if it can help someone...

[hriad]

@wollanup You nailed it dude. I've a smilar dev machine and been strugling with this issue since a few months now. After reading your findings, I ran integration tests on a laravel app I'm working on with mysql on docker, it took 14 minutes to complete. I then set slice_idle and group_idle to 0 and ran the same test suite again, it completed within 4 minutes. However, as you noted setting those values back to 1 didn't impact performance, because (at least in my case) group_idle default value is set to 8 not 1. Setting it back to 8 gets me the same level of initial performance.

After digging a bit more https://wiki.archlinux.org/index.php/Improving_performance#Changing_I.2FO_scheduler. I did reset slice_idle & group_idle back to their default values and changed the IO scheduler from cfq to noop and my test suite is runing within the 4 minutes mark. So far I didn't notice any negative impact on disk performance.

[wollanup]

@hriad You're right about the default value 8 set to group_idle.
After a kernel update (I guess), I experienced slow performance again so I ran :

# cat /sys/block/sda/queue/iosched/group_idle
8

And again, setting this value to 0 restores blowing fast SQL INSERTs...

echo 0 > /sys/block/sda/queue/iosched/group_idle

[hcomnetworkers]

On our new Ubuntu 18 development VM we had the same issue again. Following this article and setting the I/O-scheduler to "noop" solved it for us: https://blog.codeship.com/linux-io-scheduler-tuning/

(Our functional php unit tests with lots of SQL went from 13 minutes with the cfq-scheduler down to under 2 minutes with the noop-scheduler.)

[timmipetit]

Changing the scheduler to noop also fixed our performance issues. We are running a software raid1 setup, and had to change the scheduler for both our disk (sda and sdb). I hope that this info might help someone else.

[omarjebari]

Running OPTIMIZE on the table or fix the whole db, see: https://stackoverflow.com/a/23944159/2867894