I could have set MySQL between parenthesis in the title as this article is more about how to use OpenTofu to deploy on OCI.

I will explain how to install OpenTofu and how to use it to deploy on OCI. I will also mention what are the required changes be able to use my previous Terraform deployment files.

As an example, let’s use the modules to deploy WordPress with MySQL HeatWave Database Service: oci-wordpress-mds.

Installing OpenTofu

If like me you are using a RPM based Linux distro, you can find the necessary information to create the yum repository on OpenTofu’s website:

$ sudo su -
# cat >/etc/yum.repos.d/opentofu.repo <<EOF
[opentofu]
name=opentofu …

We recently saw that we can benefit from using JSON documents with MySQL HeatWave cluster in OCI (HeatWave accelerator for MySQL Database Service).

However sometimes the data can’t be loaded to HeatWave Cluster or the query cannot use it.

And this is not always easy to understand why. Let’s get familiar on how to find the info.

Data not loaded in HW Cluster

Let’s see an example with this table (collection):

SQL > show create table listingsAndReviews\G
*************************** 1. row ***************************
       Table: listingsAndReviews
Create Table: CREATE TABLE `listingsAndReviews` (
  `doc` json DEFAULT NULL,
  `_id` char(28) GENERATED ALWAYS AS
 (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
  `_json_schema` json GENERATED ALWAYS AS 
  (_utf8mb4'{"type":"object"}') VIRTUAL,
  PRIMARY KEY (`_id`),
  CONSTRAINT `$val_strict_B70EB65BDDBAB20B0EE5BB7532C9060C422A06F8` …

Since the release of MySQL 8.0, the MySQL X Dev API has provided users with the convenient ability to utilize MySQL without the need to write a single line of SQL!

MySQL X Dev API brings the support for CRUD operations on JSON documents that are stored in MySQL. MySQL Document Store is ACID compliant and is compatible with everything MySQL like replication, InnoDB Cluster, …

The MySQL X Dev API is available using the MySQL X Protocol, listening by default on port 33060.

If you are interested in learning more about MySQL Document Store, please refer to the these presentations [1], [2], [ …

Some time ago, we saw how we could deploy WordPress on OCI using MySQL HeatWave Database Service with Read Replicas. We had to modify WordPress to use a specific plugin that configures the Read/Write Splitting on the application (WordPress): LudicrousDB.

Today, we will not modify WordPress to split the Read and Write operations, but we will use MySQL Router 8.2.0 (see [1], [2], [3]).

Architecture

The …

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 …

We’ve seen in previous articles what Transparent Read/Write Splitting is with MySQL 8.2 and how to use it with MySQL Connector/Python.

I love this new feature, but I was wondering if it was really worth it.

Will the application benefit from offloading reads to another node, and won’t using the MySQL Router and parsing the request slow down connections?

These are the kinds of questions I’d like to cover and answer in this article.

The environment

To perform the test, I use the following environment:

• Linux Kernel 5.15.0 – aarch64
• MySQL Community Server 8.2.0
• MySQL Router 8.2.0
• sysbench 1.1.0 using LuaJIT 2.1.0-beta3
…

As you know, one of the most eagerly waited features was released with MySQL 8.2: the transparent read/write splitting.

In this post, we’ll look at how to use it with MySQL-Connector/Python.

Architecture

To play with our Python program, we will use an InnoDB Cluster.

This is an overview of the cluster in MySQL Shell:

JS > cluster.status()
{
    "clusterName": "fred", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "127.0.0.1:3310", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "127.0.0.1:3310": {
                "address": "127.0.0.1:3310", 
                "memberRole": "PRIMARY", …

The latest release of MySQL (October 25th, 2023) is the second Innovation Release of the most popular Open Source database.

This new evolution release brings us ever closer to the very first MySQL LTS Release!

This new Innovation Release already contains contributions from our great Community. MySQL 8.2.0 contains patches from Meta, Nikolai Kostrigin, Meng-Hsiu Chiang (Amazon), Richard Dang, Shaohua Wang, Hao Lu, Wen He (Tencent), Yin Peng (Tencent) and Daniël van Eeden.

Let’s have a look at all these contributions:

Server Compiling

• #111549 – Made additional improvements to WITH_ZLIB functionality – Nikolai Kostrigin
• #111467 – Building with WITH_ZLIB=”system” would break the MySQL build as it failed to find …