In my organization developers are using "docker" technology to develop & test their applications. developer's machines are mostly linux-mint with different specs on each machine. Every time the docker is activated, after the mysql-server container is up & running - an SQL script is then executed to create the schemas (an output of mysqldump) in the following way:
mysql -h localhost -u root --comments < base_schema.sql
I've received complaints that the script execution takes around 25 seconds, which is intolerable.
When I execute the same script on a database on my local machine It does take between 25 to 35 seconds. My machine's specs:
Windows 7 64bit SP1
mysql Ver 14.14 Distrib 5.6.28, for Win64 (x86_64)
On the other hand when i execute the script on a virtual machine (on my local machine), the same script takes about 5-6 seconds. the VM specs:
VirtualBox Version 5.0.20 r106931
Ubuntu 14.04.4 LTS
11,136 MB RAM (~11GB)
mysql Ver 14.14 Distrib 5.6.31, for Linux (x86_64) using EditLine wrapper
Furthermore, i created a simple sql script mySchema.sql:
drop database if exists myschema; create database myschema; create table myschema.tab_1 (x int); create table myschema.tab_2 (x int); create table myschema.tab_3 (x int); create table myschema.tab_4 (x int); create table myschema.tab_5 (x int); ... ... ... create table myschema.tab_100 (x int);
one Bash script mySchema.sh:
SECONDS=0 mysql -h localhost -u dbuser --comments < mySchema.sql duration=$SECONDS; echo "ran for $(($duration * 1000)) ms"
And one batch script mySchema.bat:
@echo off set t0=%time: =0% mysql -h localhost -uroot --comments < mySchema.sql set t=%time: =0% set /a h=1%t0:~0,2%-100 set /a m=1%t0:~3,2%-100 set /a s=1%t0:~6,2%-100 set /a c=1%t0:~9,2%-100 set /a starttime = %h% * 360000 + %m% * 6000 + 100 * %s% + %c% set /a h=1%t:~0,2%-100 set /a m=1%t:~3,2%-100 set /a s=1%t:~6,2%-100 set /a c=1%t:~9,2%-100 set /a endtime = %h% * 360000 + %m% * 6000 + 100 * %s% + %c% set /a runtime = %endtime% - %starttime% set runtime = %s%.%c% echo Started at %t0% echo Ran for %runtime%0 ms
and the results:
C:\Users\LiorAN\Desktop\vm>mySchema.bat Started at 16:08:12.65 Ran for 4730 ms C:\Users\LiorAN\Desktop\vm>mySchema.bat Started at 16:08:18.65 Ran for 4710 ms C:\Users\LiorAN\Desktop\vm>mySchema.bat Started at 16:08:55.16 Ran for 4920 ms
lior@lior-VirtualBox:/media/windows-share$ ./mySchema.sh ran for 1000 ms lior@lior-VirtualBox:/media/windows-share$ ./mySchema.sh ran for 1000 ms lior@lior-VirtualBox:/media/windows-share$ ./mySchema.sh ran for 1000 ms
when i changed mySchema.sql to create 1000 tables instead of 100- on windows execution times ranged from 48-60 seconds, while on the vm execution times ranged from 11-14 seconds.
what could cause the difference in executions times of the same process on the 2 platforms? I already tried to copy the server parameters from windows to the VM and the results were the same, and i doubt that the difference between 5.6.28 and 5.6.31 would be the cause.
can anyone find a better suggestion to look into this problem ? I didn't mention but my first step was to anaylze the script itself, but i couldn't find any performance issues in it.
thanks alot, Lior.