MySQL vs. MSSQL on the Amazon Cloud

Recently I’ve been working on a application that requires some serious database performance and had chosen MySQL as the engine based on the common wisdom that MySQL is faster, and cheaper, than MSSQL. After some serious performance problems on a couple of local machines, I decided to use Amazon’s MySQL RDS service and compare it’s performance to their Windows 2008 / MSSQL 2008 instance. This is by no means and exhaustive study, but it is informative at least.

Amazon Instance Type

  • Large Instance 7.5 GB of memory, 4 EC2 Compute Units (2 virtual cores with 2 EC2 Compute Units each), 850 GB of local instance storage, 64-bit platform

Table Structure

  • 5 VarChar(50) columns
  • ~2.1 Million Records
  • Un-Indexed
  • No Primary or Foreign Key Constraints

SQL Process

  • Table data copy using: INSERT INTO myTable2 (SELECT * FROM myTable1)

Processing Time

  • MySQL: Killed at +15 minutes
  • MSSQL: Completed at 1 minute 33 seconds

For large data moving operations, the winner is clear — MSSQL outperforms MySQL by an enormous margin. In further testing I’ve also found simple select operations on MySQL to be lacking as well. Looks like Microsoft had a leg up on the database world finally.

Advertisements

3 Responses to “MySQL vs. MSSQL on the Amazon Cloud”

  1. Chris Tooley Says:

    So, I was interested in this particular issue and found it thoroughly confusing. I had exceptionally different results.
    I ran this on my local laptop. Due to the fact that the company I am contracting with doesn’t like me installing Linux on their machines I am running entirely off of a USB2 enclosure so disk i/o is a bit of an issue. However, as you can tell, my performance was very different than yours.

    I created a csv with 5 columns of 50 A’s per column and then copied that row 2.1 million times.

    mysql> LOAD DATA INFILE ‘datafile.csv’ INTO TABLE table1 FIELDS TERMINATED BY ‘,’;
    Query OK, 2100001 rows affected (17.74 sec)
    Records: 2100001 Deleted: 0 Skipped: 0 Warnings: 0

    mysql> create table table2 like table1;
    Query OK, 0 rows affected (0.07 sec)

    mysql> insert into table2 SELECT * FROM table1;
    Query OK, 2100001 rows affected (17.40 sec)
    Records: 2100001 Duplicates: 0 Warnings: 0

    mysql> SHOW CREATE TABLE table1;
    +——–+———————————————————————————————————————————————————————————————————————————————-+
    | Table | Create Table |
    +——–+———————————————————————————————————————————————————————————————————————————————-+
    | table1 | CREATE TABLE `table1` (
    `col1` varchar(50) DEFAULT NULL,
    `col2` varchar(50) DEFAULT NULL,
    `col3` varchar(50) DEFAULT NULL,
    `col4` varchar(50) DEFAULT NULL,
    `col5` varchar(50) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    +——–+———————————————————————————————————————————————————————————————————————————————-+
    1 row in set (0.00 sec)

    mysql> SHOW CREATE TABLE table2;
    +——–+———————————————————————————————————————————————————————————————————————————————-+
    | Table | Create Table |
    +——–+———————————————————————————————————————————————————————————————————————————————-+
    | table2 | CREATE TABLE `table2` (
    `col1` varchar(50) DEFAULT NULL,
    `col2` varchar(50) DEFAULT NULL,
    `col3` varchar(50) DEFAULT NULL,
    `col4` varchar(50) DEFAULT NULL,
    `col5` varchar(50) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
    +——–+———————————————————————————————————————————————————————————————————————————————-+
    1 row in set (0.00 sec)

  2. Wayne Walker Says:

    Andrew,

    I don’t know what’s up, but that query, on MySQL, on my desktop with 8 GB of RAM takes less than 10 seconds. My tables are over 550 MB in size.

    Please update your article to show the full create table statements and what kind of data you have.

    It could just be terrible configuration on the part of Amazon RDS, but something is wrong and it is not MySQL that is the problem.

    
    
    mysql> show table status;
    +----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
    | Name     | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
    +----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
    | mytable1 | MyISAM |      10 | Dynamic    | 2100000 |            260 |   546000000 | 281474976710655 |         1024 |         0 |           NULL | 2011-02-03 16:16:45 | 2011-02-03 16:17:03 | NULL       | latin1_swedish_ci |     NULL |                |         |
    | mytable2 | MyISAM |      10 | Dynamic    |       0 |              0 |           0 | 281474976710655 |         1024 |         0 |           NULL | 2011-02-03 16:52:52 | 2011-02-03 16:52:52 | NULL       | latin1_swedish_ci |     NULL |                |         |
    +----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
    2 rows in set (0.00 sec)
    
    mysql> insert into mytable2 (select * from mytable1);
    Query OK, 2100000 rows affected (2.31 sec)
    Records: 2100000  Duplicates: 0  Warnings: 0
    
    mysql> show table status;
    +----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
    | Name     | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
    +----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
    | mytable1 | MyISAM |      10 | Dynamic    | 2100000 |            260 |   546000000 | 281474976710655 |         1024 |         0 |           NULL | 2011-02-03 16:16:45 | 2011-02-03 16:17:03 | NULL       | latin1_swedish_ci |     NULL |                |         |
    | mytable2 | MyISAM |      10 | Dynamic    | 2100000 |            260 |   546000000 | 281474976710655 |         1024 |         0 |           NULL | 2011-02-03 16:52:52 | 2011-02-03 16:54:07 | NULL       | latin1_swedish_ci |     NULL |                |         |
    +----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
    2 rows in set (0.00 sec)
    
  3. Wayne Walker Says:

    Here is the same thing using the InnoDB engine. It took 55 seconds.

    mysql> create table mytable1 (a1 varchar(50), a2 varchar(50), a3 varchar(50), a4 varchar(50), a5 varchar(50)) engine=InnoDB;
    Query OK, 0 rows affected (0.18 sec)
    
    mysql> create table mytable2 like mytable1;
    Query OK, 0 rows affected (0.19 sec)
    
    mysql> show table status;
    +----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
    | Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
    +----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
    | mytable1 | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2011-02-03 17:07:21 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
    | mytable2 | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2011-02-03 17:07:31 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
    +----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
    2 rows in set (0.00 sec)
    
    mysql> load data local infile 'data.csv' into table mytable1 fields terminated by ',' ; 
    Query OK, 2100000 rows affected, 65535 warnings (55.17 sec)
    Records: 2100000  Deleted: 0  Skipped: 0  Warnings: 10500000
    
    mysql> show table status;                  
    +----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
    | Name     | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
    +----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
    | mytable1 | InnoDB |      10 | Compact    | 2100069 |            304 |   639631360 |               0 |            0 |   7340032 |           NULL | 2011-02-03 17:07:21 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
    | mytable2 | InnoDB |      10 | Compact    |       0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2011-02-03 17:07:31 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
    +----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
    2 rows in set (0.00 sec)
    
    mysql> insert into mytable2 (select * from mytable1);
    Query OK, 2100000 rows affected (55.16 sec)
    Records: 2100000  Duplicates: 0  Warnings: 0
    
    mysql> show table status;
    +----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
    | Name     | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
    +----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
    | mytable1 | InnoDB |      10 | Compact    | 2100069 |            304 |   639631360 |               0 |            0 |   7340032 |           NULL | 2011-02-03 17:07:21 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
    | mytable2 | InnoDB |      10 | Compact    | 2100069 |            304 |   639631360 |               0 |            0 |   7340032 |           NULL | 2011-02-03 17:07:31 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
    +----------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
    2 rows in set (0.00 sec)
    
    mysql> 
    

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: