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 …

If like me you are an old experienced MySQL DBA, to analyze your MySQL workload, you certainly have used the slow query log with long_query_time set to 0.

The slow query log is a file that contains all the queries whose execution time is greater than the value of long_query_time. This file can be huge and uses up all available disk space on busy systems.

Constantly writing to that file can also add an overhead to the server.

For those reasons, the slow query log is not available in MySQL Database Service (HeatWave) in OCI.

Plan B ?

As a DBA, what are my options for finding the queries that need to be optimized? As usual, the queries that consume the most time are the ones that need some attention

It can be a very long query or a short query executed too many times.

Currently the MySQL DBAs use …

Let’s continue our journey of deploying the MySQL Database System on OCI with Terraform.
This time we will see how we can use a backup (see [1] and [2]) as a source (initial data) for a new instance.

Within the oci_mysql_mysql_db_system it’s possible to define a source detailing how to provision the initial data of the db system.

Let’s deploy a new MySQL Database Instance using a manual backup we made earlier:

We can also find the backup’s ocid using the oci cli command:

 $ oci mysql backup list …

Recently I wrote an article on how to define a backup policy for MySQL Database Service in OCI using Terraform. We saw that it was possible to define tags (defined_tags and freeform_tags) in the backup_policy section of a oci_mysql_mysql_db_system resource.

However, it seems that these tags are never used for manual or automated backups in the MySQL Database Service backup process. In the strategy implemented on OCI, the backups inherit the tags from the MySQL DB system itself.

This means that if you want to have some custom tags on your backups, you need to specify them in the MySQL Database resource like this (on line 12):

resource "oci_mysql_mysql_db_system" "MDSinstance" {
    admin_password = var.admin_password
    admin_username = …

Let’s continue the discovery of the MySQL Database Resource when deploying on Oracle Cloud Infrastructure using Terraform.

Last week, we saw how to create custom configurations and define user variables. Today we will see how we can define a backup policy and a maintenance window.

Backup Policy

In the oci_mysql_mysql_db_system resource, we will add a new section called backup_policy like this:

backup_policy {
       is_enabled        = "true"
       retention_in_days = "3"
       window_start_time = "01:00-00:00"
       freeform_tags = {"backup_defined_by"="Terraform"}
       pitr_policy {
            is_enabled = "true"
       }
}

This part of code (you can see in a working Terraform architecture sample), enables backup, sets the retention days to 3. It also defines the starting time …

With Terraform OCI provider 4.90.0 (released August 24th, 2022), we have added the possibility to specify the value of editable configuration variables directly from Terraform.

These variables must be defined in a resource of type oci_mysql_mysql_configuration like this:

resource "oci_mysql_mysql_configuration" "mds_mysql_configuration" {
        #Required
        compartment_id = var.compartment_ocid
    shape_name   = var.mysql_shape

         …

The following is a short HOWTO about deployment and use of Benchmark-kit (BMK-kit). The main idea of this kit is to simplify your life in running various MySQL benchmark workloads with less blood and minimal potential errors.

Generally as simple as the following :

$ bash /BMK/sb_exec/sb11-Prepare_50M_8tab-InnoDB.sh 32   # prepare data

$ for users in 1 2 4 8 16 32 64 128 256 512 1024 2048
do   
  # run OLTP_RW for 5min each load level..
  bash /BMK/sb_exec/sb11-OLTP_RW_50M_8tab-uniform-ps-trx.sh $users 300
  sleep 15
done

the latest public online version of the following HOWTO is always available from here : http://dimitrik.free.fr/blog/posts/mysql-perf-bmk-kit.html

Read more... (42 min remaining to read)

This fall, from October 16 to 20, the MySQL Summit will be held in La Vegas.

This conference is totally dedicated to your favorite dolphin database and is part of Oracle CloudWorld.

MySQL Summit will bring together a large community of new and expert MySQL users.

Attendees will be able to meet the engineers, product managers and developers who make MySQL the number one open source database in the world!

MySQL Summit will be different from MySQL’s previous appearances at Oracle OpenWorld, for the summit, we, as MySQL Team, users of MySQL… MySQL Community, will have more sessions, dedicated tracks and more !

The call for papers is now open until June 24, 2022.

CfP is extented !

The Call for Papers ends on June 30th !

If you …