1

I have a dedicated machine with intel i5, 8GB RAM, 7200RPM HDD with Ubuntu 18.04 OS. I have installed only MySQL-5.7.22 in it, see the configuration file's content at the end of this question. I downloaded sample employees database to test the performance of MySQL using mysqlslap. (See How to measure MySQL query performance with mysqlslap).

On the dedicated Ubuntu:

The following command took 6 seconds to return the result while the result says the maximum number of seconds to run all the queries require 0.286 seconds:

mysqlslap --user=root --password --host=localhost  --auto-generate-sql --verbose

Average number of seconds to run all queries: 0.286 seconds
Minimum number of seconds to run all queries: 0.286 seconds
Maximum number of seconds to run all queries: 0.286 seconds
Number of clients running queries: 1
Average number of queries per client: 0

Similarly, the following command took 1 minute and 53 seconds to return the result while the result says the maximum number of seconds to run all the queries require 1.576 seconds:

mysqlslap --user=root --password --host=localhost  --concurrency=50 --iterations=10 --auto-generate-sql --verbose

Average number of seconds to run all queries: 1.405 seconds
Minimum number of seconds to run all queries: 0.760 seconds
Maximum number of seconds to run all queries: 1.576 seconds
Number of clients running queries: 50
Average number of queries per client: 0

In the Ubuntu installed in the VMWare:

Now on the SAME SYSTEM, as I have dual booted Windows with Ubuntu, I ran the Windows and installed Ubuntu in the VMWare with 2GB RAM & 50GB HDD and with the same file system Ext4, I got the following results with the same mysql configuration:

The following command INSTANTLY returned the result, as soon as I typed the password and pressed the Enter key the result was displayed:

mysqlslap --user=root --password --host=localhost  --auto-generate-sql --verbose

Average number of seconds to run all queries: 0.002 seconds
Minimum number of seconds to run all queries: 0.002 seconds
Maximum number of seconds to run all queries: 0.002 seconds
Number of clients running queries: 1
Average number of queries per client: 0

While the following command took 2.5 seconds to return the result:

mysqlslap --user=root --password --host=localhost  --concurrency=50 --iterations=10 --auto-generate-sql --verbose

Average number of seconds to run all queries: 0.154 seconds
Minimum number of seconds to run all queries: 0.131 seconds
Maximum number of seconds to run all queries: 0.193 seconds
Number of clients running queries: 50
Average number of queries per client: 0

Looking at the results, I am lost how the MySQL in the VM perform fast while it's slow in the dedicated system.

My question is, what is the possible thing which may be slowing down the MySQL in the dedicated machine? How can I make it as fast as it is in VM?

CONFIGURATION FILE:

In both VM and dedicated system with Ubuntu, I have used the same default MySQL configurations, I haven't changed anything in the configuration files.

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_open_cache       = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#slow_query_log     = 1
#slow_query_log_file    = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem
3

This is likely due to VM configuration rather than directly being a database issue: given you are using the same versions of the DB engine, the same DB config, the same data, and the same test script, all on the same OS, the physical/virtual environment is the only variable.

I assume that benchmark performs a fair amount of write operations? If it does then I suspect that the difference may be that VMWare is caching writes sent to the virtual disk (effectively ignoring any "make sure this is sync to physical media before letting me continue" instructions mysql is giving off) where in the physical setup it really is waiting for the data to be confirmed written at various points. In both cases the Linux kernel will think that the data has been written and will tell mysql so, but VMWare is providing a write cache so in that instance it hasn't reached the physical disk yet (just the virtual one).

Either that or the issue is one step further on and Windows is caching the writes but telling VMWare that they have completed when they have not. In either case the difference would not be within mysql or its configuration.

If this is a dev/test environment the speed boost may be desirable, but if it is a production environment where you really care about the data you should seek to disable this behaviour as it can lead to data corruption if you experience something serious like a power cut.

Other Possibilities:

Are you using a differencing disk or other form of snapshot? In that case vmware may be writing to a log structured snapshot file instead of the vdisk file itself - effectively turning random writes into sequential ones. On a spinning-surface-moving-head drive this could make a huge difference. IIRC vmare supports compressed vdisks too, which may also be log formatted and so effectively turn some random write patterns into sequential ones as well a reducing the amount actually being written. In both these cases the data may be completely flushed to disk safely while being that much faster then on the raw disk, unlike if the issue is down to unexpected write caching.

  • Does my system specs really deserve such a slow speed? Core i5 6th Generation, 8GB RAM, 7200RPM HDD, fresh Ubuntu Minimal installation, no other background services running other than default? – Shuji Jul 31 '18 at 14:25
  • @ShujaatAliKhan - no idea, I'm not a mysql expert unfortunately. Do you have benchmark results from other similarly specced systems? – David Spillett Jul 31 '18 at 14:46
  • Unfortunately no. – Shuji Jul 31 '18 at 14:48

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.