Why is MySQL slow on the Ubuntu dedicated machine while fast on the Ubuntu VM?
2
votes
2
answers
3976
views
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
Asked by Shuji
(123 rep)
Jul 31, 2018, 12:08 PM
Last activity: Nov 26, 2021, 02:42 AM
Last activity: Nov 26, 2021, 02:42 AM