Showing entries 1 to 10 of 675
10 Older Entries »
Displaying posts with tag: Insight for DBAs (reset)
Split-Brain 101: What You Should Know

Disclaimer: The following blog post does not try to provide a solution for split-brain situations, and the example provided is for demonstrative purposes only. Inconsistencies resulting from a split-brain scenario might possibly be more complex than the one shown, so do not use the following example as a complete guide.

What is Split-Brain?

A split-brain scenario is the result of two data sets (which were originally synced) losing the ability to sync while potentially continuing to receive DMLs over the same rows and ids on both sides. This could have consequences such as data corruption or inconsistencies where each side has data that does not exist on the other side.

For example before split-brain:

After split-brain:

Node1:

Node2:

It can be seen that after the split-brain scenario there are many differences between the nodes:

  • Customer row id=2 was deleted …
[Read more]
Help Drive the Future of Percona XtraDB Cluster

Percona is happy to announce the experimental release of Percona XtraDB Cluster 8.0. This is a major step for tuning Percona XtraDB Cluster to be more cloud- and user-friendly. This is the second experimental release that combines the updated and feature-rich Galera 4, with substantial improvements made by our development team.

Improvements and New Features in Percona XtraDB Cluster

Galera 4, included in Percona XtraDB Cluster 8.0, has many new features. Here is a list of the most essential improvements:

  • Streaming replication to support large transactions
  • The synchronization functions allow action coordination (wsrep_last_seen_gtid, wsrep_last_written_gtid, wsrep_sync_wait_upto_gtid)
  • More granular and improved error logging. wsrep_debug is …
[Read more]
ClickHouse Versus MySQL Handling of Double Quotes

If you’re a MySQL user trying ClickHouse, one thing which is likely to surprise – and annoy you – is the handling of Double Quotes. In MySQL, you can use both double quotes and single quotes to quote strings, and as an example, these two queries are equivalent:

mysql> select * from performance_schema.global_variables where variable_name='max_connections';
+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+----------------+
| max_connections | 151            |
+-----------------+----------------+
1 row in set (0.01 sec)

mysql> select * from performance_schema.global_variables where variable_name="max_connections";
+-----------------+----------------+
| VARIABLE_NAME   | VARIABLE_VALUE |
+-----------------+----------------+
| max_connections | 151            |
+-----------------+----------------+
1 row in set (0.00 sec)

This means that many of us tend to use single quotes and double quotes …

[Read more]
Introduction to MySQL 8.0 Recursive Common Table Expression (Part 2)

This is the second part of a two-articles series. In the first part, we introduced the Common Table Expression (CTE), a new feature available on MySQL 8.0 as well as Percona Server for MySQL 8.0. In this article, we’ll present the Recursive Common Table Expression. SQL is generally poor at recursive structures, but it is now possible on MySQL to write recursive queries. Before MySQL 8.0, recursion was possible only by creating stored routines.

What is a Recursive Common Table Expression?

A recursive CTE is one having a subquery that refers to its own name. It is particularly useful in the following cases:

  • To generate series
  • Hierarchical or tree-structured data traversal
[Read more]
Introduction to MySQL 8.0 Common Table Expressions (Part 1)

This blog is the first part of a two-articles series. In this article, I’m going to introduce the Common Table Expression (CTE), a new feature available on MySQL 8.0, as well as Percona Server for MySQL 8.

What is a Common Table Expression?

We can define a CTE as an alternative to a derived table. In a small way, CTE simplifies complex joins and subqueries, improving the readability of the queries. CTE is part of ANSI SQL 99 and was introduced in MySQL 8.0.1. The same feature is available even on Percona Server for MySQL 8.0.

The main reasons for using CTE are:

  • Better readability of the queries
  • Can be referenced multiple times in the same query
  • Improved performance
  • A valid alternative to a VIEW, if your user cannot create VIEWs
  • Easier chaining of multiple CTE …
[Read more]
ClickHouse and MySQL – Better Together

It’s been a while since I wrote about ClickHouse, there are a lot of new features that are worth mentioning, and for this, I recommend to follow the Altinity Blog but today I want to look at the improved integration of ClickHouse and MySQL.

There are two features:

  1. Using MySQL protocol and MySQL client to connect to ClickHouse
  2. Use MySQL tables to select and join with ClickHouse tables

Using MySQL Protocol

By enabling MySQL protocol in ClickHouse server, you will allow for the MySQL command line tool or applications that typically connect to MySQL to connect to ClickHouse and execute queries.

With this, keep in mind:

  • It does not automatically allow you to use MySQL SQL syntax, you still need to write queries in ClickHouse-dialect.
  • The same issue with datatypes. ClickHouse uses Int32/Int64 …
[Read more]
InnoDB Flushing in Action for Percona Server for MySQL

As the second part of the earlier post Give Love to Your SSDs – Reduce innodb_io_capacity_max! we wanted to put together some concepts on how InnoDB flushing works in recent Percona Server for MySQL versions (8.0.x prior to 8.0.19, or 5.7.x). It is important to understand this aspect of InnoDB in order to tune it correctly. This post is a bit long and complex as it goes very deep into some InnoDB internals.

InnoDB internally handles flush operations in the background to remove dirty pages from the buffer pool. A dirty page is a page that is modified in memory but not yet flushed to disk. This is done to lower the write load and the latency of the transactions. Let’s explore the various sources of flushing inside InnoDB.

Idle Flushing

We already discussed the idle flushing in the previous post …

[Read more]
Setup and Deploy Vitess on Kubernetes (Minikube) for MySQL – Part III of III

In this blog post, we will continue to explore Vitess and test an example database provided in its repository. This is Part III of the previously discussed installation of Vitess on minikube environment, so please make sure to follow those steps to bring the cluster up to the following level.  

$ kubectl get pods,jobs
NAME                                                                READY     STATUS    RESTARTS   AGE
po/etcd-global-kbbcqlgvp9                                           1/1       Running   0          43m
po/etcd-zone1-lpc5zmdxxn                                            1/1       Running   0          43m
po/my-release-etcd-operator-etcd-backup-operator-6684dd6d8c-pr4n4   1/1       Running   0          1h
po/my-release-etcd-operator-etcd-operator-86d94989d6-w9lpx          1/1       Running …
[Read more]
Setup and Deploy Vitess on Kubernetes (Minikube) for MySQL – Part II of III

In this blog post, I’d like to share some experiences in setting up a Vitess environment for local tests and development on OSX/macOS. As previously, I have presented How To Test and Deploy Kubernetes Operator for MySQL(PXC) in OSX/macOS, this time I will be showing how to Run Vitess on Kubernetes.

Since running Kubernetes on a laptop is only experimental, I had faced several issues going through straight forward installation steps so I had to apply a few workarounds to the environment. This setup will have only minimum customization involved.

For a high-level overview of Vitess, please visit Part I of this series, …

[Read more]
Amazon RDS MySQL Minor Upgrades: Not So Fast!

The promise of DBaaS like RDS is to reduce operational overhead (among other things) and one of the stellar cases is upgrades (major and minor). The suggested procedure involves just a couple of steps. For example, using AWS Console, you can enable “Auto minor upgrade” or modify the DB instance and schedule the upgrade to run in the next maintenance window.

But, both these options are risky because the upgrade process will start during the maintenance window but it is NOT guaranteed that the upgrade will be completed within the specified duration. 

The Problem

RDS performs a few extra steps to ensure the data consistency and rollback, making the minor version upgrade a time-consuming process:

  • It takes a backup (if automated backups are enabled) prior to starting the upgrade process.
  • Performs slow shutdown after setting …
[Read more]
Showing entries 1 to 10 of 675
10 Older Entries »