You may have come across numerous presentations showcasing MySQL HeatWave as a Query Accelerator for MySQL. However, if you have not yet had the opportunity to test it yourself (if you have, you already know the answer), allow us to conduct a test using actual data to determine the potential benefits of utilizing a MySQL HeatWave Cluster.
Data & Queries
The data is simple, some arduinos with a DHT22 sensor sending temperature and humidity.
So first let’s have a look at the amount of collected data:
select format_bytes(sum(data_length)) DATA,
format_bytes(sum(index_length)) INDEXES,
format_bytes(sum(data_length + index_length)) 'TOTAL SIZE'
from information_schema.TABLES order by data_length + index_length;
+-----------+-----------+------------+
| DATA | INDEXES | TOTAL SIZE |
+-----------+-----------+------------+
| 21.89 GiB | 14.06 GiB | 35.95 GiB |
+-----------+-----------+------------+
1 row in set (0.0043 sec)Let us execute some queries to establish a point of reference:
select * from
(select device_id, max(value) as `max temp`,
min(value) as `min temp`,
avg(value) as `avg temp`
from temperature_history
group by device_id) a
natural join
(select device_id, max(value) as `max humidity`,
min(value) as `min humidity`,
avg(value) as `avg humidity`
from humidity_history group by device_id) b;
...
5 rows in set (6 min 39.1304 sec)And another one:
select * from
(select date(time_stamp) as `day`, device_id,
count(*) as `tot`, max(value) as `max hum`,
min(value) as `min hum`, avg(value) as `avg hum`
from humidity_history group by device_id, day) a
natural join
(select date(time_stamp) as `day`, device_id,
count(*) as `tot`, max(value) as `max temp`,
min(value) as `min temp`, avg(value) as `avg temp`
from temperature_history group by device_id, day) b
order by day, device_id;
...
51 rows in set (8 min 14.3943 sec)HeatWave Cluster
Now let’s enable HeatWave Cluster on OCI Console:
When ready, we can verify that everything is fine and that the cluster is ready:
show global status like 'rapid_%er%_status';
+----------------------+--------+
| Variable_name | Value |
+----------------------+--------+
| rapid_cluster_status | ON |
| rapid_service_status | ONLINE |
+----------------------+--------+
2 rows in set (0.0013 sec)
show global status like 'rapid_%number';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| rapid_cluster_ready_number | 1 |
+----------------------------+-------+
1 row in set (0.0012 sec)Then, we need to load the data to HeatWave:
call sys.heatwave_load(JSON_ARRAY('piday'), NULL);
...
Query OK, 0 rows affected (2 min 52.6052 sec)Testing the queries with HeatWave
It took less than 3 minutes to load the data to HeatWave, now it’s time to test again our queries:
select * from
(select device_id, max(value) as `max temp`,
min(value) as `min temp`,
avg(value) as `avg temp`
from temperature_history
group by device_id) a
natural join
(select device_id, max(value) as `max humidity`,
min(value) as `min humidity`,
avg(value) as `avg humidity`
from humidity_history group by device_id) b;
...
5 rows in set (4.0097 sec)And the second one:
select * from
(select date(time_stamp) as `day`, device_id,
count(*) as `tot`, max(value) as `max hum`,
min(value) as `min hum`, avg(value) as `avg hum`
from humidity_history group by device_id, day) a
natural join
(select date(time_stamp) as `day`, device_id,
count(*) as `tot`, max(value) as `max temp`,
min(value) as `min temp`, avg(value) as `avg temp`
from temperature_history group by device_id, day) b
order by day, device_id;
...
51 rows in set (4.4113 sec)This is a huge improvement:
| Initial Query Time | Query Time with HeatWave Cluster |
|---|---|
| 6 min 39.1304 sec | 4.0097 sec |
| 8 min 14.3943 sec | 4.4113 sec |
Could it be faster ?
We have increased the amount of HeatWave Cluster nodes:
We can verify it:
show global status like 'rapid_%number';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| rapid_cluster_ready_number | 2 |
+----------------------------+-------+
1 row in set (0.0020 sec)And let’s attempt the queries once more:
select * from
(select device_id, max(value) as `max temp`,
min(value) as `min temp`,
avg(value) as `avg temp`
from temperature_history
group by device_id) a
natural join
(select device_id, max(value) as `max humidity`,
min(value) as `min humidity`,
avg(value) as `avg humidity`
from humidity_history group by device_id) b;
...
5 rows in set (2.1237 sec)And for query 2:
select * from
(select date(time_stamp) as `day`, device_id,
count(*) as `tot`, max(value) as `max hum`,
min(value) as `min hum`, avg(value) as `avg hum`
from humidity_history group by device_id, day) a
natural join
(select date(time_stamp) as `day`, device_id,
count(*) as `tot`, max(value) as `max temp`,
min(value) as `min temp`, avg(value) as `avg temp`
from temperature_history group by device_id, day) b
order by day, device_id;
...
51 rows in set (2.3271 sec)Having more HeatWave nodes helps improving even more the query response time.
Conclusion
In conclusion, the answer to the question “Is MySQL HeatWave really faster?” is certainly YES! MySQL HeatWave exhibits remarkable speed and efficiency, surpassing expectations and delivering exceptional performance.
As we’ve seen, the HeatWave cluster can turbocharge all your queries. The more complex your query, the faster it’ll be! And guess what? Adding multiple HeatWave nodes can enhance your queries even more. So why wait? Go ahead and make your queries lightning-fast!
Enjoy fast queries on MySQL HeatWave !
