We saw in the previous post that MySQL likes memory. We also saw how to perform operating system checks and some configuration changes for Swap and NUMA.

Today, we will check what MySQL server can tell us about its memory usage.

Introduced in MySQL 5.7 and enabled by default in MySQL 8.0, the Performance_Schema‘s Memory instrumentation allows us to have a better overview of what MySQL is allocating and why.

Let’s check on our MySQL server using SYS:

Pay attention that there is a bug related to how InnoDB Buffer Pool statistics are accounted in Performance_Schema. This is fixed in 8.0.13. …

As you may know, sometimes MySQL can be memory-hungry. Of course having data in memory is always better than disk… RAM is still much faster than any SSD disk.

This is the reason why we recommended to have the working set as much as possible in memory (I assume you are using InnoDB of course).

Also this why you don’t want to use Swap for MySQL, but don’t forget that a slow MySQL is always better than no MySQL at all, so don’t forget to setup a Swap partition but try to avoid using it. In fact, I saw many people just removing the Swap partition… and then OOM Killer did its job… and mysqld is often its first victim.

MySQL allocates buffers and caches to improve performance of database operations. That process is explained in details in the manual.

In this article series, I will provide you some …

Alexey made this amazing tool that the majority of MySQL DBAs are using, but if you use sysbench provided with your GNU/Linux distribution or its repository on packagecloud.io you won’t be able to use it with the new default authentication plugin in MySQL 8.0 (caching_sha2_password).

This is because most of the sysbench binaries are compiled with the MySQL 5.7 client library or MariaDB ones. There is an issue on github where Alexey explains this.

So if you want to use sysbench with MySQL 8.0 …

Many of you already know how to retrieve the size of your dataset, schemas and tables in MySQL.

To summarize, below are the different queries you can run:

Dataset Size

I the past I was using something like this :

But now with sys schema being installed by default, I encourage you to use some of the formatting functions provided with it. The query to calculate the dataset is now:

SELECT sys.format_bytes(sum(data_length)) DATA,
       sys.format_bytes(sum(index_length)) INDEXES,
       sys.format_bytes(sum(data_length + index_length)) 'TOTAL SIZE'
FROM information_schema.TABLES ORDER  BY data_length + index_length;

Let’s see an example:

In April, I already posted an article on how to upgrade safely your MySQL InnoDB Cluster, let’s review this procedure now that MySQL 8.0.12 is out.

To upgrade all the members of a MySQL InnoDB Cluster (Group), you need to keep in mind the following points:

• upgrade all the nodes one by one
• always end by the Primary Master in case of Single Primary Mode
• after upgrading the binaries don’t forget to start MySQL without starting Group Replication (group_replication_start_on_boot=0)
• to run mysql_upgrade

Let’s see this in action on the video below:

As you could see, this is quick and easy.

…

We received feedback about how a member should act when leaving the group. And the majority of users wanted that when a node drops out of the group, it should kill all connections and shutdown. I totally agree with that behavior and it’s now the default in MySQL 8.0.12.

This new feature is explained in WL#11568.

Before this change, the server goes into super read only mode when dropping out of the group and allows users connected to this server or new connections (if you don’t use the router) to read old data.

Let’s check this out in the following video:

So now in MySQL 8.0.12, there is a mnew option called …

After having explained how to achieve HA for MySQL Router for people who doesn’t want to install the MySQL Router on the application servers and after having illustrated how to use Pacemaker, this article explains how to setup HA for MySQL Router using keepalived.

Keepalived is very popular, maybe because it’s also very easy to use. We can of course use 2 or more servers. The principle is the same as on the previous articles, if the router dies, the virtual IP used by the application server(s) to connect to MySQL is sent to another machine where mysqlrouter is still running.

Let’s have a look at the configuration, in this case we use 2 machines, mysql1 and …

This article will explain how to setup HA for MySQL Router as described in a previous article about where should the router stand.

For this setup, I will use Pacemaker (part of RedHat High Availability Add-on and available on RHEL, CentOS, Oracle Linux, …).

Of course we need a MySQL InnoDB Cluster but we won’t really use it for the HA setup of the MySQL Router.

Installing Pacemaker

The first step is to install pacemaker on all the machines we will use for our “MySQL Router Cluster”:

# yum install pacemaker pcs resource-agents

Now we need to start the pcsd service and enable it at boot (on all machines):

# systemctl start pcsd.service 
# systemctl enable pcsd.service

It’s time now to setup authentication, this operation is again …

As you know, MySQL InnoDB Cluster is composed of 3 elements:

• a group replication cluster of at least 3 servers
• the MySQL Shell used to manage the cluster
• the MySQL Router that send the traffic from the application server(s) to the cluster

When presenting the solution in conferences, one the main question is Where should I put the router ? and the answer is always the same: the best place to install the router is the application server !

The router is a very lightweight process that gets its configuration from the cluster’s metadata and doesn’t require a lot of resources or maintenance.

So the ideal setup is the following:

However for many (obscure?) reasons, sometimes people doesn’t want to have the MySQL …

Now that MySQL 8.0 GA is out for almost 3 months, let’s see the status of how it’s integrated with Drupal, a very popular CMS using MySQL.

For people having already a Drupal site and that wants to upgrade to MySQL 8.0, please check this post.

Now if you want to use MySQL 8.0 with a fresh new Drupal 8, let’s have a look how does that work.

Drupal 8.5

Drupal 8.5.5 is the latest available stable release from July 4th 2018.

There is no notes about supporting MySQL 8.0. So let’s try it.

…