The latest from the MySQL community

This post was originally published in July 2018 and was updated in July 2023.

Now that Database-as-a-service (DBaaS) is in high demand, there are multiple questions regarding AWS services that cannot always be answered easily: When should I use Aurora and when should I use RDS MySQL?  What are the differences between Aurora and RDS? How do I choose which one to use?

In this blog, we will answer all of these important questions and provide a general overview comparing the two database services, Aurora vs RDS.

Understanding DBaaS

DBaaS cloud services allow users to use databases without configuring physical hardware and infrastructure or installing software. But …

Often while managing and creating new users, we use all the default options and tend not to use extra features provided by MySQL. These extra options could prevent a user from using all the resources and degrading the performance of MySQL. In this blog, we will discuss a few such features that will put resource restrictions on users.

max_user_connections

Sometimes, due to unprecedented growth or huge transactions, a single user makes too many connections, and the MySQL server gets starved of free connections. This blocks the DBA from logging into MySQL to fix it. To fix it in MySQL 5.7 and below, we have to do a restart. For MySQL 8, it can be done without a restart, but we have to configure the admin interface beforehand. You can read more about it here in Dealing With “Too Many Connections” Error in MySQL 8.

Codership is pleased to announce a new Generally Available (GA) release of the multi-master Galera Cluster for MySQL 5.7, consisting of MySQL-wsrep 5.7.42 (release notes, download) and MySQL-wsrep 8.0.33 (release notes, download), with Galera replication library 4.15 (release notes, download) implementing wsrep API version …

Learn how Multi-Valued Indexing in MySQL 8.0 can enhance query performance by efficiently indexing and querying JSON arrays. Discover the benefits, implementation steps, and considerations for optimizing your MySQL database.

1. Multi-Valued Indexing in MySQL 8.0
2. Understanding Multi-Valued Indexes and their benefits
3. Creating Multi-Valued Indexes in MySQL …

When working with MySQL (or any database!), it's essential to understand how indexes work and how they can be used to improve the efficiency of queries. An index is a separate data structure that maintains a copy of part of your data, structured to allow quick data retrieval. Usually, this structure is a B+ Tree. We have an entire post on how indexes work if you want to go into greater detail.

Obfuscated indexes

Creating indexes is only part of the battle. You must also know how to write your queries so that you allow MySQL to use your indexes. One common mistake people make when writing queries is that they obfuscate their indexes. Obfuscating an index simply …

Historically MySQL is great in horizontal READ scale. The scaling, in that case, is offered by the different number of Replica nodes, no matter if using standard asynchronous replication or synchronous replication.

However, those solutions do not offer the same level of scaling for writes operation.

Why? Because the solutions still rely on writing in one single node that works as Primary. Also, in the case of multi-Primary, the writes will be distributed by transaction. In both cases, when using virtually-synchronous replication, the process will require certification from each node and local (by node) write; as such, the number of writes is NOT distributed across multiple nodes but duplicated.

The main reason behind this is that MySQL is a relational database system (RDBMS), and any data that is going to be written in it must respect the RDBMS …

Historically MySQL is great in horizontal READ scale. The scaling in that case is offered by the different number of Replica nodes, no matter if using standard asynchronous replication or synchronous replication. 

However those solutions do not offer the same level of scaling for writes operation. 

Why? Because the solutions still rely on writing in one single node that works as Primary. Also in case of multi-Primary the writes will be distributed by transaction. In both cases, when using virtually-synchronous replication, the process will require certification from each node and local (by node) write, as such the number of writes are NOT distributed across multiple nodes but duplicated. 

The main reason behind this is that MySQL is a relational database system (RDBMS), and any data that is going to be written in it, must respect the RDBMS rules ( …

Historically MySQL is great in horizontal READ scale. The scaling in that case is offered by the different number of Replica nodes, no matter if using standard asynchronous replication or synchronous replication. 

However those solutions do not offer the same level of scaling for writes operation. 

Why? Because the solutions still rely on writing in one single node that works as Primary. Also in case of multi-Primary the writes will be distributed by transaction. In both cases, when using virtually-synchronous replication, the process will require certification from each node and local (by node) write, as such the number of writes are NOT distributed across multiple nodes but duplicated. 

The main reason behind this is that MySQL is a relational database system (RDBMS), and any data that is going to be written in it, must respect the RDBMS rules ( …

Some times it’s important to know the size of a transaction, especially when you plan to migrate to a HA solution where by default transactions have a limited size to guarantee an optimal behavior of the cluster.

Today we will see the different possibilities to have an idea of the size of transactions.

First we need to split the transaction in two types:

• those generating data (writes, like insert, delete and update, DML)
• those only ready data (select, DQL)

To implement High Availability, only the first category is important.

Size of DML

To know the size of a DML transaction, the only possibility we have is to parse the binary log (or query the binlog event).

We need to check the binlog event from the binlog file and then calculate its size. To illustrate this, let’s try to find the transaction identified by a specific GTID: …

One of the most frustrating experiences when dealing with databases is when you've designed the perfect index, but MySQL still doesn't use it. There are several reasons why this could be the case, and in this article, we'll explore some of the most common ones.

Throughout this article, we'll be working with a very simple people table that looks like this:

CREATE TABLE `people` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `state` char(2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `first_name` (`first_name`),
  KEY `state` (`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

We'll be adding and dropping keys throughout to show different scenarios, but this is a good starting place.

…