On the previous article we saw how to easily deploy Apache Zeppelin on OCI with MySQL using Terraform.
In this article we will see how we can accelerate queries enabling MySQL HeatWave.
Enabling HeatWave
If you used a MySQL Database Service Shape compatible with HeatWave (MySQL.HeatWave.VM.Standard.E3), using the same Resource Manager Stack, you have the possibility to enable HeatWave and deploy a HeatWave Cluster for your MySQL.
The first think to do, is to edit the previous applied stack:
As we used a HeatWave compatible shape, we have the choice to deploy a HeatWave Cluster and we can choose it:
Save and Apply… the HeatWave cluster will be added:
If you have a MySQL Shell session open (on the Compute instance) to your database, you can also check that all has been deployed successfully:
You can see some details in OCI’s dashboard too:
Loading Data to HeatWave
If you are familiar with MySQL HeatWave, you might already know how to load some data to HeatWave, if not, it’s very simple to find the easiest way to perform this operation.
Just edit the HeatWave Cluster and click on “Estimate Node Count”:
After having generated the estimate, select the airportdb:
And as result you will see if more nodes are recommended and also a statement to run:
After having entered that command in MySQL, the data is loaded in HeatWave and Zeppeling will use HeatWave everytime a query qualifies for HeatWave.
I’ve added another query in Zeppelin that executes very very quickly with HeatWave:
If the same query is ran without HeatWave, instead of being less than 1 sec, it will take more than 4 minutes:
I’ve tried the same query in Zeppelin without HeatWave, I stopped it after 38 hours… I’ve no idea why the query stayed in Executing state in processlist. If you know the reason let me know. We tried to execute using jdbc and MySQL-Connector-Java and it executed in several minutes too. With HeatWave it’s almost instantaneous.
We can also check that the queries were offloaded successfully to HeatWave:
Conclusion
It is very easy to add HeatWave to an existing MySQL Database Service instance if it runs one supported shape.
Using Resource Manager’s Stack this is just one click.
And as you can see Zeppelin can highly benefit from HeatWave when the queries are slow.
