Undo Tablespaces can be truncated either implicitly or explicitly in MySQL 8.0. Both methods use the same mechanism. This mechanism could cause periodic stalls on very busy systems while an undo tablespace truncate completes. This problem has been fixed in MySQL 8.0.21.…

Facebook Twitter LinkedIn

So you have waited two weeks (cause the crash isn’t easily repeatable) and finally you get the crash again. You check your non-datadir core file directory with loads of free space and discover nothing was written. When MySQL crashes, you … Continue reading →

We all know as SQL professionals that a common use of the ALTER TABLE command is that we can change a tables’ structure in a myriad number of ways. And, that’s a good thing too because chances are, you won’t always nail down the initial structure. Due to changing business or application requirements, you may even have to add additional columns that were not considered during the schema design phase. Suppose you have many tables that are structured similarly and they all need a specific column added to their already-existing design. Under certain circumstances, using the MySQL Shell in Python mode (\py), can reduce the number of manual ALTER TABLE statements you have to type. Continue reading to see examples in the MySQL Shell…

Photo by elCarito …

As with any new releases, MySQL 8.0.21 introduces many new improvements and updates, many of which deserve their own blog post for a deep dive into the new features.  Among the notable changes are: Account Management Notes, JSON Notes, Authentication Notes and changes related to InnoDB, Optimizer, Group Replication, and more.

Here are the MySQL 8.0.21 top blog posts:

Server:
- The MySQL 8.0.21 Maintenance Release is Generally Available
- MySQL 8.0 – Who stopped mysqld and how long did it take?
- MySQL: who’s filling my error log?

Shell:
- …

As with any new releases, MySQL 8.0.21 introduces many new improvements and updates, many of which deserve their own blog post for a deep dive into the new features. Among the notable changes are: Account Management Notes, JSON Notes, Authentication Notes and changes related to InnoDB, Optimizer, Gr...

Introduced in MySQL Shell 8.0.21, the new MySQL Shell Dump and Load utilities has as its main goal to minimize the time needed to create and restore logical dumps of large data sets.

Through heavy parallelization and other techniques, we were able to reduce the time needed for these tasks by an order of magnitude compared to previous dump utilities.…

Facebook Twitter LinkedIn

So this has turned into a small series, explaining how to work with MYSQL from a developers perspective. This post is intended as a directory for the individual articles. It will be amended and re-dated as necessary.

The code for the series is also available in isotopp/mysql-dev-examples on GitHub.

The Tag #mysqldev will reference all articles from this series.

• MySQL Transactions - the physical side. Looking at how MySQL InnoDB handles transactions on the physical media, enabling rollback and commit. Introduces a number of important concepts: The Undo Log, the Redo Log, the Doublewrite Buffer, and the corrosponding in memory …

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 …

This second part about the new MySQL Shell Dump & Load utilities aims to demonstrate the performance while also comparing it with various other logical dump and load tools available: mysqldump, mysqlpump & mydumper.

To make the numbers more meaningful, I used several real world production datasets that are available online: stackoverflow.com…

Facebook Twitter LinkedIn

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 …