Showing entries 1 to 10 of 311
10 Older Entries »
Displaying posts with tag: mysql-and-variants (reset)
Why You Need To Keep Track of Bugs

Most of us are lucky that software bugs are a small part of our lives. The ‘things just work’ attitude works for most situations, but occasionally something appears that may seem trivial at first glance may be catastrophic.

A bug appeared in MySQL’s recently released 8.0.32 that really caught my attention. This is a prime example of how a minor bug could have a significant impact on your life and a detrimental impact on your company or project.

The title Wrong result for AVG() OVER(ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) is the first thing that caught my eye. A lot of developers heavily use the AVG() function. My first thought was, ‘ut-oh, AVG() being broken is a terrible thing,’ but then I realized it was thankfully not that drastic.

The OVER() clause indicates this is a problem in a Window Function. And odds are, if you are using OVER() for …

[Read more]
Working of MySQL Replication Filters When Using Statement-based and Row-based Replication

A couple of days ago I was creating an index on the source and when I checked the replica side it was not replicated, so I just wanted to explain how the replication filter may increase the complexity of your DBA operations.

Replication occurs by reading events from the binary log of the source and then executing them on the replica. The events in the binary log are recorded in different formats, depending on the type of event. These formats are determined by the binary logging format used when the events were initially recorded on the source. The relationship between the binary logging formats and the terminology used during replication is as follows:

When using statement-based binary logging, the source writes SQL statements to the binary log. Replication of the source to the replica is performed by executing these SQL statements on the replica. This is known as statement-based replication (SBR), and it …

[Read more]
[BUG] Stopping Purge/Resuming Purge in Error Logs After Upgrade to MySQL 5.7.40

We had a couple of cases where clients reported that the MySQL error log was flooded with the below note:

2023-01-18T13:07:56.946323Z 2 [Note] InnoDB: Stopping purge
2023-01-18T13:07:56.948621Z 2 [Note] InnoDB: Resuming purge
2023-01-18T13:08:27.229703Z 2 [Note] InnoDB: Stopping purge
2023-01-18T13:08:27.231552Z 2 [Note] InnoDB: Resuming purge
2023-01-18T13:08:28.581674Z 2 [Note] InnoDB: Stopping purge

One of my colleagues Sami Ahlroos found that whenever we trigger a truncate on any table, the function is stopping the purge and then resuming it once it has found it stopped.

Below are the steps to reproduce.

  1. Log verbosity needs to be set to 3 (the default value)
mysql> show variables like 'log_error_verbosity%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| …
[Read more]
Help! I Am Out of Disk Space!

How can we fix a nasty out-of-space issue leveraging the flexibility of Percona Operator for MySQL?

When planning a database deployment, one of the most challenging factors to consider is the amount of space we need to dedicate to data on disk.

This is even more cumbersome when working on bare metal, as it is more difficult to add space when using this kind of solution with respect to the cloud.

When using cloud storage like EBS or similar, it is normally easy(er) to extend volumes, which gives us the luxury to plan the space to allocate for data with a good grade of relaxation. 

Is this also true when using a solution based on Kubernetes like Percona Operator for MySQL? Well, it depends on where you run it. However, if the platform you choose supports the option to extend volumes, K8s per se gives you the …

[Read more]
A Quick Peek at MySQL 8.0.32

Recently Oracle released new versions of their software and there are some things of note. MySQL 8.032 is not a major change but you do need to be aware of some changes just in case they have the potential to send you into the court of Murphy’s Law. So let’s look through the release notes to see what is new, changed, and deprecated.

My own comments are in italics and reflect the views of neither Percona nor anyone else. Percona’s release of ‘32 is in the works as we have to add the features Percona provides such as enterprise features (data masking, connection pooling, RocksDB, and much more).

The following items caught my eye as either interesting or ‘I need to add this to my to-do list’.

No more leading dollar signs for table names

> CREATE TABLE $Dollar (i int);
Query OK, 0 rows affected, 1 warning (0.0192 sec)
Warning (code 1681): '$ as the first character of an unquoted identifier' is …
[Read more]
COUNT(*) vs COUNT(col) in MySQL

Looking at how people are using COUNT(*) and COUNT(col), it looks like most of them think they are synonyms and just use what they happen to like, while there is a substantial difference in performance and even query results. Also, we find a difference in execution on InnoDB and MyISAM engines.

NOTE: All tests were applied for MySQL version 8.0.30, and in the background, I ran every query three to five times to make sure that all of them were fully cached in the buffer pool (for InnoDB) or by the filesystem (for MyISAM).

Count function for Innodb engine:

Let’s have look at the following series of examples for InnoDB engine:

CREATE TABLE count_innodb (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  val_with_nulls int(11) default NULL,
  val_no_null int(10) unsigned NOT NULL,
  PRIMARY KEY idx (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

(mysql) > select count(*) from count_innodb; …
[Read more]
Quick Data Archival in MySQL Using Partitions

Space constraint has been an endless and painstaking process for many of us, especially in systems that have a high number of transactions and data growth exceeding hundreds of GBs in a matter of days. In this blog, I will share a solution to remove this space and remove rows from tables in a few seconds regardless of the size of a table without causing any additional load on the database using table partitions.

The first approach that comes to anyone’s mind for deleting the row is using a DELETE query in SQL. Suppose, one wants to delete rows from a table that are older than one year—the query for such operations would be like this:

DELETE FROM salaries WHERE from_date <DATE_SUB(NOW(),INTERVAL 1 YEAR);

The above query is pretty straightforward but there are a few caveats:

  • Server business will grow exponentially and could impact the usual traffic on the server.
  • To speed up the above …
[Read more]
Tale of a MySQL 8 Upgrade and Implications on Backup

Recently, we performed a database engine major version upgrade in one of our customers’ environments from MySQL 5.7.26 to 8.0.27. After this version upgrade, we experienced issues with backups and replication for one of the nodes.

In this article, I will explain these issues in detail and recommend a way to take backups from a replication environment.

To begin with, we upgraded all the database nodes from 5.7.26 to 8.0.27 and as a recommended way we have a backup set-up from one of the replica nodes. Physical backups are being taken using Percona XtraBackup (PXB) so it does not lock the database during the backup.

With MySQL 5.7, a backup was taken using PXB 2.4. Due to the new data dictionaries, redo log and undo log in MySQL 8.0, we also upgraded PXB to 8.0.27 to avoid …

[Read more]
Duplicate, Redundant, and Invisible Indexes

MySQL index is a data structure used to optimize the performance of database queries at the expense of additional writes and storage space to keep the index data structure up to date. It is used to quickly locate data without having to search every row in a table. Indexes can be created using one or more columns of a table, and each index is given a name. Indexes are especially useful for queries that filter results based on columns with a high number of distinct values.

Indexes are useful for our queries, but duplicate, redundant, and unused indexes reduce performance by confusing the optimizer with query plans, requiring the storage engine to maintain, calculate, and update more index statistics, as well as requiring more disk space.

Since MySQL 8.0, indexes can be marked as invisible. In this blog, I will detail how to detect duplicate and underused indexes as well as the new feature of invisible indexes and how it can help …

[Read more]
Deep Dive into MySQL’s Performance Schema

Recently I was working with a customer wherein our focus was to carry out a performance audit of their multiple MySQL database nodes. We started looking into the stats of the performance schema. While working, the customer raised two interesting questions: how can he make complete use of the performance schema, and how can he find what he requires? I realized that it is important to understand the insights of the performance schema and how we can make effective use of it. This blog should make it easier to understand for everyone.

The performance schema is an engine in MySQL which can easily be checked whether enabled or not using SHOW ENGINES. It is entirely built upon various sets of instruments (also can be called event names) each serving different purposes.

Instruments are the main part of the performance schema. It is useful when I want to investigate a problem and its root causes. Some of the examples are …

[Read more]
Showing entries 1 to 10 of 311
10 Older Entries »