Showing entries 11 to 20 of 449
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: Percona Software (reset)
Webinar 2/26: Building a Kubernetes Operator for Percona XtraDB Cluster

This talk covers some of the challenges we sought to address by creating a Kubernetes Operator for Percona XtraDB Cluster, as well as a look into the current state of the Operator, a brief demonstration of its capabilities, and a preview of the roadmap for the remainder of the year. Find out how you can deploy a 3-node PXC cluster in under five minutes and handle providing self-service databases on the cloud in a cloud-vendor agnostic way. You’ll have the opportunity to ask the Product Manager questions and provide feedback on what challenges you’d like us to solve in the Kubernetes landscape.

Please join Percona Product Manager Tyler Duzan on Wednesday, February 26, 2020, at 1 pm EST for his webinar “Building a Kubernetes Operator for Percona XtraDB Cluster”.

[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]
How to Measure MySQL Performance in Kubernetes with Sysbench

As our Percona Kubernetes Operator for Percona XtraDB Cluster gains in popularity, I am getting questions about its performance and how to measure it properly. Sysbench is the most popular tool for database performance evaluation, so let’s review how we can use it with Percona XtraDB Cluster Operator.

Operator Setup

I will assume that you have an operator running (if not, this is the topic for a different post). We have the documentation on how to get it going, and we will start a three-node cluster using the following cr.yaml file:

apiVersion: pxc.percona.com/v1-3-0
kind: PerconaXtraDBCluster
metadata:
  name: cluster1
  finalizers:
    - delete-pxc-pods-in-order
spec:
  secretsName: my-cluster-secrets
  sslSecretName: …
[Read more]
Making Sense of MySQL Group Replication Consistency Levels

From the initial release, one of the biggest complaints I had about Group Replication is that it allowed “stale” reads and there was no way to prevent them or to even know that you read “stale” data. That was a huge limitation. Thankfully, Oracle released features to control the consistency levels, and it was exactly a year ago! I don’t know about you, but I personally was confused by naming it group_replication_consistency=’AFTER’ or ‘BEFORE’.

So now I want to try to make sense of it and share my understanding (even if it is one year later).

Setup:

[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]
Beware of Unstable Query Digests in MySQL

If you’re using MySQL’s Performance Schema you may use “query digests” as IDs to identify specific query patterns in the events_statements_summary_by_digest Performance Schema Table.

You might assume these hashes are stable between different versions, so, for example, when upgrading from MySQL 5.7 to MySQL 8, you can compare the query response time and other execution details for the same hashes as part of your upgrade process and have confidence that queries run the same (or better) after the upgrade.   Unfortunately, you can’t.

For some reason, the hashing algorithm is different between MySQL 5.6,  MySQL 5.7, and MySQL 8 even for the most trivial queries:

MySQL 5.6

mysql> select digest,digest_text  from events_statements_summary_by_digest where digest_text='commit';
+----------------------------------+-------------+
| digest                           | digest_text | …
[Read more]
Group Replication in Percona Server for MySQL

Percona Server for MySQL 8.0.18 ships all functionality to run Group Replication and InnoDB Cluster setups, so I decided to evaluate how it works and how it compares with Percona XtraDB Cluster in some situations.

For this I planned to use three bare metal nodes, SSD drives, and a 10Gb network available for in-between nodes communication, but later I also added tests on three bare metal nodes with NVMe drives and 2x10Gb network cards.

To simplify deployment, I created simple ansible scripts.

Load Data

The first initial logical step is to load data into an empty cluster, so let’s do this with our sysbench-tpcc …

[Read more]
Using Flame Graphs to Process Outputs From pt-pmp

In a previous post Profiling Software Using perf and Flame Graphs, we took a look at how to generate Flame Graphs from perf outputs. In this short follow-up, we will use Flame Graphs to process outputs from pt-pmp. Flame Graphs were not originally thought of for this purpose, but since pt-pmp outputs are similar to the folded samples (the result of using stackcollapse-perf.pl), we can take advantage of its functionality.

There are mainly two differences in the pt-pmp outputs:

  • The first column is the number of threads with that same backtrace, and
  • The function names are separated by a comma instead of a semicolon

Additionally, pt-pmp will print the date in the first line, so we’ll need to trim that part, too.

Lastly, before …

[Read more]
MySQL 8 Minor Version Upgrades Are ONE-WAY Only

In the many years we have used MySQL, we got accustomed to the fact that upgrades from MySQL 5.7.11 to 5.7.12 was a minor event. This meant that if something was going wrong, we could roll back the binaries and be happy again. 

From MySQL 8, this is no longer true. Any upgrade, even minor, is seen as irreversible. (This is valid for Percona Server for MySQL as well.)

Say we have MySQL 8.0.17 and we upgrade to 8.0.18. In our MySQL log at the start, we will have this:

[System] [MY-010116] [Server] /opt/mysql_templates/mysql-8P/bin/mysqld (mysqld 8.0.18) starting as process 13242
…
[System] [MY-013381] [Server] Server upgrade from '80017' to '80018' started.
…
[System] [MY-013381] [Server] Server upgrade from '80017' to '80018' completed.
…
[System] [MY-010931] [Server] /opt/mysql_templates/mysql-8P/bin/mysqld: …
[Read more]
Showing entries 11 to 20 of 449
« 10 Newer Entries | 10 Older Entries »