Recently I was dealing with an unexpected issue raised by our Support customer, in which data became inconsistent after a schema change was applied.

After some investigation, it turned out that affected tables had a special word in the comments of some columns, which triggered an already known (and fixed) issue with the TableParser.pm library of Percona Toolkit.  The problem is that the customer was using an outdated Toolkit version, where pt-online-schema-change was using that buggy parser.

This bug applies only to Percona Toolkit versions up to 3.0.10, so if you have already 3.0.11 or newer installed, you can skip the rest of this post as these are no longer affected.

I am writing this post to warn every user of pt-online-schema-change who has not upgraded the toolkit, as …

Visibility is a blessing, and with databases, visibility is a must. That’s true not only for metrics but for the queries themselves. Having info on all the stats around query execution is priceless, and Percona Monitoring and Management (PMM) offers that in the form of the Query Analytics dashboard (QAN).

But where to start? QAN helps you with that by calculating the query profile. What is the profile? It’s a rank of queries, ordered by Load, so it is easy to spot the heaviest queries hitting your database. The Load is defined as the “Average Active Queries” but can also be defined as a mix of Query Execution Time Plus Query count. In other words, all the time the query was alive and kicking.

The Profile in PMM 2.10.0 looks like this:

The purpose of this profile is to facilitate the task of finding the …

In Use MySQL BLOB column with PHP to store .pdf file, I covered an example of how to store a .pdf file in the actual database table using the MySQL BLOB datatype and PHP. Now that we have .pdf’s stored in the database, how do we display them in the browser? This blog post answers that exact question. Continue reading to see a working example using PHP…

Photo by Ben on Unsplash

Self-Promotion:

If you …

Schema changes are the big challenges in Galera replication. So, it is recommended to understand the schema changes operation for everyone who uses the Percona XtraDB Cluster (PXB)/Galera clusters. In this blog, I am going to explain the operation and impact of the various schema changes methods used in the PXB/Galera cluster.

• Schema changes with “wsrep_OSU_method = TOI”
• Schema changes with “wsrep_OSU_method = RSU”
• Schema changes with “ONLINE ALGORITHMS”
• Schema changes with “pt-osc”
• Schema changes with “gh-ost”

For testing:

• I have configured the 3-node Percona Xtradb Cluster (8.0.19).
• Executing read/write load using the sysbench.

mysql> select @@wsrep_cluster_address\G
*************************** 1. row …

MySQL 8.0 Performance Benchmarking on Amazon EC2 The scope of performance benchmarking

The core objective of this benchmarking exercise is to measure MySQL 8.0 performance, This include INSERTs , SELECTs and complex transaction processing (both INSERTs and SELECTs) without any tuning of MySQL 8 instance’s my.cnf. We agree tuning my.cnf will greatly improve performance but in this activity we wanted to benchmark MySQL 8 transaction processing capabilities and technically in MinervaDB we measure performance by Response Time and believe you can build high performance MySQL applications by writing optimal SQL. We have used Sysbench (https://github.com/MinervaDB/MinervaDB-Sysbench release 1.0.20) for this benchmarking activity. This is not a paid / sponsored benchmarking effort by any of the software or hardware vendors, We will remain …

Hello everyone, in this little post we will review a new feature in MySQL 8.

What is “CHECK Constraint”?

This is a new feature to specify a condition to check the value before INSERT or UPDATE into a row. The constraint could return an error if the result of a search condition is FALSE for any row of the table (but not if the result is UNKNOWN or TRUE).

This feature starts working on MySQL 8.0.16, and in previous versions, we could create it, but it doesn’t work, meaning the syntax is supported but it is not working,

There are some rules to keep in mind…

– AUTO_INCREMENT columns are not permitted
– Refer to another column in another table is not permitted
– Stored functions and user-defined functions are not permitted (you can not call a function or any user-defined functions)
– Stored procedure and function parameters are not permitted (you cannot call …

Maybe one of the more “obscure” operations when dealing with replica lag, or, in general, when one needs to control writes to the database, is the Rate limit. It’s also lately one of the most popular conversations around the community.

But what is it? In plain words: holding up queries for a while, giving air to the replicas to breath and catch up. Something similar to the Galera’s Flow Control mechanism, although flow control, when it kicks in, stops all the writes while the nodes catch up. With a throttle no write is stopped, just delayed.

There are several ways to do this. A popular tool is Freno but this is also something that can be achieved with ProxySQL. Let’s see how.

Delay

ProxySQL has a variable called …

In this post, we will discuss what to do when you add more memory to your instance. Adding memory to a server where MySQL is running is common practice when scaling resources.

First, Some Context

Scaling resources is just adding more resources to your environment, and this can be split in two main ways: vertical scaling and horizontal scaling.

Vertical scaling is increasing hardware capacity for a given instance, thus having a more powerful server, while horizontal scaling is adding more servers, a pretty standard approach for load balancing and sharding.

As traffic grows, working datasets are getting bigger, and thus we start to suffer because the data that doesn’t fit into memory has to be retrieved from disk. This is a costly operation, even with modern NVME drives, so at some point, we will need to deal with either of the scaling solutions we mentioned.

In this case, we will discuss adding more …

Like always I am sharing new things I learn here on my blog. I was recently working on a requirement for a LAMP stack web application reporting dashboard in which I needed to store – and eventually – retrieve a .pdf file. I have read in several places (this fantastic book is a great resource) that a viable option is storing images or documents (.pdf in this case) in the actual database table as opposed to on the server file system. MySQL has the BLOB datatype that can be used to store files such as .pdf, .jpg, .txt, and the like. In this blog post, I cover how I accomplished uploading and storing the actual .pdf file in a BLOB column in MySQL using PHP. Any corrections, tips, pointers, and recommendations for best practices are always welcome. We all learn as we go!!!

Photo by …

Today there is NEW ERA for Galera Cluster monitoring and management and as we release Galera Manager 1.0 into the wild for everyone to evaluate, test, and deploy their Galera Clusters within an Amazon Web Services (AWS) Elastic Compute Cloud (EC2) environment to achieve MySQL High Availability, Multi-Master MySQL on the cloud and Disaster Recovery, all from the comfort of a web-based graphical user interface (GUI).

What does Galera Manager do? Galera Manager is a deployment, management and monitoring solution for Galera Clusters. A user can easily create clusters, add and remove nodes, and create geo-distributed clusters across multiple AWS regions, all with the click of a few buttons in one’s web browser. Even more useful is the over 620 monitoring metrics available to monitor the health of your clusters. Being fully web-based, you can say goodbye to …