This brief summarises the proceedings and outcomes of the 2nd MyWebinar which was held on 13th February 2021 at Online Webinar. As part of our thought leadership webinar series, our latest hosting webinar Migrate your EOL MySQL Servers (seamless migration to MySQL group replication / InnoDB cluster)

We have conducted MyWebinar with a very positive response with the help of software like zoom hosting arrangement and YouTube streaming and commitment of our business team, We have easily planned the perfect broadcasting for all of the attendees.

Over 30+ people took part in our webinar on 13th Feb 2021, to learn MySQL EOL and upgrade path. The session “Migrate your EOL MySQL servers to HA Complaint GR Cluster / InnoDB Cluster With Zero Downtime” by  …

The goal of this blog post is to explain the various types of transaction isolation levels available in MySQL. After reading the blog, you will be able to explain dirty reads, non-repeatable reads, and the concept of phantom rows as well.

What is the Isolation Level in MySQL?

Isolation (I) is one of the properties from ACID. It defines how each transaction is isolated from other transactions and is a critical component of application design. As per the SQL:1992 standard, InnoDB has four types of Isolation levels. Below, I have listed the types in order, and each transaction isolation level provides better consistency compared to the previous one.

• READ-UNCOMMITTED
• READ-COMMITTED
• REPEATABLE-READ – ( MySQL’s DEFAULT )
• SERIALIZABLE

You can change the isolation level using the variable “transaction_isolation” at runtime. As transaction isolation changes can …

Large replication topologies are quite common nowadays, and this kind of architecture often requires a quick method to rebuild a replica from another server.

The Clone Plugin, available since MySQL 8.0.17, is a great feature that allows cloning databases out of the box. It is easy to rebuild a replica or to add new nodes to a cluster using the plugin. Before the release of the plugin, the best open-source alternative was Percona XtraBackup for MySQL Databases.

In this blog post, we compare both alternatives for cloning purposes. If you need to perform backups, Percona XtraBackup is a better tool as it supports compression and incremental backups, among other features not provided by the plugin. The plugin supports compression only for network transmission, not for storage.

But one of the plugin’s strong points is simplicity. …

First we will talk about some of the other internal users of the technology that underpins the InnoDB Clone. MySQL Enterprise Backup (MEB) is an enterprise offering that provides backup and recovery for MySQL. Among various types of backups available, the following two types are of interest to us:

• Full Backup – A backup that backs up the entire MySQL instance – all the tables in each MySQL database.

… Facebook Twitter LinkedIn

In this post, we will discuss what to do when you add more memory to your instance. Adding memory to a server where MySQL is running is common practice when scaling resources.

First, Some Context

Scaling resources is just adding more resources to your environment, and this can be split in two main ways: vertical scaling and horizontal scaling.

Vertical scaling is increasing hardware capacity for a given instance, thus having a more powerful server, while horizontal scaling is adding more servers, a pretty standard approach for load balancing and sharding.

As traffic grows, working datasets are getting bigger, and thus we start to suffer because the data that doesn’t fit into memory has to be retrieved from disk. This is a costly operation, even with modern NVME drives, so at some point, we will need to deal with either of the scaling solutions we mentioned.

In this case, we will discuss adding more …

These are the slides of the “Efficient Use Of Indexes In MySQL” talk we delivered on the SFMySQL Meetup.

This is an introductory talk for developers on MySQL indexes. In my opinion, it’s quite important to understand how InnoDB organizes data. If you know how MySQL accesses data, it’s easier to write optimal queries.

When working with queries, I imagine secondary indexes as a table with records sorted by secondary key fields. This is a powerful concept that helps to understand the MySQL logic. It’s also easy to understand complex optimizations like loose index scan.

For example, for index (last_name, rank) the secondary index table looks like:

Enjoy the slides!

…

Recently I posted about checkpointing in MySQL, where MySQL showed interesting “wave” behavior.

Soon after Dimitri posted a solution with how to fix “waves,” and I would like to dig a little more into proposed suggestions, as there are some materials to process.

This post will be very heavy on InnoDB configuration, so let’s start with the basic configuration for MySQL, but before that some initial environment.

I use MySQL version 8.0.21 on the hardware as described here. 

As for the storage, I am not using some “old dusty SSD”, but production available Enterprise-Grade Intel SATA SSD …

InnoDB ReplicaSet was introduced from MySQL 8.0.19. It works based on the MySQL asynchronous replication. Generally, InnoDB ReplicaSet does not provide high availability on its own like InnoDB Cluster, because with InnoDB ReplicaSet we need to perform the manual failover. AdminAPI includes the support for the InnoDB ReplicaSet. We can operate the InnoDB ReplicaSet using the MySQL shell. 

• InnoDB cluster is the combination of MySQL shell and Group replication and MySQL router
• InnoDB ReplicaSet is the combination of MySQL shell and MySQL traditional async replication and MySQL router

Why InnoDB ReplicaSet?

• You can manually perform the switchover and failover with InnoDB ReplicaSet
• You can easily add the new node to your replication environment. InnoDB ReplicaSet helps with data provisioning (using MySQL clone plugin) and setting up the replication.

In this …

Few weeks ago with a big curiosity I was reading several articles published by Percona about TPCC Benchmark results and MySQL 8.0 "checkpointing" issues..

Unfortunately, in these articles there was no any explanation nor any tentative to understand what is going on, an probably at least try and validate some "first coming in mind" tuning / troubleshooting options.. (And even no any try to show in action so often advertised PMM, and see on what it'll point ?)..

All in all, in the following article I'll try to feel up the "white holes" left in this TPCC testing..

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

When you create a general tablespace in MySQL 8.0, you can choose the directory where the associated datafile is created.

CREATE TABLESPACE tablespace_name ADD DATAFILE ‘/my/table/space/dir’;

However, that directory must be known to InnoDB. Known directories are defined by the following settings: datadir,  innodb_data_home_dir,  innodb_undo_directory  &  innodb_directories.…

Facebook Twitter LinkedIn