Today’s article is again related to WordPress and MySQL 8.0. We will see how we can setup MySQL InnoDB ReplicaSet and configure WordPress to split the load using both MySQL Instances: we will split reads and writes between the Primary and the Secondary member of our ReplicaSet.

This will be the first part of our journey to achieve HA for our WordPress site on OCI and using all MySQL Servers we have deployed. We don’t want to have a server idle just waiting to take over in case of an incident.

MySQL InnoDB ReplicaSet

First some words about MySQL InnoDB ReplicaSet.

The ease of use of …

Tweet

On a busy server, the binary logs can end up being one of the largest contributors to amount of disk space used. That means higher I/O, larger backups (you are backing up your binary logs, right?), potentially more network traffic when replicas fetch the logs, and so on. In general, binary logs compress well, so it has been a long time wish for a feature that allowed you to compress the logs while MySQL are still using them. Starting from MySQL 8.0.20 that is now possible. I will take a look at the new feature in this post.

Configuration

The binary log compression feature is controlled by two variables, one for enabling the feature and …

On Monday April 27th, MySQL released a much-anticipated patch release 8.0.20. Along with many bug fixes and improvements, a new property was introduced – binlog-transaction-compression. During our own internal testing we have discovered an incompatibility with our Continuent Tungsten products when this property is enabled.

The newly-released binlog-transaction-compression feature is really interesting because it compresses transaction payloads before being written into the binary logs, which in turn reduces the disk space overhead required for storage. I’m sure many users will be keen to implement this, however at this time the use of binlog-transaction-compression=ON will prevent Replication from functioning correctly.

This …

We have just released MySQL 8.0.20. And it has some interesting replication enhancements. In particular one big and exciting feature: binary log compression. Here is the list of things in this release:

• Binary Log Compression (WL#3549). This work done by Luís Soares implements binary log compression, making use of the popular compression algorithm ZSTD.

… Tweet Share

In this post, I want to evaluate Group Replication Scaling capabilities in cases when we increase the number of nodes and increase user connections. While this setup is identical to that in my post “Evaluating Group Replication Scaling Capabilities in MySQL”,  in this case, I will use an I/O bound workload.

For this test, I will deploy multi-node bare metal servers, where each node and client are dedicated to an individual server and connected between themselves by a 10Gb network.

Also, I will use 3-nodes and 5-nodes Group Replication setup. In both cases, the load is directed only to ONE node, but I expect with five nodes there is some additional overhead from replication.

Hardware specifications:

System | Supermicro; SYS-F619P2-RTN; v0123456789 (Other)
Service Tag | …

In this blog, I want to evaluate Group Replication Scaling capabilities to handle several writers, that is, when the read-write connection is established to multiple nodes, and in this case, two nodes. This setup is identical to my previous post, Evaluating Group Replication Scaling Capabilities in MySQL.

For this test, I deploy multi-node bare metal servers, where each node and client are dedicated to an individual server and connected between themselves by a 10Gb network.

I use the 3-nodes Group Replication setup.

Hardware specifications:

System | Supermicro; SYS-F619P2-RTN; v0123456789 (Other)
Service Tag | S292592X0110239C
   Platform | Linux
    Release | Ubuntu 18.04.4 LTS (bionic)
     Kernel | 5.3.0-42-generic
Architecture | CPU = 64-bit, OS = 64-bit
  Threading | NPTL 2.27 …

In this blog, I want to evaluate Group Replication Scaling capabilities in cases when we increase the number of nodes and increase user connections.

For testing, I will deploy multi-node bare metal servers, where each node and client are dedicated to an individual server and connected between themselves by a 10Gb network.

Also, I will use 3-nodes and 5-nodes Group Replication setup.

Hardware specifications:

System | Supermicro; SYS-F619P2-RTN; v0123456789 (Other)
Service Tag | S292592X0110239C
   Platform | Linux
    Release | Ubuntu 18.04.4 LTS (bionic)
     Kernel | 5.3.0-42-generic
Architecture | CPU = 64-bit, OS = 64-bit
  Threading | NPTL 2.27
    SELinux | No SELinux detected
Virtualized | No virtualization detected
# Processor ##################################################
 Processors | physical = 2, cores = 40, virtual = 80, hyperthreading = yes
     Models | 80xIntel(R) Xeon(R) Gold 6230 CPU @ …

This blog post illustrates ” How to setup SSL enabled replication”

By default, mysql package installation creates SSL file in the data directory at the time of installation. If you would like to use different self-signed certificates then create them as described here.

Add SSL setting to my.cnf on all servers.

ssl=on
ssl-ca=/etc/sslcerts/ca.pem
ssl-cert=/etc/sslcerts/server-cert.pem
ssl-key=/etc/sslcerts/server-key.pem

Restart mysql server and verify the settings.

Example: client connections using SSL

#mysql -urpluser -p -P22403 --host 127.0.0.1 --ssl-cert=/etc/sslcerts/client-cert.pem --ssl-key=/etc/sslcerts/client-key.pem -e '\s'
Enter password:
--------------

Connection id: 5
Current database:
Current user: rpluser@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current …

This blog is about one of the issues encountered by our Remote DBA Team in one of the production servers. We have a setup of MySQL 5.7 Single Primary (Writer) GR with cluster size of  3 . Due to OOM, the MySQL process in the primary node got killed, this repeated over the course of the time.

We all know about the OOM (out of memory), theoretically, it is a process that the Linux kernel employs when the system is critically low on memory.

In a dedicated DB server, when the OOM triggers the direct impact will be on mysqld process since it will be the most memory consuming one.

Going forward will look into the detailed analysis made to tackle the issue of OOM.

DB Environment:-

• Service – Group Replication Cluster
• Cluster Nodes – 3
• GR mode – Single Primary …

My post-FOSDEM detox has started - despite preparing by reading some survival guides, I hadn't really fathomed the variety and quantity (and quality) of beer that would flow over four days.  On reflection however, the beer flow has been far exceeded by the flow of tech content and conversation.

On Thursday and Friday I attended the pre-FOSDEM MySQL Days fringe event, where there were two tracks of talks and tutorials on MySQL including sessions on :
 - MySQL Server simplification
 - MySQL replication tooling improvements
 - Configuring group replication
 - Troubleshooting group replication
 - Using DNS for loadbalancing and failover
 - Upgrading to MySQL 8.0 …