1

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?

  • 1 lakh... will edit the question if it is misleading – Dinesh Kumar Dec 28 '18 at 20:16
  • (Another common Indian term: 1 Crore = 10M) – Rick James Dec 28 '18 at 20:20
  • 1
    for not add new answer - difference very reasonable (already mentioned by @RickJames. SSD (even old SATA ssd) - faster always, but much better for random read. – a_vlad Jan 2 at 5:28
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.

  • I have updated the question as per you guidance, please check – Dinesh Kumar Dec 28 '18 at 20:30
  • both case 1 and case 2 were tested on both HDD and SSD – Dinesh Kumar Dec 28 '18 at 20:33
  • 1
    @DineshKumar - OK, I will accept that 8 and 5.5 are realistic. (My other questions were aimed at determining why those ratios are not the same. However, at some level, this is not important.) – Rick James Dec 28 '18 at 20:41
  • I want to make sure whether these type of queries as described in case1 will have random i/o especially when rows from first table were equi-referred to second table. – Dinesh Kumar Dec 28 '18 at 20:52
  • do you want any further info from my side? – Dinesh Kumar Dec 28 '18 at 21:06

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.