Featured
Percona Products
Percona Services
Use Cases
Przemysław MalkowskiIt may be surprising when a new InnoDB Cluster is set up, and despite not being in production yet and completely idle, it manifests a significant amount of writes visible in growing binary logs. This effect became much more spectacular after MySQL version 8.4. In this write-up, I will explain why it happens and how to address […]
What index will be used when you count all rows in a table? Well, the MySQL documentation provides a straightforward answer to this, quoting: InnoDB processes SELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is […]
An application down due to not being able to write into a table anymore due to a maximum allowed auto-increment value may be one of the worst nightmares of a DBA. Typical errors related to this problem in MySQL will look like this:
|
1 |
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'my_table.PRIMARY' |
or
|
1 |
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine |
While the solution could be easy and fairly quick […]
This post was originally published in July 2023 and was updated in October 2024. Upgrading to MySQL version 8.0 is a hot topic since version 5.7 reached EOL. MySQL 5.7 EOL was at the end of October 2023. If you feel unprepared for the upgrade, consider post-EOL support from Percona. But it would be the […]
Mysterious warning Recently, I was involved in an investigation whose goal was to find out the reason for a warning message like this:
|
1 |
[Warning] [MY-011825] [InnoDB] Cannot add field `c11` in table `db1`.`test` because after adding it, the row size is 8484 which is greater than maximum allowed size (8126) for a record on index leaf page. |
The message looks clear, isn’t it? Well, the problem was that this particular table had not been changed for years, and so no DDL (ALTER) query was involved here. Moreover, there […]
Some of our Percona Support customers report performance degradation after a major MySQL upgrade, and there can be many different reasons for this. These days, the most common major upgrade is from MySQL 5.7 (which recently reached EOL) to 8.0, and I am going to emphasize one important case that affects many database instances. Range […]
Replication has been the core functionality, allowing high availability in MySQL for decades already. However, you may still encounter replication errors that keep you awake at night. One of the most common and challenging to deal with starts with: “Got fatal error 1236 from source when reading data from binary log“. This blog post is […]
If you have enough experience with MySQL, it is very possible that you stumbled upon an unusually slow SELECT COUNT(*) FROM TABLE; query execution, at least occasionally. Recently, I had a chance to investigate some of these cases closer, and it stunned me what huge differences there can be depending on the circumstance given the […]
Historically, MySQL does not require explicit primary key defined on tables, and it’s like that by default till this day (MySQL version 8.3.0). Such a requirement is imposed through two replication methods, though: Group Replication and Percona XtraDB Cluster (PXC), where using tables without a primary key is not allowed by default. There are many […]
It is a known good practice to keep only necessary indexes to reduce the write performance and disk space overhead. This simple rule is mentioned briefly in the official MySQL Documentation: https://dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html However, in some cases, the overhead from adding a new index can be way above the expectations! Recently, I’ve been analyzing a customer […]
Will 2023 be called the year of Generative Artificial Intelligence (AI)? I don’t know, but certainly, the launching of ChatGPT at the end of 2022 sparked a huge hype explosion around that technology throughout this year. Interesting time indeed, we’re eyewitnesses to something that started changing our world. I played a bit with ChatGPT in […]
Sometimes, there is a need to update the table and index statistics manually using the ANALYZE TABLE command. Without going further into the reasons for such a need, I wanted to refresh this subject in terms of overhead related to running the command on production systems. However, the overhead discussed here is unrelated to the […]
Have you ever encountered queries delayed by flow control but found no lagged secondaries? This article shows a possible scenario of why this happens. Replica Sets provide high availability and redundancy to MongoDB clusters. There is always one primary node that can accept writes, but the replica set topologies vary depending on the use case […]
If you ever had to make a quick ad-hoc backup of your MongoDB databases, but there was not enough disk space on the local disk to do so, this blog post may provide some handy tips to save you from headaches. It is a common practice that before a backup can be stored in the […]
The binary logging format in MySQL has been ROW by default since MySQL 5.7, yet there are still many users sticking with STATEMENT or MIXED formats for various reasons. In some cases, there is just simple hesitation from changing something that has worked for years on legacy applications. But in others, there may be serious […]
Do you wonder if MySQL tells you the truth about writes to tables that have foreign key constraints? The situation is complex, and getting visibility on what is really happening can be a problem. I found this issue intriguing and decided to share and highlight some examples. Query Plan Let us take this example table: […]
Percona XtraDB Cluster (PXC) offers a great deal of flexibility when it comes to the state transfer (SST) options (used when a new node is automatically provisioned with data). For many environments, on-the-fly compression capability gives great benefits of saving network bandwidth during the process of sending sometimes terabytes of data. The usual choice for […]
If you ever had to deal with performance and/or disk space issues related to temporary tables, I bet you eventually found yourself puzzled. There are many possible scenarios depending on the type of temporary table, settings, and MySQL version used. We have observed a pretty long evolution in that matter due to a couple of […]
The Problem State Snapshot Transfer can be a very long and expensive process, depending on the size of your Percona XtraDB Cluster (PXC)/Galera cluster, as well as network and disk bandwidth. There are situations where it is needed though, like after long enough node separation, where the gcache on other members was too small to […]
Multi-writer replication has been a challenge in the MySQL ecosystem for years before truly dedicated solutions were introduced – first Galera (and so Percona XtradDB Cluster (PXC)) replication (around 2011), and then Group Replication (first GA in 2016). Now, with both multi-writer technologies available, do we still need traditional asynchronous replication, set up in active-active […]