As you may already know,  since MySQL 5.7.17, the generic partitioning handler in the MySQL server is deprecated, and is completely removed in MySQL 8.0.

So now, in MySQL 5.7, the storage engine used for a given table is expected to provide its own (“native”) partitioning handler. Currently, only the InnoDB and NDB storage engines do.

MySQL 5.7 supports generic partitions & native partitions

What does that imply for users using partitions in an earlier version MySQL  migrating to 5.7 ?

As the documentation describes it, the generic partitioning handler is still supported in all MySQL 5.7 releases, although it is deprecated in 5.7.17 and later. (Note that “deprecated” is not the same …

With MySQL 8.0, one key feature is the new Data Dictionary.

The system tables that were previously in MyISAM are now replaced by new protected ones in the DD.

My friend Giuseppe already explained how you could see those tables using sandbox  and he also warned you that you should not mess up with them in this post too.

I’ll explain you how you can see those tables and their actual content. But will also explain why we decided to protect them and why it should stay like that.

DD protected internal tables list

The easiest way …

Sometimes when you are using a MySQL InnoDB Cluster, you might encounter some performance issue because one node becomes dramatically slow.

Why ?

First of all, why ? A node can apply the transactions slower than the other nodes for many different reasons. The most frequents are for example, slower disks (remember, it’s advised to have nodes with the same specifications), but if you are using a RAID controller with a BBU, during the learning cycle, the write performance can decrease by 10 or even more. Another example could be an increase of IO operations that will flood the full IO capacity of the system. Making a local backup or sharing the server resources with some other components could lead in such behavior.

Flow Control

To avoid to have a node lagging to much behind and try to sustain the same throughput all over the cluster, Group Replication uses a flow control mechanism ( …

So, recently we had a runaway query eat up all sorts of temporary table space on our machines. Several machines had several terabytes in their ibtmp1 file after this happened. So I set out to find out more about why the InnoDB temporary tablespace is used, why it is better than using regular files, which was what was used prior to MySQL 5.7, and how to make sure that runaway queries do not end up filling up disk space.

Unfortunately, the manual does not go into why ibtmp1 is better than one file per temporary query, which disappears once the query ends. There are a few sections to look into:

Temporary Table Undo Logs – has one paragraph that states that these are the undo logs for temporary tablespaces. Given that these are undo logs, my guess is that this makes MySQL more crash-safe. But that is just a …

Now that MySQL is upgraded to 8.0 RC1, let’s have a look on how we could check the workload and see if we can optimize something by adding indexes for example. The same technique can be used to find inefficient queries requiring rewriting.

The first thing we will check is some information regarding the usage of the user used for lefred.be’s website:

mysql> select * from sys.user_summary where user= 'wp_lefred'\G
*************************** 1. row ***************************
user: wp_lefred
statements: 65060
statement_latency: 2.71 m
statement_avg_latency: 2.50 ms
table_scans: 234
file_ios: 17151
file_io_latency: 54.83 s
current_connections: 0
total_connections: 2541
unique_hosts: 1
current_memory: 52.27 MiB
total_memory_allocated: 12.50 GiB

This was after the upgrade, so not much info yet but if I compare with the other sites hosted on the …

It was expected for a long time…. here is the fix for bug #199 !!

The bug #199 submitted by PeterZ has been fixed in 8.0. Thank you to Zhang Simon for his contribution that inspired us to implement the fix.

Let’s have a look at the test case described in the bug report, I will reproduce it on MySQL 5.7.19 & MySQL 8.0.3.

The initial steps are exactly the same on both versions:

mysql> create table a(id int unsigned not null primary key auto_increment);
Query OK, 0 rows affected (0.17 sec)

mysql> show create table a\G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `id` int(10) unsigned …

From September 25th to 27th 2017, the MySQL engineers and community team will be speaking at Percona Live Europe in Dublin.

MySQL is also part of the contributing sponsors.

For the conference, we tried to bring new faces to the Community. Usually, it’s always the same famous team leaders speaking at the conferences, this time, it’s not. A lot of key developers will present their own work. We have so much talented people in MySQL we want to present to our community.

Of course, once again we will focus our talks on MySQL 8.0. This is the list of sessions the MySQL Team will deliver:

Monday, Sep 25th

On Monday, I will deliver a tutorial with my friend …

Today is my last day in Asia (China and Taiwan), and between the sessions I had the time to code a small proof-of-concept for people that are asking how they could read their writes on all nodes and ensure the read consistency with their last write ?

The usual (and easier) answer to this particular question is to read on the same host you just wrote. But currently MySQL provides you all the elements to force a consistent read across all the nodes of a group.

Since MySQL 5.7.5, we introduced session_track_gtidsand in 5.7.6 we also introduced  GTIDs context to the OK packet (session tracker) (WL#6128 and WL#6972). So we can use this implementation in …

Recently, my friend Marco Tusa(MySQL Daddy or the Grinch) wrote his first impression on MySQL Group Replication (part of InnoDB Cluster). And his conclusion was not that positive. But when I analyze his setup, I understand that his assumptions were not so right.

Let's try to explain what were the issues and why his test wasn't correct.

Before commenting Marco's tests, I would like to clarify the flow-control implementation in Group Replication:

We designed the flow-control feature in Group Replication as a safety measure for delaying writer nodes when they consistently exceed the write capacity of the Group, so that a large backlog would not make it hard to switch over from a member to another.

Flow-control is …

When using MySQL Group Replication, it’s possible that some members are lagging behind the group. Due to load, hardware limitation, etc… This lag can become problematic to keep good certification behavior regarding performance and keep the possible certification failure as low as possible. Bigger is the applying queue bigger is the risk to have conflicts with those not yet applied transactions (this is problematic on Multi-Primary Groups).

Galera users are already familiar with such concept. MySQL Group Replication’s implementation is different 2 main aspects:

• the Group is never totally stalled
• the node having issues doesn’t send flow control messages to the rest of the group asking for slowing down

In fact, every member of the Group send some statistics about its queues (applier queue and certification queue) to the other members. Then every node decide to slow down or not if they …