Jul 23, 2025 |
Insight for DBAs, MongoDB, MySQL, Open Source, PostgreSQL
Index maintenance can be a real challenge for anyone managing databases, and what makes it even trickier is that open source databases each handle it differently. In this post, we’ll take a closer look at how those differences show up in practice, and what they mean for you. When rows are added, updated, or deleted […]
Jan 02, 2024 |
Insight for DBAs, MySQL
TL&DR: Depending on the workload, the thread pool plugin can cause serious performance drops. This post was motivated by two recent cases I’ve worked with. Two setups running in cluster mode: one in MariaDB+Galera and one with Percona XtraDB Cluster (Percona Server+Galera). In both cases, the clusters had the thread pool plugin enabled and were […]
Aug 04, 2023 |
Monitoring, MySQL, Percona Software
Percona has bundled together some great open source pieces of software to create Percona Monitoring and Management (PMM), a very flexible and powerful monitoring solution. The goal of this post is not to introduce you to the existing PMM dashboards but to show you how to create your own application-centric custom PMM dashboards. If you […]
Feb 15, 2023 |
Insight for DBAs, MySQL, Storage Engine
Bloom filters are an essential component of an LSM-based database engine like MyRocks. This post will illustrate through a simple example how bloom filters work in MyRocks. Why? With MyRocks/RocksDB, data is stored in a set of large SST files. When MyRocks needs to find the value associated with a given key, it uses a […]
Dec 23, 2022 |
Cloud, Insight for DBAs, MySQL, Storage Engine
I wrote this post on MyRocks because I believe it is the most interesting new MySQL storage engine to have appeared over the last few years. Although MyRocks is very efficient for writes, I chose a more generic workload that will provide a different MyRocks use case. The use case is the TPC-C benchmark but […]
Aug 30, 2022 |
Insight for DBAs, MySQL
Recently I have been working with a few customers with multiple terabytes of transactional data on their MySQL clusters. These very large datasets are not really needed for their daily operations but they are very convenient because they allow them to query historical data easily. However the convenience comes at a high price, you pay […]
Jul 13, 2022 |
Insight for DBAs, MySQL
As a principal architect at Percona, one of my duties is to tune MySQL database servers for our customers. The tuning effort looks at every aspect of the database service like the operating system, the MySQL configuration, the schema, the queries, etc. We have well-defined processes to tune the operating system and the MySQL configuration. […]
Mar 24, 2022 |
Insight for DBAs, MySQL
For a very long part of its history, MySQL replication has been limited in terms of performance. Because there was no way of knowing if transactions or updates were independent, the updates had to be executed on a replica following the exact same sequence of operations as on the primary server. The only way to […]
Jan 04, 2022 |
Benchmarks, MySQL
Following my post MySQL/ZFS Performance Update, a few people have suggested I should take a look at BTRFS (“butter-FS”, “b-tree FS”) with MySQL. BTRFS is a filesystem with an architecture and a set of features that are similar to ZFS and with a GPL license. It is a copy-on-write (CoW) filesystem supporting snapshots, RAID, and […]
Sep 13, 2021 |
Cloud, Insight for DBAs, MySQL
Here’s a second post focusing on the performance of MySQL on ZFS in cloud environments. In the first post, MySQL/ZFS Performance Update, we compared the performances of ZFS and ext4. This time we’ll look at the benefits of using ephemeral storage devices. These devices, called ephemeral in AWS, local in Google cloud, and temporary in […]
Jul 09, 2021 |
Benchmarks, Hardware and Storage, Insight for DBAs, MySQL
As some of you likely know, I have a favorable view of ZFS and especially of MySQL on ZFS. As I published a few years ago, the argument for ZFS was less about performance than its useful features like data compression and snapshots. At the time, ZFS was significantly slower than xfs and ext4 except […]
May 27, 2021 |
Insight for DBAs, MySQL, Storage Engine
Recently we had a debate in the consulting team about how InnoDB handles TEXT/BLOB columns. More specifically, the argument was around the Barracuda file format with dynamic rows. In the InnoDB official documentation, you can find this extract: When a table is created with ROW_FORMAT=DYNAMIC, InnoDB can store long variable-length column values (for VARCHAR, VARBINARY, […]
May 04, 2021 |
Insight for DBAs, MySQL, Storage Engine
There is a common pattern in life, you often discover or understand things by accident. Many scientific discoveries fit such a description. In our database world, I was looking to see how BLOB/TEXT columns are allocated using overlay pages and I stumbled upon something interesting and unexpected. Let me present to you my findings, along […]
Oct 27, 2020 |
Cloud, Insight for DBAs, MySQL
If you often deploy services in the cloud, you certainly, at least once, forgot to stop a test instance. I am like you and I forgot my share of these. Another mistake I do once in a while is to provision a bigger instance than needed, just in case, and forget to downsize it. While […]
Aug 04, 2020 |
MariaDB, MySQL, Storage Engine
This post has been written in collaboration with Nicolas Modalvsky of e-planning. Nicolas and I recently worked together on a tuning engagement involving MyRocks on MariaDB. While it is easy to find online articles and posts about InnoDB performance, finding information about MyRocks tuning is more difficult. Both storage engines are well documented but what […]
May 14, 2020 |
Insight for DBAs, MySQL
In this post, the third in a series explaining the internals of InnoDB flushing, we’ll focus on tuning. (Others in the series can be seen at InnoDB Flushing in Action for Percona Server for MySQL and Give Love to Your SSDs – Reduce innodb_io_capacity_max!) Understanding the tuning process is very important since we don’t want to […]
Jan 22, 2020 |
Insight for DBAs, MySQL, Percona Software, Storage Engine
As the second part of the earlier post Give Love to Your SSDs – Reduce innodb_io_capacity_max! we wanted to put together some concepts on how InnoDB flushing works in recent Percona Server for MySQL versions (8.0.x prior to 8.0.19, or 5.7.x). It is important to understand this aspect of InnoDB in order to tune it […]
Dec 18, 2019 |
Insight for DBAs, MySQL, Storage Engine
The innodb_io_capacity and innodb_io_capacity_max are often misunderstood InnoDB parameters. As consultants, we see, at least every month, people setting this variable based on the top IO write specifications of their storage. Is this a correct choice? Is it an optimal value for performance? What about the SSD/Flash wear leveling? Innodb_io_capacity 101 Let’s begin with what […]
Nov 22, 2019 |
Insight for DBAs, Insight for Developers, MySQL
If you do a quick web search about UUIDs and MySQL, you’ll get a fair number of results. Here are just a few examples: Storing UUID and Generated Columns Storing UUID Values in MySQL Illustrating Primary Key models in InnoDB and their impact on disk usage MySQL UUID Smackdown: UUID vs. INT for Primary Key […]
Update on fsync Performance
Sep 19, 2019 |
Hardware and Storage
Last year, I wrote a post focused on the performance of the 3D_XPoint technology and sold under the Optane brand. Those devices are outperforming regular flash devices and have higher endurance. In the context of this post, I found they are also very good at handling the fsync call, something many flash devices are not […]