The Aurora hash join feature for relational databases has been around for a while now. But unlike MySQL Block Nested Loop algorithm, an Aurora hash join only caters to a specific number of use cases. When implemented with the optimizer properly, they can provide great benefits with certain workloads. Below we’ll see a brief example of a quick win.

This new feature is available in Aurora lab mode version 1.16. Because this is a lab feature, it’s important to make sure to test your queries before upgrading, especially if you are looking to scale up to the new R4 instances before the Superbowl to avoid hitting the same problem I discuss below.

When lab mode is enabled and

hash_join

  is ON, you can verify the optimizer feature from the

optimizer_switch

 variable:

In this blog post, I’ll look at whether optimizing indexing is always the key to improving MySQL query performance (spoiler, it isn’t).

As we look at MySQL query performance, our first concern is often whether a query is using the right indexes to retrieve the data. This is based on the assumption that finding the data is the most expensive operation – and the one you should focus on for MySQL query optimization. However, this is not always the case.

Let’s look at this query for illustration:

mysql> show create table tbl G
*************************** 1. row ***************************
      Table: tbl
Create Table: CREATE TABLE `tbl` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `k` int(11) NOT NULL DEFAULT '0',
 `g` int(10) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `k_1` (`k`)
) ENGINE=InnoDB …

In this blog post, we’ll look at ways you can use MySQL 5.7 generated columns (or virtual columns) to improve query performance.

Introduction

About two years ago I published a blog post about Generated (Virtual) Columns in MySQL 5.7. Since then, it’s been one of my favorite features in the MySQL 5.7 release. The reason is simple: with the help of virtual columns, we can create fine-grained indexes that can significantly increase query performance. I’m going to show you some tricks that can potentially fix slow reporting queries with GROUP BY and ORDER BY.

The Problem

Recently I was working with a customer who was struggling with this query:

SELECT
CONCAT(verb, ' - …

In this blog post, we’ll provide an overview of the new MySQL 8.0 RESOURCE_GROUP feature.

One great new feature introduced in MySQL 8.0 that – from my point of view – requires attention is RESOURCE_GROUP.

Short disclaimer: I want to point out that MySQL 8.0 is not GA yet, so it is possible for the MySQL 8.0 RESOURCE_GROUP implementation to change in features and/or behavior.

I’ve used MySQL Community Server 8.0 RC, and everything mentioned below applies to this MySQL version.

In this post, I will quickly look at this feature and summarize what it’s for, how it makes the DBA’s life a little bit easier and highlight some known limitations.

The MySQL documentation describes it as follows:

“MySQL supports creation and …

The news that the latest MySQL 8.0.4 RC (release candidate) is available is indeed exciting. Unfortunately for users of the auth_socket plugin, dangers lie in wait!

Back in November 2015, I reported Failure of auth_socket authentication with sha256_password as default. This prevents users that identify with the

auth_socket

 plugin from logging in after SHA256 authentication has been made the default authentication method. With the …

Please join Percona’s Principal Support Engineer, Sveta Smirnova, as she presents Troubleshooting MySQL Crashes on January 25, 2018, at 10:00 am PST (UTC -8) / 1:00 pm EST (UTC -5).

Register Now

This webinar is for every MySQL user! In this talk, I won’t focus on how to analyze core files, read the source code or set breakpoints. Instead, I will focus on techniques that are available to anyone, even a novice.

Many tutorials, including my own, written based on Roel Van de Paar’s video, suggest how to create and analyze core files created at the time of a crash. While this …

In this blog post, we’ll look at the performance hit from the Spectre bug fix on Ubuntu.

Recently we measured the performance penalty from the Meltdown fix on Ubuntu servers. It turned out to be negligible.

Today, Ubuntu made a Spectre bug fix on Ubuntu available, shipped in kernel 4.4.0-112. As with the Meltdown fix, we measured the effect of this update. Unfortunately, we observed a major performance penalty on MySQL workloads with this new kernel.

Our benchmark used the following:

System:

• CPU:
  • 2 x Intel(R) Xeon(R) CPU E5-2680 v3 @ 2.50GHz (Codename Haswell)
  • /proc/cpuinfo has 48 …

Join Percona’s Chief Evangelist, Colin Charles as he presents Differences Between MariaDB and MySQL on Wednesday, January 24, 2018, at 7:00 am PST (UTC -8) / 10:00 am EST (UTC -5).

Register Now

Tags: MariaDB, MySQL, Percona Server for MySQL, DBA, SysAdmin, DevOps
Experience Level: Novice

MariaDB and MySQL. Are they syntactically similar? Where do these two query languages differ? Why would I use one over the other?

MariaDB is on the path of gradually diverging from MySQL. One obvious example is the internal data …

In this blog post, we’ll look at does the Meltdown fix affect performance for MySQL on bare metal servers.

Since the news about the Meltdown bug, there were a lot of reports on the performance hit from proposed fixes. We have looked at how the fix affects MySQL (Percona Server for MySQL) under a sysbench workload.

In this case, we used bare metal boxes with the following specifications:

• Two-socket Intel(R) Xeon(R) CPU E5-2683 v3 @ 2.00GHz (in total 56 entries in /proc/cpuinfo)
• Ubuntu 16.04
• Memory: 256GB
• Storage: Samsung SM863 1.9TB SATA SSD
• Percona Server for MySQL 5.7.20
• Kernel (vulnerable) 4.13.0-21
• Kernel (with Meltdown fix) 4.13.0-25

Please note, the current kernel for Ubuntu 16.04 contains only a Meltdown fix, …

In this post, we’ll look at updating and deleting rows with ClickHouse. It’s the second of two parts.

In the first part of this post, we described the high-level overview of implementing incremental refresh on a ClickHouse table as an alternative support for UPDATE/DELETE. In this part, we will show you the actual steps and sample code.

Prepare Changelog Table

First, we create the changelog table below. This can be stored on any other MySQL instance separate from the source of our analytics table. When we run the change capture script, it will record the data on this table that we can consume later with the incremental refresh script:

CREATE TABLE `clickhouse_changelog` (
  `db` varchar(255) NOT NULL …