Some times it’s important to know the size of a transaction, especially when you plan to migrate to a HA solution where by default transactions have a limited size to guarantee an optimal behavior of the cluster.

Today we will see the different possibilities to have an idea of the size of transactions.

First we need to split the transaction in two types:

• those generating data (writes, like insert, delete and update, DML)
• those only ready data (select, DQL)

To implement High Availability, only the first category is important.

Size of DML

To know the size of a DML transaction, the only possibility we have is to parse the binary log (or query the binlog event).

We need to check the binlog event from the binlog file and then calculate its size. To illustrate this, let’s try to find the transaction identified by a specific GTID: …

When you connect to a server (or cluster) using a TCP proxy level 7, also referred to as an application-level or Layer 7 proxy (highest level of the OSI model), the application doesn’t connect directly to the back-end server(s). The proxy usually understands the protocol used and can eventually take some decisions or even change the request.

The problem when using such proxy (like HA Proxy, ProxySQL and MySQL Router) is that the server doesn’t really know from where the client is connecting. The server sees the IP address of the proxy/router as the source IP of the client.

HA Proxy initially designed the Proxy Protocol, a simple protocol that allows a TCP connection to transport proxy-related information between the client, the proxy server and the destination server. The main purpose of the Proxy Protocol is then to preserve the client’s original IP address (with some others metadata). See …

In this new article about how to find the info when using MySQL Database Service on Oracle Cloud Infrastructure, we will learn about the query accelerator: HeatWave.

With HeatWave, you can boost the performance of your MySQL queries, providing your applications with faster, more reliable, and cost-effective access to data.

HeatWave is a high-performance in-memory query accelerator for MySQL Database Service on Oracle Cloud Infrastructure. It is designed to accelerate analytics workloads (OLAP) and increase the performance of your MySQL databases by orders of magnitude. This is achieved through the use of in-memory processing, advanced algorithms, and machine learning techniques to optimize query performance. If identified by the optimizer, OLTP requests can also be accelerated using HeatWave.

Today we will try to answer the following questions:

1. Can I use HeatWave ?
2. Is HeatWave enabled ?
…

As a MySQL DBA, you like to know who is connected on the system you manage. You also like to know who is trying to connect.

In this article, we will discover how we can retrieve the information and control who is using the MySQL DB instance we launched in OCI.

Secure Connections

The first thing we can check is that all our clients encrypt their connection to the MySQL server.

We use again Performance_Schema to retrieve the relevant information:

select connection_type, substring_index(substring_index(name,"/",2),"/",-1) name,
       sbt.variable_value AS tls_version, t2.variable_value AS cipher,
       processlist_user AS user, processlist_host AS host
from performance_schema.status_by_thread AS sbt
join performance_schema.threads AS t 
  on t.thread_id = sbt.thread_id
join performance_schema.status_by_thread AS t2 
  on t2.thread_id = t.thread_id
where sbt.variable_name = 'Ssl_version' and …

For this third article of the series dedicated on how a DBA can find the info he needs with MySQL Database Service in Oracle Cloud Infrastructure, we will see how we can find the error log.

When using MySQL DBAAS, the DBA doesn’t have direct access to the files on the filesystem. Hopefully, with MySQL 8.0, the error log is also available in Performance_Schema.

This is exactly where you will find the information present also in the error log file when using MDS in OCI:

select * from (select * from performance_schema.error_log order by logged desc limit 10) a order by logged\G
*************************** 1. row ***************************
    LOGGED: 2023-03-19 08:41:09.950266
 THREAD_ID: 0
      PRIO: System
ERROR_CODE: MY-011323
 SUBSYSTEM: Server
      DATA: X Plugin ready for connections. Bind-address: '10.0.1.33' port: 33060, socket: /var/run/mysqld/mysqlx.sock
*************************** 2. row …

This article is the second of the new series dedicated on how a DBA can find the info he needs with MySQL Database Service in Oracle Cloud Infrastructure.

The first article was dedicated on Backups, this one is about Disk Space Utilization.

This time we have two options to retrieve useful information related to disk space:

1. Metrics
2. Performance_Schema

Metrics

In the OCI Web Console, there is a dedicated metric for the disk usage:

As for the backup, we can create Alarms for …

In this new series of articles we will explore the different sources of information available when using MySQL Database Service on OCI to effectively perform your daily DBA job.

Of course there is way less things to take care of, like backups, upgrades, operating system and hardware maintenance, …

But as a serious DBA, you want to know the status of all this and maintain some control.

Some information is available on OCI’s webconsole and some in Performance_Schema and Sys.

If you use MySQL Shell for Visual Studio Code, you have the possibility to see an overview of your server using the Performance Dashboard:

But today we will take a look at the backup, a very important responsibility of the DBA.

When you use MySQL Database Service on OCI, you can define the backup policy at the DB Instance’s creation. You can always modify it later:

In …

Recently, I wrote three articles on how to analyze queries and generate a slow query log for MySQL Database Service on OCI:

• https://lefred.be/content/analyzing-queries-in-mysql-database-service/
• https://lefred.be/content/analyzing-queries-in-mysql-database-service-slow-query-log-part-1/
• https://lefred.be/content/analyzing-queries-in-mysql-database-service-slow-query-log-part-2/

In these post, we were generating a slow query log in text or JSON directly in Object Storage.

Today, we will see how we can generate …

In the previous part, we created our application and our 2 functions.

Now we need to create an API Gateway to be able to call these functions from outside OCI. Using a third party scheduler, our laptop, etc…

Before creating the gateway, we need to create some policy to allow the API Gateway to call our functions. I’ve tried to create the policy after, it seems to not work as expected.

Security

Dynamic Group

We need to create a Dynamic Group that will match our gateway:

The matching rule is checking the type of resource that should be ApiGateway and my compartment’s id:

All {resource.type='ApiGateway', resource.compartment.id = 'ocid1.compartment.oc1..xxxxxxxxx'}

Policy …

In my previous post, I explained how to deal with Performance_Schema and Sys to identify the candidates for Query Optimization but also to understand the workload on the database.

In this article, we will see how we can create an OCI Fn Application that will generate a slow query log from our MySQL Database Service instance and store it to Object Storage.

The creation of the function and its use is similar to the one explained in the …