Performance of mysql equi-join observed in HDD and SSD

Question

I experimented performance of queries having equi-joins. I have simplified version of EXPLAINs of the queries.

Mysql version : 5.7.18

Engine : Innodb

Cache state : Mysql restarted before experiment so no buffer pool cache. Since, physical machine is restarted before every experiment there won't be OS cache.

Note : Primary key is nothing but an increasing INT.

CPUs: SSD machine is a Mac machine with 2.5 GHz Intel Core i7. HDD machine is a Mac machine with 2.3 GHz Intel Core i7

Case 1:

| id | select_type | table | partitions | type   | possible_keys | key     |  ref             | rows   | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+--------+----------+----+
|  1 | SIMPLE      | c     | NULL       | ref    | PRIMARY       | PRIMARY |  const,const     | 190864 |   100.00 | Using index |
|  1 | SIMPLE      | r     | NULL       | eq_ref | PRIMARY       | PRIMARY |  c.id.           |      1 |    10.00 | Using where |

Case 2:

+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+--------+-------
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                         | rows   |Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+--------+------+
|  1 | SIMPLE      | c     | NULL       | ref    | PRIMARY       | PRIMARY | 771     | const,const                 | 190864 |Using index 
|  1 | SIMPLE      | g     | NULL       | eq_ref | PRIMARY       | PRIMARY | 775     | const,const,c.id            |      1 |Using index 
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------------+--------+

I both cases it can be noted that:

• the first table having 190864 rows ready to be joined to second table
• the second table is about to join in 'eq_ref' order.

Interpretation: Each row from first table is going to find its pair in second table. Since there are 100k rows in first table , there will be 100k i/o to disk in random fashion (i.e., simply random i/o correct me if I were wrong). I have run this experiment with HDD and SSD.

Results (time taken):

Case 1 : 3.409s (SSD) and 26.651s (HDD)

Case 2 : 0.248s (SSD) and 1.364s (HDD)

It can be seen that there is a huge difference in performance. Is this because of that SSD are better at random I/Os? or simply SSDs are better at I/O itself?

Answer 1

I am excited to hear reliable comparisons between HDD and SSD, but first...

Here are some of the variables you have not accounted for. (Or maybe you did, but failed to state.)

• InnoDB?
• One says "Using index", meaning that the processing happened only in the index's BTree ("Covering" index); the other apparently had to bounce between the secondary index and the data. This could make a significant difference.
• Table sizes?
• Was the cache (buffer_pool) cold? Please restart mysqld before each test.
• Same CPU, since you want to focus on the I/O, not the CPU time.
• If it was the same server, which drive was c on?
• SHOW CREATE TABLE -- there could be subtle issues.
• MySQL version.
• Are the indexes aligned with the data? For example: AUTO_INCREMENT and TIMESTAMP might be virtually in lock-step. But UUID is very random.

8x and 5.5x seem to be reasonable.