1

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

i7-5600 CPU

16GB RAM

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

2 CPU's

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:

Windows:

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

Ubuntu:

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.

QUESTIONS:

  1. 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.

  2. 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.

  • It is generally "bad schema design" to have 1000 tables. – Rick James Aug 5 '16 at 0:37
  • Creating a table involves file manipulation in the OS. Unix is more efficient at such than Windows. – Rick James Aug 5 '16 at 0:38
  • @RickJames Thank you for your comments. The "1000 tables" test was mentioned just for emphasizing the issue description. And about the windows being inefficient compared to linux, it can explain the scenario above. However developers in my organization use Linux-mint hosts and still experience the same execution time for the script (without vm) Thanks again. – lioran Aug 9 '16 at 7:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.