Welcome to the 86th installment of the OpenLampTech developer newsletter. The authors whose content I share sure do make my job easy (and enjoyable). Thank you for reading.

Custom WooCommerce and Shopify Solutions

Discover useful WooCommerce and Shopify custom solutions for your online store today at affordable prices!

Learn More

The Newsletter for PHP and MySQL Developers

Receive a copy of my ebook, “10 MySQL Tips For Everyone”, absolutely free when you subscribe to the OpenLampTech newsletter.

In OpenLampTech issue #86, we have some fantastic articles on:

• A new …

As was announced on Jun 1, 2023, you can find MySQL team at following conference in July 2023: Come to meet us, we are looking forward to talking to you during the shows above!

JAPAC conferences with MySQL, July 2023

On the Internet, you can get a lot of advice from almost anywhere when you’re looking for information on how to do your DBA job.

My blog is one of these sources of advice, and depending on the source, we generally trust the advice more or less.

But sometimes advice doesn’t take the whole picture into account, and even if it comes from a recognized authority, it can lead to more or less serious problems.

Let’s consider the following situation:

We have an InnoDB ReplicaSet with 1 replication source (primary) and 2 replicas:

 JS > rs.status()
{
    "replicaSet": {
        "name": "myReplicaSet", 
        "primary": "127.0.0.1:3310", 
        "status": "AVAILABLE", 
        "statusText": "All instances available.", 
        "topology": {
            "127.0.0.1:3310": {
                "address": "127.0.0.1:3310", 
                "instanceRole": "PRIMARY", 
                "mode": "R/W", …

To continue our journey to Moodle on Oracle Cloud Infrastructure using Ampere compute instances and MySQL HeatWave Database Service [1] [2], in this article we will see how to scale our architecture using multiple Moodle instances, High Availability for the Database and Read Scale-Out.

This is the architecture we will deploy:

The same principles can be applied to other projects, not just Moodle.

Multiple Compute Instances & MySQL HeatWave High Availability

The first step is to use again the Stack to deploy the initial resources. We must insure that we use a MySQL Shape that has at least 4 OCPUs to …

To continue our journey to Moodle on Oracle Cloud Infrastructure using Ampere compute instances and MySQL HeatWave Database Service [1] [2], in this article we will see how to scale our architecture using multiple Moodle instances, High Availability for the Database and Read Scale-Out. This is the architecture we will deploy: The same principles can […]

In this final article on deploying Moodle to OCI with MySQL HeatWave, we are exploring a more complex architecture ensuring high availability and performance.

There is a really nice article by Pep Pla, over at the Percona blog

about fragmentation in MySQL InnoDB tablespaces, which you should read.

The article discusses “fragmentation” of data in tables, which happens in a way similar to how it happens in filesystems.

InnoDB stores data by default in tablespaces, which by default are a file per table. These files are subject to the fragmentation and growth rules of your filesystem, but if you are smart, you are running MySQL on Linux on the XFS. In that case, filesystem fragmentation (and unexplained commit latency variance) are not an issue, because XFS takes care of handling this properly, and only database-internal fragmentation remains.

…

Fragmentation is a common concern in some database systems. Highly fragmented tables can affect performance and resource allocation. But reducing fragmentation often involves rebuilding the table completely. This blog post will discuss fragmentation and its impact on InnoDB.

What is fragmentation?

We say that something is fragmented when it is formed by parts that are separate or placed in a different order than the natural one. In databases, we can experiment with different types of fragmentation:

• Segment Fragmentation: segments are fragmented; they are stored not following the order of data, or there are empty pages gaps between the data pages.
• Tablespace Fragmentation: the tablespace is stored in non-consecutive filesystem blocks.
• Table Fragmentation: data is stored not following the primary key order (heap tables), …

Upgrading to MySQL version 8.0 is a hot topic since version 5.7 is approaching the official end of life very soon. MySQL 5.7 EOL is set for the end of October 2023.

If you feel unprepared for the upgrade, consider post-EOL support from Percona. But it would be the worst if you proceeded with the upgrade in haste.

• Long database service downtime
• Some queries become slow
• Applications stop working due to incompatibilities
• Data loss

This is what can happen if MySQL’s major upgrade to 8.0 turns out not as you hoped for. Yes, it can be that serious, so do consider the major upgrade as an important project, not just a routine maintenance task! From my experience as a Support Engineer, I think the most frequent problem is query …