Showing entries 1 to 10 of 576
10 Older Entries »
Displaying posts with tag: Percona Software (reset)
Percona XtraBackup Changing to Strict by Default

Backups are a key part of a disaster recovery strategy, making sure you can continue or restore your business in case of an unwanted event with your data.

We always work on trying to improve Percona XtraBackup reliability, always favoring consistency, attempting to make unwanted outcomes be noticed as earlier as possible in the process.

Enabling –strict by Default

As of the upcoming release of 8.0.27, XtraBackup will no longer accept invalid parameters. Since the beginning of the times, validation of parameters has been a difficult task for XtraBackup as it mixes server-side and XtraBackup only parameters.

Starting at Percona XtraBackup 8.0.7 we implemented …

[Read more]
Attack No-PK Replication Lag with MySQL/Percona Server 8 Invisible Columns!

The most common issue when using row-based replication (RBR) is replication lag due to the lack of Primary keys.

The problem is that any replicated DML will do a full table scan for each modified row on the replica. This bug report explains it more in-depth: https://bugs.mysql.com/bug.php?id=53375

For example, if a delete is executed on the following table definition:

CREATE TABLE `joinit` (
  `i` int NOT NULL,
  `s` varchar(64) DEFAULT NULL,
  `t` time NOT NULL,
  `g` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

 

With this amount of rows:

mysql> select count(*) from joinit;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+

 

The delete being:

mysql> flush status ;

mysql> delete from joinit where i > 5 and i < 150;
Query OK, 88 rows affected (0.04 sec) …
[Read more]
Online DDL With Group Replication in MySQL 8.0.27

In April 2021, I wrote an article about Online DDL and Group Replication. At that time we were dealing with MySQL 8.0.23 and also opened a bug report which did not have the right answer to the case presented. 

Anyhow, in that article I have shown how an online DDL was de facto locking the whole cluster for a very long time even when using the consistency level set to EVENTUAL.

This article is to give justice to the work done by the MySQL/Oracle engineers to correct that annoying inconvenience. 

Before going ahead, let us remember how an Online DDL was propagated in a group replication cluster, and identify the differences with what happens now, all with the consistency level set to EVENTUAL ( …

[Read more]
MySQL 8.0 Functional Indexes

Working with hundreds of different customers I often face similar problems around running queries. One very common problem when trying to optimize a database environment is index usage. A query that cannot use an index is usually a long-running one, consuming more memory or triggering more disk iops.

A very common case is when a query uses a filter condition against a column that is involved in some kind of functional expression. An index on that column can not be used.

Starting from MySQL 8.0.13 functional indexes are supported. In this article, I’m going to show what they are and how they work.

The Well-Known Problem

As already mentioned, a very common problem about index usage is when you have a filter condition against one or more columns involved in some kind of functional expression.

Let’s see a simple example.

You have a table called products containing the details of your products, …

[Read more]
Percona Server for MySQL Encryption Options and Choices

Security will always be a main focal point of a company’s data. A common question I get from clients is, “how do I enable encryption?” Like every good consulting answer, it depends on what you are trying to encrypt. This post is a high-level summary of the different options available for encryption in Percona Server for MySQL.

Different certifications require different levels of encryption. For example, PCI requires both encryptions of data at rest and in transit. Here are the main facets of encryption for MySQL:

  • Data at Rest
    • Full disk encryption (at the OS level)
    • Transparent Data Encryption – TDE
    • Column/field-level encryption
  • Data in Transit
    • TLS Connections

Data at Rest

[Read more]
Backup Performance Comparison: mysqldump vs MySQL Shell Utilities vs mydumper vs mysqlpump vs XtraBackup

In this blog post, we will compare the performance of performing a backup from a MySQL database using mysqldump, MySQL Shell feature called Instance Dump, mysqlpump, mydumper, and Percona XtraBackup. All these available options are open source and free to use for the entire community.

To start, let’s see the results of the test.

Benchmark Results

The benchmark was run on an …

[Read more]
A Look Into Percona XtraDB Cluster Non-Blocking Operation for Online Schema Upgrade

Percona XtraDB Cluster 8.0.25 (PXC) has introduced a new option to perform online schema modifications: NBO (Non-Blocking Operation).

When using PXC, the cluster relies on the wsrep_OSU_method parameter to define the Online Schema Upgrade (OSU) method the node uses to replicate DDL statements. 

Until now, we normally have three options:

  • Use Total Isolation Order (TOI, the default)
  • Use Rolling Schema Upgrade (RSU)
  • Use Percona’s online schema change tool (TOI + PTOSC)

Each method has some positive and negative aspects. TOI will lock the whole …

[Read more]
Testing Percona Distribution for MySQL Operator Locally with Kind

We have a quickstart guide for how to install Percona Distribution for MySQL Operator on minikube. Installing the minimal version works well as it is described in the guide. After that, we will have one HAproxy and one Percona XtraDB Cluster (PXC) node to work with.

Minikube provides Kubernetes locally. One can try using the provided local k8s to try the more advanced scenarios such as the one described here.

Following that guide, everything works well, until we get to the part of deploying a cluster with

deploy/cr.yaml

Even after that, things seemingly work.

$ kubectl get pods
NAME READY STATUS RESTARTS …
[Read more]
PXC Scheduler Handler: The Missing Piece for Galera/Percona XtraDB Cluster Puzzle

Working on a real case scenario in a five node Percona XtraDB Cluster (PXC), we were forced to use wsrep_sync_wait = 1, because the app does reads-after-write and we send reads to all the nodes. We had the idea to leave some nodes in DESYNC mode to reduce the flow control messages during peak load and expected to have a steadier write throughput keeping the read consistency.

We decided to test Perconas’s new PXC Scheduler Handler which is an application that manages integration between ProxySQL and Galera/PXC (the scope is to maintain the ProxySQL mysql_server table, if a negative scenario occurs, like: failures, service degradation, and maintenance). However, we realized that when a node is in DESYNC mode, it is kicked out of the read hostgroup. That is …

[Read more]
Getting Started with ProxySQL in Kubernetes

There are plenty of ways to run ProxySQL in Kubernetes (K8S). For example, we can deploy sidecar containers on the application pods, or run a dedicated ProxySQL service with its own pods.

We are going to discuss the latter approach, which is more likely to be used when dealing with a large number of application pods. Remember each ProxySQL instance runs a number of checks against the database backends. These checks monitor things like server-status and replication lag. Having too many proxies can cause significant overhead.

Creating a Cluster

For the purpose of this example, I am going to deploy a test cluster in GKE. We need to follow these steps:

1. Create a cluster

gcloud container clusters create ivan-cluster --preemptible --project my-project --zone us-central1-c --machine-type n2-standard-4 --num-nodes=3

2. Configure command-line access

gcloud …
[Read more]
Showing entries 1 to 10 of 576
10 Older Entries »