Oracle releases updates to MySQL on a quarterly basis and the Release Notes for 8.0.31 arrived just before the software.  This time around there are some very interesting new features that will be handy including SQL standards support, as well as over 130 bug fixes.

The TL;DR:  Some nice new features but nothing spectacular.

• FULL is now a reserved word.
• InnoDB now supports parallel index builds, which improves index build performance. In particular, loading sorted index entries into a B-tree is now multithreaded.
• The OpenSSL library for MySQL Server has been updated to version 1.1.1q.
• The optimizer has been improved so that the old ER_NOT_SUPORTED_YET is not thrown when you try something like this:
  

  ((SELECT a, b, c FROM t ORDER BY a LIMIT 3) ORDER BY b LIMIT 2) ORDER BY c …

As we have Kubernetes installed in part one (see Using Percona Kubernetes Operators With K3s Part 1: Installation), now we will install Percona Server for MySQL Operator into the running cluster.

I will copy some ideas from Peter’s Minukube tutorial (see Exploring MySQL on Kubernetes with Minkube).

In this case, I will use not Percona XtraDB Cluster Operator but a regular Percona Server for MySQL with Asynchronous replication.

We have recently released version 0.3.0 and it is still in the technical preview state, so we are actively looking for more feedback!

If we go with all …

A few weeks back, we faced an issue in a replication environment for a Managed Services client:

LAST_ERROR_MESSAGE: Worker 2 failed executing transaction ‘UUID:GTID’ at master binlog.0012345, end_log_pos 98765; Error ‘Operation CREATE USER failed for ‘test_user’@’10.10.10.10” on query. Default database: ‘mysql’. Query: ‘CREATE USER ‘test_user’@’10.10.10.10’ IDENTIFIED WITH ‘mysql_native_password’ AS ‘************”

After some initial investigation, we noticed that the user in the replica didn’t exist! Was MySQL going crazy? But then the customer mentioned they had the following error in the primary before being able to execute the query successfully:

root@localhost [mysql]> CREATE USER 'test_user'@'10.10.10.10' identified WITH 'mysql_native_password' BY …

If you watched Finding Poorly Designed Schemas and How to Fix Them you witnessed Marcos Albe use some very interesting queries. These queries let you find tables without primary keys, tables with non-integer primary keys, tables that do not use InnoDB, tables and indexes with the most latency, indexes that are 50% larger than the table, find duplicate indexes, and find unused indexes. As promised, they are below.

— Find tables without PK
SELECT t.table_schema,t.table_name,t.engine
FROM information_schema.tables t
JOIN information_schema.columns c
ON t.table_schema=c.table_schema
AND t.table_name=c.table_name
WHERE t.table_schema NOT IN (‘mysql’, ‘information_schema’, ‘sys’, ‘performance_schema’)
AND t.table_type = ‘BASE TABLE’
GROUP BY t.table_schema,t.table_name, t.engine
HAVING …

Geo locations are a cornerstone of modern applications. Whether you’re a food delivery business or a family photographer, knowing the closest “something” to you or your clients can be a great feature.

In our ‘Scaling and Optimization’ training class for MySQL, one of the things we discuss is column types. The spatial types are only mentioned in passing, as less than 0.5% of MySQL users know of their existence (that’s a wild guess, with no factual basis). In this post, we briefly discuss the POINT type and how it can be used to calculate distances to the closest public park.

Import the data

To start off, we need a few tables and some data. The first table will hold the mapping between the zip code and its associated latitude/longitude. GeoNames has this data under the Creative Commons v3 license, …

When you develop an application you expect success, and often success comes with growth problems.  These problems especially show themselves in the area of data storage, where being stateful is not as easy to scale as the stateless parts of the application.

There are several stages of approaching database scalability:

1. Configuration and query optimization. This step can help a lot, and I would recommend a recent book by Daniel Nichter “Efficient MySQL Performance: Best Practices and Techniques”, which goes into this topic.
2. If #1 is done and you continue to push the limits of your database, the next step is to improve the hardware: adding extra memory, improving storage throughput (regular SSD, NVMe storage layer, etc.), or increasing the size of the cloud instances (this is what I call “optimization by credit card”). This typically should help, but only to a certain limit. And there is only so much …

In a post written earlier this year – Percona Server for MySQL Encryption Options and Choices –  I discussed some of the options around encryption in MySQL.  Being such a complex topic, that post was meant to clarify and highlight various aspects of “encryption” at different levels.  I recently had this topic come up again, but specifically around column-level encryption and various options so I wanted to touch on this in more detail.

As of the current release of Percona Server for MySQL, there is no built-in way to define a single column as encrypted.  Ideally, there could be some metadata passed in a create statement and this would just automatically happen, such as this:

CREATE TABLE pii_data ( …

In my previous blog post, ProcFS UDF: A Different Approach to Agentless Operating System Observability in Your Database, I wrote about the ProcFS UDF MySQL plugin, which allows you to get operating systems stats, through the MySQL database, without having shell access to the server and any local agent installation.

Some of you wondered whether there is a way to use this goodness in Percona Monitoring and Management (PMM), and this blog post will show you exactly how to do that.

Unfortunately, at this point, Percona Monitoring and Management does not support the ProcFS UDF MySQL plugin out of the box. It is in the backlog, along with many other cool things. However, …

You have your database instance deployed with AWS and you are using AWS RDS for MySQL. All work smoothly in terms of satisfying queries for your application and delivering reliable uptime and performance. Now you need to take care of your backup strategy. Business is defined to have this retention policy:

• 7 daily full backups
• 4 weekly backups
• 12 monthly backups

Plus the ability to do point-in-time recovery (PITR) for the last 24 hours since the last full backup was taken.

The cloud vendor solution

This is a piece of cake. The daily backups: just set the backup retention period to six days. Done. This also has the plus that PITR is already covered since RDS uploads the transaction logs to S3 every five minutes and stores them in the parquet format, making it smaller than the regular text file. All amazing, right?

Now, the weekly and …

A large table is a pain for many reasons as long as it is in a system. And as if that’s not enough, it is also a difficult task to get rid of it. In this post, we will understand why it is a pain to do this operation and what we can do about it. It will be like asking the table “Tell me what happened and I will ease up the eviction”.

So what happened? When a table is dropped (or truncated), InnoDB has to scan the pages throughout the buffer pool and remove all those belonging to that table. For a large buffer pool, this crawling in the buffer pool pages and eviction process will be slower. When we say “scan buffer pool”, it mainly looks for “LRU”, “FLUSH” (Dirty pages), and “AHI” entries.

LRU: Buffer pool pages are stored in a linked list of pages in order of usage. As the data reaches the end of the list, it is evicted to make space for new data. When the room is needed to add …