The ability to store data on Object Storage and retrieve it dynamically when necessary is a notable advantage of Lakehouse when managing MySQL historical data we would like to archive.

Let’s illustrate this with the following table:

CREATE TABLE `temperature_history` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `time_stamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `device_id` varchar(30) DEFAULT NULL,
  `value` decimal(5,2) NOT NULL DEFAULT '0.00',
  `day_date` date GENERATED ALWAYS AS (cast(`time_stamp` as date)) STORED NOT NULL,
  PRIMARY KEY (`id`,`day_date`),
  KEY `device_id_idx` (`device_id`)
) ENGINE=InnoDB AUTO_INCREMENT=129428417 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SECONDARY_ENGINE=RAPID
/*!50500 PARTITION BY RANGE  COLUMNS(day_date)
(PARTITION p0_before2023_11 VALUES LESS THAN ('2023-11-01') ENGINE = InnoDB,
 PARTITION p2023_12 VALUES LESS THAN ('2023-12-01') ENGINE = InnoDB,
 PARTITION p2024_01 VALUES LESS THAN …

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 …

Since MySQL Shell 8.1, it’s even easier to create a logical backup of your MySQL instance and store it directly in Object Storage, an internet-scale, high-performance storage platform in Oracle Cloud Infrastructure (OCI).

MySQL Shell now offers the option of dumping to Object Storage Bucket using PAR (Pre-Authenticated Request).

Bucket Creation

The first step is to create an Object Storage Bucket in the OCI Console:

Let’s call it lefred-mysql-backups:

When created, we can click on the three-dots and create a new PAR:

In this blog post, we’ll review how to run Linux profilers such as perf and produce flame graphs on Kubernetes environments.

Flame graphs are a graphical representation of function calls. It shows which code paths are more busy on the CPU in given samples. They can be generated with any OS profiler that contains stack traces such as perf, eBPF, and SystemTap.

An example of a flame graph can be found below:

Each box is a function in the stack, and wider boxes mean more time the system was busy on CPU on these functions.

Kubernetes limitations

In Linux, by default, performance system events can’t be collected by unprivileged users. In regular environments, this can be easily worked around by running the profiler with a sudo privilege.

On the other hand, in Kubernetes environments, pods are the smallest deployable unit that consists of one or more containers. Exploits are generally targeted to …

Let’s see how to deploy WordPress and MySQL on a Kubernetes Cluster. The Kubernets cluster we are using is OKE (Oracle Kubernetes Engine) in OCI (Oracle Cloud Infrastructure):

OKE Cluster

We start by creating a Kubernetes Cluster on OCI using the Console:

We select the Quick create mode:

We need to name our cluster and make some choices:

When created, we can find it in the OKE Clusters list:

And we can see the pool of workers nodes and the workers:

kubectl

I like to use kubectl directly on my latop to manage my K8s Cluster.

On my Linux Desktop, I need to install kubernetes-client package (rpm).

Then on the K8s Cluster details, you can click on Access Cluster to get all the commands to use:

We need to copy them on our terminal and then, I like to also enable the bash completion for …

Performing an operation is always challenging when dealing with K8s.

When on-prem or DBaaS like RDS or Cloud SQL, it is relatively straightforward to apply a change. You can perform a DIRECT ALTER, use a tool such as pt-osc, or even, for certain cases where async replication is in use, perform changes on replicas and failover.

In this blog post, I’ll provide some thoughts on how schema changes can be performed when running MySQL on Kubernetes

I won’t focus on DIRECT ALTERs as it is pretty straightforward to apply them. You can just connect to the MySQL service POD and perform the ALTER.

But how can we apply changes in more complex scenarios where we may want to benefit from pt-osc, gain better control over the operation, or take advantage of the K8s features?

One convenient way that I’ve found …

To copy a MySQL server to another server or to the cloud, there are several ways.

We can distinguish between two different types of copy:

• physical copy
• logical copy

The physical copy is often the fastest. However, it requires some tools to ensure that you have a consistent online backup. For example, you can use MySQL Enterprise Backup (MEB).

Alternatively, it’s possible to use the CLONE plug-in to provision a new instance with existing data from a source server. This is my preferred approach.

Finally, the last physical solution is the use of a file system snapshot, but this requires the right infrastructure and even more care to have a consistent …

Sometimes it’s convenient to retrieve the user creation statement and to copy it to another server.

However, with the new authentication method used as default since MySQL 8.0, caching_sha2_password, this can become a nightmare as the output is binary and some bytes can be hidden or decoded differently depending of the terminal and font used.

Let’s have a look:

If we cut the create user statement and paste it into another server what will happen ?

We can see that we get the following error:

ERROR: 1827 (HY000): The password hash doesn't have the expected format.

How could we deal with that ?

The solution to be able to cut & paste the authentication string without having any issue, is to change it as a binary representation (hexadecimal) like this:

And then replace the value in the user create statement:

But there is an easier way. …

To continue our journey to Moodle on Oracle Cloud Infrastructure using Ampere compute instances and MySQL HeatWave Database Service [1] [2], in this article we will see how to scale our architecture using multiple Moodle instances, High Availability for the Database and Read Scale-Out.

This is the architecture we will deploy:

The same principles can be applied to other projects, not just Moodle.

Multiple Compute Instances & MySQL HeatWave High Availability

The first step is to use again the Stack to deploy the initial resources. We must insure that we use a MySQL Shape that has at least 4 OCPUs …

In the previous post, we saw how to quickly deploy Moodle to Oracle Cloud Infrastructure on Ampere compute instances and using MySQL HeatWave.

In this post, we will explore some other features and the benefits of running on OCI and MySQL HeatWave to extend our architecture dedicated to Moodle in the Cloud.

Read Replicas

Moodle natively offers the possibility of distributing the load between reads and writes. When using MySQL HeatWave Database Service, adding read replicas is also a very easy task. Let’s see how we can benefit from it.

To be able to use MySQL HeatWave Read Replicas, the MySQL shape must have at least 4 OCPUs.

Let’s modify the moodle stack and deploy it again but this time we choose a bigger shape for MySQL:

When everything is ready, …