Sample Header Ad - 728x90

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