Showing entries 1 to 10 of 851
10 Older Entries »
Displaying posts with tag: Insight for DBAs (reset)
Building Percona Server for MySQL 8.0 with RocksDB Storage Engine on macOS

In Percona Server for MySQL 8.0.29-21, we added one more patch that helps us to build server code on macOS. To be precise here, we still could do this even before this patch but only partially. Now it is possible to build RocksDB Storage Engine as well.

A word of disclaimer here, at the moment, by macOS we still understand macOS for Intel x86_64 architecture (the most recent ARM versions with Apple M1 / M2 processors are out of the scope of this blog post). Moreover, Percona does not provide …

[Read more]
MySQL 8.0 Dynamic Redo Log Sizing

This blog post will discuss the newest feature available in MySQL 8.0.30: dynamic redo log sizing. After the InnoDB buffer pool size, we can say that having a proper size for the redo logs is crucial for MySQL performance. There are numerous blog posts about how to calculate a good redo log size. One of our classic blog posts is this one from Baron: How to calculate a good InnoDB log file size (That blog post is from 2008 and is still a valid formula to use.)

Now, which problem is this feature trying to solve?

In MySQL, the buffer pool and redo log size are settings that do not change frequently. Usually, these settings are set during database installation; after that, they are forgotten until an issue arises. The problem is that they used to be static, which means that you had to restart MySQL so the changes …

[Read more]
How to Benchmark Replication Performance in MySQL

In this blog, I will cover important aspects which you need to test when benchmarking replication setup. MySQL has great tools that could be used to test its performance. They include:

sysbench – https://github.com/akopytov/sysbench

BMK-kit – http://dimitrik.free.fr/blog/posts/mysql-perf-bmk-kit.html

mysqlslap – https://dev.mysql.com/doc/refman/8.0/en/mysqlslap.html

LinkBench – https://github.com/facebookarchive/linkbench

I will not describe how to use them here, as you can find instructions on the provided links or in the Percona blog by browsing tags …

[Read more]
A Quick Peek At MySQL 8.0.30

MySQL 8.0 is now over four years old and Oracle released the latest quarterly offering today with MySQL 8.0.30, which comes with lots of bug fixes and some interesting additions.  This is my take on the release notes for those who do not have the time to wade through them, comments in italics are my comments and reflect the views of only me

This is an interesting release with a good many bug fixes and I urge those who need fixes to upgrade as soon as they can. For everyone else, the TL;DR is that unless you are on the Oracle Cloud Infrastructure or one of the fixed bugs is causing you problems, then upgrade at your leisure. 

Deprecation and removals

Setting the  replica_parallel_workers system variable to 0 is now deprecated and to use single threading set replica_parallel_workers=1 instead.  …

[Read more]
Preventing Stale Reads on Percona XtraDB Cluster by Enforcing Causality Checks on a Per-Query Basis

When we run a SELECT in a replica server and it returns a different result to the one we would have obtained had we queried the source server instead, due to changes in the dataset that have not yet been replicated or synchronized to the replica, we get what is known as a stale read. My colleague Marcelo has blogged about Stale Reads and How to Fix Them before.

The need to avoid stale reads is a constraint that leads people into migrating to Percona XtraDB Cluster (PXC) / Galera Cluster or, more recently, Group Replication. I am focusing on PXC and Galera in this short blog post, which, similar to Group Replication, implements not exactly a synchronous replication model but what is commonly referred to as a …

[Read more]
How to Resolve “Error Reading Relay Log Event” After Relay Log Corruption

In this blog, I explain how to recover from a replication failure caused by a corrupted relay log file.

MySQL replica stores data received from its source binary log in the relay log file. This file could be corrupted due to various reasons, mostly hardware failures. If this happens, replication will stop working, and the error log on the replica will have entries similar to:

2022-05-12T12:32:07.282374Z 2 [ERROR] Error in Log_event::read_log_event(): 'Event too small', data_len: 0, event_type: 0
2022-05-12T12:32:07.282386Z 2 [ERROR] Error reading relay log event for channel '': slave SQL thread aborted because of I/O
...
2022-05-12T12:32:07.282396Z 2 [ERROR] Slave SQL for channel '': Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running …
[Read more]
Percona XtraBackup 8.0.29 and INSTANT ADD/DROP Columns

Oracle’s MySQL 8.0.29 release extended the support for ALTER TABLE … ALGORITHM=INSTANT to 1) allow users to instantly add columns in any position of the table, and 2) instantly drop columns. As part of this work, the InnoDB redo log format has changed for all DML operations on the server. This new redo log format introduced a design flaw that can cause data corruption for tables with INSTANT ADD/DROP COLUMNS.

The corruption happens when InnoDB crash recovery takes place. InnoDB applies redo logs at startup. Percona XtraBackup copies the redo log during backup and applies it as part of the –prepare step to bring the backup to a consistent state.

Percona fixed the corruption issue and several other issues with the INSTANT ADD/DROP column …

[Read more]
Exploring MySQL on Kubernetes with Minkube

In this blog post, I will show how to install the MySQL-compatible Percona XtraDB Cluster (PXC) Operator on Minikube as well as perform some basic actions.   I am by no means a Kubernetes expert and this blog post is the result of my explorations preparing for a local MySQL Meetup, so if you have some comments or suggestions on how to do things better, please comment away!

For my experiments, I used Minikube version 1.26 with the docker driver in the most basic installation on Ubuntu 22.04 LTS, though it should work with other combinations, too. You also can find the official “Running Percona XtraDB Cluster on Minikube” documentation …

[Read more]
Tags and FullText indexes in MySQL

As a principal architect at Percona, one of my duties is to tune MySQL database servers for our customers. The tuning effort looks at every aspect of the database service like the operating system, the MySQL configuration, the schema, the queries, etc. We have well-defined processes to tune the operating system and the MySQL configuration. However, tuning the schema and the queries using it can be anywhere from trivial to extremely challenging.

The challenge with the schema and the queries is mostly with the indexes. The most common types of indexes are based on b-trees or hash lists. InnoDB doesn’t support hash indexes, a bummer for equality conditions. B-tree indexes are more general-purpose, they are decent for equality conditions and very good for range conditions. They are however quite heavy and very dependent on the defined order of columns. They are also poor for double range conditions.

Double range conditions can be …

[Read more]
Digital Signatures: Another Layer of Data Protection in Percona Server for MySQL

Imagine you need to design an online system for storing documents on a per-user basis where nobody, including database administrators, would be able to change the content of those documents without being noticed by document owners.

In Percona Server for MySQL 8.0.28-20, we added a new component called Encryption UDFs – an open-source alternative to MySQL Enterprise Encryption that allows users to access a number of low-level OpenSSL encryption primitives directly from MySQL. This includes calculating digests (with a great variety of hash functions), asymmetric key generation (RSA, …

[Read more]
Showing entries 1 to 10 of 851
10 Older Entries »