Full table scans can be problematic for performance. Certainly if the scanned tables are large. The worst case is when full table scans are involved in joins and particularly when the scanned table is not the first one (this was dramatic before MySQL 8.0 as Block Nested Loop was used) !

A full table scans means that MySQL was not able to use an index (no index or no filters using it).

Effects

When Full Table Scans happen (depending of the size of course), a lot of data gets pulled into the Buffer Pool and maybe other important data from the working set is pulled out. Most of the time that new data in the Buffer Pool might even not be required by the application, what a waste of resources !

You then understand that another side effect of Full Table Scans is the increase of I/O operations.

The most noticeable symptoms of Full Table Scans are:

• increase of CPU usage
• increase of …

Today, the book I would like to recommend is Efficient MySQL Performance – Best Practices and Techniques, Daniel Nichter, O’Reilly, 2021.

I participated (just a bit) in the writing of this book as technical reviewer with Vadim and Fipar. I really enjoyed that role of carefully reading the early drafts of the chapters Daniel was writing.

Although Daniel says the book is not for the experts, I think even experts will enjoy it because several key InnoDB concepts are also covered. You can see that I refer to the book often in my A graph a day, keeps the doctor away ! series on monitoring and trending.

If you’re looking for information on transaction isolation and undo logs, fuzzy checkpointing, etc… you’ll find …

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 …

This is the second article of the series dedicated to MySQL trending.

As I wrote before, understanding your workload and seeing the evolution of it over time can help anticipating problems and work on solutions before the breakdown.

This article covers MySQL History List Length also known as HLL.

MySQL History List is related to InnoDB Undo Logs. InnoDB is a multi-version storage engine (MVCC). It keeps information about old versions of changed rows to support transactional features such as concurrency and rollback. This information is stored in undo tablespaces in a data structure called a rollback segment.

This means that you can start a transaction and continue to see a consistent snapshot even if the data changed by other transaction. This behavior is related to the …

Aurora Serverless V2 is generally available around the corner recently 21-04-22 for MySQL 8 and PostgreSQL, with promising features that overcome the V1 disadvantages. Below are those major features

Features

• Online auto instance upsize (vertical scaling)
• Read scaling (Supports up to 15 Read-replica)
• Supports mixed-configuration cluster ie, the master can be normal Aurora(provisioned) and readers can be in serverlessv2 and vice versa
• MultiAZ capability (HA)
• Aurora global databases (DR)
• Scaling based on memory pressure
• Vertically Scales while SQL is running
• Public IP allowed
• Works with custom port
• Compatible with Aurora version 3.02.0 ie., >= MySQL 8.0.23 (only supported)
…

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 …

When it’s time for a new MySQL release, it’s also time to thank our contributors !

Released on April 26th, MySQL 8.0.29 contains several contributions from our awesome Community and on behalf of the entire MySQL Team, I would like to thank you all !

This new releases contains patches from Vilnis Termanis, Luke Weber, Meik Milevczik, Song Zhibai, Zheng Lai, Øystein Grøvlen, Facebook, Biran Yue, Hope Lee, Rahul Malik, Christopher Chavez, Chen Yi, Ning PeiPei, Jianjian Song, Jack Wotherspon and Bin Wang.

Once again, thank you all for your great contributions and to the company you are working for.

Here is the list of the above contributions and related bugs:

Clients / Connectors

• #81519 Connector/Python: Guarantee file closing of input files in optionfiles module – Vilnis Termanis
…

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 …

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

• Poorman’s MySQL table audit information
• Poorman’s MySQL table audit information – part 2

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 …

I really enjoyed to dig into the solution I described yesterday in this post, to generate table audit information using invisible columns and triggers.

In this post, I will focus only on the solution using a JSON column to store the audit information.

Yesterday, I wrote that it’s also possible to track all changes an not only the last one but also keep information about what changed.

I wanted to illustrate that with an example, let’s start with the output:

This is exactly what I was looking for !

And of course we can search in that audit information. For example let’s search for all records that have been modified and where the old or new name was/is frederic:

We can verify that indeed that record had frederic as initial value.

Triggers

Compare …