The latest from the MySQL community

More and more people are using UUID’s to identify records in their database.

As you already know, for MySQL’s storage engine (InnoDB) the primary key is very important ! (for performance, memory and disk space).

See the following links:

• https://lefred.be/content/mysql-invisible-column-part-ii/
• https://dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html (GIPK mode in MySQL 8.0.30 !)

Problems

There are 2 major problems having a UUID as Primary Key in InnoDB:

1. generally they are random and cause clustered index to be rebalanced
2. they are included in each secondary indexes (consuming disk and memory)

Let’s have a look at …

Recently, somebody asked me how he can find the long running transactions in MySQL.

I already have one MySQL Shell plugin that allows you to find the current transactions sorted by time. The plugin allows you to also get the details about the desired transaction. See check.getRunningStatements().

Let’s see how we can easily find those long transaction that can be a nightmare for the DBAs (see MySQL History List Length post).

SELECT thr.processlist_id AS mysql_thread_id,
       concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User,
       Command,
       FORMAT_PICO_TIME(trx.timer_wait) AS trx_duration,
       current_statement as `latest_statement`
  FROM …

Since 8.0.30, you have the possibility to modify the InnoDB Redo Log Capacity online. An undersized Redo Log Capacity is problematic and lead to performance issues.

However, it’s not recommended to oversize the Redo Log either. Redo Log files consume disk space and increases the recovery time in case of a restart (innodb_fast_shutdown=1) or a sudden crash. And it also slows down shutdown when innodb_fast_shutdown=0.

This means that now, you don’t need to restart MySQL if you want to increase or decrease the size of the InnoDB Redo Logs files. In fact, we don’t talk anymore about file size but about capacity ! The DBA doesn’t need to specify any file size and/or amount of files for Redo Logs anymore …

During my summer holidays, we released MySQL 8.0.30.

MySQL 8.0.30 was released on July 26th 2022. If you are using 8.0.29, it’s highly recommended to upgrade as there was a bug corrupting InnoDB files in a rare and specific case.

This new release contains several contributions from our awesome Community and on behalf of the entire MySQL Team, as usual, I would like to thanks all our contributors !

This new release contains patches from Facebook/Meta, Marcelo Altmann (Percona), Mengchu Shi, Zhou Xinjing (Tencent), Yuxiang Jiang (Tencent), Namrata Bhave, Hongyuan Li, Alexey Kopytov, Wei Zhao, Piotr Jurkiewicz, Dennis Gao, Zheng Lai, Bin Wang, Hao Wu (Tencent), Weijie Wu and Rahul Malik (Percona).

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

…

Usually, I receive a lot of emails, and sometimes I read them on my phone and then… I forgot about them.. (shame on me).

On my Linux desktop, I used to use Get Things Gnome for a long time, due to the declining appeal of the project and the end of the extension for Thunderbird, I found it less and less useful.

I was then looking for a solution to have my todolist accessible from everywhere and that I could manage it myself, not hosted somewhere.

I found a very nice, fast and practical project that was easy to deploy and was using MySQL as backend: myTinyTodo.

However, I was missing the possibility to easily create a new task from an email (and especially on my phone).

This is why I decided to write a script that would perform exactly what I was looking for and integrated …

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)
…