Back in October I have write about possible ways of running multiple MySQL instances on the same hardware. As the months passing by, the project of splitting our database schemas into standalone instances is closing in, so I started to check the different ways.
EDIT: This post is outdated, here is the follow up.
I started with docker, because we’ll use containers anyway with the applications, and I think it is a good idea to minimise the diversity of an infrastructure. I used the docker’s “official” Percona image (it is official by Docker not by Percona!) which is easy to use, and flexible enough. (https://hub.docker.com/_/percona/) This image supports using custom config files, you can mount your existing directories (data & log) for the container, it is nice at the first sight. I found only one caveat with this: if I stop the docker container with docker kill <container id> then the mysql server will be crash, so if you want to clean shut down the instance you have to kill the mysql server inside the container with kill, and after it stopped you can remove the container itself.
[firstname.lastname@example.org /home/banyek/conf]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES e791b3728ab5 percona "/docker-entrypoint.s" 3 minutes ago Up 3 minutes 0.0.0.0:3306->3306/tcp mysql-dev [email@example.com /home/banyek/conf]# docker exec e791b3728ab5 killall mysqld [firstname.lastname@example.org /home/banyek/conf]# docker rm e791b3728ab5 e791b3728ab5
My first test was to run our development servers with docker containers instead of native mysql servers which was jolly good. I start the database instance with mounting the current data directory and the binlog directory inside the container, and I use a slightly modified (datafiles & directories) config file inside the container.
[email@example.com /home/banyek/conf]# docker run --name mysql-dev -v /home/banyek/conf:/etc/mysql/conf.d -v /var/lib/mysql:/var/lib/mysql -v /var/log/mysql:/var/log/mysql -p 3306:3306 -d percona 954360158d7a5c2e050b7049c73a8a098b550da6e3d2e336b43bd690e34e9738 [firstname.lastname@example.org /home/banyek/conf]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 954360158d7a percona "/docker-entrypoint.s" 7 seconds ago Up 6 seconds 0.0.0.0:3306->3306/tcp mysql-dev [email@example.com /home/banyek/conf]# mysql -h 127.0.0.1 Welcome to the MySQL monitor. Commands end with ; or \g. [...] firstname.lastname@example.org (127.0.0.1) [(none)]>
There’s only one thing you have to worried about: the local connections will come from the docker0 network interface, so you have to add it’s ip address to the allowed hosts list. (Which is 172.17.0.1 at our site.)
This instance can be used from any of the applications it can be a part of the replica chain, etc.
After I used dockerized mysql for a while in the dev environment, I decided to bechmark it with sysbench.
I recommend using the 0.5 branch (which is available on github) because the 0.4.12 (the stable one) didn’t supports parallel benchmarking of databases so your results won’t be applicable comparing to a real workload. After compiling sysbench, we can start our tests.
During the tests I created once the ‘sbtest’ database manually (in mysql prompt create database sbtest) and after I was populated it with test data with the following command:
./sysbench --test=tests/db/parallel_prepare.lua --mysql_host=127.0.0.1 --mysql-user=XXX --mysql-password=YYY --oltp-tables-count=64 --num-threads=8 run
#and the tests were ran with the following command: ./sysbench --test=tests/db/oltp.lua --mysql_host=127.0.0.1 --mysql-user=XXX --mysql-password=YYY --oltp-tables-count=64 --num-threads=8 --max-time=60 run
I ran the test 3 times with native mysql and 3 times with the containerised one. I recorded the end results from the 3rd runs only for avoiding the problems which could be caused by cold caches.
Here are the results:
Running the test with following options: Number of threads: 8 Random number generator seed is 0 and will be ignored Threads started! OLTP test statistics: queries performed: read: 140140 write: 40040 other: 20020 total: 200200 transactions: 10010 (1928.10 per sec.) read/write requests: 180180 (34705.80 per sec.) other operations: 20020 (3856.20 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 5.1916s total number of events: 10010 total time taken by event execution: 41.4529s response time: min: 2.97ms avg: 4.14ms max: 15.98ms approx. 95 percentile: 5.34ms Threads fairness: events (avg/stddev): 1251.2500/43.21 execution time (avg/stddev): 5.1816/0.00
And here are the results with docker:
OLTP test statistics: queries performed: read: 140000 write: 40000 other: 20000 total: 200000 transactions: 10000 (1127.79 per sec.) read/write requests: 180000 (20300.19 per sec.) other operations: 20000 (2255.58 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 8.8669s total number of events: 10000 total time taken by event execution: 70.8362s response time: min: 4.52ms avg: 7.08ms max: 22.28ms approx. 95 percentile: 9.14ms
Threads fairness: events (avg/stddev): 1250.0000/60.86 execution time (avg/stddev): 8.8545/0.00
The results are disappointing. The MySQL server running in a docker instance performs somewhere between 1/2 and 2/3 of the native one, which is unacceptable.
I started the container with the following command, so it is possible that we can avoid this performance overhead by mounting the data directory more smart (directly from LVM? With some magic mount parameters?) but so far the results are these, and the verdict is “it is not the best idea”.
[email@example.com /home/banyek]# docker run --name mysql-dev -v /home/banyek/conf:/etc/mysql/conf.d -v /var/lib/mysql:/var/lib/mysql -v /var/log/mysql:/var/log/mysql -p 3306:3306 -d percona
I’ll check the other options & performance tuning later.