Featured
Percona Products
Percona Services
Use Cases
Carlos TutteOn MySQL and Percona Server for MySQL, there is a schema called information_schema (I_S) which provides information about database tables, views, indexes, and more. A lot of useful information can be retrieved from this schema, for example, table metadata and foreign key relations, but trying to query I_S can induce performance degradation if your server […]
The most common issue when using row-based replication (RBR) is replication lag due to the lack of Primary keys. The problem is that any replicated DML will do a full table scan for each modified row on the replica. This bug report explains it more in-depth: https://bugs.mysql.com/bug.php?id=53375 For example, if a delete is executed on […]
When doing migrations or failovers in MySQL, there is usually a need to do a topology change and repoint replica servers to obtain replication data from a different server. For example, given servers {A, B, and C} and the following topology: If you need to repoint C to be a replica of B, i.e: You […]
The Problem I recently worked on a customer engagement where the customer needed to archive a high amount of rows from different tables into another server (in this example for simplicity I am just archiving the results into a file). As explained in this other blog post, “Want to archive tables? Use Percona Toolkit’s pt-archiver“,you […]
Although how range_optimizer_max_mem_size operates is explained in the official doc, it’s not a well-known variable and surely not a variable you need to modify under most circumstances. But from time to time we get some performance tickets related to this. What problem does this variable cause if it is not properly sized? Let’s find out […]
Changing the default value (3) of log_error_verbosity in MySQL/Percona Server for MySQL 5.7 can have a hidden unintended effect! What does log_error_verbosity do exactly? As per the documentation: “The log_error_verbosity system variable specifies the verbosity for handling events intended for the error log.“ Basically a value of 1 logs only [Errors]; 2 is 1)+[Warnings]; and […]
Disclaimer: the following script only works for Percona Server for MySQL 5.7, and relies on enabling performance schema (PS) instrumentation which can add overhead on high concurrent systems, and is not intended for continuous production usage as it’s a POC (proof of concept). Introduction In Percona Support, we frequently receive tickets related to deadlocks and […]
Disclaimer: This blog post is about migrating Percona Monitoring and Management 2 (PMM) data between PMM2 versions, and not for migrating data from PMM1 to PMM2. Restoring data from PMM1 to PMM2 is NOT supported since there were many architectural changes. I recently worked on a customer case where he was not using a […]
In systems nowadays, improving security is a must! One of the weakest links in the security system is the user password from where an attacker can enter. In order to improve password strength and security, MySQL provides a plugin called “Validation plugin” which can be configured to enforce a set of rules for passwords. […]
Disclaimer: The following blog post does not try to provide a solution for split-brain situations, and the example provided is for demonstrative purposes only. Inconsistencies resulting from a split-brain scenario might possibly be more complex than the one shown, so do not use the following example as a complete guide. What is Split-Brain? A split-brain […]
One of our support customers approached us with the following problem the other day:
|
1 2 3 4 5 6 7 |
mysql> CREATE TABLE child_table ( `id` int unsigned auto_increment, `column1` varchar(64) NOT NULL, parent_id int unsigned NOT NULL, PRIMARY KEY (`id`), CONSTRAINT FOREIGN KEY (parent_id) REFERENCES parent_table (id)); ERROR 1215 (HY000): Cannot add foreign key constraint |
They could not create a table with an FK relation! So, of course, we asked to see the parent table definition, which was:
|
1 2 3 4 5 6 7 |
CREATE TABLE `parent_table` ( `id` int unsigned auto_increment, `column1` varchar(64) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB PARTITION BY HASH (id) PARTITIONS 4; |
The parent table is partitioned! This immediately explained the problem; partitioned tables can not be part […]