The latest from the MySQL community

We are glad to inform our MySQL users, that a new version of our Visual Studio plugin for MySQL database development, dbForge Fusion for MySQL, v.6.6, has been just rolled out. Connectivity The new version features a massive update of connectivity options implemented to ensure that you can easily connect and work with the latest […]

The post New Connectivity Options in dbForge Fusion for MySQL, v6.6 appeared first on Devart Blog.

When you insert data into a database and run COMMIT you expect things to be there: Atomically, Consistent, Isolated and Durable

, like Codd commanded us 40 years ago, but also quickly. There is a surprising amount of sophistication being poured into this, but since I do not want to shame MongoDB and Redis developers in this post, I am not going to talk about that much in this place.

We are instead trying to understand what our databases are doing all day, from the point of view of the storage stack.

That Brendan Gregg Graphics

Here is your performance tooling (sans EBPF) as shown by Brendan Gregg in various iterations. We are interested into the various blues in this graphic, VFS and downwards. The most detailed information of what goes on in the blue stack is given by blktrace. It’s an I/O request recorder.

Unfortunately, blktrace …

MySQL Shell is the advanced MySQL client, which has many excellent features. In this blog, I am going to explain the MySQL shell commands “\show” and “\watch”. Both commands are very useful to monitor the MySQL process. It provides more insights into the foreground and background threads as well. 

Overview

“\show” and “\watch” are the MySQL shell commands, which can be executed using the Javascript (JS), Python (Py), and SQL interfaces. Both commands are providing the same information, but the difference is you can refresh the results when using the command “\watch”. The refresh interval is two seconds. 

• \show: Run the specified report using the provided options and arguments.
• \watch: Run the specified report using the provided options and arguments, and refresh the results at regular intervals.

Below are the available options you can use with the “\show” …

I’ve already provided some solutions to connect to your MDS instance, using MySQL Router, SSH tunnel, … but one of the best way if you have multiple instance to manage, is to use a VPN.

This post summarize the steps on how to deploy Open VPN and configure your VCN to use it.

So, in OCI, we have already some MDS & Compute instances deployed, this is how the dashboard looks like:

OpenVPN Deployment

We can start by deploying our OpenVPN instance using OCI’s Marketplace:

And you follow the wizard by adding your administrator username and password:

An important step is to use the existing VCN and place the OpenVPN in the public subnet:

And you create the instance:

…

From MySQL 5.7, we had a Multi-threaded Slave (MTS) Applier mechanism called LOGICAL_CLOCK to overcome the problems of parallel replication within a database.

To further improve the parallelisation mechanism, from MySQL 8 (5.7.22) we have write-set replication, so before going further , lets look at the difference between Logical clock (LC) and Writeset.

LOGICAL_CLOCK

Transactions that are part of the same binary log group commit on a master are applied in parallel on a slave. The dependencies between transactions are tracked based on their timestamps to provide additional parallelisation where possible.

WRITESET

Write-set is a mechanism to track independent transactions that can be executed in parallel in the slave. Parallelising on write sets has potentially much more parallelism than logical_clock ,since it does not depend …

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 …

Hi and thanks to all who attended my webinar on Tuesday, January 26th titled Using PMM to Identify & Troubleshoot Problematic MySQL Queries!

Like we do after all our webinars, we compile the list of questions that were answered verbally and also those that were posed yet remained unanswered since we ran out of time during the broadcast.  Before we get to the questions, I wanted to make sure to include a link to the RED Method for MySQL Queries by Peter Zaitsev, Percona’s CEO:

https://grafana.com/grafana/dashboards/12470

Hi Michael, you suggested that table create and update times should be ignored. Surely these values come from information_schema.tables? Does that not reflect what I would see if I …

Query attributes have in a long time been used to add metadata to queries to provide additional context such as where in the application the query was generated. The traditional way to do this is to add a comment at the start of the query with the attributes. Since the MySQL parser ignores the comment, it does not change the semantics of the query, but various tools can extract the comments.

This post looks into the reasons for using query attributes, how it is done using a comment, and the new component for MySQL 8.0.23 that provides native support for query attributes.

Advice

Query attributes are also known as query tags.

Why Query Attributes?

At the surface, it may seem that there is no major benefit from having metadata attached to a query – after all it is ignored by MySQL. However, there are good reasons. The …

In March 2020, my book MySQL 8 Query Performance Tuning (Apress) was released. That book with its more than 900 pages in 27 chapters covered a wide range of topics from executing benchmarks with sysbench over indexes and the optimizer to query rewriting, replication, and caching. Some time later my editor Jonathan Gennick suggested to take a limited set of the topics and go into more detail. We settled on locks and transactions which together have become MySQL Concurrency.

If you have already read MySQL 8 Query Performance Tuning, you may wonder what the new book offers compared to the previous one. In some way, you can think of MySQL Concurrency to be a second and expanded edition for the content on locks and transactions. Of course, given MySQL Concurrency is limited to those two topics, it has also been …

There was an excellent question from Francis on my MySQL X DevAPI tutorial on how to work with NULL values:

Is it possible to store an attribute with the null value, and how to find it (xxx is null ?

It may sound like a trivial question, but the answer is not all that simple, so instead of just replying directly to the comment, I think it is worth covering the subject of NULL values in the X DevAPI in a separate post.

The Short Story

In short there are two ways to work with “not a value” in the X DevAPI. One way is to not set a value for those fields in which case you can compare the retrieved values with IS NULL as you would for a relational table, for example in MySQL Connector/Python:

# Find documents where "Birthday IS NULL"
stmt_find = …