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.
J-F Gagné's MySQL Blog
Monday, October 28, 2024
Tuesday, September 3, 2024
Faster MySQL Startup with Many Tables (1M+)
Friday, June 28, 2024
Trick to Simulate Higher Latency IOs on Linux
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.
Wednesday, June 19, 2024
Trusting mysqldump and Insecure MySQL Client Lead to Remote Code Execution
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.
Monday, June 17, 2024
RFC: Database / Schema in the Slow Query Log File
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.
Wednesday, April 10, 2024
17 Years of Insecure MySQL Client !
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).
Thursday, April 4, 2024
dbdeployer Tutorial on Mac
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.
Wednesday, November 22, 2023
Thoughts on the October 2023 MySQL Releases
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.
Monday, June 26, 2023
Rows Examined Blindspot when Looking for non-Existing Data
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.
Monday, May 22, 2023
Avoiding a STOP SLAVE Crash with MTR in Percona Server older than 5.7.37-40
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.
Thursday, March 30, 2023
Running and Mountain Climbing at Percona Live Denver
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.
Monday, January 23, 2023
Planet for the MySQL Community Graduating from Beta
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.
Monday, December 12, 2022
Free Page Consumption by InnoDB Table Compression (Percona backoff Algo. part #2)
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.
Wednesday, November 30, 2022
Tail Latencies in Percona Server because of InnoDB Stalls on Empty Free List
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.
Tuesday, November 15, 2022
Bad Optimizer Plan on Queries Combining WHERE, ORDER BY and LIMIT
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.
Tuesday, May 31, 2022
Triggering Replication Lag for Testing a Script
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.
Tuesday, March 8, 2022
Rows Examined not Trustworthy because of Index Condition Pushdown
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.
Wednesday, January 19, 2022
Crashing MySQL with Malicious Intent and a lot of Determination
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.
Monday, December 13, 2021
Trick to Simulate a Linux Server with less RAM
Tuesday, November 30, 2021
Follow-up on an Unprivileged User can Crash your MySQL Server
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.
Friday, November 12, 2021
Duplicate Entry in ALTER TABLE (and in OPTIMIZE TABLE)
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.
Saturday, December 5, 2020
Fixing low durability GTID replica with Voodoo
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.
Sunday, October 18, 2020
An Unprivileged User can crash your MySQL Server
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.
Sunday, June 7, 2020
Update on Planet for the MySQL Community (June 7, 2020)
Thursday, May 14, 2020
Planet [for the] MySQL Community: Pluto Beta
Tuesday, May 12, 2020
Planet MySQL Community: Requirements RFC
Friday, April 24, 2020
Planning for the AFTER Planet MySQL (bis)
As written in a previous post, the state of Planet MySQL is unhealthy ! I am still aggregated there for now as, before leaving what was the best news-feed for the MySQL Community, we need a replacement. This post aims at starting a discussion on this replacement.
Planning for the AFTER Planet MySQL
State of Planet MySQL: unhealthy !
I have been complaining for some time about Planet MySQL filtering content that I think it should not. Shlomi made a courageous decision on this and explained it in his recent post (Pulling his blog out of Planet MySQL aggregator, over community concerns). I am thinking of doing the same. What are your thoughts on this ? Please share them in the comments below (or on Shlomi's blog).
Monday, March 30, 2020
JFG MySQL Advice during COVID-19
We are all impacted by COVID-19. On my side, this virus disrupted my travel, professional and personal plans for the upcoming months. To overcome these disruptions, and following the idea of someone on MySQL Community Slack, I am experimenting with giving MySQL advice on a 30-minute basis. You can book a slot using this link:
Monday, January 27, 2020
A Legacy Behavior of MySQL Corrupting Restored Backups (replicate-same-server-id = OFF)
Thursday, January 9, 2020
Puzzled by MySQL Replication (War Story)
Monday, October 7, 2019
The dark side of super_read_only
Tuesday, July 16, 2019
MySQL Master Replication Crash Safety Part #5a: making things faster without reducing durability - using better hardware
MySQL Master Replication Crash Safety Part #5: faster without reducing durability (under the hood)
Tuesday, July 9, 2019
MySQL Master Replication Crash Safety Part #4: benchmarks of high and low durability
MySQL Master Replication Crash Safety part #4: benchmarks (under the hood)
Tuesday, June 25, 2019
Getting past 2400 threads with sysbench and dbdeployer for benchmarking MySQL 5.7.26
Wednesday, May 8, 2019
Not enforcing SSL on CloudSQL, really !
Wednesday, May 1, 2019
Care with using the max_connections beta database flag on CloudSQL...
Tuesday, February 26, 2019
MySQL Master High Availability and Failover: more thoughts
This is a great series, and I recommend its reading for everybody implementing master failover, with or without Orchestrator, even if you are not fully automating the process yet. Taking a step back, I realized that service discovery is only one of the five parts of a full MySQL Master Failover Strategy; this post is about these five parts. In some follow-up posts, I might analyze some deployments using the framework presented in this post.
Tuesday, February 12, 2019
MySQL Master Replication Crash Safety Part #3: GTID
Tuesday, January 8, 2019
Care when changing MASTER_DELAY with CHANGE MASTER TO (delayed replication)
TL&DR: be careful when using CHANGE MASTER TO MASTER_DELAY = N: it might wipe your relay logs !
Thursday, November 15, 2018
MySQL Master Replication Crash Safety Part #2: lagging slaves
Tuesday, November 13, 2018
How to install Percona Server 5.7 on Debian/Ubuntu without a root password prompt
Tuesday, October 30, 2018
On the consequences of sync_binlog != 1 (part #1)
Tuesday, October 23, 2018
Arg: relay_log_space_limit is (still) not dynamic !
Tuesday, September 11, 2018
Unforeseen use case of my GTID work: replicating from AWS Aurora to Google CloudSQL
The surprising part for me is that it uses my 4-year-old work for online migration to GTID with MySQL 5.6. This is a completely unforeseen use case of my work as I never thought that my hack would be useful after Oracle include an online migration path to GTID in MySQL 5.7 (Percona did something similar for MySQL 5.6).