Discover how to troubleshoot a MySQL replica IO thread stuck in a connecting state. Learn about the replication architecture, security group rules for AWS EC2 instances, and how to address common issues like network restrictions and bind address configuration.

MySQL is a powerful database management and a widely used cloud database service. One of its key features is the ability to create replicas of a master database to improve its availability and scalability. However, at times the IO thread in a MySQL replica may get stuck in a connecting state, which can cause replication issues and affect the overall data consistency …

Cover image by DALL-E: “a manometer with pipe with a dolphin on a background, 3d render”

What’s the best way to store binary data in MySQL? This is a question that has multiple answers, depending on what you want to achieve. For example, if you need to optimize for the storage size, you probably need to use some compression algorithm that compresses your data efficiently. In my case, I really need the best performance, meaning the fastest response time to fetch the entire blob from MySQL.

Let’s put aside the question of whether MySQL is a good fit for storing binary data. The question here, is how to store binary data so the reads from DB are as fast as possible?

The …

The Primary key is like the hero of a row, which has more beneficial features in the table while performing any task on the table.

The DBA knows the importance of the primary key in the table and how to handle it.

1. Notable features of having a primary key:
2. Requirements:
3. Enabling GIPK:
4. Handling GIPK:
…

This is one of the exciting troubleshooting related to XA transactions let’s dive in. Let me provide a few backgrounds.

For one of our clients, when we try to drop a table, it is waiting for metadata lock. On debugging it is one of the XA transactions is holding the shared write lock and causing metadata lock on all the other local transactions.

Let us view the metadata locks from the Performance Schema.

The following is a short HOWTO about deployment and use of Benchmark-kit (BMK-kit). The main idea of this kit is to simplify your life in running various MySQL benchmark workloads with less blood and minimal potential errors.

Generally as simple as the following :

$ bash /BMK/sb_exec/sb11-Prepare_50M_8tab-InnoDB.sh 32   # prepare data

$ for users in 1 2 4 8 16 32 64 128 256 512 1024 2048
do   
  # run OLTP_RW for 5min each load level..
  bash /BMK/sb_exec/sb11-OLTP_RW_50M_8tab-uniform-ps-trx.sh $users 300
  sleep 15
done

the latest public online version of the following HOWTO is always available from here : http://dimitrik.free.fr/blog/posts/mysql-perf-bmk-kit.html

UPDATE : May.2021 -- workaround fixes for A1 ARM64 systems

UPDATE : Sep.2021 -- dedicated Sysbench binary for MySQL 8.0 & SSL

UPDATE : Oct.2021 -- …

In this article I will show you how to properly configure Apache Superset in order to take advantage of a high performance, in-memory query accelerator: MySQL HeatWave.

The post Explore & visualize your MySQL HeatWave data with Superset first appeared on dasini.net - Diary of a MySQL expert.

Replication Lag is just a fact of life with async-replication. We can’t stop lag, but we can help to reduce it. Many times the Seconds_Behind_Source can be very deciving, I have seen it go from 1 hour behind to 0 lag in the blink of an eye. There are many factors that can add to replica lag. Some of these are:

• Network IO
• Disk IO
• Database Workload
• Database settings

In this blog we will look a few database settings to help reduce lag. The settings we will look at are listed below.

1. blinlog_transaction_dependency_tracking
2. binlog_group_commit_sync_delay
3. replica_parallel_type
4. replica_parallel_workers

Hardware:

1. Two Raspberry Pi 4 with 8GB of RAM.
2. Sandisk 128GB Extreme microSDXC card.

Software:

1. OS Raspbian Bullseye 64bit.
2. Percona Server version …

While exporting the table with MySQL native utility, we don’t have any control on the process, and also there will be no progress update as well on the process completion. So when exporting the larger table will consume high resource utilization and also the disk space usage will also be high.

MySQL shell utility will make the process easier. It will export the table and we can import the data back with a parallel thread and also will provide the current progress status on export/import progress.

util.exportTable() utility was introduced in Shell – 8.0.22 version, will export the data in a controlled manner. We can store the data in either local or Cloud Infrastructure Object Storage bucket as well.

We will see about the compression ratio along with the time taken for native MySQL vs Shell utility

Feature :

• Compression
• Progress status
• Supported output …

On July 20th, 2021, we’ve celebrated the release of MySQL NDB Cluster 8.0.26. MySQL NDB Cluster (or NDB for short) is part of the MySQL family of open-source products providing an in-memory, distributed, shared-nothing, high-availability storage engine usable on its own or with MySQL servers as front-ends. For the complete changeset see release notes. Download it here.

Choosing a database can be an overwhelming task, requiring to consider performance (throughput and latency), high availability, data volume, scalability, ease of use/operations, etc. These considerations are affected by where the database runs — whether that is in a cloud provider such as …

While it's easier to measure the impact of Dynimize if you are running a MySQL benchmark with clear metrics, it can sometimes be a challenge on a production workload where you don't have precise performance analytics or metrics available. There are many great MySQL performance analysis tools out there, however they can often take time and effort to setup.

The simple measureDyniMysql script was created for this exact reason, and does not incur any MySQL downtime. You can find it at /opt/dynimize/measureDyniMysql after installing Dynimize. It will report the change in MySQL queries per second and mysqld CPU usage after applying Dynimize. Here is how to use it.

1. Install and start dynimize, and get your mysqld process into the dynimized state. For example, the …