You’ve been using Amazon Aurora to manage your data but recently found out that you could manage the same data on Oracle cloud for a ⅓ of the cost on Amazon. You also learned that you could run your MySQL queries on Oracle Cloud 1100x faster than on Amazon Aurora, including Analytics queries. Now di...

Disasters are waiting to happen. Like OVH data center fire on March 9, 2021. Do you have a solid MySQL Disaster Recovery (DR) plan in place to activate when the time inevitably comes?

If not, Continuent is happy to review your current MySQL HA/DR deployment and to recommend possible changes that might be recommended. Naturally, free of charge.

Tags: data center fireovh firemysql disaster recovery (dr)multi-site mysql

There is a common pattern in life, you often discover or understand things by accident. Many scientific discoveries fit such a description. In our database world, I was looking to see how BLOB/TEXT columns are allocated using overlay pages and I stumbled upon something interesting and unexpected. Let me present to you my findings, along with my attempt at explaining what is happening.

InnoDB Tablespaces

The first oddity I found is a bunch of free pages in each tablespace it is skipping. Here’s an example from a simple table with only an integer primary key and a char(32) column:

root@LabPS8_1:~/1btr# innodb_space -f /var/lib/mysql/test/t1.ibd space-extents
start_page  page_used_bitmap
0               #####################################........................... <--- free pages
64              ################################################################
128 …

While I was working on my grFailOver POC, I have also done some additional parallel testing. One of them was to see how online DDL are executed inside a Group Replication cluster.

The online DDL feature provides support for instant and in-place table alterations and concurrent DML. Checking the Group Replication (GR) official documentation I was trying to identify if any limitation exists, but the only thing I have found was:

"Concurrent DDL versus DML Operations.  Concurrent data definition statements and data manipulation statements executing against the same object but on different servers is not supported when using multi-primary mode. During execution of Data Definition Language (DDL) statements on an object, executing concurrent Data Manipulation Language (DML) on the same object but on a different server instance has the risk of conflicting DDL executing on different instances not being detected."

This …

AKA Cluster Error Voting

What is Cluster Error Voting (CEV)?

“Cluster Error Voting is a new feature implemented by Alexey Yurchenko, and it is a protocol for nodes to decide how the cluster will react to problems in replication. When one or several nodes have an issue to apply an incoming transaction(s) (e.g. suspected inconsistency), this new feature helps. In a 5-node cluster, if 2-nodes fail to apply the transaction, they get removed and a DBA can go in to fix what went wrong so that the nodes can rejoin the cluster. (Seppo Jaakola)”

This feature was ported to Percona PXC in version 8.0.21, and as indicated above, it is about increasing the resilience of the cluster especially when TWO nodes fail to operate and may drop from the cluster abruptly. The protocol is activated in a cluster with any number of nodes.  

Before CEV if a node has a problem/error during a transaction, …

In the late 1990s I learned MySQL and Java at approximately the same time. How did I teach myself? For MySQL I read the online MySQL manual cover to cover. For Java it was the Java Language Specification or Java Programming Language book, again cover to cover. Then for record I read Effective Java, and I was totally lost. I read it three or four years later and then it made sense.

At that time, with MySQL 3.22/3.23 the scope of the product was smaller, so was Java 1.2. I am confident I have forgotten as much as I retain, however it does marvel me when sometimes the most simplest of functionality I do not recall, or perhaps never learned. Today’s What Did I Learn This Week.

In MySQL, there is a TRIM() function, as the name suggests it trims whitespace, or so I thought. It actually does a lot more as the current MySQL 8.0 manual …

From MySQL Shell 8.0.24, it’s possible to log all the SQL statements issued in MySQL Shell.

Everything is well documented in the dedicated manual section: System Logging for SQL Statements.

Let’s see it in action. We will first start MySQL Shell with --syslog option:

$ mysqlsh --syslog --sql root@localhost

We enter a statement:

MySQL  localhost:33060+   2021-04-30 09:18:39 
SQL  show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| bookstore                     |
| clusterdemo                   |
...

And we can verify in syslog if something has been logged. I use Systemd and Journald therefor I will …

While testing a XA transactions based workload, I noticed in the output of SHOW ENGINE INNODB STATUS;

---TRANSACTION 108674, ACTIVE (PREPARED) 35 sec
4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2

So far, so good. A XA transaction has been PREPARED, and reported as such. But after a while (and I was not really looking at the session), I repeated the command and the output turned to:

---TRANSACTION 108674, ACTIVE (PREPARED) 91 sec recovered trx
4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2

I went to check logs, because XA transactions are recovered after MySQL Server restart, and I made sure …

Today a customer gave me the idea of using a separate file as a volume for limiting the MySQL tmpdir size. On some not so well set-up Linux systems the MySQL tmpdir is located under /tmp which is the same mount point as / (root). This can lead to troubles in case the tmpdir is filled up with implicit temporary MyISAM tables which fills up the / (root) directory of the O/S as well.

MySQL itself has no possibility to limit explicitly the total size nor the number of implicit temporary tables. So this can happen easily if your application runs amok or you do not have your application under control.

An sometimes there is no possibility to have an extra mount point for tmpdir because the disk is completely used by volumes etc. But you have still some space in the file system.

In this case you can, similar to a swap file, use a file in the …

Release of RonDB 21.04.0

RonDB is based on MySQL NDB Cluster optimised for use in modern cloud settings. Today we launch RonDB 21.04.0. In RonDB 21.04.0 we have integrated benchmark scripts to execute various benchmarks towards RonDB.

There are three ways of using RonDB. The first is using the managed version provided by Logical Clocks. This is currently available in AWS and is currently being developed to also support Azure. This is still in limited access mode. To access it contact Logical Clocks at the rondb.com website.

The second way is to use a script provided by Logical Clocks that automates the creation of VMs and the installation of the software components required by RonDB. These scripts are available to create RonDB clusters on Azure and GCP (Google Cloud). This script can be downloaded from nexus.hops.works/rondb-cloud-installer.sh.