The latest from the MySQL community

    MySQL has had replication for decades but the various options can be confusing.  One social media post I read recently could be summarized with the statement 'there are too many good options but how do I know which is right for me?'

Cluster

    Of all the words used in the computer related world, 'cluster' is the one that I wish we could have found a replacement for as the confusion over the word has created it's own cluster. Since MySQL has InnoDB Cluster and NDB cluster with other vendors using the word we end up with a lot of confusion. Add in clustered indexes and other non replication uses of the word to add more confusion. So which is what and where do we apply then for best performance.

MySQL Replication 101

    The follow is an overview of MySQL replication offerings and the details are in the manual.  And of course there are third party software that …

Rewriting a MySQL query for performance is an important process that every DBA should be aware of so they can fix the wrong queries on runtime without code changes on the application end. ProxySQL has great support for rewriting the queries, which Alkin Tezuysal already explored in his excellent blog ProxySQL Query Rewrite Use Case.

So far, MySQL community provides two built-in query rewrite plugins to perform this task. Recently they introduced the plugin “ddl_rewriter”.

• query rewriter plugin : It has support for INSERT / UPDATE / DELETE / REPLACE statements from MySQL 8.0.12. 
• ddl_rewritter plugin: It supports the CREATE TABLE statement. Introduced at MySQL 8.0.16.

In this blog, I am going to explain the complete process of the implementation and the …

Recently I had the opportunity to test a storage device from ScaleFlux called CSD 2000. In this blog post, I will share the results of using it to run MySQL in comparison with an Intel device that had a similar capacity.

First of all, why do we need another storage device? Why is ScaleFlux any different?

The answer is simple; it gives us built-in compression and atomic writes. For many workloads, but especially for database-type workloads, these are very important features.

Because of built-in compression, we can store more data on the ScaleFlux device than on a similar device with the same capacity.

Because of atomic writes, we can disable InnoDB Double Write buffer which means less writes/fsync on the disk layer. This should give us a performance advantage against non-atomic drives.

I ran many different tests on different data sizes, with different …

Since we now know how to look at the state of locking in a live database, let’s look at what happens when we run a normal insert or update and an insert or update with foreign key relationships defined, and compare.

We will be using the tables and structures from our previous examples, a simple 1:n relationship between a and b:

CREATE TABLE a (
  a_id int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (a_id)
);

INSERT INTO a VALUES (10), (20), (30), (40);

CREATE TABLE b (
  b_id int NOT NULL AUTO_INCREMENT,
  a_id int NOT NULL,
  PRIMARY KEY (b_id),
  KEY `a_id` (a_id),
  CONSTRAINT a_id_exists FOREIGN KEY (a_id) REFERENCES a (a_id) ON DELETE RESTRICT ON UPDATE RESTRICT
);

INSERT INTO b VALUES (10,10), (40,40);

or the same definition for b without the constraint.

A normal INSERT and UPDATE

First, let’s look at an insert and update into b without any …

ARM introduced LSE (Large System Extensions) as part of its ARMv8.1 specs. This means if your processor is ARMv8.1 compatible it would support LSE. LSE are meant to optimize atomic instructions by replacing the old styled exclusive load-store using a single CAS (compare-and-swap) or SWP (for exchange), etc…. Said extensions are known to inherently increase performance of applications using atomics.

Understanding LSE

To better understand LSE let’s take a working example to see how the code is generated and possible optimization.

LSE turned off

As you can see there is a loop for doing CAS. Load the value, check with expected value and if different then store the value. Main loop is a 5 step process with 2 exclusive instructions with respective memory ordering. SWAP too has a loop for checking if the store is successful.

ARM has multiple variant of load/store instructions so before we …

A small thing that provides a huge help.

The other day I was writing some code to process a very large amount of items coming from a social media API. My items were ending in a queue in MySQL and then needed to be processed and eventually moved.

The task was not so strange,  but what I have to do is to develop a queue processor.  Now when you need to process a queue you have two types of queue: static and dynamic.

The static comes in a batch of N number of items in a given time interval and is normally easier to process given you have a defined number of items that you can split in chunks and process in parallel.

The dynamic is… well… more challenging. One option is to wait to have a predefined number of items, and then process them as if they were a static queue.

But this approach is not very good, given it is possible that it will delay a lot …

A small thing that brings huge help.

The other day I was writing some code to process a very large amount of items coming from a social media API. My items were ending in a queue in MySQL and then needed to be processed and eventually moved.

The task was not so strange,  but what I have to do is to develop a queue processor.  Now when you need to process a queue you have two types of queue: static and dynamic.

The static comes in a batch of N number of items in a given time interval and is normally easier to process given you have a defined number of items that you can split in chunks and process in parallel.

The dynamic is… well... more challenging. One option is to wait to have a predefined number of items, and then process them as if they were a static queue.

But this approach is not very good, given it is possible that it will delay a lot the …

A small thing that brings huge help.

The other day I was writing some code to process a very large amount of items coming from a social media API. My items were ending in a queue in MySQL and then needed to be processed and eventually moved.

The task was not so strange,  but what I have to do is to develop a queue processor.  Now when you need to process a queue you have two types of queue: static and dynamic.

The static comes in a batch of N number of items in a given time interval and is normally easier to process given you have a defined number of items that you can split in chunks and process in parallel.

The dynamic is… well... more challenging. One option is to wait to have a predefined number of items, and then process them as if they were a static queue.

But this approach is not very good, given it is possible that it will delay a lot the …

Foreign Keys are what links tables together and turns a set of tables into a model. Foreign Key Constraints are conditions that must be true for the content of the tables to be an internally consistent model. Foreign Key Constraints can be defined and enforced in InnoDB, but this comes at a considerable price, and for some it may hurt more than it is worth.

A very simple shop as a ER-model. order_id is the primary key of the Orders table. customer_id in the Orders table is a foreign key: The primary key of another (“foreign”) table in this table. Foreign keys can model relationships between tables (“entities”) in an entity-relationship diagram.

Entities and their relationships

In Entity-Relationship modelling we are using tables in a database to model entities in the real world. Each entry in such a table is meant to represent a “thing” in the real world. For example, in our …

As a data architect I always ensure that for any database schema change there a fully recoverable execution path.
I have generally advised to create a patch/revert process for every change.  For example, if a change adds a new column or index to a table, a revert script would remove the respective column or index.
The goal is to always have a defensive position for any changes. The concept is that simple, it is not complex.

In its simplest form I use the following directory and file structure.

/schema
    schema.sql
    /patch
        YYYYMMDDXX.sql     where XX,ZZ are sequential 2 digit numbers, e.g. 01,02
        YYYYMMDDZZ.sql
   /revert
       YYYYMMDDXX.sql   This is the same file name in the revert sub-directory.
       YYYYMMDDZZ.sql

At any commit or tag in configuration management it is possible to create a current copy of the schema, i.e. use schema.sql.
It is also possible to take the first …