Showing entries 161 to 170 of 474
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: mysql-and-variants (reset)
Using Slow Query Log to Find High Load Spots in MySQL

This post was originally published in October 2018 and was updated in March 2023.

pt-query-digest is one of the most commonly used tools for query auditing in MySQL. By default, pt-query-digest reports the top ten queries consuming the most amount of time inside MySQL. A query that takes more time than the set threshold for completion is considered slow, but it’s not always true that tuning such queries makes them faster. Sometimes, when resources on the server are busy, it will impact every other operation on the server, and so will impact queries too. In such cases, you will see the proportion of slow queries going up. That can also include queries that work fine in general.

This article explains a small trick to identify such spots using pt-query-digest and the slow query log. pt-query-digest …

[Read more]
Purging Data When the Table Is Big and Has Children Tables

Hello friends, at Percona Managed Services, we usually do different types of things every day, including routine tasks, monitoring, and, very frequently, answering questions that are not always easy to answer.

A few days ago, a client asked us the following question: “Hey Percona, I have a question and a problem simultaneously: I want to delete rows from a table from a specific date back because the data is not necessary. I tried to run the DELETE command, which gave me this error: Cannot delete or update a parent row: a foreign key constraint fails. Could you please help me?”

At first glance, the error message was obviously clear: the table from which rows were to be deleted had a child table, which prevented the execution of the DELETE directly.

“Don’t worry, we’ll take a look at the issue, and …

[Read more]
Reduce Your Cloud Costs With Percona Kubernetes Operators

Public cloud spending is slowing down. Quarter-over-quarter growth is no longer hitting 30% gains for AWS, Google, and Microsoft. This is businesses’ response to tough and uncertain macroeconomic conditions, where organizations scrutinize their public cloud spending to optimize and adjust.

In this blog post, we will see how running databases on Kubernetes with Percona Operators can reduce your cloud bill when compared to using AWS RDS.

Inputs

These are the following instances that we will start with:

  • AWS
  • RDS for MySQL in us-east-1
  • 10 x db.r5.4xlarge
  • 200 GB storage each

The cost of RDS consists mostly of two things – compute and storage. We will not consider data transfer or backup costs in this article.

  • db.r5.4xlarge – $1.92/hour or …
[Read more]
MySQL 5.7 End of Life Six Months Away – Switch to Percona Server for MySQL Today!

Oracle’s MySQL 5.7 has had a good, long run, but the official End of Life is October of 2023. The Era of MySQL 5.x will be over, and only MySQL 8.0 will be officially supported. Yup, six months away. So if you are running MySQL, you need to consider upgrading to version 8.0 N-O-W!!

What does an upgrade provide?

MySQL 8.0 has many really cool features and improvements that are well worth the upgrade. The default character set of UTF8MB4 gives Unicode version 9.0 support. So you get the Umaluts, Cedils, and C-J-K Language support in your data, plus emojis. 8.0 is optimized around this character set. This gives you all the international characters you probably need to support global operations.

The Structured Query Language has been greatly enhanced. If you have trouble writing subqueries, you rejoice in lateral-derived joins and Common Table Expressions (CTEs). There is a new intersect clause to aid with sets. …

[Read more]
Feedback Wanted: Making EXPLAIN Require Less Privileges for INSERT/UPDATE/DELETE Statements

Introduction/TLDR:

We are considering changing EXPLAIN in Percona Server for MySQL to require less privileges for providing execution plans for INSERT/UPDATE/DELETE statements (and possibly changing the behavior for EXPLAIN SELECT as well), to make it more convenient and safer to use with monitoring and query analysis tools. We would like to get feedback from the Community about the different approaches for achieving this.

The problem:

Running EXPLAIN is a great way to understand how complex SQL statements are executed. So it is natural that monitoring and query analysis tools utilize EXPLAIN for these purposes.

However, there is a problem for cases when INSERT/UPDATE/DELETE statements need to be explained. Running EXPLAIN for these statements, a read-only operation, requires the same privileges as running the original statements …

[Read more]
ChatGPT Won’t Replace MySQL DBA – An Example of a Question About Encryption at Rest

ChatGPT is the hottest topic in the tech world right now. One story even says that ChatGPT has passed Google’s Level 3 programming interview.  I wondered, does that mean ChatGPT is ready to replace MySQL DBAs, too? No. Let me show you why.

Recently, one of our clients was considering encrypting their data at rest using the Percona file-based keyring plugin. To make the process more secure, they considered removing the local keyring_file after MySQL started. So even if someone gets host access, the data files are still protected because they do not have access to the master key used to encrypt the keys for the tables running Encryption at Rest.

Let ChatGPT try its hand at MySQL administration

Let’s see what ChatGPT will say.

I asked ChatGPT the …

[Read more]
Deploying MySQL Group Replication With Terraform

Previously, I wrote about our Terraform provider to deploy Percona Server for MySQL (Percona Server for MySQL: Automatic Cloud Deployment With Terraform) and Percona Monitoring and Management (Deploying Percona Monitoring and Management (PMM) With Terraform). Now we also added the capability to deploy Group Replication configuration with Percona Server for MySQL, and assuming we have PMM installed (see previous post), we also can automatically add Group Replication nodes to PMM monitoring.

resource "percona_ps" "psgr" {
  count = 1
  instance_type            = "t3.micro" # for AWS
  key_pair_name            = "sshKey1"
  password                 = "password"
  replication_type         = "group-replication" …
[Read more]
How Bloom Filters Work in MyRocks

Bloom filters are an essential component of an LSM-based database engine like MyRocks. This post will illustrate through a simple example how bloom filters work in MyRocks.

Why?

With MyRocks/RocksDB, data is stored in a set of large SST files.  When MyRocks needs to find the value associated with a given key, it uses a bloom filter to guess if the key could potentially be in an SST file.

How?

A bloom filter is a space-efficient way of storing information about a list of keys. At its base, there is a bitmap and a hash function.  The hash value of the keys stored in an SST is computed, and the results are used to set some bits to “1” in the bitmap.  When you want to know if a key is present or not in the list, you run it through the hash function and check if the corresponding bits in the bitmap are …

[Read more]
Differing MySQL Client Versions Causing Broken Replication and Collations in Aurora

Recently, I was working with my colleagues Edwin Wang and Taras Onishchuk and found an interesting edge case involving a situation where a replica running Percona Server for MySQL 5.7, external to AWS Aurora instance version 2.10.2 (5.7-compatible), broke. I recreated the issue in my lab with a simple create database statement, as you will see below.

Error 'Character set '#255' is not a compiled character set and is not specified in the '/usr/share/percona-server/charsets/Index.xml' file' on query. Default database: 'lab'. Query: 'create database test'

The interesting thing to note here is the character set ‘#255’. You won’t see this available if you check the list of available collations in Percona Server for MySQL 5.7 for the UTF8MB4 character set.

mysql> SHOW COLLATION WHERE Charset = 'utf8mb4' and id = 255; 
Empty set (0.01 sec)

[Read more]
Percona XtraBackup and MySQL 5.7 Queries in Waiting for Table Flush State

Percona XtraBackup is an open source hot backup utility for MySQL-based servers. To take consistent and hot backup, it uses various locking methods, especially for non-transactional storage engine tables. This blog post discusses the cause and possible solution for queries with ​Waiting for table flush state in processlist when taking backups using Percona XtraBackup. Only MySQL 5.7 version is affected by this, as per my tests.

Type of locks taken by Percona XtraBackup

Before discussing the main issue, let’s learn about the type of locks used by Percona XtraBackup to take consistent backups. Percona XtraBackup uses backup locks as a lightweight alternative to FLUSH TABLES WITH READ LOCK. This feature is …

[Read more]
Showing entries 161 to 170 of 474
« 10 Newer Entries | 10 Older Entries »