I recently troubleshoot an interesting MySQL crash, and I think it is worth sharing (with the related bugs). MySQL crashed when the disk was full, you can see the free disk graph below. The Y-axis is in the tens of GiB scale and the X-axis is in the hour scale. Can you guess what happened ?
As I wrote in a LinkedIn post, I am working on a blog post related to binary logging of big transactions. I thought I would split this post in two, so here is the first part where I cover the recent binary logging optimization in MariaDB and its unspoken advantage.
While doing benchmarks on 5.7 and 8.0, I came across a performance regression in MySQL 8.0 over 5.7 and opened a bug (Bug #111353 : 3x Performance Regression from 5.7 to 8.0 on ALTER TABLE FORCE). There has been recent activity on this bug, showing an easy workaround. This, even if it is known since 16 July 2024, has not been talked about much, so this deserves a blog post.
I just submitted a MySQL Contribution and I would like to gather feedback about it. Depending on the received feedback, I might submit an updated contribution. The contribution is Counters for Slow InnoDB Sync Reads, and its goal is to make MySQL easier to operate on “complex” IO subsystems (like network drives in the cloud).
The bug report / feature request Bug #117740 : Please consider adding Slow IO Counters contains all the details about the contribution and the problem this is solving, including what is a sync read. If you prefer looking at a PR over a patch file, you can check the Contrib / RFC Fake PR : Bug #117740 - Counters for Slow InnoDB Sync Reads (and if you want to know what is a Fake PR, it is also detailed in there). If you have thoughts or questions about this, feel free to comment below or in the Contrib Fake PR.
Thanks in advance for any feedback you might have : this will allow me to make the contribution and MySQL even better !
In the last weeks / months, I have been working on understanding / improving MySQL startup with many tables. I already wrote five posts on the subject, they are listed below. In this post, I use the knowledge we gained in the previous two posts to show the interest of tuning InnoDB Tablespace Duplicate Check Threads, making startup 30% in one case (2:28 vs. 3:33) and 5% in another (5:33 vs. 5:53).
I am writing this quick post to share what I think is a problematic new behavior of Offline Mode in MySQL 9. Basically, the new default behavior in MySQL 9 is to write the username of the user which set offline_mode to ON. I think this behavior has not been considered from a security point of view because it leaks a root username in the error message presented to the users.
In the last weeks / months, I have been working on understanding / improving MySQL startup with many tables. I already wrote four posts on the subject, they are listed below. In this post, I use the system analysis of the previous post to revisit the light optimization on EBS volumes. With this analysis, I am able to determine why the previous tests did not show improvements, and I am able to provide an example of a faster startup.
This post is a little long, please bear with me as after the intro, you can skip to the essential.
In the last weeks / months, I have been working on understanding / improving MySQL startup with many tables. I already wrote three posts on the subject, they are listed below. In this post, I focus on describing the startup phase InnoDB Tablespace Duplicate Check (Duplicate Check for short). This allows to better understand the optimization I already submitted, and find another optimization that could be leveraged.
In the last weeks / months, I have been working on understanding / improving MySQL startup with many tables. I already wrote two posts on the subject, the links are below. So far, I did not share what brought my attention to this, and it is the subject of this post. Also, and because it is related, I come back to the optimization / contribution I already made on the subject, looking at it with new information from this post.
In the last weeks / months, I have been working on understanding / improving MySQL startup with many tables. I already wrote a post about my work (Faster MySQL Startup with Many Tables). In that post, I link to a bug report (Bug #115988 : Too Much Disk Read on Startup, penalizing deployments with many tables). In that bug report, I write, without much details, that the quickest way to create tables in 8.0+ is by reducing durability and disabling redo logging. We will explore this statement in this post. The TLDR is that I was very impressed by the speedup introduced by disabling Redo Logging, and I think there might be other situations where this is useful.
When running tests, it is sometimes useful to slow down IOs to clearly show the impact of reading from the disk. Modern laptops and servers usually have fast local SSDs, and most cloud vendors provide, in addition to fast local disks, relatively fast network block devices (EBS for AWS and Persistent Disk for GCP). Even when using magnetic network block devices, IOs are not guaranteed to be slow because they might hit the cache of the storage appliance (EBS volumes sometime have sub-millisecond latency). This blog post describes the solution I am using for simulating high latency IOs on Linux. TL&DR: device-mapper’s delay target.
Yes, you read this correctly: because the MySQL client is insecure and allows running arbitrary commands, and because mysqldump blindly trusts the server it is dumping from, a hostile MySQL Server on which mysqldump is executed could trigger arbitrary command execution (also known as a remote code execution). This post raises awareness on this vulnerability and shows how a secure MySQL client makes things harder to exploit.
A month ago, I announced that I am working on a MySQL Contribution to add database / schema in the slow query log file (bug report, LinkedIn, Twitter, Facebook, and MySQL Community Slack). My contribution is ready, and before submitting it, I would like feedback, so I am writing this post as a Request for Comments (RFC). If you are in a hurry, you can go directly to the RFC Fake PR. But what is a Fake PR ? It is part of my way of working on MySQL Contributions, which I describe below, and on which I would also welcome feedback. Something else covered in this post are the design choices made for this contribution, and again, I would welcome your feedback on them (I can make adjustments before submitting my contribution). In this post, I also explain why having the database / schema in the slow query log file is important.
Yes, this is a catchy title, but it is true, and it got you reading this post :-). Another title could have been “Please load this mysql-dump: what could go wrong ?”. As you guessed, loading a dump is not a risk-free operation. In this post, I explain how the insecure MySQL client makes this operation risky and how to protect against it.
And if you think this post is not worth reading because you do not load dumps, consider the similarity with running any untrusted SQL scripts. You might be doing this more often than you think (have you ever had to run a script to prepare MySQL for an upgrade of WordPress, Grafana, or any other software: these are untrusted SQL scripts).
Not very long ago (well, maybe a little longer, this post is in draft for more than a year), in the spawn of less than 5 days, I suggested many colleagues to reproduce a problem they had with MySQL in a "more simple environment". Such more simple environment can be created with dbdeployer. dbdeployer is a tool to create "MySQL Sandboxes" on a Mac (laptop or desktop) or on Linux (vm, laptop or desktop). It is relatively simple to use, but if you do not know what to do, getting things set up the first time can be challenging. The goal of this post is to ease this first setup. In this tutorial, I describe how to install dbdeployer on a Mac and how to create your first sandboxes. I might do a Linux tutorial in the future.
A few days ago, Oracle released three new MySQL GA versions: 8.2.0, 8.0.35 and 5.7.44. I skimmed the release notes (8.2.0, 8.0.35 and 5.7.44), and I am not impressed. I guess that I would be even less impressed / more disappointed if I had checked in greater detail, and if I had reviewed the 8.1.0, 8.0.34 and 5.7.43 release notes. The subject of my disappointment is Oracle not fixing bugs in ALL of the LTS releases, sometimes only fixing them in the latest Innovation Release. This post summarizes my findings and thoughts.
When trying to understand queries in the slow log, an interesting metric to look at is rows examined. Also, when trying to understand CPU spikes on a MySQL instance, the InnoDB Metric dml_reads and the global status Innodb_rows_read are interesting to check. In a similar way, when trying to gather information about which queries are loading the system, SUM_ROWS_EXAMINED from the Performance Schema table events_statements_summary_by_digest is interesting to analyze. But rows examined might not mean what you think, and I give an example in this post.
TL&DR: in MySQL and from what I understand, rows examined means rows returned by the storage engine, even if the name would lead you to think that it is rows read. A consequence of this is that a query looking for non-existing data has 0 rows examined, and this is what I call in this post a rows examined blindspot.
I am finalizing my Percona Live talk MySQL and Vitess (and Kubernetes) at HubSpot. In this talk, I mentioned that I like that Percona is providing better MySQL with Percona Server. This comes with a little inconvenience though: with improvements, sometimes comes regression. This post is about such regression and a workaround I implemented some time ago (I should have shared it earlier). This regression is fixed in Percona Server 5.7.37-40, so this is mostly interesting for historical purposes, or if you are running a Percona Server version older than that.
I just booked my travel arrangements for Percona Live 2023. In case you missed it, one of the most important MySQL Conference of the year is happening in Denver from Monday May 22 to Wednesday 24. I will be there and I am giving a talk about how HubSpot operates Percona Server / MySQL with Vitess in Kubernetes. My colleague, Mali Akmanalp, is also speaking about the tools we provide developers at HubSpot to keep their query workloads efficient with as little involvement from the database team as possible. But this post is not about our talks, it is about running and mountain climbing.
In May 2020, I published a Planet for the MySQL Community Pluto Beta. I was satisfied with the result and was considering it done, but I did not invest the time to promote it to release (a non-beta site was running for a few months without being advertised). I finally came to it, and today I am happy to deprecate the beta and announce the release of Planet for the MySQL Community. In this post, I recall what led to the creation of this new blog aggregator, I list recent changes, and I mention where I would like things to go in the future. But before continuing to read this post, if you are using the beta, please update your bookmarks :
Also, if you are using Planet for the MySQL Community, could you follow Planet for the MySQL Community / @PlanetMysqlComT on Twitter. This allows me to know my users and to gauge the interest for this work.
In my previous post about InnoDB Stalls on Empty Free List, I used a test environment that might look a little artificial : a table larger than the InnoDB Buffer Pool but fitting in the Linux Page Cache. This configuration allows serving very quickly what MySQL thinks are IOs because these are hit in the filesystem cache. In this post, I explain why this environment is not totally artificial and can be a good representation of another more complex environment. I also explain how an Empty Free List backoff stall can occur when no IO is done. You have probably already guessed what this post is covering from the title, so let's dive in.
If, in Percona Server, you are observing tail latencies on queries that should be fast, this might be a side effect of Percona's improved InnoDB Empty Free List Algorithm. When using this algorithm (the default in 5.6 and 5.7 and optional configuration in 8.0), a query needing a free page while none are available waits until the LRU Manager Thread refills the free list. Because this thread is waking-up only at regular intervals, the query is blocked (which explains the latency) until the LRU Manager Thread is done sleeping and completes its work. These latencies / stalls might last up to one second in the worse case. All the details are in this post, starting with InnoDB Free Pages.
Sometimes, the MySQL Optimizer chooses a wrong plan, and a query that should execute in less than 0.1 second ends-up running for 12 minutes ! This is not a new problem: bugs about this can be traced back to 2014, and a blog post on the subject dates of 2015. But even if this is old news, because this problem recently came to my attention, it is a problem worth writing on.
I am currently working on a script to auto-enable parallel replication / multi-threaded replication (MTR) when there is replication lag. For testing this script, I need to trigger replication lag that would disappear after enabling MTR. I came-up with a simple solution for that, and I thought it could be useful to more people, so I am writing this blog post about it. Read-on for the details.
When Index Condition Pushdown is used, you cannot trust rows examined as reported by the slow query log (nor in some Performance Schema tables) and you cannot trust the global status Innodb_rows_read (nor the InnoDB Metrics dml_reads). These can lead to an incorrect query complexity analysis, to a mistaken estimation of the total load of a MySQL server, and to a lot of wasted time. As we go through the details in this post, we will understand why a SELECT * is sometimes faster than only selecting the columns from an index (we would normally expect the opposite) and we will visit inner workings of the MySQL Storage Engine Architecture. But first, let's start by exposing the problem.
A year ago, I blogged about An Unprivileged User can crash your MySQL Server. At the time, I explained how to protect yourself against this problem. A few weeks ago, I revisited this vulnerability in a follow-up post in which I explained the fix, claimed that the MySQL 5.7 default configuration for Group Replication is still problematic, and explained a tuning to avoid the vulnerability. In this last post in the series, I explain how to exploit this vulnerability to crash older version of MySQL (or untuned Group Replication in 5.7), but this needs a lot of determination.
A year ago, I blogged about An Unprivileged User can Crash your MySQL Server. At the time, I presented how to protect yourself against this problem without explaining how to generate a crash. In this post, I am revisiting this vulnerability, not giving the exploit yet, but presenting the fix. Also, because the default configuration of Group Replication in 5.7 is still vulnerable (it is not in 8.0), I show the adjustment to make to avoid problems.
A few weeks ago and in MySQL 5.7, I had an ALTER TABLE that failed with a duplicate entry error. This is old news as it is happening since MySQL 5.6, but I only saw this recently because I am normally using online schema change from the Percona Toolkit (pt-osc) or GitHub's online schema migration (gh-ost). I do not like that and I am disappointed this has not been improved, so this post is partially a rant, but not only, because I explain why ALTER TABLE is failing and I give solutions to avoid the failure.
At my FOSDEM talk earlier this year, I gave a trick for fixing a crashed GTID replica. I never blogged about this, so now is a good time. What is pushing me to write on this today is my talk at MinervaDB Athena 2020 this Friday. At this conference, I will present more details about MySQL replication crash safety. So you know what to do if you want to learn more about this subject. Let's now talk about voodoo.
Yes, your read the title correctly: an unprivileged user can crash your MySQL Server. This applies for the default configuration of MySQL 8.0.21 (and it is probably the case for all MySQL 8 GA versions). Depending on your configuration, it might also be the case for MySQL 5.7. This needs malicious intent and a lot of determination, so no need to panic as this will not happen by accident. I am not sharing the exploit in this post (I might do it later), but I am telling you how to avoid the problem.
