Showing entries 1 to 10 of 111
10 Older Entries »
Displaying posts with tag: mysql shell (reset)
A graph a day, keeps the doctor away ! – MySQL Checkpoint Age

In a previous post, I explained how you can collect and plot metrics using MySQL Shell.

This is a new series of article where I will explain how to read and understand some of the generated graphs.

Understanding your workload and seeing the evolution of it over time can help anticipating problems and work on solutions before the breakdown.

Let’s start the series with a concept that is not always well understood or at least not always considered at its true value: MySQL Checkpoint Age.

example of checkpoint age graphInnoDB Checkpointing

Before analyzing the graph, we need to understand what is MySQL InnoDB Checkpointing.

Each change to a data page in the InnoDB Buffer Pool are also written into the Write Ahead Logs.

In the literature they are sometimes called Transaction …

[Read more]
MySQL RegExp Default

We had an interesting set of questions regarding the REGEXP comparison operator in MySQL today in both sections of Database Design and Development. They wanted to know the default behavior.

For example, we built a little movie table so that we didn’t change their default sakila example database. The movie table was like this:

CREATE TABLE movie
( movie_id     int unsigned primary key auto_increment
, movie_title  varchar(60)) auto_increment=1001;

Then, I inserted the following rows:

INSERT INTO movie 
( movie_title )
VALUES
 ('The King and I')
,('I')
,('The I Inside')
,('I am Legend');

Querying all results with this query:

SELECT * FROM movie;

It returns the following results:

+----------+----------------+
| movie_id | movie_title    |
+----------+----------------+
|     1001 | The King and I |
|     1002 | I              |
|     1003 | The I Inside   |
|     1004 | I am Legend …
[Read more]
Poorman’s MySQL monitoring/trending

I could have also called this article When Pandas meet Dolphins in MySQL Shell.

Some time ago, I wrote a post related on how to collect initial relevant data when trying to seek help for MySQL.

Since then, the MySQL Shell engineering team implemented another powerful native utility that collect all the essential information and more and store them in a single zip file.

This zip archive contains TSV and YAML files that, for example, the MySQL Support Team could use to solve your eventual issue.

For more information regarding MySQL Shell’s util.debug.collectDiagnostics(), I encourage you to check the manual.

This tool is …

[Read more]
Poorman’s MySQL table audit information – part 3

Recently we saw how we can catch audit information using invisible column, JSON data type and triggers in MySQL 8.0:

Of course, the creation of these triggers can quickly become a tedious operation. Once again, MySQL has all what we need to make it simple.

As you may know, it’s impossible to create triggers from store procedure, so using a sys schema function would not be something possible. However, MySQL Shell is again the answer ! It’s very easy to use a python plugin to perform the necessary operations.

I’ve written such example, available on …

[Read more]
Live Migration from Azure Database for MySQL to MySQL Database Service on OCI

MySQL Database Service on OCI is all over the tech news with the latest HeatWave ML announcement [1][2][3][4][5].

This article explains how to perform a live migration of your Azure/MySQL to OCI/MySQL.

As Azure allows public connections directly to the database, this is what …

[Read more]
Using MySQL Database Service in OCI – Part 3: faster with MySQL HeatWave !

This post is the third and last of a series of articles dedicated to MySQL Database Service (MDS):

The goal of this series if to get familiar with MDS, HeatWave and useful MySQL Shell utilities.

We can now start a query on our MDS instance that has been populated with >200M rows:

 SQL > SELECT year, Reporting_Airline,  
              AVG(ArrDelay) AS avgArrDelay …
[Read more]
Using MySQL Database Service in OCI – Part 2: importing Data to MySQL DB System

This post is the second of a series of articles dedicated to MySQL Database Service (MDS):

The goal of this series if to get familiar with MDS, HeatWave and useful MySQL Shell utilities.

Before we start, I would like to highlight that the fastest and recommended way to import data to a MySQL DB System in OCI is to use a parallel dump created using MySQL to Object Storage and load it …

[Read more]
Using MySQL Database Service in OCI – Part 1: creating a MySQL DB System

This post is the first of a series of articles dedicated to MySQL Database Service (MDS):

The goal of this series if to get familiar with MDS, HeatWave and useful MySQL Shell utilities.

In this first article we will start by creating a MySQL DB System and a Compute instance in OCI. One of the goal is also to see the benefits of HeatWave, so we will start by creating this …

[Read more]
MySQL 8.0 Document Store – How to deal with date & time

As you know, MySQL 8.0 can be used as JSON Document Store to store your documents without being linked to any schema. You can also use CRUD operations to deal with these documents using the MySQL X DevAPI.

Of course in documents, it’s possible to also store temporal attributes like date, time, datetime, …

Let’s see how we can deal with such attributes.

This is an example of a collection of documents with a datetime attribute createdOn:

As those attributes don’t have a real type, can we use the createdOn attribute as if it was a real datetime field ?

Let’s try to get all the documents have a created data > '2021-12-02':

We can see that the document “dave” has been filtered out. However, we can see that “kenny” and “miguel” are also present… and this is correct as …

[Read more]
MySQL 8.0 – locking details

Recently, I saw many interest in understanding and getting information about database locking.

MySQL InnoDB’s locking can be complex and having an overview not always simple.

For more information about how InnoDB locking is working, I can only recommend this excellent series of articles by Kuba:

[Read more]
Showing entries 1 to 10 of 111
10 Older Entries »